题目描述

商品信息表tb_product_info

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

订单总表tb_order_overall

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

订单明细表tb_order_detail

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

问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

注：

动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

输出示例：

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

解释：

10月1日的近7日（9月25日---10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；

10月2日的近7日（9月26日---10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；

10月3日的近7日（9月27日---10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，

滞销率为0.000；

解题

注意：该题不能用窗口函数，因为窗口函数没有count(distinct xxx) over()形式。

一、求每个日期截至到当前日期上架商品总数c

select date(t2.event_time) as dt,
       count(distinct case when datediff(t2.event_time,t1.release_time)>=0 then t1.product_id end) as onsale_cnt
from tb_product_info t1,tb_order_overall t2
where t1.shop_id='901' and t2.status=1
group by date(t2.event_time)

2021-09-30|3

2021-10-01|3

2021-10-02|3

2021-10-03|3

`二、求每个有订单的日期以及售出商品编号b`

select date(t2.event_time) as dt,
       t3.product_id
from tb_product_info as t1
join tb_order_detail as t3
on t1.product_id=t3.product_id and t1.shop_id=901
join tb_product_overall t2
on t2.order_id=t3.order_id and t2.status=1

2021-09-30|8002

2021-10-02|8003

2021-10-03|8003

2021-10-03|8001

`三、制作一张包含所需日期的时间表a`

select date(event_time) as dt
from tb_order_overall
where date(event_time) between '2021-10-01' and '2021-10-03'

四、三表连接

将表c当前的日期与表b相应日期之前的6天内数据连接之后再连接表a

左连接的目的是可以将没有销售记录的日期也显示出来。

select *
from
(select date(event_time) as dt
from tb_order_overall 
where date(event_time) BETWEEN '2021-10-01' and '2021-10-03') as a
left JOIN
(select date(t2.event_time) as dt,
       t3.product_id
from tb_order_overall t2
join tb_order_detail t3
on t2.order_id=t3.order_id
join tb_product_info t1
on t1.product_id=t3.product_id and t1.shop_id=901) as b
on datediff(a.dt,b.dt) BETWEEN 0 and 6
JOIN
(select date(event_time) as dt,
       count(case when datediff(date(event_time),release_time)>=0 then product_id end) as onsale_cnt
from tb_product_info,tb_order_overall
where shop_id=901
group by dt) as c
on a.dt=c.dt

2021-10-01|2021-09-30|8002|2021-10-01|3
2021-10-02|2021-10-02|8003|2021-10-02|3
2021-10-02|2021-09-30|8002|2021-10-02|3
2021-10-03|2021-10-02|8003|2021-10-03|3
2021-10-03|2021-10-03|8003|2021-10-03|3
2021-10-03|2021-10-03|8001|2021-10-03|3
2021-10-03|2021-09-30|8002|2021-10-03|3

这样，当按照日期分组计算时，就可以计算每个日期下对应的该日期前6天内有过购买记录的商品，因为没用窗口函数，可以使用count（distinct xxx)

五、做最后的计算

select a.dt,
       round(count(distinct b.product_id)/onsale_cnt,3) as sale_rate,
       round(1-(count(distinct b.product_id)/onsale_cnt),3) as unsale_rate
from
(select date(event_time) as dt
from tb_order_overall 
where date(event_time) BETWEEN '2021-10-01' and '2021-10-03') as a
left JOIN
(select date(t2.event_time) as dt,
       t3.product_id
from tb_order_overall t2
join tb_order_detail t3
on t2.order_id=t3.order_id and t2.status=1
join tb_product_info t1
on t1.product_id=t3.product_id and t1.shop_id=901) as b
on datediff(a.dt,b.dt) BETWEEN 0 and 6
JOIN
(select date(event_time) as dt,
       count(distinct case when datediff(date(event_time),date(release_time))>=0 then product_id end) as onsale_cnt
from tb_product_info,tb_order_overall
where shop_id=901
group by dt) as c
on a.dt=c.dt
group by a.dt
order by a.dt