hivesql练习_复购率问题
现有电商订单表（order_detail）如下。
| order_id (订单id) | user_id (用户id) | product_id (商品id) | price （售价） | cnt （数量） | order_date （下单时间） |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 5000 | 1 | 2022-01-01 |
| 2 | 1 | 3 | 5500 | 1 | 2022-01-02 |
| 3 | 1 | 7 | 35 | 2 | 2022-02-01 |
| 4 | 2 | 2 | 3800 | 3 | 2022-03-03 |
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。
期望结果如下：
| product_id <int> (商品id) | cpr <decimal(16,2)> (复购率) |
|---|---|
| 3 | 1.00 |
| 9 | 1.00 |
| 8 | 0.50 |
| 5 | 0.33 |
| 7 | 0.25 |
| 1 | 0.00 |
| 2 | 0.00 |
| 6 | 0.00 |
解答：
-- 先求电商订单表，最后一次下单日期 => tt
-- 根据product_id和user_id分组，求近90天购买某个商品至少两次的人 => tt1
-- 根据product_id分组，按复购率倒序、商品ID升序排序，计算每个商品复购率
1 WITH tt AS( 2 SELECT 3 MAX(order_date) end_date --订单详情表最后的日期 4 FROM 5 order_detail 6 ), tt1 as( 7 SELECT 8 od.product_id, user_id, 9 if(count(user_id) >=2, 1, 0) repeat -- 某商品至少购买两次的人数 10 FROM 11 order_detail od 12 JOIN tt 13 ON datediff(end_date, order_date) <= 90 --近90天 14 group by od.product_id, user_id 15 ) 16 SELECT 17 product_id, cast(sum(repeat)/ count(user_id) as decimal(16, 2)) cpr 18 FROM 19 tt1 20 group by product_id 21 order by cpr desc, product_id asc
