Mysql calculates the number of visits per day

Mysql by the day statistics, no supplement 0 support for the original text! 😋

First put the time format, and then query by time group

SELECT
	DATE_FORMAT( created_at,'%Y-%m-%d') access_day,
	count( id ) num
FROM
	access_logs
GROUP BY
	access_day;
Copy the code

Let’s verify that

Used in the gorm

The above query can only query recorded data. If there is no data on a day, the following phenomenon will occur:

And what we want is continuity:

Online query some information, the general idea is to use a zero-hour table, generate the date you need, and then connect the table query, here I give my actual operation.

Select * from interval, date_sub, curdate; select * from interval, date_sub, curdate;

    SELECT curdate() as createdAt
    union all
    SELECT date_sub(curdate(), interval 1 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 2 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 3 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 4 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 5 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 6 day) as createdAt
Copy the code

Let’s see what happens

Then in another table, look up the data you need:

SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
Copy the code

Take a look at the results:

Finally, join the two tables to find the data you need:

select a.created_at as label,b.access_num as value
from(
    SELECT curdate() as created_at
    union all
    SELECT date_sub(curdate(), interval 1 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 2 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 3 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 4 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 5 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 6 day) as created_at
) a left join (
SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
) b on a.created_at = b.access_day order by a.created_at asc;
Copy the code

In this case, the ifNULL function is used to set null to 0

select a.created_at as label,IFNULL(b.access_num, 0) as value
from(
    SELECT curdate() as created_at
    union all
    SELECT date_sub(curdate(), interval 1 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 2 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 3 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 4 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 5 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 6 day) as created_at
) a left join (
SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
) b on a.created_at = b.access_day order by a.created_at asc;
Copy the code

At this point the mysql query is complete.

The following is the need to use in GORM, in order to better use experience, we must be able to specify any number of days, you need to cycle the number of days of THE SQL statement, the following gives my method.