阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL实现按天分组统计,提供完整日期列表,无数据自动补0

366次阅读
没有评论

共计 1020 个字符,预计需要花费 3 分钟才能阅读完成。

业务需求
最近要在系统中加个统计功能,要求是按指定日期范围里按天分组统计数据量,并且要能够查看该时间段内每天的数据量。

解决思路
直接按数据表日期字段 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

查询结果如下图所示:

MySQL 实现按天分组统计,提供完整日期列表,无数据自动补 0

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计1020字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中