Mysql如何将小时维度的统计数据以2小时、3小时、4小时、6小时进行汇总统计呢?

朴灿烈づ我的快乐病毒、 2022-12-02 12:24 212阅读 0赞

背景:

公司有订单汇总数据存储的时间维度是到小时的，如果是按小时推送，直接group小时字段即可。现在有需求是要分段汇总统计的。分段的时间间隔可能是1小时、2小时、3小时、4小时、6小时(没有5小时，这个小时没有意义)。

如果是按6小时进行汇总，想得到时间段汇总这样的:

00:00~06:00 、06:00~12:00、12:00~18:00、18:00~00:00

这个需求处理感觉是合理的，但不太简单，如果用程序代码进行分组汇总，作为一个大数据工程师来说，这样处理太鸡肋，所以还是从Sql统计语句方面处理，别的不多说了，直接上语句。

select
            tenant_id,
            shop_id,
            date,
            CONCAT(IF(FLOOR(`hour`/100/${hour_interval})*${hour_interval} < 10,CONCAT('0',FLOOR(`hour`/100/${hour_interval})*${hour_interval}),FLOOR(`hour`/100/${hour_interval})*${hour_interval}),':00') hour,
            sum(total_amount) total_amount,
            sum(promo_amt) promo_amt,
            sum(total_orders) total_orders,
            sum(total_refund_orders) total_refund_orders
            from t_order_sales_stat_hour
            <include refid="queryConditionForStat"/>
            GROUP BY tenant_id,shop_id,date,CONCAT(IF(FLOOR(`hour`/100/${hour_interval})*${hour_interval} < 10,CONCAT('0',FLOOR(`hour`/100/${hour_interval})*${hour_interval}),FLOOR(`hour`/100/${hour_interval})*${hour_interval}),':00')
            order by date desc,`hour`

简单说明一下上面的sql:

1. 上面的语句是在mybatis中应用的

2. 其中\{hour\_interval\}是时间段的时间间隔(1小时、2小时、3小时、4小时、6小时)

3. 标签include为过滤条件

4. 关键点在于除以时间间隔再FLOOR(向下取整)，然后再乘以时间间隔(eg: 时间间隔6，小时数(0~23)除以6，再向下取整得到的整数为0、1、2、3再乘以间隔6则为0、6、12、18，再在这个基础上进行group分组统计，得到的结果便是分段统计的计算记过了。至于显示则可以稍作处理，再这个小时基础上再加上一个时间间隔，形成一个时间段，00:00~06:00、06:00~12:00、12:00~18:00、18:00~00:00)。

希望以上对读者有所帮助。