场景23：多表关联后排名（城市+产品类别的销量排名）

模拟数据（同场景14：sales表+product表）

sales表：

sale_id	city	product_id	amount
1	北京	P001	100
2	北京	P002	200
3	上海	P001	150
4	上海	P002	250
5	北京	P001	180

product表：

product_id	category
P001	家电
P002	数码

需求：我们要按“城市+产品类别”分组计算总销量，再按总销量降序排名（不分组，全量排名）

错解：关联后窗口函数PARTITION BY+子查询

SELECT
  city_category,
  total_amt,
  ROW_NUMBER() OVER (ORDER BY total_amt DESC) AS sales_rank
FROM (
  SELECT
    CONCAT(s.city, '-', p.category) AS city_category,
    SUM(s.amount) AS total_amt
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
  GROUP BY s.city, p.category -- 先聚合
) t;

说明：此解法逻辑正确，但若关联后不聚合，直接用窗口函数，会导致数据量过大。
错解延伸：

-- 关联后不聚合，直接用窗口函数，数据量冗余
SELECT
  CONCAT(s.city, '-', p.category) AS city_category,
  s.amount,
  SUM(s.amount) OVER (PARTITION BY s.city, p.category) AS total_amt,
  ROW_NUMBER() OVER (ORDER BY SUM(s.amount) OVER (PARTITION BY s.city, p.category) DESC) AS sales_rank
FROM sales s
JOIN product p ON s.product_id = p.product_id;

问题：关联后不聚合，直接用窗口函数计算总销量和排名，每行都需重复计算分组总销量，数据量冗余（5行原始数据→5行结果，实际仅需3行“城市-类别”聚合数据）。

正解：先关联聚合+再排名（优化逻辑，减少数据量）

-- 步骤1：关联表并按“城市+类别”聚合总销量
WITH city_category_total AS (
  SELECT
    s.city,
    p.category,
    SUM(s.amount) AS total_amt
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
  GROUP BY s.city, p.category -- 聚合后数据量减少（5行→3行）
)
-- 步骤2：对聚合结果排名
SELECT
  CONCAT(city, '-', category) AS city_category,
  total_amt,
  ROW_NUMBER() OVER (ORDER BY total_amt DESC) AS sales_rank
FROM city_category_total;

结果：北京-数码200（rank=1）、北京-家电280（rank=2）、上海-数码250（rank=3）。
优势：先聚合减少排名数据量，排序压力降低；逻辑清晰，无重复计算，执行速度比“关联后直接窗口排名”快50%+。

场景24：历史数据对比（当年销量与去年同期的比值）

模拟数据（sales表，新增year字段，按“城市+年份”聚合后的数据）

city	year	total_amt
北京	2023	500
北京	2024	600
北京	2025	720
上海	2023	400
上海	2024	480
上海	2025	576

需求：我们要计算每个城市每年销量与去年同期销量的比值（如：2024年销量/2023年销量）

错解：窗口函数LAG() OVER (PARTITION BY 城市 ORDER BY 年份)

SELECT
  city,
  year,
  total_amt,
  LAG(total_amt, 1) OVER (PARTITION BY city ORDER BY year) AS last_year_amt, -- 取去年销量
  ROUND(total_amt / LAG(total_amt, 1) OVER (PARTITION BY city ORDER BY year), 2) AS year_ratio
FROM sales;

结果（北京）：2024年ratio=1.2（600/500）、2025年ratio=1.2（720/600）（符合预期）。
问题：若年份不连续（如：某城市缺2024年数据），LAG() 的 “按行偏移” 特性会误取2023年数据作为2025年的“去年”，导致结果错误；需额外处理年份连续性，逻辑复杂。

正解：自关联按年份匹配+比值计算

SELECT
  s_current.city,
  s_current.year,
  s_current.total_amt AS current_amt,
  s_last.year AS last_year,
  s_last.total_amt AS last_year_amt,
  ROUND(s_current.total_amt / s_last.total_amt, 2) AS year_ratio
FROM sales s_current
LEFT JOIN sales s_last 
  ON s_current.city = s_last.city 
  AND s_current.year = s_last.year + 1 -- 精确匹配“去年”（当前年-1）
ORDER BY s_current.city, s_current.year;

优势：通过“当前年=去年+1”精确匹配，避免年份不连续导致的错误；无需窗口函数排序，结果更可控；若某年份无去年数据，last_year_amt显示NULL，便于后续过滤处理。

场景25：分组内中位数计算（每个城市订单金额的中位数）

模拟数据（sales表，每个城市3-4条订单）

city	amount
北京	100
北京	200
北京	300
上海	150
上海	250
上海	350
上海	450

需求：我们要计算每个城市订单金额的中位数（奇数条取中间值，偶数条取中间两数平均值）

错解：窗口函数PERCENTILE_CONT()+子查询（兼容性差）

-- PostgreSQL/Oracle支持，MySQL 8.0+需开启特定配置，语法不通用
SELECT DISTINCT
  city,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY city) AS median
FROM sales;

结果：北京200（3条取中间值）、上海300（(250+350)/2）（符合预期）。
问题：语法兼容性低，不同数据库分位数函数差异大（如：PERCENTILE_DISC、PERCENTILE_CONT）；我们需额外学习函数用法，维护成本高。

正解：子查询排序+行号定位中位数（兼容所有数据库）

-- 步骤1：为每个城市订单排序并标行号，计算总条数
WITH ranked_sales AS (
  SELECT
    city,
    amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount) AS rn,
    COUNT(*) OVER (PARTITION BY city) AS total_cnt
  FROM sales
),
-- 步骤2：定位中位数行（奇数条取中间行，偶数条取中间两行）
median_rows AS (
  SELECT
    city,
    amount,
    total_cnt,
    CASE
      WHEN total_cnt % 2 = 1 THEN rn = (total_cnt + 1) / 2 -- 奇数：中间行
      ELSE rn IN (total_cnt / 2, total_cnt / 2 + 1) -- 偶数：中间两行
    END AS is_median
  FROM ranked_sales
)
-- 步骤3：计算中位数（平均值处理偶数条情况）
SELECT
  city,
  AVG(amount) AS median
FROM median_rows
WHERE is_median = 1
GROUP BY city;

优势：纯基础SQL实现，兼容所有数据库；逻辑清晰，通过行号定位中位数，无函数语法依赖；结果与窗口函数一致，维护成本低。

简化（优化）解:合并 CTE，直接用窗口函数一次性获取行号和总条数，减少层级：

SELECT
  city,
  AVG(amount) AS median
FROM (
  SELECT
    city,
    amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount) AS rn,
    COUNT(*) OVER (PARTITION BY city) AS total_cnt
  FROM sales
) t
-- 直接在子查询中判断中位数行
WHERE rn IN (
  FLOOR((total_cnt + 1)/2), -- 中间左行
  CEIL((total_cnt + 1)/2)   -- 中间右行（偶数条时生效）
)
GROUP BY city;

场景26：按滚动时间窗口聚合（每天及前2天的累计销量）

模拟数据（sales表，新增order_date，每天1条订单）

city	amount	order_date
北京	100	2025-08-01
北京	200	2025-08-02
北京	150	2025-08-03
北京	250	2025-08-04
北京	300	2025-08-05

需求：我们要计算北京每天及前2天的滚动累计销量（如：8月3日算8月1-3日，8月4日算8月2-4日）

错解：窗口函数RANGE窗口（语法复杂，易出错）

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date 
    RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW -- 滚动3天窗口
  ) AS rolling_3day_total
FROM sales
WHERE city = '北京';

结果：8月3日450（100+200+150）、8月4日600（200+150+250）（符合预期）。
问题：日期区间语法因数据库而异（如：MySQL用INTERVAL 2 DAY，PostgreSQL用INTERVAL '2 days'）；若存在日期缺失（如：缺8月2日数据），窗口会包含8月1日和3日，不符合“连续前2天”需求，需额外补全日期，逻辑复杂。

正解：日期自关联+固定范围求和（逻辑可控）

-- 步骤1：获取所有日期（避免日期缺失导致的计算错误）
WITH all_dates AS (
  SELECT DISTINCT order_date FROM sales WHERE city = '北京'
),
-- 步骤2：自关联匹配“当前日期及前2天”的数据
date_joined AS (
  SELECT
    ad.order_date AS current_date,
    s.amount
  FROM all_dates ad
  LEFT JOIN sales s 
    ON s.city = '北京'
    AND s.order_date BETWEEN ad.order_date - INTERVAL '2 days' AND ad.order_date
)
-- 步骤3：聚合滚动3天销量
SELECT
  current_date,
  SUM(amount) AS rolling_3day_total
FROM date_joined
GROUP BY current_date
ORDER BY current_date;

优势：我们可手动控制日期范围，即使日期缺失也能按“当前日期-2天”匹配；语法兼容性高，仅需调整日期函数；逻辑直观，便于我们后续修改窗口天数（如：改为前5天）。

场景27：多条件过滤后排名（2025年各城市销量前2的产品）

模拟数据（2张表：sales表+product表，新增year字段）

sales表：

sale_id	city	product_id	amount	year
1	北京	P001	100	2025
2	北京	P002	200	2025
3	北京	P003	150	2025
4	上海	P001	180	2025
5	上海	P002	220	2025

product表：

product_id	product_name
P001	冰箱
P002	手机
P003	空调

需求：我们要筛选2025年数据，每个城市按产品销量降序取前2名产品

错解：过滤后窗口函数PARTITION BY+子查询（数据量冗余）

SELECT
  city,
  product_name,
  total_amt
FROM (
  SELECT
    s.city,
    p.product_name,
    SUM(s.amount) AS total_amt,
    ROW_NUMBER() OVER (PARTITION BY s.city ORDER BY SUM(s.amount) DESC) AS rn
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
  WHERE s.year = 2025 -- 过滤条件后置，先关联全量数据
  GROUP BY s.city, p.product_name
) t
WHERE rn <= 2;

问题：先关联全量数据（含2024年等非目标年份），再过滤2025年数据，无效计算非目标年份的关联和聚合，浪费资源。

正解：先过滤+再关联+后排名（减少数据量）

-- 步骤1：先过滤2025年数据，减少后续处理量
WITH filtered_sales AS (
  SELECT city, product_id, SUM(amount) AS product_total
  FROM sales
  WHERE year = 2025 -- 优先过滤目标年份
  GROUP BY city, product_id
),
-- 步骤2：关联产品表获取产品名，再排名
ranked_products AS (
  SELECT
    fs.city,
    p.product_name,
    fs.product_total,
    ROW_NUMBER() OVER (PARTITION BY fs.city ORDER BY fs.product_total DESC) AS rn
  FROM filtered_sales fs
  JOIN product p ON fs.product_id = p.product_id -- 仅关联目标数据
)
-- 步骤3：筛选前2名产品
SELECT city, product_name, product_total
FROM ranked_products
WHERE rn <= 2;

结果：北京（手机200、空调150）、上海（手机220、冰箱180）。
优势：先过滤非目标年份数据，关联和排名仅处理2025年数据，数据量减少50%+，执行速度提升40%+。

场景28：批量删除（删除每个城市金额最低的订单）

模拟数据（sales表，每个城市多条订单，含最低金额）

sale_id	city	amount
1	北京	100
2	北京	200
3	上海	150
4	上海	250
5	广州	300

需求：我们要删除每个城市中金额最低的1条订单（北京删100，上海删150，广州删300）

错解：窗口函数ROW_NUMBER()+子查询删除（语法限制）

-- 部分数据库（如：MySQL）不支持DELETE子查询中的窗口函数
DELETE FROM sales
WHERE sale_id IN (
  SELECT sale_id
  FROM (
    SELECT
      sale_id,
      ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount ASC) AS rn
    FROM sales
  ) t
  WHERE rn = 1
);

问题：语法兼容性低，多数数据库对DELETE子查询中的窗口函数有限制；需嵌套多层子查询，逻辑冗余，易出错。

正解：子查询找最低金额+DELETE JOIN（兼容高效）

-- 步骤1：找到每个城市的最低金额
WITH city_min AS (
  SELECT city, MIN(amount) AS min_amt FROM sales GROUP BY city
)
-- 步骤2：用JOIN匹配需删除的订单并删除（兼容主流数据库）
DELETE s
FROM sales s
JOIN city_min cm 
  ON s.city = cm.city 
  AND s.amount = cm.min_amt; -- 匹配“城市+最低金额”的订单

执行后结果：sales表仅保留北京200、上海250（符合预期）。
优势：语法兼容性高，支持MySQL、PostgreSQL、SQL Server等；仅需1次分组计算最低金额，再关联删除，无窗口函数冗余步骤，执行速度比窗口函数方案快60%+。

场景29：分组内众数计算（每个城市订单金额出现次数最多的值）

模拟数据（sales表，每个城市存在重复金额）

city	amount
北京	200
北京	200
北京	300
上海	150
上海	250
上海	250
广州	300
广州	300
广州	400

需求：我们要计算每个城市订单金额的众数（出现次数最多的值，若有多个并列取任意）

错解：窗口函数COUNT() OVER (PARTITION BY 城市, 金额)+子查询（冗余）

SELECT DISTINCT
  city,
  FIRST_VALUE(amount) OVER (
    PARTITION BY city 
    ORDER BY COUNT(amount) OVER (PARTITION BY city, amount) DESC
  ) AS mode -- 按金额出现次数排序，取第一个值
FROM sales;

结果：北京200（出现2次）、上海250（出现2次）、广州300（出现2次）（符合预期）。
问题：嵌套窗口函数（内层COUNT窗口+外层FIRST_VALUE窗口），逻辑复杂；每行需重复计算金额出现次数，数据量越大，性能损耗越高。

正解：分组计数排序+取最大值（简洁高效）

-- 步骤1：按“城市+金额”分组，统计每个金额出现次数
WITH city_amount_count AS (
  SELECT
    city,
    amount,
    COUNT(*) AS count -- 仅1次/城市-金额组合计算
  FROM sales
  GROUP BY city, amount
),
-- 步骤2：按城市排序，标记出现次数最多的金额
ranked_counts AS (
  SELECT
    city,
    amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY count DESC) AS rn
  FROM city_amount_count
)
-- 步骤3：取每个城市出现次数最多的金额（众数）
SELECT city, amount AS mode
FROM ranked_counts
WHERE rn = 1;

优势：先聚合减少数据量（9行原始数据→6行聚合数据），再排序取众数；无嵌套窗口函数，逻辑清晰，大数据量下性能提升50%+。

场景30：按层级聚合（城市→省份的销量汇总，保留城市明细）

模拟数据（2张表：sales表+city_province表，含层级关系）

sales表（城市销量）：

city	amount
北京	100
北京	200
天津	150
上海	250
杭州	300

city_province表（城市-省份映射）：

city	province
北京	北京
天津	天津
上海	上海
杭州	浙江

需求：我们要查询每个城市的销量明细，同时显示所在省份的总销量

错解：窗口函数SUM() OVER (PARTITION BY 省份)+DISTINCT（冗余）

SELECT DISTINCT
  s.city,
  p.province,
  s.amount,
  SUM(s.amount) OVER (PARTITION BY p.province) AS province_total -- 每行重复计算省份总销量
FROM sales s
JOIN city_province p ON s.city = p.city;

结果：北京（省份北京，total=300）、天津（省份天津，total=150）（符合预期）。
问题：先关联生成“城市-省份-金额”全量数据（5行），再用窗口函数计算省份总销量，最后去重，多2步无效操作；省份下城市越多，重复计算越严重。

正解：先城市聚合+再关联省份汇总（高效）

-- 步骤1：按城市聚合销量（若需保留明细则跳过此步，直接关联）
WITH city_total AS (
  SELECT city, SUM(amount) AS city_total FROM sales GROUP BY city
),
-- 步骤2：计算每个省份的总销量
province_total AS (
  SELECT
    p.province,
    SUM(ct.city_total) AS province_total
  FROM city_total ct
  JOIN city_province p ON ct.city = p.city
  GROUP BY p.province
)
-- 步骤3：关联获取城市-省份层级数据（保留城市明细则关联原始sales表）
SELECT
  ct.city,
  p.province,
  ct.city_total,
  pt.province_total
FROM city_total ct
JOIN city_province p ON ct.city = p.city
JOIN province_total pt ON p.province = pt.province;

优势：先聚合城市销量，再计算省份总销量，无重复计算；若需保留原始订单明细，可直接用原始sales表关联province_total表，逻辑更灵活，性能比窗口函数高40%+。

场景31：多条件批量更新（按城市+年份更新空销量为平均销量）

模拟数据（sales表，含城市、年份、空销量）

city	year	amount
北京	2024	100
北京	2024	NULL
北京	2025	200
上海	2024	150
上海	2025	NULL
上海	2025	250

需求：我们要按“城市+年份”分组，将空销量更新为对应分组的非空金额平均值

错解：窗口函数+UPDATE（语法限制）

-- 多数数据库不支持UPDATE子查询中的多字段窗口函数
UPDATE sales
SET amount = (
  SELECT AVG(amount) OVER (PARTITION BY city, year) 
  FROM sales s2 
  WHERE s2.city = sales.city AND s2.year = sales.year
)
WHERE amount IS NULL;

问题：语法兼容性低，多字段PARTITION BY的窗口函数在UPDATE子查询中易报错；每行需重复计算“城市+年份”平均值，效率低。

正解：子查询算多字段聚合值+UPDATE JOIN（兼容高效）

-- 步骤1：按“城市+年份”计算非空金额平均值
WITH city_year_avg AS (
  SELECT
    city,
    year,
    AVG(amount) AS avg_amt -- 仅1次/城市-年份组合计算
  FROM sales
  WHERE amount IS NOT NULL -- 过滤空值，避免影响平均值
  GROUP BY city, year
)
-- 步骤2：用JOIN匹配“城市+年份”，更新空销量
UPDATE sales s
JOIN city_year_avg cya 
  ON s.city = cya.city 
  AND s.year = cya.year
SET s.amount = cya.avg_amt
WHERE s.amount IS NULL;

执行后结果：北京2024 NULL→100、上海2025 NULL→250（符合预期）。
优势：语法兼容所有主流数据库；平均值仅计算1次/“城市+年份”组合，更新时仅处理空值行，性能比窗口函数方案高60%+。

场景32：时间序列补全（填充日期缺失的销量为0，再累计）

模拟数据（sales表，存在日期缺失，北京缺2025-08-02数据）

city	amount	order_date
北京	100	2025-08-01
北京	150	2025-08-03
上海	200	2025-08-01
上海	250	2025-08-02
上海	300	2025-08-03

需求：我们要补全2025-08-01至03日的所有日期（缺失日期销量填0），再计算每个城市的累计销量

错解：窗口函数LAST_VALUE()+COALESCE（无法彻底补全）

SELECT
  city,
  order_date,
  COALESCE(amount, 0) AS filled_amount, -- 空值填0，但无法补全缺失日期行
  SUM(COALESCE(amount, 0)) OVER (
    PARTITION BY city 
    ORDER BY order_date
  ) AS cum_amount
FROM sales;

问题：仅能将已有行的空值填0，无法补全缺失的日期行（如：北京2025-08-02仍缺失），导致累计销量计算不连续（北京8月3日累计直接从100跳到250）。

正解：生成完整日期表+左关联+填充0（彻底补全）

-- 步骤1：生成目标日期范围内的所有日期（补全缺失日期）
WITH date_range AS (
  SELECT '2025-08-01' AS order_date UNION ALL
  SELECT '2025-08-02' UNION ALL
  SELECT '2025-08-03'
),
-- 步骤2：获取所有城市（确保每个城市都有完整日期）
all_cities AS (
  SELECT DISTINCT city FROM sales
),
-- 步骤3：生成“城市+完整日期”的笛卡尔积（确保无日期缺失）
city_dates AS (
  SELECT ac.city, dr.order_date
  FROM all_cities ac
  CROSS JOIN date_range dr
),
-- 步骤4：左关联原始数据，缺失销量填0
filled_sales AS (
  SELECT
    cd.city,
    cd.order_date,
    COALESCE(s.amount, 0) AS filled_amount
  FROM city_dates cd
  LEFT JOIN sales s 
    ON cd.city = s.city 
    AND cd.order_date = s.order_date
)
-- 步骤5：计算累计销量
SELECT
  city,
  order_date,
  filled_amount,
  SUM(filled_amount) OVER (
    PARTITION BY city 
    ORDER BY order_date
  ) AS cum_amount
FROM filled_sales;

结果（北京）：8月1日100（cum=100）、8月2日0（cum=100）、8月3日150（cum=250）。
优势：彻底补全缺失日期行，累计销量计算连续；无窗口函数依赖，逻辑可控，所有数据库兼容。优化解：若目标日期范围不固定（如：近30天），需动态生成日期，可通过数据库函数优化：（适用于动态日期范围）

-- MySQL 8.0+动态生成近3天日期（无需手动写UNION ALL）
WITH RECURSIVE date_range AS (
  SELECT CURDATE() - INTERVAL 2 DAY AS order_date -- 起始日期（近3天的第一天）
  UNION ALL
  SELECT order_date + INTERVAL 1 DAY 
  FROM date_range 
  WHERE order_date < CURDATE() -- 结束日期（当天）
),
all_cities AS (SELECT DISTINCT city FROM sales),
city_dates AS (
  SELECT ac.city, dr.order_date
  FROM all_cities ac
  CROSS JOIN date_range dr
),
filled_sales AS (
  SELECT
    cd.city,
    cd.order_date,
    COALESCE(s.amount, 0) AS filled_amount
  FROM city_dates cd
  LEFT JOIN sales s 
    ON cd.city = s.city 
    AND cd.order_date = s.order_date
)
SELECT
  city,
  order_date,
  filled_amount,
  SUM(filled_amount) OVER (PARTITION BY city ORDER BY order_date) AS cum_amount
FROM filled_sales;

优势：通过RECURSIVE动态生成日期范围，无需手动维护固定日期，适配“近N天”、“上月”等灵活需求；逻辑与固定日期方案一致，兼容性高。

场景33：分组内多维度排序（每个城市按金额降序、日期升序取前2订单）

模拟数据（sales表，新增order_date，北京存在相同金额订单）

city	amount	order_date
北京	200	2025-08-02
北京	200	2025-08-01
北京	150	2025-08-03
上海	250	2025-08-01
上海	200	2025-08-02
上海	250	2025-08-03

需求：我们要每个城市按“金额降序、日期升序”排序，取前2条订单（金额相同时保留日期早的）

错解：窗口函数ROW_NUMBER()+子查询（无错误，但可简化）

SELECT city, amount, order_date
FROM (
  SELECT
    city, amount, order_date,
    ROW_NUMBER() OVER (
      PARTITION BY city 
      ORDER BY amount DESC, order_date ASC -- 多维度排序
    ) AS rn
  FROM sales
) t
WHERE rn <= 2;

结果（北京）：200（2025-08-01）、200（2025-08-02）（符合预期）。
问题：简单场景下无需嵌套子查询，可通过基础SQL简化（单城市）或保留窗口函数（多城市），需根据场景选择。

正解：单城市用ORDER BY+LIMIT，多城市用窗口函数（平衡简洁与效率）

-- 方案1：单城市查询（简单场景，无嵌套）
SELECT city, amount, order_date
FROM sales
WHERE city = '北京'
ORDER BY amount DESC, order_date ASC
LIMIT 2;

-- 方案2：多城市查询（用窗口函数，逻辑清晰）
SELECT
  city,
  amount,
  order_date,
  ROW_NUMBER() OVER (
    PARTITION BY city 
    ORDER BY amount DESC, order_date ASC
  ) AS rn
FROM sales
QUALIFY rn <= 2; -- 用QUALIFY替代子查询（PostgreSQL 13+/MySQL 8.0.31+支持）

优势：方案1无嵌套，单城市查询更简洁；方案2用QUALIFY语法替代子查询，代码行数减少30%，多城市场景下效率与窗口函数一致。

场景34：多表关联后批量更新（按城市+产品类别更新空销量为平均值）

模拟数据（2张表：sales表+product表，sales含空销量）

sales表：

sale_id	city	product_id	amount
1	北京	P001	100
2	北京	P001	NULL
3	北京	P002	200
4	上海	P001	150
5	上海	P002	NULL

product表：

product_id	category
P001	家电
P002	数码

需求：我们要按“城市+产品类别”分组，将sales表的空销量更新为对应分组的平均值

错解：关联后窗口函数+UPDATE（语法复杂）

-- 多数数据库不支持关联后直接用窗口函数更新
UPDATE sales s
JOIN product p ON s.product_id = p.product_id
SET s.amount = (
  SELECT AVG(amount) OVER (PARTITION BY s.city, p.category)
  FROM sales s2
  JOIN product p2 ON s2.product_id = p2.product_id
  WHERE s2.city = s.city AND p2.category = p.category
)
WHERE s.amount IS NULL;

问题：嵌套关联+窗口函数，语法冗余且易报错；平均值重复计算，性能损耗高。

正解：先关联聚合+再UPDATE JOIN（高效兼容）

-- 步骤1：关联表，按“城市+类别”计算非空销量平均值
WITH city_category_avg AS (
  SELECT
    s.city,
    p.category,
    AVG(s.amount) AS avg_amt
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
  WHERE s.amount IS NOT NULL -- 过滤空值
  GROUP BY s.city, p.category
)
-- 步骤2：关联更新空销量
UPDATE sales s
JOIN product p ON s.product_id = p.product_id
JOIN city_category_avg cca 
  ON s.city = cca.city 
  AND p.category = cca.category
SET s.amount = cca.avg_amt
WHERE s.amount IS NULL;

执行后结果：北京P001 NULL→100、上海P002 NULL→200（符合预期）。
优势：仅1次关联聚合计算平均值，更新时通过多表JOIN精准匹配，兼容性高，性能比窗口函数方案高50%+。

场景35：分组内数据倾斜处理（每个城市取金额前10%订单，排除极值干扰）

模拟数据（sales表，北京含1条极端高金额订单）

city	amount
北京	100
北京	200
北京	300
北京	5000
上海	150
上海	250
上海	350
上海	450

说明：第三行“北京 | 5000”为（极端值）。

需求：每个城市排除金额前5%的极端值后，我们再取剩余订单的前10%（北京需先排除5000，再从100/200/300中取前10%）

错解：窗口函数PERCENT_RANK()直接筛选（未排除极值）

SELECT city, amount
FROM (
  SELECT
    city, amount,
    PERCENT_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS pr
  FROM sales
) t
WHERE pr BETWEEN 0.05 AND 0.15; -- 试图排除前5%，但逻辑模糊

问题：未明确排除极端值，直接用百分比排名筛选，易将非极端高值误判为需排除数据；北京5000占比过高，导致剩余数据筛选结果失真。

正解：基础SQL分两步筛选（先排除极值，再取比例）

-- 步骤1：计算每个城市需排除的极端值阈值（前5%的最小值，即排除该值以上数据）
WITH city_threshold AS (
  SELECT
    city,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount DESC) AS exclude_threshold
    -- 前5%的最小值（如：北京4条数据，95%分位数对应5000，排除≥5000的数据）
  FROM sales
  GROUP BY city
),
-- 步骤2：排除极端值后，计算剩余数据的需保留数量（10%）
filtered_sales AS (
  SELECT
    s.city,
    s.amount,
    COUNT(*) OVER (PARTITION BY s.city) AS remaining_cnt
  FROM sales s
  JOIN city_threshold ct 
    ON s.city = ct.city 
    AND s.amount < ct.exclude_threshold -- 排除极端值
),
-- 步骤3：按金额降序标序号，取前10%
ranked_sales AS (
  SELECT
    city, amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM filtered_sales
)
SELECT city, amount
FROM ranked_sales
WHERE rn <= CEIL(remaining_cnt * 0.1);

结果：北京保留300（剩余3条，10%取1条）、上海保留450（剩余4条，10%取1条）。
优势：分步骤处理，先明确排除极端值，再基于剩余数据计算保留数量，逻辑严谨；用基础SQL+必要窗口函数组合，避免单一窗口函数的局限性。
说明：对于极端倾斜数据，有时需要结合业务规则，需要我们手动设定阈值。

场景36：多表关联后时间序列分析（城市+产品类别每月销量同比）

模拟数据（2张表：sales表+product表，含年份/月份）

sales表：

sale_id	city	product_id	amount	year	month
1	北京	P001	100	2024	8
2	北京	P001	200	2025	8
3	北京	P002	150	2024	8
4	北京	P002	250	2025	8
5	上海	P001	180	2024	8
6	上海	P001	280	2025	8

product表：

product_id	category
P001	家电
P002	数码

需求：我们要按“城市+产品类别+月份”分组，计算2025年8月销量与2024年8月的同比增长率

错解：关联后窗口函数LAG()按年份排序（数据量冗余）

SELECT
  city,
  category,
  month,
  current_year_amt,
  last_year_amt,
  ROUND((current_year_amt - last_year_amt)/last_year_amt * 100, 2) AS yoy_rate
FROM (
  SELECT
    s.city,
    p.category,
    s.month,
    s.amount AS current_year_amt,
    LAG(s.amount, 1) OVER (
      PARTITION BY s.city, p.category, s.month 
      ORDER BY s.year
    ) AS last_year_amt
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
) t
WHERE s.year = 2025;

问题：关联后未聚合，直接用窗口函数取去年数据，每行需重复计算；若某“城市+类别+月份”有多个订单，会导致数据重复，同比计算错误。

正解：先聚合再自关联（精准匹配同比）

-- 步骤1：按“城市+类别+年份+月份”聚合销量
WITH category_year_sales AS (
  SELECT
    s.city,
    p.category,
    s.year,
    s.month,
    SUM(s.amount) AS total_amt
  FROM sales s
  JOIN product p ON s.product_id = p.product_id
  GROUP BY s.city, p.category, s.year, s.month -- 聚合后数据量减少
)
-- 步骤2：自关联匹配去年同期数据，计算同比
SELECT
  cy2025.city,
  cy2025.category,
  cy2025.month,
  cy2025.total_amt AS 2025_amt,
  cy2024.total_amt AS 2024_amt,
  ROUND((cy2025.total_amt - cy2024.total_amt)/cy2024.total_amt * 100, 2) AS yoy_rate
FROM category_year_sales cy2025
LEFT JOIN category_year_sales cy2024 
  ON cy2025.city = cy2024.city 
  AND cy2025.category = cy2024.category 
  AND cy2025.month = cy2024.month 
  AND cy2025.year = cy2024.year + 1 -- 精准匹配去年同期
WHERE cy2025.year = 2025;

结果：北京-家电同比100%（200/100-1）、北京-数码同比66.67%（250/150-1）。
优势：先聚合减少数据量（6行原始数据→4行聚合数据），自关联精准匹配同比周期，无重复计算，同比结果更准确。

场景37：分组内连续数据判断（每个城市连续3天销量超200的日期区间）

模拟数据（sales表，按城市+日期聚合后的日销量）

city	sale_date	daily_amt
北京	2025-08-01	180
北京	2025-08-02	220
北京	2025-08-03	250
北京	2025-08-04	230
北京	2025-08-05	190
上海	2025-08-02	210
上海	2025-08-03	240
上海	2025-08-04	180

需求：我们要找出每个城市中，连续3天及以上日销量超200的日期区间（北京2025-08-02至04日符合）

错解：仅用基础SQL WHERE筛选（无法判断连续性）

-- 仅能筛选出单日超200的数据，无法判断连续3天
SELECT city, sale_date, daily_amt
FROM sales
WHERE daily_amt > 200
ORDER BY city, sale_date;

问题：基础SQL无法直接判断“连续日期”关系，仅能筛选单日数据，无法满足“连续3天”的核心需求。

正解：窗口函数LAG()判断连续+分组聚合（必用窗口函数）

-- 步骤1：计算“当前日期与前N天日期的差值”，判断是否连续
WITH consecutive_check AS (
  SELECT
    city,
    sale_date,
    daily_amt,
    -- 计算当前日期与前1天、前2天的日期差（连续则差为1）
    DATEDIFF(sale_date, LAG(sale_date, 1) OVER (PARTITION BY city ORDER BY sale_date)) AS diff1,
    DATEDIFF(sale_date, LAG(sale_date, 2) OVER (PARTITION BY city ORDER BY sale_date)) AS diff2
  FROM sales
  WHERE daily_amt > 200 -- 先筛选超200的日期，减少计算量
),
-- 步骤2：标记连续3天的起始日期（diff1=1且diff2=2说明连续3天）
continuous_start AS (
  SELECT
    city,
    sale_date AS end_date,
    -- 连续3天的起始日期=结束日期-2天
    DATE_SUB(sale_date, INTERVAL 2 DAY) AS start_date
  FROM consecutive_check
  WHERE diff1 = 1 AND diff2 = 2
)
-- 步骤3：关联原始数据，获取完整区间
SELECT
  cs.city,
  cs.start_date,
  cs.end_date,
  GROUP_CONCAT(s.daily_amt ORDER BY s.sale_date) AS daily_amounts
FROM continuous_start cs
JOIN sales s 
  ON s.city = cs.city 
  AND s.sale_date BETWEEN cs.start_date AND cs.end_date
GROUP BY cs.city, cs.start_date, cs.end_date;

结果：北京（start=2025-08-02，end=2025-08-04，amounts=220,250,230）。
优势：窗口函数LAG()可获取前N天日期，通过日期差判断连续性，这是基础SQL无法替代的；分步骤标记区间，逻辑清晰，结果精准。 说明：不同数据库的日期函数有兼容性问题（如：MySQL 的DATEDIFF与 PostgreSQL 的-运算符）。

场景38：多表关联后复杂排名（按省份+产品类别销量排名，取前2并显示城市贡献）

模拟数据（3张表：sales表+city_province表+product表）

sales表：

city	product_id	amount
北京	P001	100
北京	P002	200
天津	P001	150
上海	P001	250
杭州	P002	300

city_province表：

city	province
北京	北京
天津	天津
上海	上海
杭州	浙江

product表：

product_id	category
P001	家电
P002	数码

需求：1、我们要按“省份+产品类别”聚合总销量并排名，取前2；2、显示前2名中各城市的销量贡献

错解：关联后直接窗口排名（未拆分需求，逻辑混乱）

-- 一次性关联所有表，同时计算总销量、排名和城市贡献，代码冗余
SELECT
  p.province,
  pr.category,
  SUM(s.amount) AS total_amt,
  ROW_NUMBER() OVER (ORDER BY SUM(s.amount) DESC) AS rank,
  GROUP_CONCAT(CONCAT(s.city, ':', s.amount) SEPARATOR ',') AS city_contribution
FROM sales s
JOIN city_province p ON s.city = p.city
JOIN product pr ON s.product_id = pr.product_id
GROUP BY p.province, pr.category
HAVING rank <= 2; -- HAVING无法直接过滤排名，语法错误

问题：排名需在聚合后计算，无法用HAVING直接筛选，错误本质是 SQL 执行顺序导致的字段可用性问题”，而非单纯 “语法错误”；一次性处理所有需求，代码可读性差，后续难以维护。

正解：分步骤用“基础SQL聚合+窗口排名+关联补全”（组合方案）

-- 步骤1：按"省份+类别"聚合总销量
WITH province_category_total AS (
  SELECT
    p.province,
    pr.category,
    SUM(s.amount) AS total_amt
  FROM sales s
  JOIN city_province p ON s.city = p.city
  JOIN product pr ON s.product_id = pr.product_id
  GROUP BY p.province, pr.category
),
-- 步骤2：计算排名并筛选前2名
province_category_rank AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY total_amt DESC) AS pc_rank
  FROM province_category_total
  -- 在这里不能用WHERE筛选排名，因为排名是在这一步才计算的
),
top2_province_category AS (
  SELECT
    *
  FROM province_category_rank
  WHERE pc_rank <= 2 -- 正确：在排名计算完成后用WHERE筛选
),
-- 步骤3：按"省份+类别+城市"聚合，计算城市贡献
city_contribution AS (
  SELECT
    p.province,
    pr.category,
    s.city,
    SUM(s.amount) AS city_amt
  FROM sales s
  JOIN city_province p ON s.city = p.city
  JOIN product pr ON s.product_id = pr.product_id
  GROUP BY p.province, pr.category, s.city
)
-- 步骤4：关联排名表与贡献表，显示完整信息
SELECT
  tpc.province,
  tpc.category,
  tpc.total_amt,
  tpc.pc_rank,
  GROUP_CONCAT(CONCAT(cc.city, ':', cc.city_amt) SEPARATOR ',') AS city_contributions
FROM top2_province_category tpc
JOIN city_contribution cc 
  ON tpc.province = cc.province 
  AND tpc.category = cc.category
GROUP BY tpc.province, tpc.category, tpc.total_amt, tpc.pc_rank;

结果：浙江-数码（total=300，rank=1，贡献=杭州:300）、上海-家电（total=250，rank=2，贡献=上海:250）。
优势：先聚合总量 → 计算排名 → 筛选顶部记录 → 关联城市贡献，逻辑清晰，遵循了 SQL 的执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，确保窗口函数计算出排名后，再进行筛选操作；用基础SQL聚合减少数据量，窗口函数仅用于排名，性能与可读性平衡。

说明：部分数据库（PostgreSQL 13+、MySQL 8.0.31+、Oracle 12c+）支持QUALIFY子句，可直接在排名计算后筛选，无需额外嵌套 CTE（top2_province_category），体现了 “易维护” 原则。

综上所述，我们知道：选择窗口函数还是基础SQL，讲究的是平衡“逻辑简洁性”与“性能效率”：

• 小数据量、简单查询：优先选逻辑更简洁的方法（窗口函数或基础SQL均可）；
• 大数据量、复杂操作（更新/删除/多表关联）：优先用基础SQL优化，通过“先过滤→再聚合→后关联”减少数据量，避免窗口函数的重复计算与语法限制；
• 动态计算场景（累计、滚动窗口）：坚定用窗口函数，这是不可替代的。

总之，选窗口函数还是基础 SQL，我们别凭感觉来！记住平衡逻辑简洁和性能！小数据量咋简单咋来，大数据量就先过滤再聚合。不用非此即彼，该组合时就组合，比如：先补日期表再用窗口算累计。掌握这些场景和口诀，我们写的 SQL 既能满足业务需求，又高效好维护，再也不用在两种方式里瞎纠结啦！