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