背景:
公司有订单汇总数据存储的时间维度是到小时的,如果是按小时推送,直接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)。
希望以上对读者有所帮助。