【Hive SQL 每日一题】统计用户连续下单的日期区间

测试数据

create table test(user_id string,order_date string);INSERT INTO test(user_id, order_date) VALUES('101', '2021-09-21'),('101', '2021-09-22'),('101', '2021-09-23'),('101', '2021-09-27'),('101', '2021-09-28'),('101', '2021-09-29'),('101', '2021-09-30'),('102', '2021-10-01'),('102', '2021-10-02'),('102', '2021-10-05'),('102', '2021-10-06'),('102', '2021-10-07'),('106', '2021-10-04'),('106', '2021-10-05'),('106', '2021-10-08'),('107', '2021-10-05'),('107', '2021-10-06');

需求说明

统计用户连续下单的日期区间，所以连续的下单日期必须 >= 2，例如：2023-01-01,2023-01-02。

分析步骤如下：

1. 按 user_id、order_date 进行分组，同天的下单日期只保留一条。

2. 使用 row_number 窗口函数对行号进行标记。

3. 使用 date_sub 函数与行号标记进行运算，如果数据连续的话，那么运算后的日期必然是一样的。

4. 按 user_id 和 date_sub 运算后日期进行分组，过滤数量 < 2 的分组，最大值与最小值日期统计。

需求实现

selectuser_id,min(order_date) order_start_date,max(order_date) order_end_date
from(selectuser_id,order_date,date_sub(order_date,rn) same_dayfrom(selectuser_id,order_date,row_number() over (partition by user_id order by order_date) rnfromtestgroup byuser_id,order_date )t1 -- 分组后进行行号标记)t2 -- 使用日期和行号进行运算
group byuser_id,same_day
havingcount(user_id) >= 2;

输出结果

解决这题的关键是使用 row_number 窗口函数进行行号标记，然后和 date_sub 进行运算，如果日期是连续的，那么运算结果得到的日期就是一致的，如下所示：

date		rn
2023-05-04 	1
2023-05-05 	2
2023-05-06 	3

运算后，日期结果都为 2023-05-03，显然该日期是连续的，利用这一特性完成该需求。