题解 | #零食类商品中复购率top3高的商品#

# 求商品，限制是零食类商品复购率前三的
# 复购率：近90天购买它大于等于2次的人数；购买它的总人数

# 先联结三个表，查询商品id，uid；限制付款的订单，还有近90天，还有商品类型为零食
# 今天的定义是最大日期，在临时表0的where语句里面没有办法使用聚合函数，所以在下一个表里面筛选日期
# 查询必要的 商品id，uid，记录的日期， 聚合开窗函数求记录的最大日期作为一个新字段
WITH temp_0 AS(
    SELECT product_id, uid, DATE(event_time) event_date, MAX(DATE(event_time)) OVER() today
    FROM tb_order_detail
    JOIN tb_product_info a USING(product_id)
    JOIN tb_order_overall b USING(order_id)
    WHERE status = 1 AND tag = '零食'
    ),
	# 临时表1，查询必要的product_id, uid和这两个组合起来的记录数——就是每个用户购买了几次这个商品
	# 根据日期筛选，因为是连今天算的近90天，所以跨89天
    temp_1 AS(
    SELECT product_id, uid, COUNT(*) u_p_c
    FROM temp_0
    WHERE event_date BETWEEN DATE_SUB(today, INTERVAL 89 DAY) AND today
    GROUP BY product_id, uid
    ),
	# 临时表2，查询上面所有，查询每个用户对每个商品的复购次数，如果购买次数大于等于2，那么复购次数为1，
	# 还有每个商品被多少人购买过，因为在上个表就已经对product_id, uid分组，所以可以这样执行
	# 这样就得到了每个商品的复购人次，和总共购买人次
    temp_2 AS(
    SELECT *, 
        SUM(IF(u_p_c >= 2, 1, 0)) OVER(PARTITION BY product_id,uid) p_c_again,
        COUNT(*) OVER(PARTITION BY product_id) p_c_all
    FROM temp_1
    )

# 主查询根据公式计算复购率
# p_c_again在一些人那里是0，是因为没有复购，所以只求和计算复购的人次
# 最后不要忘了是求前三
SELECT product_id, ROUND(SUM(p_c_again)/p_c_all, 3) repurchase_rate
FROM temp_2
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
# LIMIT 0, 3
LIMIT 3 OFFSET 0

踩的坑：没有限制前三条

思路：

1.联结三张表，找到符合各种要求的记录‘

2.要计算复购率，先要找打多少人次复购，多少人次购买

3.复购的定义是购买两次及以上，所以要先查询每个用户对每个商品的购买次数，再根据复购条件计算出复购人次