【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量

测试数据

drop table if exists sales;
create table if not exists sales(
user_id int,
product_id int,
quantity int,
sale_date string);

INSERT INTO sales (user_id, product_id, quantity, sale_date) VALUES
(1001, 100, 1, '2024-05-16'),
(1001, 101, 3, '2024-05-15'),
(1001, 102, 2, '2024-05-14'),
(1001, 101, 2, '2024-05-14'),
(1002, 101, 3, '2024-05-17'),
(1002, 101, 3, '2024-05-16'),
(1002, 101, 2, '2024-05-15'),
(1002, 100, 1, '2024-05-15'),
(1002, 102, 1, '2024-05-14'),
(1002, 101, 3, '2024-05-11'),
(1002, 101, 2, '2024-05-10'),
(1003, 101, 3, '2024-05-17'),
(1003, 101, 3, '2024-05-16'),
(1003, 101, 2, '2024-05-14'),
(1003, 102, 1, '2024-05-13'),
(1003, 101, 2, '2024-05-10'),
(1004, 101, 3, '2024-05-17'),
(1004, 101, 2, '2024-05-14'),
(1004, 101, 2, '2024-05-10'),
(1005, 101, 3, '2024-05-17'),
(1005, 101, 4, '2024-05-16'),
(1005, 100, 2, '2024-05-16'),
(1005, 101, 1, '2024-05-16'),
(1005, 101, 2, '2024-05-15'),
(1005, 101, 3, '2024-05-13'),
(1005, 101, 2, '2024-05-12'),
(1005, 101, 2, '2024-05-11');

字段说明

• user_id：用户ID；
• product_id：商品ID；
• quantity：商品销售数量；
• sale_date：商品销售日期。

需求说明

统计最近 7 天内连续下单 3 日的用户量（假设今天为 2024-05-18）。

结果示例：

order_3_user_count
3

其中：

• order_3_user_count 表示最近 7 天内连续下单 3 日及以上的用户个数。

需求实现

select
    count(distinct user_id) order_3_user_count
from
    (select
        user_id
    from
        (select
            user_id,  
            date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
        from
            (select
                user_id,
                sale_date
            from
                sales
            group by
                user_id,sale_date)t1 )t2
    group by
        user_id,start_date
    having
        count(user_id) >= 3)t3;

输出结果如下：

一共有 3 名用户在 7 天内连续下单了 3 日及以上。

那么现在来说说这个需求是如何实现的，通过如下 1005 用户的购买数据来进行演化说明：

user_id	product_id	quantity	sale_date
1005	101	3	‘2024-05-17’
1005	101	4	‘2024-05-16’
1005	100	2	‘2024-05-16’
1005	101	1	‘2024-05-16’
1005	101	2	‘2024-05-15’
1005	101	3	‘2024-05-13’
1005	101	2	‘2024-05-12’
1005	101	2	‘2024-05-11’

在这个需求中，并不关心用户购买了多少商品，在意的是用户是否进行了下单，同天下单多次的用户也只算作一次。

select
    user_id,
    sale_date
from
    sales
group by
    user_id,sale_date;

按用户、下单时间进行聚合，每天仅保留一条数据，聚合后，数据变化如下：

user_id	sale_date
1005	‘2024-05-17’
1005	‘2024-05-16’
1005	‘2024-05-15’
1005	‘2024-05-13’
1005	‘2024-05-12’
1005	‘2024-05-11’

那么现在就有一个问题，就是该如何判断日期是否连续呢？

其实很简单，我们可以通过开窗的方式，计算日期是否连续，我这里是通过打标记来实现的。

那么具体是如何实现呢？需要先了解 row_number 方法的使用，如下所示：

user_id	sale_date	row_number	start_date
1005	‘2024-05-17’	6	‘2024-05-11’
1005	‘2024-05-16’	5	‘2024-05-11’
1005	‘2024-05-15’	4	‘2024-05-11’
1005	‘2024-05-13’	3	‘2024-05-10’
1005	‘2024-05-12’	2	‘2024-05-10’
1005	‘2024-05-11’	1	‘2024-05-10’

通过上面的规律可以发现，如果日期是连续的，那么当它们减去一串顺序的数字后，得到的结果是一致的，我们可以利用这一规则，判断日期是否连续。

select
    user_id,  
    date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
from
    (select
        user_id,
        sale_date
    from
        sales
    group by
        user_id,sale_date)t1;

既然求出了日期是否连续，那么下面就是分组判断各个用户是否满足连续购买 3 日的条件。

select
    user_id
from
    (select
        user_id,  
        date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
    from
        (select
            user_id,
            sale_date
        from
            sales
        group by
            user_id,sale_date)t1 )t2
group by
    user_id,start_date
having
    count(user_id) >= 3;

现在得到的结果并不是最终的结果，因为某个用户（1005）可能在 7 日内满足多次该条件，但是在这个需求中，每个用户只能算一次，所以还需要去重才能得到最终结果。

select
    count(distinct user_id) order_3_user_count
from
    (select
        user_id
    from
        (select
            user_id,  
            date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
        from
            (select
                user_id,
                sale_date
            from
                sales
            group by
                user_id,sale_date)t1 )t2
    group by
        user_id,start_date
    having
        count(user_id) >= 3)t3;

解本题的关键在于如何判断日期是否连续，通过 row_number 开窗可以轻松实现这一需求。