【背景】 连锁门店在每晚库存快照日需要快速评估近 7 天销量与库存覆盖天数（库存 ÷ 近 7 天日均销量），并挑出每家门店近 7 天最畅销的 Top3 SKU 以指导补货。请你基于门店信息、库存快照与日销量，完成该分析。 【原始表】 1)store_info_（门店主数据） store_idINT 主键，0 store_nameVARCHAR(64) 非空 cityVARCHAR(32) 非空 2)store_stock_（库存快照） store_idINT 非空 sku_idVARCHAR(32) 非空 stock_qtyINT 非空，≥0 snapshot_dateDATE 非空（每日 1 次快照；同一门店同一 SKU 同一天只有 1 行） 3)sales_daily_（门店-商品-日销量） store_idINT 非空 sku_idVARCHAR(32) 非空 sale_dateDATE 非空 qtyINT 非空，≥0（当日销量） 【要求】 以最新库存快照日期为统计基准日，计算每家门店在该快照日的 Top3 商品（近 7 天销量排行，统计窗口为"基准日向前 7 天，含基准日"），输出： store_id、store_name、city、sku_id last7d_qty：近 7 天销量和（无销量则为 0） avg_daily_qty：ROUND(last7d_qty7, 2) stock_qty：该快照日库存 coverage_days：CASE WHEN avg_daily_qty0 THEN ROUND(stock_qtyavg_daily_qty, 1) ELSE NULL END rank_in_store：按 last7d_qty DESC、sku_id ASC 的门店内排名（使用 ROW_NUMBER()） 只保留 rank_in_store 【示例输入】 store_info_ +----------+-----------+--------+ store_id store_name city +----------+-----------+--------+ 1 EastShop Beijing 2 WestShop Shanghai +----------+-----------+--------+ store_stock_（最新快照日期为 2024-08-08） +----------+--------+-----------+---------------+ store_id sku_id stock_qty snapshot_date +----------+--------+-----------+---------------+ 1 sku100 50 2024-08-08 1 sku101 20 2024-08-08 1 sku102 5 2024-08-08 2 sku100 10 2024-08-08 2 sku200 40 2024-08-08 +----------+--------+-----------+---------------+ sales_daily_（基准日 2024-08-08，统计窗口：2024-08-02 ~ 2024-08-08） +----------+--------+------------+-----+ store_id sku_id sale_date qty +----------+--------+------------+-----+ 1 sku100 2024-08-02 5 1 sku100 2024-08-03 6 1 sku100 2024-08-08 9 1 sku101 2024-08-04 2 2 sku100 2024-08-02 3 2 sku100 2024-08-07 2 2 sku200 2024-08-05 10 2 sku200 2024-08-06 8 +----------+--------+------------+-----+ 【示例输出】 +----------+-----------+----------+--------+------------+---------------+-----------+---------------+---------------+ store_id store_name city sku_id last7d_qty avg_daily_qty stock_qty coverage_days rank_in_store +----------+-----------+----------+--------+------------+---------------+-----------+---------------+---------------+ 1 EastShop Beijing sku100 20 2.86 50 17.5 1 1 EastShop Beijing sku101 2 0.29 20 69.0 2 1 EastShop Beijing sku102 0 0.00 5 NULL 3 2 WestShop Shanghai sku200 18 2.57 40 15.6 1 2 WestShop Shanghai sku100 5 0.71 10 14.1 2 +----------+-----------+----------+--------+------------+---------------+-----------+---------------+---------------+
示例1

输入

DROP TABLE IF EXISTS sales_daily_;
DROP TABLE IF EXISTS store_stock_;
DROP TABLE IF EXISTS store_info_;

CREATE TABLE store_info_(
  store_id INT PRIMARY KEY,
  store_name VARCHAR(64) NOT NULL,
  city VARCHAR(32) NOT NULL
);

CREATE TABLE store_stock_(
  store_id INT NOT NULL,
  sku_id VARCHAR(32) NOT NULL,
  stock_qty INT NOT NULL,
  snapshot_date DATE NOT NULL,
  PRIMARY KEY(store_id, sku_id, snapshot_date)
);

CREATE TABLE sales_daily_(
  store_id INT NOT NULL,
  sku_id VARCHAR(32) NOT NULL,
  sale_date DATE NOT NULL,
  qty INT NOT NULL,
  PRIMARY KEY(store_id, sku_id, sale_date)
);

INSERT INTO store_info_ VALUES
(1,'EastShop','Beijing'),
(2,'WestShop','Shanghai');

INSERT INTO store_stock_ VALUES
(1,'sku100',50,'2024-08-08'),
(1,'sku101',20,'2024-08-08'),
(1,'sku102', 5,'2024-08-08'),
(2,'sku100',10,'2024-08-08'),
(2,'sku200',40,'2024-08-08');

INSERT INTO sales_daily_ VALUES
(1,'sku100','2024-08-02',5),
(1,'sku100','2024-08-03',6),
(1,'sku100','2024-08-08',9),
(1,'sku101','2024-08-04',2),
(2,'sku100','2024-08-02',3),
(2,'sku100','2024-08-07',2),
(2,'sku200','2024-08-05',10),
(2,'sku200','2024-08-06',8);

输出

store_id|store_name|city|sku_id|last7d_qty|avg_daily_qty|stock_qty|coverage_days|rank_in_store
1|EastShop|Beijing|sku100|20|2.86|50|17.5|1
1|EastShop|Beijing|sku101|2|0.29|20|69.0|2
1|EastShop|Beijing|sku102|0|0.00|5|None|3
2|WestShop|Shanghai|sku200|18|2.57|40|15.6|1
2|WestShop|Shanghai|sku100|5|0.71|10|14.1|2
加载中...