sql业务题复购率计算
订单表结构:
CREATE TABLE orders (
`order_code` varchar(20) COMMENT '订单号',
`user_id` int(10) unsigned COMMENT '用户ID',
`pay_price` float(10,2) unsigned COMMENT '支付金额',
`order_time` datetime COMMENT '下单时间'
) COMMENT='订单表,存储了2018年1月1日至当前的所有用户订单,一个用户可以买多单';
with t1 as(
select left(order_time,7) as 月份,
user_id,
count(1) as cnt
from orders
where left(order_time,7) between 2022-01 and 2022-03
group by left(order_time,7) ,user_id )
select 月份,
count(1) as 总人数,
count(if(cnt>1,1,null)) as 复购,
count(if(cnt>1,1,null))/count(1) as 复购率
from t1
group by 月份;