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

SQL题解：店铺动销率与滞销率计算

关键：只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

潜台词：输出逻辑与店铺901的销售情况无关，只取决于平台整体是否有销售记录。

表面理解：计算店铺901的动销率 → 应该只关注901的数据
实际规则：只要全平台当天有任何销售（哪怕不是901的），就必须输出901这天的结果

条件	是否输出
全平台当天有任意销售	✅ 必须输出（即使901销售为0）
全平台当天无任何销售	❌ 不输出

题目理解

题目要求计算店铺901在2021年国庆头3天（10月1日-10月3日）的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

关键定义

动销率：一段时间内有销量的商品占当前已上架总商品数的比例
滞销率：一段时间内没有销量的商品占当前已上架总商品数的比例

特殊要求

"只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0"

解题思路分析

1. 数据准备

首先需要从三个表中获取必要信息：

tb_order_overall：订单基本信息
tb_order_detail：订单商品明细
tb_product_info：商品信息（店铺ID在这里）

2. 核心逻辑

确定需要计算的日期范围（10月1日-10月3日）
计算7日滚动窗口内的动销商品数
计算当前已上架的商品总数
计算动销率和滞销率

SQL代码解析

CTE部分

date_range表：获取平台有销售记录的日期

  date_range AS (SELECT DISTINCT DATE(event_time) AS order_dateFROM tb_order_overallWHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03')

从订单总表中筛选出国庆3天内有销售记录的日期
使用distinct确保日期不重复

shop_products 表：获取店铺901的销售记录

shop_products AS (SELECTDATE(a.event_time) AS order_date,b.product_idFROMtb_order_overall aJOIN tb_order_detail b ON a.order_id = b.order_id AND a.status = 1JOIN tb_product_info c ON b.product_id = c.product_id AND c.shop_id = '901')

关联三个表获取店铺901的有效订单（status=1）的商品记录
结果包含日期和商品ID

product_counts 表：计算每日在售商品数

product_counts AS (SELECTDATE(o.event_time) AS order_date,COUNT(DISTINCT p.product_id) AS total_products
FROMtb_order_overall oCROSS JOIN tb_product_info p -- 显式交叉连接
WHEREp.shop_id = '901'AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0
GROUP BYDATE(o.event_time)
)

计算店铺901在每个订单日期时已经上架的商品总数
DATEDIFF(...) >= 0：只保留商品上架时间早于或等于订单日期的记录
使用逗号,表示的是隐式交叉连接（CROSS JOIN），这会生成两个表的笛卡尔积

商品表（p）：

product_id | shop_id | release_time
----------------------------
8001      | 901     | 2020-01-01
8002      | 901     | 2020-01-01
8003      | 901     | 2021-09-01

订单表（o）：

order_id | event_time
---------------------
301004   | 2021-10-01
301005   | 2021-10-02
301003   | 2021-10-03

连接后会产生 3商品 × 3订单 = 9行中间结果：

product_id | shop_id | release_time | order_id | event_time
-----------------------------------------------------------
8001       | 901     | 2020-01-01   | 301004   | 2021-10-01
8001       | 901     | 2020-01-01   | 301005   | 2021-10-02
8001       | 901     | 2020-01-01   | 301003   | 2021-10-03
8002       | 901     | 2020-01-01   | 301004   | 2021-10-01
8002       | 901     | 2020-01-01   | 301005   | 2021-10-02
8002       | 901     | 2020-01-01   | 301003   | 2021-10-03
8003       | 901     | 2021-09-01   | 301004   | 2021-10-01
8003       | 901     | 2021-09-01   | 301005   | 2021-10-02
8003       | 901     | 2021-09-01   | 301003   | 2021-10-03

按订单日期分组后，计算每个日期的唯一商品数：

order_date | COUNT(DISTINCT p.product_id)
---------------------------
2021-10-01 | 3 (8001,8002,8003都已上架)
2021-10-02 | 3
2021-10-03 | 3

主查询部分

SELECTdr.order_date AS dt,ROUND(COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS sale_rate,ROUND(1 - COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS unsale_rate
FROMdate_range drLEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6JOIN product_counts pc ON dr.order_date = pc.order_date
GROUP BYdr.order_date
ORDER BYdr.order_date

计算动销率和滞销率
date_range dr：国庆3天的日期（10月1日、2日、3日）
LEFT JOIN shop_products：关联7天内的销售记录
- DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6表示：
  - 0：当天
  - 6：7天前（含当天共7天）
JOIN product_counts：关联每天的商品总数

分子：COUNT(DISTINCT sp.product_id)
- 计算7天内销售过的不同商品数量
分母：AVG(pc.total_products)
- 因为pc已经按日期分组，每个日期只有一行，AVG相当于直接取值
- 表示当天已上架的商品总数
ROUND(..., 3)：保留3位小数

关键理解要点

时间窗口：每个日期的前7天（含当天）
三层数据组合：
基础日期 + 7天内销售记录 + 当日商品总数

LEFT JOIN的意义：确保无销售日也能显示
AVG的使用场景：对单值分组列的巧妙处理

自建测试数据

USE niuke_mall;-- (1)创建商品信息表
CREATE TABLE tb_product_info (id INT AUTO_INCREMENT PRIMARY KEY,product_id INT NOT NULL,shop_id INT NOT NULL,tag VARCHAR(20),in_price DECIMAL(10,2) NOT NULL,quantity INT NOT NULL,release_time DATETIME NOT NULL
);-- 插入测试数据
INSERT INTO tb_product_info (product_id, shop_id, tag, in_price, quantity, release_time) VALUES
-- 901店铺的商品(3个)
(8001, 901, '日用', 60.00, 1000, '2020-01-01 10:00:00'),  -- 长期在售商品
(8002, 901, '零食', 140.00, 500, '2020-01-01 10:00:00'), -- 长期在售商品
(8003, 901, '零食', 160.00, 500, '2021-09-01 10:00:00'), -- 较晚上架商品-- 902店铺的商品(3个)
(8004, 902, '电子', 1200.00, 200, '2021-08-15 10:00:00'), -- 高单价商品
(8005, 902, '电子', 800.00, 150, '2021-09-15 10:00:00'),  -- 中等单价商品
(8006, 902, '配件', 50.00, 1000, '2021-10-01 10:00:00'),  -- 新上架商品-- 903店铺的商品(4个)
(8007, 903, '服装', 200.00, 300, '2021-07-01 10:00:00'),  -- 夏季服装
(8008, 903, '服装', 300.00, 250, '2021-09-01 10:00:00'),  -- 秋季服装
(8009, 903, '鞋帽', 400.00, 200, '2021-09-15 10:00:00'),  -- 鞋类商品
(8010, 903, '鞋帽', 350.00, 180, '2021-10-01 10:00:00');  -- 新上架鞋类-- (2)创建订单总表
CREATE TABLE tb_order_overall (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,uid INT NOT NULL,event_time DATETIME NOT NULL,total_amount DECIMAL(10,2) NOT NULL,total_cnt INT NOT NULL,`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1-已完成'
);-- 插入测试数据
INSERT INTO tb_order_overall (order_id, uid, event_time, total_amount, total_cnt, status) VALUES
-- 国庆前订单(3个)
(301001, 101, '2021-09-28 10:00:00', 300.00, 2, 1),  -- 901店铺订单
(301002, 102, '2021-09-29 11:00:00', 450.00, 3, 1),  -- 902店铺订单
(301003, 103, '2021-09-30 14:00:00', 200.00, 1, 1),  -- 903店铺订单-- 国庆期间订单(9个)
-- 10月1日
(301004, 101, '2021-10-01 10:00:00', 170.00, 1, 1),  -- 901店铺订单
(301005, 102, '2021-10-01 11:00:00', 800.00, 1, 1),  -- 902店铺订单
(301006, 103, '2021-10-01 14:00:00', 350.00, 1, 1),  -- 903店铺订单-- 10月2日
(301007, 104, '2021-10-02 09:00:00', 300.00, 2, 1),  -- 901店铺订单
(301008, 105, '2021-10-02 10:30:00', 1200.00, 1, 1), -- 902店铺订单
(301009, 106, '2021-10-02 15:00:00', 600.00, 2, 1),  -- 903店铺订单-- 10月3日
(301010, 107, '2021-10-03 10:00:00', 235.00, 2, 1),  -- 901店铺订单
(301011, 108, '2021-10-03 11:30:00', 1600.00, 2, 1), -- 902店铺订单
(301012, 109, '2021-10-03 16:00:00', 950.00, 3, 1);  -- 903店铺订单-- (3)创建订单明细表
CREATE TABLE tb_order_detail (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,product_id INT NOT NULL,price DECIMAL(10,2) NOT NULL,cnt INT NOT NULL
);-- 插入测试数据
INSERT INTO tb_order_detail (order_id, product_id, price, cnt) VALUES
-- 国庆前订单明细
(301001, 8002, 150.00, 2),  -- 901店铺商品
(301002, 8004, 400.00, 1),  -- 902店铺商品
(301002, 8005, 800.00, 1),  -- 902店铺商品
(301002, 8006, 50.00, 1),   -- 902店铺商品
(301003, 8007, 200.00, 1),  -- 903店铺商品-- 国庆期间订单明细
-- 10月1日
(301004, 8002, 170.00, 1),  -- 901店铺商品
(301005, 8005, 800.00, 1),  -- 902店铺商品
(301006, 8010, 350.00, 1),  -- 903店铺商品-- 10月2日
(301007, 8002, 150.00, 1),  -- 901店铺商品
(301007, 8003, 150.00, 1),  -- 901店铺商品
(301008, 8004, 1200.00, 1), -- 902店铺商品
(301009, 8008, 300.00, 2),  -- 903店铺商品-- 10月3日
(301010, 8001, 85.00, 1),   -- 901店铺商品
(301010, 8003, 150.00, 1),  -- 901店铺商品
(301011, 8004, 1200.00, 1), -- 902店铺商品
(301011, 8005, 400.00, 1),  -- 902店铺商品
(301012, 8007, 200.00, 1),  -- 903店铺商品
(301012, 8008, 300.00, 1),  -- 903店铺商品
(301012, 8009, 450.00, 1);  -- 903店铺商品

参考大佬们的回答

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

 -- 定义日期范围CTE，获取2021-10-01到2021-10-03期间的所有订单日期
WITH-- 步骤1：确定要计算的日期范围（国庆3天）date_range AS (SELECT DISTINCTDATE(event_time) AS order_dateFROMtb_order_overallWHEREDATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'),-- 步骤2：找出901店铺有销售的商品和销售日期shop_products AS (SELECTDATE(a.event_time) AS order_date,b.product_idFROMtb_order_overall aJOIN tb_order_detail b ON a.order_id = b.order_idAND a.status = 1JOIN tb_product_info c ON b.product_id = c.product_idAND c.shop_id = '901'),-- 定义产品总数CTE，计算901店铺每天可销售的产品总数product_counts as (SELECTDATE(o.event_time) AS order_date,COUNT(DISTINCT p.product_id) AS total_productsFROMtb_order_overall oCROSS JOIN tb_product_info p -- 显式交叉连接WHEREp.shop_id = '901'AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0GROUP BYDATE(o.event_time))-- 主查询：计算每天的产品销售率和未销售率-- 最终计算
SELECTdr.order_date AS dt,ROUND(COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS sale_rate,ROUND(1 - COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS unsale_rate
FROMdate_range drLEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6JOIN product_counts pc ON dr.order_date = pc.order_date
GROUP BYdr.order_date
ORDER BYdr.order_date