在我们统计按时间维度统计数据的时候,通常会有些时间是没有数据的,那么group by 的时候时间就是不连续的。对于这样的数据,如果用折线图显示的话,是不满足我们的需求的,所以我们需要自动补全数据,当天没有数据的时候,补0。
下段sql片段所展示的就是获取七天前的时间,sql中的表可以随便,只要数据库中存在都可以,但是建议用数据量小的表作为原表。
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS time,
0 AS count
FROM
( SELECT @cdate := date_add( CURDATE( ), INTERVAL + 1 DAY ) FROM sys_log_login ) tmp
WHERE
@cdate > DATE_SUB( CURDATE( ), INTERVAL 7 DAY )
结果如下图:
然后我们就可以用这个临时数据作为关联表,获取这些天的数据情况:
下面例子 从登录日志中查询每天访问数据的访问量:
SELECT
t1.time,
COALESCE ( t2.allData, 0 ) 'allData',
COALESCE ( t2.myData, 0 ) 'myData'
FROM
(
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS time,
0 AS count
FROM
( SELECT @cdate := date_add( CURDATE( ), INTERVAL + 1 DAY ) FROM sys_log_login ) tmp
WHERE
@cdate > DATE_SUB( CURDATE( ), INTERVAL 7 DAY )
) t1
LEFT JOIN (
SELECT
DATE_FORMAT( login_time, '%Y-%m-%d' ) 'time',
COUNT( 1 ) 'allData',
SUM( CASE WHEN username = 'admin' THEN 1 ELSE 0 END ) 'myData'
FROM
sys_log_login
WHERE
DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= DATE( login_time )
GROUP BY
DATE_FORMAT( login_time, '%Y-%m-%d' )
) t2 ON t1.time = t2.time
结果如图示:
当然可以参考我的开源项目spark
spark: https://gitee.com/dreamfeng/spark-platform