再也不想做了！ #店铺901国庆期间的7日动销率和滞销率#

店铺901国庆期间的7日动销率和滞销率

http://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9

这题太让人崩溃了，来来回回折腾了五六个小时，算是解出了，但是效率和运行时间都不佳😰

有题目理解的不到位，也有思路卡壳的~~~及时现在写下思路，可能有的地方也表述不清楚

先mark，再看看各位大神的，看有没有更好解题思路

【问题】店铺901 国庆期间 的 7日<动销率> 和 <滞销率>

【1、拆解指标】-- 核心计算<动销率>，坑在于搞清楚商品数是时间段里的还是某时刻的

题中给出的动销率、滞销率定义，如下

动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。

大眼一看，滞销率=1-动销率，所以解题核心落在求解动销率

【拆解动销率】两个指标，这是一个坑！！！

求解动销率需要解决两个指标：①销售的商品数 ②已上架（在售）的商品数

接下来，给两个指标添加限定条件

① 销售的商品数（时间段） -- 店铺某个时间内销售的商品数

② 在售的商品数（时间点） -- 店铺当前上架的商品数，时间点概念，换言之截止某日店铺在售n种产品

两个指标，同时提到了时间概念，只是一个是时间段概念，一个是时间点概念。问题又来了，如何确定题目中的时间参考？

继续给细化指标，添加时间限定条件 -- 某日的7日动销率

① 销售的商品数量 被限定在某日过去7天里的产品数量

模糊可以感知

-- 7日里产品会有重复，求时间段里的产品数量，需要去重。应该会用到count(distinct product_id)

-- 过去7天，表达式

※ 以当前时间点倒推7天 date_sub(date,interval 6 day)得到距今最远时刻，当前时刻>=最远时刻即可

※ 时间间隔在0到6间，datediff(当前日期，对照当前日期的日期) between 0 and 6 即可

② 在售商品的数量 某日在售商品的数量，翻译为各日期在售商品数量

所以，某日动销率=7日内销售产品种类数量总和/截止当日在售的产品种类数量。题目中，某日为国庆节前3日

【2、开始解题】

筛选出国庆前3天有交易的日期dt_3，得到表t_dt（只要当天任一店铺有任何商品的销量就输出该天的结果）
筛选出店铺901各个交易日期的交易产品id，得到表t_pro（注意此处还不急于限定国庆期内）
由以上两表计算出店铺901 在国庆前3天每日过去7天售出的产品总数（此时开始剥洋葱，因为使用窗口函数count()时，不支持distinct，要费一番周折）
计算店铺901在国庆节前3天各天在售商品数
第三步和第四步得到的表连接，即可计算比率了

代码如下↓

#【t_dt】国庆前3天有交易的日期dt_3
-- 只要当天任一店铺有任何商品的销量就输出该天的结果
WITH t_dt AS(
SELECT DISTINCT date(event_time) dt_3 FROM tb_order_overall
WHERE status=1
and date(event_time) BETWEEN '2021-10-01'  AND '2021-10-03'
),

#【t_pro】店铺901有销售的日期dt，及其各日里售出产品id
t_pro AS(
SELECT DISTINCT DATE(o.event_time) dt,d.product_id FROM tb_order_overall o
LEFT JOIN tb_order_detail d
ON o.order_id=d.order_id
LEFT JOIN tb_product_info p
ON p.product_id=d.product_id
WHERE p.shop_id=901
and o.status=1
)



select a.dt,
round(a.sale_cnt/b.onsale_cnt,3) sale_rate,
round(1-a.sale_cnt/b.onsale_cnt,3) unsale_rate 
from
# 店铺901 国庆前3天 每日售出的产品在近7日里一共的产品数量sale_cnt
(SELECT t_dt.dt_3 dt,
count(DISTINCT t_pro.product_id) sale_cnt
FROM t_dt
LEFT JOIN t_pro
ON datediff(t_dt.dt_3,t_pro.dt) BETWEEN 0 AND 6
GROUP BY t_dt.dt_3)a
JOIN
(# 店铺901 国庆前3天 截止当日在售产品数
SELECT t_dt.dt_3 dt,
count(DISTINCT 
CASE WHEN DATE(p.release_time)<=t_dt.dt_3 then p.product_id
else null end) onsale_cnt
FROM t_dt,tb_product_info p
where p.shop_id=901
GROUP BY t_dt.dt_3)b
on a.dt=b.dt
order by a.dt;