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

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

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

一、知识点梳理与拓展

从这一道真题，我所收获到的核心是各种表关联的用法。以前我常用到的表关联是：表1 JOIN 表2 ON 字段1=字段2

表关联的另2种用法：

用法1——这种用法可以适用于没有可直接用的关联字段情况

SELECT 字段1，字段2

FROM 表1，表2 WHERE 筛选条件

用法2——调整关联条件

SELECT 字段1，字段2

FROM 表1JOIN 表2 ON 条件

这里的条件比较宽泛WHERE后面可以跟啥ON后面也可以跟啥。

二、题目理解与解题步骤

1、题目理解

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

难点1：动销率=销售数/在架数；滞销率=1-动销率

由上可知，需要统计两个核心指标：每天的销售商品数以及在架商品数。

难点2：如何判断头3天的7日商品销售情况。

我的第一念头是使用滚动窗口函数，试了后有两个因素导致本题无法使用滚动窗口函数：因素a.窗口函数内无法直接使用DISTINCT，例如COUNT(DISTINCT product_id)OVER()代码运行出错。因素b.滚动窗口函数需要销售日期是连续的，因而也不行。所以这个思路就PASS掉了。
参考了其他人的解题方法学习到了可以直接使用JOIN+ON的条件来进行时间判断，在后面解题步骤详细讲解。

整个解题思路是：先求每天在架商品，再求每天的销售商品，之后把两张表关联起来，就能计算动销率啦（动销率=销售数/在架数），当然，难点是用“ON DATEDIFF(dt,ddt) BETWEEN 0 AND 6”这个条件去关联两张表得出7日内的数据。

2、解题步骤

1）求每天的在架商品数：先找出每天的在架商品详情，再求每天的在架商品数。

因为没有商品下架时间，所以默认只要商品销售日期大于上架日期，即为在架商品。
通过event_time>=release_time这个条件用来判断每天的在架商品id
SELECT 字段1，字段2... FROM 表1，表2。这种关联表的方法不需要使用关键字段进行关联，直接把两个表格里的每一条数据都进行遍历排列。

SELECT DATE(event_time) dt,CASE WHEN event_time>=release_time THEN product_id END onsale #通过event_time>=release_time判断商品是否在架
FROM tb_order_overall,tb_product_info WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'; #看901店铺10月1日-3日期间每天的在架商品详情

求10月1日到10月3日的每天的在架商品数（此处默认，9月在架的商品，10月也都在架，不存在下架行为）

SELECT DATE(event_time) dt,COUNT( DISTINCT CASE WHEN event_time>=release_time THEN product_id END) onsale_cnt
FROM tb_order_overall,tb_product_info WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
GROUP BY dt;

2）10月1日——3日每天的销售商品数

先统计一张9月26-10月3日期间，每天销售的商品详情表

SELECT DATE(event_time)ddt,product_id FROM tb_order_overall 
LEFT JOIN tb_order_detail USING(order_id)
LEFT JOIN tb_product_info USING(product_id) WHERE shop_id='901' #901店铺每天的商品销售情况
AND DATE(event_time) BETWEEN '2021-09-26' AND'2021-10-03' #限制日期是为了缩小运行数据的范围，增加运行效率
;

使用DATEDIFF(dt,ddt) BETWEEN 0 AND 6来关联每日在架商品数目表和每日销售表

SELECT dt,ddt,product_id,onsale_cnt
FROM(
	SELECT DATE(event_time) dt,COUNT( DISTINCT CASE WHEN event_time>=release_time THEN product_id END) onsale_cnt
	FROM tb_order_overall,tb_product_info WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03' GROUP BY dt
) t1
LEFT JOIN (
	SELECT DATE(event_time)ddt,product_id FROM tb_order_overall 
	LEFT JOIN tb_order_detail USING(order_id)
	LEFT JOIN tb_product_info USING(product_id) WHERE shop_id='901' 
	AND DATEDIFF('2021-10-03',DATE(event_time)) BETWEEN 0 AND 8 
) t2 ON DATEDIFF(dt,ddt) BETWEEN 0 AND 6

得出10月1日至3日期间（7日内的销售商品详单）

计算每日销售商品数

SELECT dt,COUNT(DISTINCT product_id)sale_cnt,onsale_cnt
FROM(
	SELECT DATE(event_time) dt,COUNT( DISTINCT CASE WHEN event_time>=release_time THEN product_id END) onsale_cnt
	FROM tb_order_overall,tb_product_info WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03' GROUP BY dt
) t1
LEFT JOIN (
	SELECT DATE(event_time)ddt,product_id FROM tb_order_overall 
	LEFT JOIN tb_order_detail USING(order_id)
	LEFT JOIN tb_product_info USING(product_id) WHERE shop_id='901' 
	AND DATEDIFF('2021-10-03',DATE(event_time)) BETWEEN 0 AND 8 
) t2 ON DATEDIFF(dt,ddt) BETWEEN 0 AND 6
GROUP BY dt;

SELECT dt,ROUND(COUNT(DISTINCT product_id)/onsale_cnt,3)sale_rate ,ROUND(1-COUNT(DISTINCT product_id)/onsale_cnt,3)unsale_rate
FROM(
    SELECT DATE(event_time) dt,COUNT( DISTINCT CASE WHEN event_time>=release_time THEN product_id END) onsale_cnt
    FROM tb_product_info,tb_order_overall 
    WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03' GROUP BY dt
) t1
LEFT JOIN (
    SELECT DATE(event_time)ddt,product_id FROM tb_order_overall 
    LEFT JOIN tb_order_detail USING(order_id)
    LEFT JOIN tb_product_info USING(product_id) WHERE shop_id='901' 
    AND DATEDIFF('2021-10-03',DATE(event_time)) BETWEEN 0 AND 8
) t2 ON DATEDIFF(dt,ddt) BETWEEN 0 AND 6 GROUP BY dt ORDER BY dt;