【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 开窗可以轻松实现这一需求。
原文地址：https://blog.csdn.net/weixin_46389691/article/details/139025851
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！