MySQL按日统计数据,时间范围内无数据补0
统计用户在指定日期范围内的 日访问量 ,根据此数据画出走势图。
如果直接按照日期字段group by 查询,若某天没有访问,则该日期数据是不出现的,将导致走势图不能很好地表明当前状况,因此无数据情况下补0,来达成需求。
解决思路:
-
先用一个查询把指定日期范围的日期列表搞出来
SELECT @cdate := date_add(@cdate, INTERVAL - 1 DAY) AS date_str, 0 AS date_count FROM ( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY) FROM 数据库随便某个有数据的表名 ) t1 WHERE @cdate > '2020-07-01' AND @cdate < '2020-07-31'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
-
业务统计查询也按上述日期查询给统计日期和数量设置别名
SELECT FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d") AS date_str, count(*) AS date_count FROM 业务数据表名 AS m GROUP BY FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d")
- 1
- 2
- 3
- 4
- 5
- 6
- 7
-
把两个查询用左连接合起,没数量的日期填0
SELECT t1.date_str,COALESCE (t2.date_total_count, 0) AS date_total_count FROM ( SELECT @cdate := date_add(@cdate, INTERVAL - 1 DAY) AS date_str FROM ( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY) FROM 业务数据表名 ) tmp1 WHERE @cdate > '2020-07-01' ) t1 LEFT JOIN ( SELECT FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d") AS date_str, count(*) AS date_total_count FROM 业务数据表名 AS m WHERE m.时间戳字段 > 'XXXX-XX-XX' GROUP BY FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d") ) t2 ON t1.date_str = t2.date_str
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
实际示例:
SELECT
t1.logintime, COALESCE (t2.count, 0) AS count
FROM
(
SELECT@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS logintime
FROM
(
SELECT
@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
FROM
tb_userloginhistory
) tmp1
WHERE
@cdate > '2020-07-01'
) t1
LEFT JOIN (
SELECT
FROM_UNIXTIME(logindatetime, "%Y-%m-%d") AS logintime, COUNT(1) AS count
FROM
tb_userloginhistory
WHERE
logindatetime > '2020-07-01'
GROUP BY
FROM_UNIXTIME(logindatetime, "%Y-%m-%d")
) t2
ON t1.logintime = t2.logintime
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
运行结果:
</div><div><div></div></div>
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-60ecaf1f42.css" rel="stylesheet">
</div>
</article>