零食类商品中复购率top3高的商品

id	product_id	shop_id	tag	int_	quantity	release_time
1	8001	901	零食	60	1000	2020-01-01 10:00:00
2	8002	901	零食	140	500	2020-01-01 10:00:00
3	8003	901	零食	160	500	2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id	order_id	uid	event_time	total_amount	total_cnt	status
1	301001	101	2021-09-30 10:00:00	140	1	1
2	301002	102	2021-10-01 11:00:00	235	2	1
3	301011	102	2021-10-31 11:00:00	250	2	1
4	301003	101	2021-10-02 10:00:00	300	2	1
5	301013	105	2021-10-02 10:00:00	300	2	1
6	301005	104	2021-10-03 10:00:00	170	1	1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id	order_id	product_id	price	cnt
1	301001	8002	150	1
2	301011	8003	200	1
3	301011	8001	80	1
4	301002	8001	85	1
5	301002	8003	180	1
6	301003	8002	140	1
7	301003	8003	180	1
8	301013	8002	140	2
9	301005	8003	180	1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

场景逻辑说明：

用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-订单状态为1表示已付款；
若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。

问题：请统计零食类商品中复购率top3高的商品。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

输出示例：

示例数据的输出结果如下：

product_id	repurchase_rate
8001	1.000
8002	0.500
8003	0.333

解释：

商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；

商品8002被101购买了两次，被105购买了1次，复购率0.500；

商品8003被102购买两次，被101和105各购买1次，复购率为0.333。

示例1

输入:

DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '零食', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-09-30 10:00:00', 140, 1, 1),
  (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
  (301011, 102, '2021-10-31 11:00:00', 250, 2, 1),
  (301003, 101, '2021-11-02 10:00:00', 300, 2, 1),
  (301013, 105, '2021-11-02 10:00:00', 300, 2, 1),
  (301005, 104, '2021-11-03 10:00:00', 170, 1, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8002, 150, 1),
  (301011, 8003, 200, 1),
  (301011, 8001, 80, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 140, 1),
  (301003, 8003, 180, 1),
  (301013, 8002, 140, 2),
  (301005, 8003, 180, 1);

输出:

8001|1.000
8002|0.500
8003|0.333

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6496KB, 提交时间: 2022-01-03

select product_id,round(sum(if(cnt>=2,1,0))/count(*),3) repurchase_rate from
(
    select a.product_id,b.uid,count(uid) cnt 
    from tb_order_detail as a left join tb_order_overall as b on a.order_id=b.order_id
    left join tb_product_info as c on c.product_id=a.product_id
    where datediff((select max(event_time) from tb_order_overall),event_time)<90 and tag='零食'
    group by a.product_id,b.uid
    ) as d
group by product_id
order by repurchase_rate desc,product_id limit 3

Mysql 解法, 执行用时: 36ms, 内存消耗: 6524KB, 提交时间: 2021-12-08

SELECT product_id,
       ROUND(SUM(case when pay_cnt>=2 then 1 else 0 end)/COUNT(uid), 3) rate
FROM (SELECT product_id,
             uid,
             COUNT(1) pay_cnt
      FROM tb_product_info pi
      JOIN tb_order_detail od USING(product_id)
      JOIN tb_order_overall oo USING(order_id)
      WHERE oo.event_time >= DATE_SUB((SELECT max(event_time) FROM tb_order_overall), interval 89 DAY)
            AND tag='零食' AND status=1
      group by product_id, uid) tmp
group by product_id
order by rate desc, product_id
LIMIT 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6376KB, 提交时间: 2021-12-08

# select t1.product_id,
# # count(if(count(uid)>=2,uid,NULL)) / count(distinct uid) as repurchase_rate
# sum(if(t1.a>=2,1,0)) / t1.b as repurchase_rate
# from
# (
# select uid, od.product_id, count(uid) as a, count(distinct uid) as b
# from tb_order_detail as od
# left join tb_order_overall as oo using(order_id)
# left join tb_product_info as pi on pi.product_id = od.product_id
# where oo.status in (0,1)
# and pi.tag = '零食'
# # and date(date_format(oo.event_time,'%Y%m%d')) >= DATE_ADD(dd,-90,max(date_format(oo.event_time,'%Y%m%d')))
# and DATEDIFF((select max(event_time) from tb_order_overall),event_time)<=89
# ) as t1

# group by t1.product_id
# order by repurchase_rate desc
# limit 3;

select product_id,
round(sum(case when n>=2 then 1 else 0 end)/count(distinct uid),3) as repurchase_rate
from
(
    # 表格aa：近90天内，每个用户购买每个零食类商品的次数
    select b.product_id,uid,count(*) n
    from tb_product_info a
    left join tb_order_detail b on a.product_id=b.product_id
    left join tb_order_overall c on b.order_id=c.order_id
    where status in (0,1)
    and DATEDIFF((select max(event_time) from tb_order_overall),event_time)<=89
    and tag='零食'
    group by b.product_id,uid
) as aa
group by product_id
order by repurchase_rate desc,product_id
limit 3;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2021-12-16

select product_id,round(count(*)/count(distinct uid)-1,3) as repurchase_rate
from tb_product_info p
join tb_order_detail d using(product_id)
join tb_order_overall o using(order_id)
where tag='零食' and 
date(event_time)>=date_sub(date((select max(event_time) from tb_order_overall)),interval 89 day)
group by product_id
order by repurchase_rate desc,product_id
limit 3;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-01-23

#求近90天购买某商品两次以上的人数

#计算统计零食类商品中复购率top3高的商品。
select product_id,round(sum(cnt)/count(cnt),3) repurchase_rate
from
(select t1.product_id,uid,if(count(event_time)>=2,1,0) cnt
from tb_product_info t1
join tb_order_detail t3
on t1.product_id=t3.product_id
join tb_order_overall t2
on t2.order_id=t3.order_id
where status=1 and tag='零食' 
and datediff(date((select max(event_time) from tb_order_overall)),date(event_time))<90
group by product_id,uid) t
group by product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;

上一题

下一题

详情