mysql 按天统计访问量，没有的补0

原文我的博客：mysql 按天统计访问量，没有的补0支持原文吧！😋

思路： 先把时间格式一下，然后按时间分组查询

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

执行验证一下

gorm 中使用

上面的查询，只能查询出有记录的数据，如果某天没有数据，就会出现下面的现象：

access_day	num
2021-8-5	1
2021-8-7	2
2021-8-8	3

而我们想要的是连续的：

access_day	num
2021-8-5	1
2021-8-6	0
2021-8-7	2
2021-8-8	3

网上查询了些资料，大概的思路是需要利用一张零时表，生成你需要的日期，然后再连表查询，这里我给出我的实际操作。

比如：我要搜素近7天的访问记录 首先利用interval 、date_sub、curdate函数创建近7天的零时表：

    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

先看看效果

然后在另一张表，查出你需要的数据：

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

看下结果：

最后，连接两张表查出你需要的数据：

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;

这时会发现,没有记录的数据value返回的null，此时需要使用ifnull函数，将null设置为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;

此时 mysql查询就完成了。

下面是需要在gorm中使用，为了更好的使用体验，我们肯定是要能指定任意天数的，就需要循环拼接天数的sql语句，下面给出我的方法。