How does MySQL find records for the last 7 days, the last 30 days, the last n days?

Source: Internet
Author: User

Viewed as an example: the original data is as follows:

Thinking Analysis: The data has, statistics on a day's views, all views, or a fixed period of browsing volume here we will not say more, we will, then how we are the last seven days of data statistics out?

First of all, the last 7 days, the most recent week, does not refer to the data in the final 7 days of the database, but the data in the database from today onwards to 7 days ahead. In this case, the time is the main Line (master table)

To count the amount of time the view corresponds to.

Select Date_format (mycreatedate, '%m-%d ') as Countkey,ifnull (COUNT (pv.createdate), 0) as CountValue from (
SELECT curdate () as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 1 day) as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 2 day) as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 3 day) as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 4 day) as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 5 day) as Mycreatedate
UNION ALL
SELECT date_sub (Curdate (), Interval 6 day) as Mycreatedate
) as Total
Left JOIN PageView PV on Date_format (pv.createdate, '%y-%m-%d ') =total.mycreatedate GROUP by Mycreatedate

First find out what the last 7 days is 7 days, then left join, there is data, we count that day of counting, no words complement 0, using the function: Ifnull (COUNT (pv.createdate), 0) to achieve the complement 0

The query results are as follows:

This will query the last 7 days of each day's views, of course, the application in the Browse Volume chart (echarts) has the following effect:

Here is the use of echarts to do the report, the use is also very simple, the official website has a lot of styles to choose, interested in the small partners can go to the official website to see: http://echarts.baidu.com/examples.html

How does MySQL find records for the last 7 days, the last 30 days, the last n days?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.