当前位置： 首页 > news >正文

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

news 2025/7/26 8:26:09

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_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位小数

关键理解要点

  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 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

    http://www.lryc.cn/news/598307.html

    相关文章：

  1. 详解力扣高频SQL50题之197. 上升的温度【简单】
  2. 【MySQL】MySQL 事务和锁详解
  3. Redis--哨兵机制详解
  4. day20 双向链表
  5. 适配器模式——以springboot为例
  6. RK3568笔记九十一：QT环境搭建
  7. 【Java基础06】ArrayList
  8. AudioLLM 开源项目了解学习
  9. 构建企业级Docker日志驱动：将容器日志无缝发送到腾讯云CLS
  10. 新mac电脑软件安装指南（前端开发用）
  11. 2025年计算机网络与教育科学国际会议（ICCNES 2025）
  12. IntelliJ IDEA中管理多版本Git子模块的完整指南
  13. Elasticsearch安全审计日志设置与最佳实践
  14. 从零构建：Jenkins与Kubernetes集成的完整指南
  15. 福佑储能轴流风扇对储能安全的重要影响
  16. 陪诊小程序系统开发：开启医疗陪护新时代
  17. JAVA图文短视频交友+自营商城系统源码支持小程序+Android+IOS+H5
  18. 盲盒抽谷机小程序：二次元经济的“社交裂变引擎”如何引爆用户增长？
  19. Apache 消息队列分布式架构与原理
  20. 移动端自动化Appium框架
  21. [数据结构]#7 哈希表
  22. 2025年6月GESP（C++六级）：学习小组
  23. OpenMed 项目深度分析：推动医疗 NLP 领域的开源革命
  24. GoLand 项目从 0 到 1：第二天 —— 数据库自动化
  25. 综合实验（4）
  26. 独家｜百度副总裁尚国斌即将离职，此前统筹百度地图；行业搜索及智能体业务总经理谢天转岗IDG
  27. Vue-23-通过flask接口提供的数据使用plotly.js绘图(二)
  28. Unity 多人游戏框架学习系列九
  29. 图机器学习（19）——金融数据分析
  30. 2026第35届中国国际健康产业博览会：探寻大健康时代的未来！