需求:
统计用户在指定日期范围内的 日访问量 ,根据此数据画出走势图。
如果直接按照日期字段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'
业务统计查询也按上述日期查询给统计日期和数量设置别名
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")
把两个查询用左连接合起,没数量的日期填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
实际示例:
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
运行结果: