网站导航
> 技术文档 > SQL173 店铺901国庆期间的7日动销率和滞销率

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

网友: 技术文档 2025-07-26 20:15:18

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. 核心逻辑

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

SQL代码解析

CTE部分

date_range表：获取平台有销售记录的日期
 date_range AS ( SELECT DISTINCT DATE(event_time) AS order_date FROM tb_order_overall WHERE DATE(event_time) BETWEEN \'2021-10-01\' AND \'2021-10-03\' )
  • 从订单总表中筛选出国庆3天内有销售记录的日期
  • 使用distinct确保日期不重复
shop_products 表：获取店铺901的销售记录
shop_products AS ( SELECT DATE(a.event_time) AS order_date, b.product_id FROM tb_order_overall a JOIN tb_order_detail b ON a.order_id = b.order_id AND a.status = 1 JOIN 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 ( SELECT DATE(o.event_time) AS order_date, COUNT(DISTINCT p.product_id) AS total_productsFROM tb_order_overall o CROSS JOIN tb_product_info p -- 显式交叉连接WHERE p.shop_id = \'901\' AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0GROUP BY DATE(o.event_time))
  • 计算店铺901在每个订单日期时已经上架的商品总数
  • DATEDIFF(...) >= 0：只保留商品上架时间早于或等于订单日期的记录
  • 使用逗号,表示的是隐式交叉连接​（CROSS JOIN），这会生成两个表的笛卡尔积

商品表​（p）：

product_id | shop_id | release_time----------------------------8001 | 901 | 2020-01-018002 | 901 | 2020-01-018003 | 901 | 2021-09-01

​订单表​（o）：

order_id | event_time---------------------301004 | 2021-10-01301005 | 2021-10-02301003 | 2021-10-03

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

product_id | shop_id | release_time | order_id | event_time-----------------------------------------------------------8001 | 901 | 2020-01-01 | 301004 | 2021-10-018001 | 901 | 2020-01-01 | 301005 | 2021-10-028001 | 901 | 2020-01-01 | 301003 | 2021-10-038002 | 901 | 2020-01-01 | 301004 | 2021-10-018002 | 901 | 2020-01-01 | 301005 | 2021-10-028002 | 901 | 2020-01-01 | 301003 | 2021-10-038003 | 901 | 2021-09-01 | 301004 | 2021-10-018003 | 901 | 2021-09-01 | 301005 | 2021-10-028003 | 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 | 32021-10-03 | 3

主查询部分

SELECT dr.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_rateFROM date_range dr LEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6 JOIN product_counts pc ON dr.order_date = pc.order_dateGROUP BY dr.order_dateORDER BY dr.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位小数

关键理解要点

  1. ​时间窗口​：每个日期的前7天（含当天）
  2. ​三层数据组合​：
    • 基础日期 + 7天内销售记录 + 当日商品总数
  3. ​LEFT JOIN的意义​：确保无销售日也能显示
  4. ​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日动销率和滞销率#_牛客博客

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

     -- 定义日期范围CTE，获取2021-10-01到2021-10-03期间的所有订单日期WITH -- 步骤1：确定要计算的日期范围（国庆3天） date_range AS ( SELECT DISTINCT DATE(event_time) AS order_date FROM tb_order_overall WHERE DATE(event_time) BETWEEN \'2021-10-01\' AND \'2021-10-03\' ), -- 步骤2：找出901店铺有销售的商品和销售日期 shop_products AS ( SELECT DATE(a.event_time) AS order_date, b.product_id FROM tb_order_overall a JOIN tb_order_detail b ON a.order_id = b.order_id AND a.status = 1 JOIN tb_product_info c ON b.product_id = c.product_id AND c.shop_id = \'901\' ), -- 定义产品总数CTE，计算901店铺每天可销售的产品总数 product_counts as ( SELECT DATE(o.event_time) AS order_date, COUNT(DISTINCT p.product_id) AS total_products FROM tb_order_overall o CROSS JOIN tb_product_info p -- 显式交叉连接 WHERE p.shop_id = \'901\' AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0 GROUP BY DATE(o.event_time) ) -- 主查询：计算每天的产品销售率和未销售率 -- 最终计算SELECT dr.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_rateFROM date_range dr LEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6 JOIN product_counts pc ON dr.order_date = pc.order_dateGROUP BY dr.order_dateORDER BY dr.order_date
    网络标签：

    相关问题