MaxCompute 使用SQL进行分区间段统计分析

2023-10-29 21:09:34

业务分析中经常会碰到'每隔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的倍数时值的整数部分都是一样的(如下)

select 20/5;--4.0
select 21/5;--4.2
select 22/5;--4.4
select 23/5;--4.6
select 24/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]之间(如下)

select 20%5;--0
select 21%5;--1
select 22%5;--2
select 23%5;--3
select 24%5;--4

生成示例数据，并对时间进行标记

with tb1 as (
    select 
        order_id,
        order_date,
        unix_timestamp(to_date(order_date,'yyyy-mm-dd hh:mi:ss'))%5 as flag,--取值:0 1 2 3 4
        order_count
    from values(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(order by 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(order by 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
group by group_flag;
--运行结果如下：
id  start_time                      end_time                            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
4       2021-08-12 18:21:23     2021-08-12 18:21:27     476

该案例主要分析了分区间段求解的思路，按照该思路结合具体业务需求可实现动态分区间。

今日分享到此结束，拜了个拜

码农公寓

相关文章