题解 | #店铺901国庆期间的7日动销率和滞销率#

这个题目要是需求真的很奇葩。。。主要问题是这句话没理解到位（只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。）

以下记录一下我题解过程的思路

我最开始想的是记录国庆三天 10-01 10-02 10-03的数据，所以想通过 (select "2021-10-01" as dt union select "2021-10-02" as dt union select "2021-10-03" as dt) as tmp作为左表直接固定死三天要统计的数据，再连接右表的订单连表详细数据(条件shop_id=901)来找到国庆三天中分别前6天的product_id，最后就可以通过distinct和聚合函数等将数据统计出来。

select
tmp.dt,
round(count(distinct tmp2.product_id)/(select count(product_id) from tb_product_info where shop_id=901),3) as sale_rate,
round(1-(count(distinct tmp2.product_id)/(select count(product_id) from tb_product_info where shop_id=901)),3) as unsale_rate
from
(select "2021-10-01" as dt union select "2021-10-02" as dt union select "2021-10-03" as dt) as tmp
left join
(select too.event_time, tpi.product_id from tb_order_overall as too
left join tb_order_detail as tod on too.order_id=tod.order_id
left join tb_product_info as tpi on tod.product_id = tpi.product_id
where tpi.shop_id=901 or tpi.shop_id is null) as tmp2
on datediff(tmp.dt,tmp2.event_time) between 0 and 6
group by tmp.dt
order by tmp.dt

这里思路是正确的，就是题意没理解到位，但是！我发现一个问题（下图），我的国庆三天分别获取前6天的数据，这里的实际输出第二行的 动销率sale_rate 为0.000，我百思不得其解，因为最少也是前一行的 动销率 数值吧，这里跳过了0.333变为0.000，到我现在解出来也没明白。（有知道的同学可以指教一下我吗，谢谢。）

然后我想了很多，依然没想出来问题在哪，没办法只能去题解里找答案（真菜），在看完这个解法后明白了。下方记录一下别人的写法。

SELECT dt, ROUND(cnt / total_cnt, 3) AS sale_rate, ROUND(1 - cnt / total_cnt, 3) AS unsale_rate
FROM
(
    SELECT DISTINCT
        DATE(event_time) AS dt,
        (
            SELECT COUNT(DISTINCT (IF(shop_id != 901, null, product_id)))
            FROM tb_order_overall
            JOIN tb_order_detail USING (order_id)
            JOIN tb_product_info USING (product_id)
            WHERE TIMESTAMPDIFF(DAY, event_time, to1.event_time) BETWEEN 0 AND 6
        ) AS cnt,
        (
            SELECT COUNT(DISTINCT product_id)
            FROM tb_product_info
            WHERE shop_id = 901
        ) AS total_cnt
    FROM tb_order_overall to1
    WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
) AS t0
ORDER BY dt;

最后把

(select "2021-10-01" as dt union select "2021-10-02" as dt union select "2021-10-03" as dt) as tmp

改成

(select date_format(event_time, "%Y-%m-%d") as dt from tb_order_overall where event_time between "2021-10-01 00:00:00" and "2021-10-04 00:00:00") as tmp

依然是作为左表，不过区别就是

• 上面是 固定写死国庆三天日期 去统计数据。
• 下面是 确定订单总表在国庆三天中哪三天有下单的日期 去统计数据。到这就好理解这句话的意思了（只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。）

以下就是最终通过测试用例的sql。

select
tmp.dt,
round(count(distinct tmp2.product_id)/(select count(product_id) from tb_product_info where shop_id=901),3) as sale_rate,
round(1-(count(distinct tmp2.product_id)/(select count(product_id) from tb_product_info where shop_id=901)),3) as unsale_rate
from
(select date_format(event_time, "%Y-%m-%d") as dt from tb_order_overall where event_time between "2021-10-01 00:00:00" and "2021-10-04 00:00:00") as tmp
left join
(select too.event_time, tpi.product_id from tb_order_overall as too
left join tb_order_detail as tod on too.order_id=tod.order_id
left join tb_product_info as tpi on tod.product_id = tpi.product_id
where tpi.shop_id=901 or tpi.shop_id is null) as tmp2
on datediff(tmp.dt,tmp2.event_time) between 0 and 6
group by tmp.dt
order by tmp.dt