业务需求
最近要在系统中加个统计功能, 要求是按指定日期范围里按天分组统计数据量, 并且要能够查看该时间段内每天的数据量.
解决思路
直接按数据表日期字段 group by 统计, 发现如果某天没数据, 该日期是不出现的, 这不太符合业务需求. 百度一番发现方案大致有两种: 一是新建日期列表, 把未来 10 年的日期放进去, 然后再跟统计表作连接查询; 二是用程序代码在 SQL 逻辑中 union 多个连续日期查询. 都比较繁琐.
参考 Oracle 的 "select level from dual connect by level <31" 的实现思路:
1, 先用一个查询把指定日期范围的日期列表搞出来
- 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 t_table1) t1
2, 业务统计查询也按上述日期查询给统计日期和数量设置别名
- SELECT
- FROM_UNIXTIME(m.sdate, '%Y-%m-%d') as date_str, count( * ) as date_count
- from t_table1 as m
- group by FROM_UNIXTIME(m.sdate, '%Y-%m-%d')
3, 把两个查询用左连接合起, 没数量的日期填 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 t_table1) tmp1 WHERE @cdate> '2018 - 12 - 01'
- ) t1
- LEFT JOIN(
- SELECT FROM_UNIXTIME(m.sdate, '%Y-%m-%d') as date_str, count(*) as date_total_count FROM t_table1 as m WHERE m.sdate between XXXX and XXXXX GROUP BY FROM_UNIXTIME(m.sdate, '%Y-%m-%d')
- ) t2
- on t1.date_str = t2.date_str
查询结果如下图所示:
来源: http://www.linuxidc.com/Linux/2019-07/159563.htm