【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');
1
2
3

需求说明

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

分析步骤如下：

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

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

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

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

需求实现

select
    user_id,
    min(order_date) order_start_date,
    max(order_date) order_end_date
from
    (select
        user_id,
        order_date,
        date_sub(order_date,rn) same_day
    from
        (select
            user_id,
            order_date,
            row_number() over (partition by user_id order by order_date) rn
        from
            test
        group by
            user_id,
            order_date )t1 -- 分组后进行行号标记
       )t2 -- 使用日期和行号进行运算
group by
    user_id,same_day
having
    count(user_id) >= 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

输出结果

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

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

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