这篇文章主要介绍了mysql实现按照某个时间段分组统计方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教
mysql按照某个时间段分组统计
今天刚好是碰到一个报表需求,要求根据时段统计各工单的数量。这个就有点皮实了,以前都没搞过这玩意。于是研究了几下。
准备开始
首先一个问题,想根据时间段进行统计,这个问题先不考虑,先考虑,如果按照一小时统计呢,该怎么去做,于是就一顿操作,写出以下sql,这个基本的相信没什么难度,主要是说,怎么去单独拿到小时,这里用到“DATE_FORMAT”函数。
SELECT DATE_FORMAT( create_order_time, '%H' ) AS h, COUNT( 1 ) FROM order_item GROUP BY DATE_FORMAT( create_order_time, '%H' ) ORDER BY DATE_FORMAT( create_order_time, '%H' )
到这里,小时统计没问题了,那么怎么改造成符合我的需求呢,请看图片
相信聪明的人已经感觉出来的,就是其实把我们取到的小时去除以时间段的区间取整数,那么就可以得到同一区间的数据,然后这个数字在乘回去我们的区间,就可以得到上限,在加区间就是下限,最终我们得到的SQL
-- 区间数字SQL SELECT (FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2) * 2) AS h2, COUNT( 1 ) FROM order_item GROUP BY FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2) ORDER BY FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2) -- 最终SQL SELECT CONCAT( FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2, '-', FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2 + 2) AS h2, COUNT( 1 ) FROM order_item GROUP BY FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) ORDER BY FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 )
样图
mysql区间分组统计
场景
表cases,有完成时间(finish_time),id等字段
现在将finish_time按时间区间划分年份
例 2021-11-01 00:00:00 ~2022-10-31 23:59:59 为2022年
下面是模拟数据:
id | finish_time |
---|---|
1 | 2019-01-23 23:23:23 |
2 | 2019-02-28 12:12:12 |
3 | 2020-08-12 08:12:12 |
4 | 2021-05-12 22:12:43 |
5 | 2022-05-12 23:23:23 |
统计结果应该如下所示:
年份 | 数量 |
---|---|
2019 | 2 |
2020 | 1 |
2021 | 1 |
2022 | 1 |
因为不能在cases表中添加字段,上网搜寻后找到两个方法:
- 1、利用了mysql提供的interval和elt函数实现了效果。
- 2、是建一张中间表用来分组
利用了mysql提供的interval和elt函数实现了效果
interval
interval(N,N1,N2,N3) ,比较列表中的N值,该函数如果N elt(n,str1,str2,str3,…) 如果n=1,则返回str1,如果n=2,则返回str2,依次类推 两个函数结合,再加上group,实现了这种范围分组的效果 但实际效果不尽人意,也许是因为mysql中interval也是处理日期的关键字吧,interval()函数对日期进行划分的时候每次都划分到最后一个区间,因此使用第二种办法即建一张中间表用来划分 创建如下一张中间表(tmp) cases表通过join连接这张表就可以实现分组效果 这样就可以在不修改原有表结构的基础上实现区间分组统计。 创建函数get_year() 以上为个人经验,希望能给大家一个参考,也希望大家多多支持0133技术站。elt
select elt(interval(finish_ime,'2019-11-01','2020-11-01','2021-11-01','2022-11-01'),2019,2020,2021,2022) as i,count(*) from cases group by i
建中间表
start_date end_date year 2018-11-01 00:00:00 2019-10-31 23:59:59 2019 2019-11-01 00:00:00 2020-10-31 23:59:59 2020 2020-11-01 00:00:00 2021-10-31 23:59:59 2021 2021-11-01 00:00:00 2022-10-31 23:59:59 2022 select count(*) as '数量', t.`year` as "年份" from cases c left join tmp t on c.finish_time>=t.start_date and c.finish_time<=t.end_date group by t.`year`
通过函数来划分区间
create function get_year(finishTime datetime) returns int begin declare d_year int(4); set d_year = year(finishTime); if (finishTime >= concat(d_year, '-11-01 00:00:00')) then return d_year + 1; else return d_year; end if; end;
select count(*) as '数量',get_year(c.finish_ime) as "年份" from cases c group by get_year(c.finish_ime)
以上就是mysql实现按照某个时间段分组统计的详细内容,更多请关注0133技术站其它相关文章!