业务分析中经常会碰到'每隔N时间交易额、平均速度、波峰..'等等问题，这类问题在实时中很好写，利用flinkSQL滚动窗口聚合即可；离线数据使用sql如何分析呢，接下来以真实需求的简化版进行分析
案例：某电商平台的某张表中存在该平台的交易数据量，示例数据如下：
order_id |
order_date |
order_count |
201 |
2021-08-12 18:21:08 |
25 |
202 |
2021-08-12 18:21:09 |
44 |
203 |
2021-08-12 18:21:10 |
23 |
204 |
2021-08-12 18:21:11 |
54 |
205 |
2021-08-12 18:21:12 |
56 |
206 |
2021-08-12 18:21:13 |
23 |
207 |
2021-08-12 18:21:14 |
14 |
208 |
2021-08-12 18:21:15 |
27 |
209 |
2021-08-12 18:21:16 |
48 |
210 |
2021-08-12 18:21:17 |
32 |
211 |
2021-08-12 18:21:18 |
65 |
212 |
2021-08-12 18:21:19 |
88 |
213 |
2021-08-12 18:21:20 |
102 |
214 |
2021-08-12 18:21:21 |
104 |
215 |
2021-08-12 18:21:22 |
120 |
216 |
2021-08-12 18:21:23 |
111 |
217 |
2021-08-12 18:21:24 |
98 |
218 |
2021-08-12 18:21:25 |
92 |
219 |
2021-08-12 18:21:26 |
90 |
220 |
2021-08-12 18:21:27 |
85 |
... |
... |
... |
需求5秒汇总一下结果，展示结果如下：
id |
start_date |
end_date |
amount |
1 |
2021-08-12 18:21:08 | 2021-08-12 18:21:12 | 202 |
2 |
2021-08-12 18:21:13 | 2021-08-12 18:21:17 |
144 |
3 |
2021-08-12 18:21:18 |
2021-08-12 18:21:22 |
479 |
... |
... |
... |
... |
分析：关键问题在于这个5秒如何划分出来？我们知道一个数除以5得到的值，在没到5的倍数时值的整数部分都是一样的(如下)
select20/5;--4.0select21/5;--4.2select22/5;--4.4select23/5;--4.6select24/5;--4.8
可以利用时间戳/5得到值进行取整，这样可以得到2021-08-12 18:21:08~2021-08-12 18:21:10、2021-08-12 18:21:11~2021-08-12 18:21:15这样分组的数据；但是这样的分组是不符合要求的，需求是从首条时间戳+5s进行统计，即2021-08-12 18:21:08~2021-08-12 18:21:12、2021-08-12 18:21:13~2021-08-12 18:21:17。
这个需求可以利用取余进行处理，任何数除以5得到的余数都在[0,4]之间(如下)
select20%5;--0select21%5;--1select22%5;--2select23%5;--3select24%5;--4
- 生成示例数据，并对时间进行标记
with tb1 as(select order_id, order_date, unix_timestamp(to_date(order_date,'yyyy-mm-dd hh:mi:ss'))%5as flag,--取值:0 1 2 3 4 order_count fromvalues(201,'2021-08-12 18:21:08',25),(202,'2021-08-12 18:21:09',44),(203,'2021-08-12 18:21:10',23),(204,'2021-08-12 18:21:11',54),(205,'2021-08-12 18:21:12',56),(206,'2021-08-12 18:21:13',23),(207,'2021-08-12 18:21:14',14),(208,'2021-08-12 18:21:15',27),(209,'2021-08-12 18:21:16',48),(210,'2021-08-12 18:21:17',32),(211,'2021-08-12 18:21:18',65),(212,'2021-08-12 18:21:19',88),(213,'2021-08-12 18:21:20',102),(214,'2021-08-12 18:21:21',104),(215,'2021-08-12 18:21:22',120),(216,'2021-08-12 18:21:23',111),(217,'2021-08-12 18:21:24',98),(218,'2021-08-12 18:21:25',92),(219,'2021-08-12 18:21:26',90),(220,'2021-08-12 18:21:27',85) t(order_id,order_date,order_count))
- 找出余数[0,4]循环的第一个数
tb2 as(select order_id, order_date, order_count, flag, first_value(flag) over(orderby order_id) first_flag from tb1 )
- 使用sum(if)进行分组(该使用方法在系列文章上一篇已经概述)
tb3 as(select order_id, order_date, order_count, flag, first_flag, sum(if(flag=first_flag,1,0)) over(orderby order_id) group_flag from tb2 )
- 对分组之后的数据进行统计
select group_flag as id, min(order_date)as start_time, max(order_date)as end_time, sum(order_count)as amount from tb3 groupby group_flag;--运行结果如下：id start_time end_time amount 12021-08-1218:21:082021-08-1218:21:1220222021-08-1218:21:132021-08-1218:21:1714432021-08-1218:21:182021-08-1218:21:2247942021-08-1218:21:232021-08-1218:21:27476
该案例主要分析了分区间段求解的思路，按照该思路结合具体业务需求可实现动态分区间。
今日分享到此结束，拜了个拜
