相关导航
Hive SQL 案例：指定商品带来的复购
更新时间：2021-02-22 13:53:45 标签：hive
sql
复购
提示
Hive SQL 教程 编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》 。作者开办 Python 数据分析培训，详情 Python 数据分析培训。
运营人员上架了一种专门用来拉新的商品，这些商品不管从需求还是价格都具有吸引力，目标是刺激用户快速下单这些商品，完成拉新。接下来，就要分析这些用户成为新用户后是否再有购买，形成复购的情况。
源数据
数据表位于 dwd.order_detail：
| p_day | uid | order_id | create_time | sku_id |
|---|---|---|---|---|
| 20190410 | 23424 | 3325264322 | 2019-04-10 14:55:37.300 | 1111 |
| 20190513 | 454121 | 9725372353 | 2019-05-13 13:54:17.300 | 23421 |
| 20190511 | 4234 | 5345433525 | 2019-05-11 10:15:31.300 | 21322 |
| 20190315 | 32546 | 5354378679 | 2019-03-15 08:01:41.667 | 14325 |
| 20190515 | 2525 | 6436438692 | 2019-05-15 19:05:55.000 | 1111 |
各字段说明：
- p_day：hive 库以日期分片，下单日期
- uid：用户的注册 id
- order_id：下单订单号
- create_time：订单创建时间，不重复
- sku_id：商品 ID，其中 1111 是拉新的活动商品
数据表是一个以订单-商品为粒度的流水表。
需求分析
需求要求用户先下单活动商品（sku_id = 1111），然后再有购买行为（后续购买不区分商品），所以，我们要剔除可能的先购买其他商品再购买活动商品的情况。
由于需要的数据是用户数，那就要按用户进行分组。我们可以先把购买活动商品的用户分组出来，取最小时间（就是最早下单时间）就得到了第一次购买活动商品的用户及时间（左表），然后再分组一次取最大时间（表中最后一次购买时间，形成右表），最终将两个表相连接，如果右表的时间大于左表的时间那么这个用户就算复购，因为如果相等就说明只下了一单。
具体代码过程往下看。
编写步骤
左表（最早下活动商品订单的时间）：
select distinct uid as uid, -- 用户
min(create_time) as f_time -- 最早下活动商品的时间
from dwd.order_detail
where sku_id = 1111
group by uid
右表（最晚下单时间，不分商品类型）：
select distinct uid as uid, -- 用户
max(create_time) as l_time -- 最晚下单时间
from dwd.order_detail
group by uid
接下来，将两个表，按 uid 联接，并比对两个下单时间便得到最终结果。
最终代码
-- 注释的此行为复购明细
-- select f_o.uid as uid, f_o.f_time as ftime, l_o.l_time
select count(f_o.uid) as users
from (
(select distinct uid as uid,
min(create_time) as f_time
from dwd.order_detail
where sku_id = 1111
group by uid) as f_o
left join (select distinct uid as uid,
max(create_time) as l_time
from dwd.order_detail
group by uid) as l_o
on f_o.uid = l_o.uid
)
where l_o.l_time > f_o.f_time
返回的 users 就是最终结果。
总结
复购、留存类需求，我们要充分利用时间的先后进行筛选，就能很方便地得到结果。
