一、知识点梳理与拓展
从这一道真题，我所收获到的核心是各种表关联的用法。以前我常用到的表关联是：表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;
京公网安备 11010502036488号