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?