别过度依赖窗口函数！基础SQL在这些场景更高效

admin

2025年8月30日 12:14 本文热度 40

我们写SQL时是不是总纠结：到底用窗口函数还是基础SQL呢？有时候想算个累计求和还得保留原始数据，用GROUP BY就给聚合没了；有时候就想简单分组求和，用窗口函数又显得没必要。其实不用死磕二选一，下面我们一起来理清场景：啥时候只能二选一，啥时候搭着用更高效，还有超实用的选择口诀，帮我们避开坑、写好SQL！

一、窗口函数与基础SQL的“非此即彼”与“组合使用”

1、非此即彼场景（只能选一种）

场景类型	唯一选择	核心原因
保留原始行+动态计算	窗口函数	基础SQL无法在保留每行数据的同时，动态计算分组/排序结果（如：累计求和、相邻对比）
仅需聚合结果+兼容旧数据库	基础SQL	窗口函数需数据库支持，且聚合场景下基础SQL更高效（如：GROUP BY求和）
批量更新/删除分组数据	基础SQL	多数数据库不支持UPDATE/DELETE中使用窗口函数，UPDATE JOIN/DELETE JOIN更兼容

说明：“批量更新/删除分组数据” 只能选择基础 SQL 的说法有特例。现代数据库（如：PostgreSQL）支持使用窗口函数的 UPDATE 语句，只是兼容性不如基础 SQL 广泛。

2、组合使用场景（两者结合更优）

业务需求	组合方案	优势
分组内比例筛选（前30%）	基础SQL算分组总量+窗口函数排名	先明确保留数量，再精准筛选，避免窗口函数百分比计算冗余
时间序列补全+累计	基础SQL生成完整日期表+窗口函数累计	先补全缺失日期，再用窗口函数计算连续累计，兼顾数据完整性与动态计算需求
多表关联排名+城市贡献	基础SQL聚合+窗口排名+关联补全	先聚合减少数据量，再排名，最后关联补全明细，逻辑清晰且性能高效

二、窗口函数与基础SQL的选择口诀

1、聚合用GROUP：分组求和、最大最小，直接GROUP BY，不绕窗口函数；
2、Top N用LIMIT：全表排序取前N，ORDER BY+LIMIT，拒绝子查询嵌套；（全表 Top N 用 LIMIT，分组 Top N 必窗口。）
3、统计用CTE：全表平均值、总和，先算一次存CTE，避免重复计算；

4、首尾用JOIN：分组首/尾记录，先查时间再关联，不碰FIRST_VALUE；（说明：单表分组首尾记录，优先用FIRST_VALUE()/LAST_VALUE()；仅当需跨表关联首尾数据时，再用 JOIN。）
5、累计用窗口：保留每行+动态计算（累计、移动平均、排名），窗口函数是最优解；
6、比例先算总：分组内占比计算，先GROUP BY算总量，再JOIN关联更高效；
7、过滤先执行：多条件聚合需求，WHERE先筛目标数据，再聚合减少压力；
8、去重先子查：分组内去重计数，子查询先去重组合，再GROUP BY计数更兼容；
9、固定范围用WHERE：固定日期/金额范围聚合，WHERE锁定范围，再GROUP BY更简洁；
10、动态范围必窗口：近N天、移动平均等动态计算，窗口函数不可替代；
11、比例筛选先算量：分组内按比例取数，先算需保留数量，再按序号筛选更直观；
12、多表聚合先关联/先聚合：简单场景先关联再GROUP BY，大数据量先聚合再关联；
13、空值填充先算均：分组内空值填充，先算分组平均值，再JOIN填充更高效；
14、固定分批用取模：每N条分1批，自增ID取模分批，比NTILE()更灵活；
15、极值关联先算极：显示分组极值+原始数据，先GROUP BY算极值，再JOIN关联更省资源；
16、周期聚合找末日：每月/每周最后一天聚合，先找周期最后一天，再关联聚合更可控；
17、多条件排名先聚合：按多字段分组排名，先聚合减少数据量，再排名降低排序开销；
18、批量更新用JOIN：用分组值更新字段，先算聚合值，再UPDATE JOIN兼容又高效；
19、差值计算先算均：分组内差值对比，先算分组平均值，再JOIN算差值更高效；
20、自定义分组用CASE+GROUP：按规则分组统计，CASE定义区间，再GROUP BY直接聚合；
21、多表排名先聚合：关联后排名，先聚合减少数据量，再排名更高效；
22、历史对比自关联：年份/周期对比，自关联匹配对应周期，避免窗口函数年份依赖；
23、中位数计算用行号：分组内中位数，子查询排序标行号，定位中间行再平均；
24、滚动窗口自关联：滚动时间范围聚合，日期自关联匹配范围，逻辑可控无语法依赖；
25、过滤排名先筛数：多条件过滤后排名，先过滤目标数据，再关联排名更高效；
26、批量删除用JOIN：删除分组内特定数据，先找目标值，再DELETE JOIN兼容无限制；
27、时间补全生成表：缺失日期补全，生成完整日期表，左关联填充0再计算；
28、多维度排序看场景：单城市用LIMIT，多城市用窗口+QUALIFY，减少嵌套更简洁；
29、多表更新先聚合：关联后批量更新，先算分组平均值，再JOIN更新兼容高效；
30、倾斜数据分步筛：排除极端值后取比例，先算阈值再筛选，避免单一窗口函数失真；
31、同比分析先聚合：多表关联同比，先按周期聚合销量，再自关联匹配同期，结果精准；
32、连续数据用LAG：判断连续日期/数值，窗口函数LAG()取前N值，计算差值定连续；
33、复杂排名分步骤：多表关联排名+明细，先聚合排名再关联补全，逻辑清晰易维护。

三、窗口函数与基础SQL的场景选择示例

场景1：分组聚合（每个城市总销量/最大最小金额）

模拟数据（sales表）

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

需求：我们要计算每个城市总销量/最大金额

错解：窗口函数+DISTINCT（冗余）

SELECT DISTINCT
  city,
  SUM(amount) OVER (PARTITION BY city) AS total_amount, -- 每行重复计算
  MAX(amount) OVER (PARTITION BY city) AS max_amount
FROM sales;

问题：先为每行计算聚合值，再用DISTINCT去重，多一步无效计算，性能损耗高。

正解：GROUP BY（高效）

SELECT
  city,
  SUM(amount) AS total_amount,
  MAX(amount) AS max_amount
FROM sales
GROUP BY city; -- 直接分组聚合，无冗余

优势：结果一致，但执行计划更简单，I/O和内存消耗减少50%以上。

场景2：全表排序取Top N（销量前2的订单）

需求：我们要获取全表中金额最高的2条订单

错解：ROW_NUMBER()+子查询（复杂）

SELECT city, amount
FROM (
  SELECT 
    city, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn -- 全表排序生成行号
  FROM sales
) t
WHERE rn <= 2;

问题：需嵌套子查询，全表排序后生成行号，数据量大时耗时显著。

正解：ORDER BY+LIMIT（简洁）

SELECT city, amount
FROM sales
ORDER BY amount DESC
LIMIT 2; -- 直接排序+限制行数，数据库优化更高效

优势：结果一致（广州300、上海250），代码行数减少40%，执行速度提升30%+。

场景3：全表统计（标记高于平均值的订单）

需求：我们要查询所有订单，标记金额是否高于全表平均值

错解：窗口函数重复计算（浪费资源）

SELECT
  city, amount,
  AVG(amount) OVER () AS avg_amount, -- 每行重复计算同一平均值
  CASE WHEN amount > AVG(amount) OVER () THEN '高于平均' ELSE '低于平均' END AS status
FROM sales;

问题：AVG(amount) OVER ()执行N次（N=总行数），冗余计算。

正解：标量子查询/CTE（仅计算1次）

-- 标量子查询方案
SELECT
  city, amount,
  (SELECT AVG(amount) FROM sales) AS avg_amount, -- 仅计算1次
  CASE WHEN amount > (SELECT AVG(amount) FROM sales) THEN '高于平均' ELSE '低于平均' END AS status
FROM sales;

-- CTE优化方案（更易读）
WITH stats AS (SELECT AVG(amount) AS avg_amt FROM sales)
SELECT
  s.city, s.amount,
  st.avg_amt,
  CASE WHEN s.amount > st.avg_amt THEN '高于平均' ELSE '低于平均' END AS status
FROM sales s, stats st;

优势：结果一致，计算次数从“N次”降为“1次”，性能提升显著。

场景4：分组取首/尾记录（每个城市最早/最晚插入的订单）

模拟数据（新增id和insert_time）

id	city	amount	insert_time
1	北京	100	2025-08-01 10:00:00
2	北京	200	2025-08-01 11:00:00
3	上海	250	2025-08-01 10:30:00

需求：我们要获取每个城市最早/最晚插入的订单金额

错解：FIRST_VALUE/LAST_VALUE+DISTINCT（易出错）

SELECT DISTINCT
  city,
  FIRST_VALUE(amount) OVER (PARTITION BY city ORDER BY insert_time) AS first_amt,
  -- LAST_VALUE默认窗口是“当前行”，必须显式指定范围，否则结果错误
  LAST_VALUE(amount) OVER (PARTITION BY city ORDER BY insert_time 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt
FROM sales;

问题：LAST_VALUE需我们手动配置窗口范围，易遗漏导致结果错误；DISTINCT增加开销。

正解：MIN/MAX（时间）+JOIN（可控）

WITH first_last_time AS (
  SELECT
    city,
    MIN(insert_time) AS first_time, -- 先获取每个城市的首/尾时间
    MAX(insert_time) AS last_time
  FROM sales
  GROUP BY city
)
SELECT
  fl.city,
  s1.amount AS first_amt, -- 关联获取首时间对应的金额
  s2.amount AS last_amt   -- 关联获取尾时间对应的金额
FROM first_last_time fl
JOIN sales s1 ON fl.city = s1.city AND fl.first_time = s1.insert_time
JOIN sales s2 ON fl.city = s2.city AND fl.last_time = s2.insert_time;

优势：逻辑清晰，无窗口函数配置风险，有索引时执行速度更快。

场景5：分组内取Top N（每个城市销量前2的订单）

需求：我们要每个城市按金额降序取前2条订单

错解：ROW_NUMBER()+子查询（嵌套冗余）

SELECT city, amount
FROM (
  SELECT
    city, amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM sales
) t
WHERE rn <= 2;

问题：嵌套层级多，数据量大时窗口排序开销高。

正解：LATERAL/APPLY+LIMIT（高效）

-- PostgreSQL/SQL Server支持（MySQL 8.0+支持LATERAL）
SELECT c.city, s.amount
FROM (SELECT DISTINCT city FROM sales) c -- 先获取所有城市
CROSS JOIN LATERAL (
  SELECT amount
  FROM sales s2
  WHERE s2.city = c.city -- 按城市过滤
  ORDER BY amount DESC
  LIMIT 2 -- 每个城市取前2
) s;

优势：减少嵌套，数据库优化为“城市循环+局部排序”，比窗口函数快20%+。

场景6：窗口函数必用场景（累计求和/移动平均）

模拟数据（新增order_date）

city	amount	order_date
北京	100	2025-08-01
北京	200	2025-08-02
北京	150	2025-08-03

需求：我们要每个城市按时间的累计销量

唯一解：窗口函数（无基础SQL替代）

SELECT
  city,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY city 
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 累计范围：从第一条到当前行
  ) AS cum_amount
FROM sales;

结果（北京）：2025-08-01累计100、2025-08-02累计300、2025-08-03累计450。
说明：“保留每行数据+动态计算分组内累计值”的场景，窗口函数是唯一高效解法。

场景7：分组内比例计算（每个订单占城市总销量的比例）

模拟数据（同场景1的sales表）

需求：我们要计算每个订单金额占所在城市总销量的百分比

常见解（无错误，但可优化）：窗口函数直接计算

SELECT
  city,
  amount,
  SUM(amount) OVER (PARTITION BY city) AS city_total,
  ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY city), 2) AS amount_ratio
FROM sales;

结果（北京）：100占比33.33%（100/300）、200占比66.67%（200/300）。
问题：大数据量时，窗口函数需重复计算“城市总销量”，排序和聚合压力大。

优化解：GROUP BY算总量+JOIN关联

WITH city_total AS (
  SELECT city, SUM(amount) AS total FROM sales GROUP BY city -- 先算城市总销量（仅1次/城市）
)
SELECT
  s.city,
  s.amount,
  ct.total AS city_total,
  ROUND(s.amount * 100.0 / ct.total, 2) AS amount_ratio
FROM sales s
JOIN city_total ct ON s.city = ct.city; -- 关联后计算比例

优势：GROUP BY聚合数据量少（仅“城市+总量”），JOIN操作比窗口函数的重复计算更高效，大数据量下性能提升40%+。

场景8：多条件过滤后聚合（2025年北京的季度总销量）

模拟数据（扩展sales表，新增year和quarter字段）

city	amount	year	quarter
北京	100	2025	Q1
北京	200	2025	Q1
北京	150	2025	Q2
上海	250	2024	Q4
广州	300	2025	Q1

需求：我们要计算2025年北京各季度的总销量

错解：窗口函数+WHERE（逻辑冗余）

SELECT DISTINCT
  city,
  quarter,
  SUM(amount) OVER (PARTITION BY city, quarter) AS quarterly_total
FROM sales
WHERE city = '北京' AND year = 2025; -- 过滤条件后置，先计算窗口再过滤

问题：虽然先WHERE过滤，再算窗口，不会计算其他城市，但SUM() OVER (PARTITION BY ...) 是一种误用，它为每行生成相同的聚合值，结果重复，且必须配合DISTINCT去重，增加了不必要计算开销。

正解：WHERE先过滤+GROUP BY聚合

SELECT
  city,
  quarter,
  SUM(amount) AS quarterly_total
FROM sales
WHERE city = '北京' AND year = 2025 -- 先过滤，仅保留目标数据
GROUP BY city, quarter;

结果（北京）：Q1总销量300（100+200）、Q2总销量150。
优势：过滤后仅对“北京2025年”的数据聚合，数据量减少80%，执行速度大幅提升。

场景9：分组内相邻数据对比（每个订单与前一天订单的金额差）

模拟数据（扩展sales表，新增order_date）

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

需求：我们要计算每个城市每天订单与前一天订单的金额差值（无数据时显示NULL）

常规解（窗口函数是最优解）：LAG窗口函数

SELECT
  city,
  order_date,
  amount,
  LAG(amount, 1) OVER (PARTITION BY city ORDER BY order_date) AS prev_day_amount, -- 取前1天金额
  amount - LAG(amount, 1) OVER (PARTITION BY city ORDER BY order_date) AS amount_diff
FROM sales;

结果（北京）：2025-08-02差值100（200-100）、2025-08-03差值-50（150-200）。
优势：无需嵌套，直接获取相邻数据，逻辑简洁，效率高。

替代解（仅适用于极小数据量）：自关联+日期条件

SELECT
  s1.city,
  s1.order_date,
  s1.amount,
  s2.amount AS prev_day_amount,
  s1.amount - s2.amount AS amount_diff
FROM sales s1
LEFT JOIN sales s2 
  ON s1.city = s2.city 
  AND s1.order_date = s2.order_date + INTERVAL '1 day' -- 关联前1天数据
ORDER BY s1.city, s1.order_date;

问题：数据量大时，自关联会产生笛卡尔积，性能急剧下降；日期计算易出错（如：跨月、闰年），窗口函数是更优选择。

场景10：按固定范围聚合（北京2025年8月1日-3日的总销量）

需求：我们要计算北京在2025年8月1日至3日期间的总销量

错解：窗口函数（范围窗口）+过滤

SELECT DISTINCT
  city,
  SUM(amount) OVER (
    PARTITION BY city 
    ORDER BY order_date 
    RANGE BETWEEN '2025-08-01' AND '2025-08-03' -- 固定日期范围，窗口函数冗余
  ) AS total_amount
FROM sales
WHERE city = '北京';

问题：固定范围聚合无需窗口函数，该写法会先对每行计算范围总和，再去重，增加无意义的计算步骤。

正解：WHERE过滤日期+GROUP BY聚合

SELECT
  city,
  SUM(amount) AS total_amount
FROM sales
WHERE 
  city = '北京' 
  AND order_date BETWEEN '2025-08-01' AND '2025-08-03' -- 先过滤固定日期范围
GROUP BY city;

结果：北京总销量450（100+200+150）。
优势：直接锁定目标数据范围，聚合步骤简单，执行速度比窗口函数快60%+。唯一解：若需求是“每个日期的近3天动态累计销量”（如：8月3日算8月1-3日，8月4日算8月2-4日），则必须用窗口函数：

SELECT
  city,
  order_date,
  SUM(amount) OVER (
    PARTITION BY city 
    ORDER BY order_date 
    RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW -- 动态近3天
  ) AS 3day_cum_amount
FROM sales;

场景11：分组内去重计数（每个城市的下单用户数）

模拟数据（扩展sales表，新增user_id）

city	amount	user_id
北京	100	U001
北京	200	U001
北京	150	U002
上海	250	U003
上海	300	U004

需求：我们要统计每个城市的 distinct 下单用户数

错解：窗口函数COUNT(DISTINCT)（兼容性很差）

-- 多数数据库（如：MySQL全版本、PostgreSQL 9.x及以下）不支持窗口函数中的COUNT(DISTINCT)
SELECT DISTINCT
  city,
  COUNT(DISTINCT user_id) OVER (PARTITION BY city) AS user_count
FROM sales;

问题：语法兼容性低，且窗口函数处理去重计数时，需额外排序去重，性能比基础SQL差。

正解：GROUP BY+子查询去重（兼容且高效）

-- 方案1：子查询先去重，再分组计数
SELECT
  city,
  COUNT(user_id) AS user_count
FROM (SELECT DISTINCT city, user_id FROM sales) t -- 先去重城市-用户组合
GROUP BY city;

-- 方案2：直接GROUP BY city, user_id（等效去重）
SELECT
  city,
  COUNT(*) AS user_count
FROM sales
GROUP BY city, user_id; -- 按“城市-用户”分组，COUNT(*)即去重后数量

结果：北京2人（U001、U002）、上海2人（U003、U004）。
优势：兼容所有数据库，先去重再计数，执行计划更简洁，无窗口函数冗余步骤。

场景12：多字段排序后取唯一记录（每个城市金额最高的唯一订单）

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

city	amount	insert_time
北京	200	2025-08-01 10:00:00
北京	200	2025-08-01 09:00:00
上海	300	2025-08-01 12:00:00

需求：我们要每个城市仅保留金额最高的1条订单（若金额相同，保留insert_time最早的）

错解：窗口函数ROW_NUMBER()+子查询（嵌套冗余）

SELECT city, amount, insert_time
FROM (
  SELECT
    city, amount, insert_time,
    ROW_NUMBER() OVER (
      PARTITION BY city 
      ORDER BY amount DESC, insert_time ASC -- 多字段排序
    ) AS rn
  FROM sales
) t
WHERE rn = 1;

问题：需嵌套子查询，多一步行号计算，简单场景下无需复杂窗口函数。

正解：DISTINCT+ORDER BY+LIMIT（简单场景）/GROUP BY+MAX（复杂场景）

-- 方案1：适用于单城市查询（简单场景）
SELECT city, amount, insert_time
FROM sales
WHERE city = '北京'
ORDER BY amount DESC, insert_time ASC
LIMIT 1; -- 直接排序取首条，无嵌套

-- 方案2：适用于多城市查询（复杂场景）
-- 场景：每个城市取金额最高且时间最早的唯一订单
WITH city_top AS (
  SELECT
    city,
    max_amt,
    MIN(insert_time) AS min_time  -- 在最高金额订单中取最早时间
  FROM (
    -- 先筛选每个城市的最高金额订单
    SELECT
      city,
      amount AS max_amt,  -- 此时的amount已是该城市的最高金额
      insert_time
    FROM sales
    WHERE (city, amount) IN (
      -- 子查询获取每个城市的最高金额
      SELECT city, MAX(amount) FROM sales GROUP BY city
    )
  ) t
  GROUP BY city, max_amt  -- 按城市和最高金额分组，确保每组是同一城市的最高金额订单集合
)
-- 最终关联获取完整订单记录
SELECT 
  s.*  -- 可根据需要替换为具体字段，如city, amount, insert_time, order_id等
FROM sales s
INNER JOIN city_top ct
  ON s.city = ct.city
  AND s.amount = ct.max_amt
  AND s.insert_time = ct.min_time;  -- 精确匹配到最高金额中最早时间的订单

结果：北京保留200（09:00）、上海保留300（12:00）。
优势：无嵌套子查询，多城市场景下，我们用CTE先锁定关键条件，再关联取数，比窗口函数更易维护。

场景13：分组内按比例筛选（每个城市销量前30%的订单）

模拟数据（sales表，北京3条订单、上海3条订单）

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

需求：我们要每个城市仅保留销量排名前30%的订单（3条订单取前1条，即金额最高的1条）

错解：窗口函数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 <= 0.3; -- 筛选前30%

结果：北京300、上海350（符合预期）。
问题：PERCENT_RANK()需对分组内所有数据排序并计算百分比，步骤冗余；若数据量不均（如：某城市4条订单，30%约1.2条，需额外处理取整），逻辑更复杂。

正解：GROUP BY算总量+比例计算+JOIN

-- 步骤1：计算每个城市需保留的订单数量（总条数×30%，向上取整）
WITH city_count AS (
  SELECT
    city,
    CEIL(COUNT(*) * 0.3) AS keep_num -- 3条×0.3=0.9，CEIL取1；4条×0.3=1.2，CEIL取2
  FROM sales
  GROUP BY city
),
-- 步骤2：为每个城市订单按金额降序标序号
city_ranked AS (
  SELECT
    city, amount,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM sales
)
-- 步骤3：关联筛选需保留的订单
SELECT cr.city, cr.amount
FROM city_ranked cr
JOIN city_count cc ON cr.city = cc.city
WHERE cr.rn <= cc.keep_num;

优势：我们先明确“需保留的数量”，再按序号筛选，逻辑更直观；避免PERCENT_RANK()的百分比计算冗余，大数据量下性能提升35%+。

场景14：多表关联后聚合（城市+产品类别的总销量）

模拟数据（2张表：sales表、product表）

sales表（订单-产品关联）：

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

product表（产品-类别关联）：

product_id	category
P001	家电
P002	数码

需求：我们要计算每个城市、每个产品类别的总销量

错解：多表关联后用窗口函数+DISTINCT

SELECT DISTINCT
  s.city,
  p.category,
  SUM(s.amount) OVER (PARTITION BY s.city, p.category) AS total_amount
FROM sales s
JOIN product p ON s.product_id = p.product_id; -- 先关联全量数据

问题：我们先关联生成“订单-城市-产品-类别”全量数据（4行），再用窗口函数分组聚合，最后去重，多2步冗余操作；若关联后数据量达10万行，窗口计算压力显著。

正解：先关联再GROUP BY（或先聚合再关联）

-- 方案1：先关联再GROUP BY（简单场景）
SELECT
  s.city,
  p.category,
  SUM(s.amount) AS total_amount
FROM sales s
JOIN product p ON s.product_id = p.product_id
GROUP BY s.city, p.category; -- 关联后直接分组聚合，无冗余

-- 方案2：先聚合再关联（大数据量场景，减少关联数据量）
WITH sale_product_total AS (
  SELECT
    product_id,
    city,
    SUM(amount) AS product_total -- 先按“城市-产品”聚合，数据量减少
  FROM sales
  GROUP BY product_id, city
)
SELECT
  spt.city,
  p.category,
  SUM(spt.product_total) AS total_amount -- 再按“城市-类别”聚合
FROM sale_product_total spt
JOIN product p ON spt.product_id = p.product_id
GROUP BY spt.city, p.category;

结果：北京-家电100、北京-数码200、上海-家电150、上海-数码250。
优势：方案1逻辑简洁，方案2先聚合减少关联数据量（如：10万订单→1万“城市-产品”组合），大数据量下性能提升50%+。

场景15：空值填充（用分组内平均值填充订单金额空值）

模拟数据（sales表，含空金额订单）

city	amount
北京	100
北京	NULL
北京	200
上海	150
上海	NULL
上海	250

需求：我们要将空金额订单填充为所在城市的订单金额平均值

错解：窗口函数AVG() OVER ()+COALESCE

SELECT
  city,
  COALESCE(amount, AVG(amount) OVER (PARTITION BY city)) AS filled_amount -- 每行计算平均值
FROM sales;

结果：北京NULL→150（(100+200)/2）、上海NULL→200（(150+250)/2）（符合预期）。
问题：AVG(amount) OVER ()为每行（包括非空行）重复计算分组平均值，空值仅占少数时，90%计算为冗余。

正解：子查询算分组平均值+JOIN+COALESCE

-- 步骤1：计算每个城市的非空金额平均值
WITH city_avg AS (
  SELECT
    city,
    AVG(amount) AS avg_amount -- 仅计算1次/城市，自动忽略NULL
  FROM sales
  GROUP BY city
)
-- 步骤2：关联填充空值
SELECT
  s.city,
  COALESCE(s.amount, ca.avg_amount) AS filled_amount
FROM sales s
JOIN city_avg ca ON s.city = ca.city;

优势：平均值仅计算1次/城市，避免每行重复计算；JOIN操作比窗口函数更轻量，空值占比越低，性能优势越明显（空值占10%时，性能提升80%+）。

场景16：按批次聚合（每5条订单为1批，计算每批总销量）

模拟数据（sales表，新增自增sale_id，共12条订单）

sale_id	amount
1	100
2	200
3	150
4	250
5	300
6	180
7	220
8	280
9	320
10	160
11	240
12	260

需求：我们要按sale_id顺序，每5条订单为1批（最后1批不足5条也为1批），计算每批总销量

错解：窗口函数NTILE(批次数)+GROUP BY

-- 需先计算批次数（12条/5条=2.4→向上取整为3批）
SELECT
  batch,
  SUM(amount) AS batch_total
FROM (
  SELECT
    amount,
    NTILE(3) OVER (ORDER BY sale_id) AS batch -- 均匀分3批，每批4条（12/3=4），不符合“每5条1批”需求
  FROM sales
) t
GROUP BY batch;

问题：NTILE()会“均匀分配”订单到每批（如：12条分3批，每批4条），无法满足“固定5条1批”的需求；若订单数变化（如：13条），需重新计算批次数，灵活性差。

正解：自增ID取模分批次+GROUP BY

SELECT
  -- 按sale_id分批：sale_id-1后取模5，结果+1（1-5→1，6-10→2，11-12→3）
  (sale_id - 1) DIV 5 + 1 AS batch,
  SUM(amount) AS batch_total
FROM sales
GROUP BY (sale_id - 1) DIV 5 + 1 -- 按批次分组聚合
ORDER BY batch;

结果：

batch	batch_total
1	1000（100+200+150+250+300）
2	1040（180+220+280+320+160）
3	500（240+260）

优势：无需提前计算批次数，我们通过“(ID-1) DIV 批次大小 +1”直接分批，逻辑灵活；无窗口函数排序开销，执行速度比NTILE()快40%+。

场景17：分组内极值关联（显示订单金额及所在城市的最大值）

模拟数据（sales表，同场景1）

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

需求：我们要查询所有订单，同时显示该订单所在城市的最大金额

错解：窗口函数MAX() OVER ()直接查询

SELECT
  city,
  amount,
  MAX(amount) OVER (PARTITION BY city) AS city_max_amt -- 每行重复计算城市最大值
FROM sales;

结果（北京）：100（max=200）、200（max=200）（符合预期）。
问题：大数据量（如：100万行）时，每行都需计算一次分组最大值，重复计算导致CPU和内存消耗过高。

正解：GROUP BY算极值+JOIN关联

-- 步骤1：计算每个城市的最大金额
WITH city_max AS (
  SELECT city, MAX(amount) AS max_amt FROM sales GROUP BY city -- 仅1次/城市计算
)
-- 步骤2：关联原表显示详情
SELECT
  s.city,
  s.amount,
  cm.max_amt AS city_max_amt
FROM sales s
JOIN city_max cm ON s.city = cm.city;

优势：最大值仅计算1次/城市（如：3个城市仅计算3次，而非5次），关联操作轻量；数据量越大，性能优势越明显（100万行时，性能提升70%+）。

场景18：按时间周期聚合（每月最后一天的订单总销量）

模拟数据（sales表，新增order_date）

city	amount	order_date
北京	100	2025-08-28
北京	200	2025-08-31
上海	150	2025-08-30
上海	250	2025-09-30
广州	300	2025-09-29

需求：我们要计算每月最后一天，所有城市的订单总销量

错解：窗口函数LAST_VALUE()+日期筛选

SELECT DISTINCT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(amount) OVER (
    PARTITION BY DATE_FORMAT(order_date, '%Y-%m') 
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_day_total
FROM sales
WHERE order_date = LAST_VALUE(order_date) OVER (
  PARTITION BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY order_date
); -- 筛选每月最后一天数据

问题：语法复杂，需嵌套窗口函数筛选日期；LAST_VALUE()需显式配置窗口框架，易遗漏导致结果错误（如：默认窗口为“当前行”，无法取到当月最后一天）。

正解：子查询找每月最后一天+GROUP BY

-- 步骤1：找到每月的最后一天
WITH month_last_day AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    MAX(order_date) AS last_day -- 直接取每月最大日期（即最后一天）
  FROM sales
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
-- 步骤2：按“月+最后一天”聚合总销量
SELECT
  DATE_FORMAT(s.order_date, '%Y-%m') AS month,
  s.order_date AS last_day,
  SUM(s.amount) AS last_day_total
FROM sales s
JOIN month_last_day mld 
  ON DATE_FORMAT(s.order_date, '%Y-%m') = mld.month 
  AND s.order_date = mld.last_day -- 关联每月最后一天数据
GROUP BY DATE_FORMAT(s.order_date, '%Y-%m'), s.order_date;

结果：2025-08总销量350（200+150）、2025-09总销量550（250+300）。
优势：逻辑直观，我们先锁定“每月最后一天”，再聚合数据；无窗口函数配置风险，执行计划更简洁。

场景19：多条件排名（按城市+年份的销量排名）

模拟数据（sales表，新增year字段）

city	amount	year
北京	100	2024
北京	200	2025
上海	150	2024
上海	250	2025
广州	300	2024
广州	280	2025

需求：我们要按“城市+年份”分组，对每个分组内的订单按金额降序排名

错解：窗口函数PARTITION BY多字段+子查询

SELECT
  city,
  year,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY city, year -- 多字段分组，窗口内数据量较大
    ORDER BY amount DESC
  ) AS rn
FROM sales;

结果（北京2024）：100（rn=1）；北京2025：200（rn=1）（符合预期）。
问题：PARTITION BY多字段会导致窗口内数据量增加（如：“北京-2024”、“北京-2025”为两个窗口），排序开销比“先聚合再排名”更高；若需基于排名筛选，需额外嵌套子查询。

正解：先GROUP BY多字段聚合+再排名（若需聚合后排名）/直接简化窗口（若需原始行排名）

-- 情况1：需先按“城市+年份”聚合总销量，再对总销量排名
WITH city_year_total AS (
  SELECT
    city,
    year,
    SUM(amount) AS total_amt -- 先聚合“城市+年份”总销量，减少数据量
  FROM sales
  GROUP BY city, year
)
SELECT
  city,
  year,
  total_amt,
  ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_amt DESC) AS year_rank -- 按年份对城市排名
FROM city_year_total;

-- 情况2：需保留原始订单行，仅简化窗口逻辑（无性能差异，逻辑更清晰）
SELECT
  city,
  year,
  amount,
  -- 直接用多字段分组窗口，若数据量小可接受；数据量大则优先用情况1的聚合方案
  ROW_NUMBER() OVER (PARTITION BY CONCAT(city, '-', year) ORDER BY amount DESC) AS rn
FROM sales;

优势：情况1先聚合减少排名数据量（6行原始数据→5行聚合数据），排序效率提升；情况2用CONCAT简化窗口分组逻辑，可读性更高。

场景20：批量更新（用分组平均值更新空金额字段）

模拟数据（sales表，含空金额订单，同场景15）

city	amount
北京	100
北京	NULL
北京	200
上海	150
上海	NULL

需求：我们要将空金额订单的amount字段，更新为所在城市的非空金额平均值

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

-- 部分数据库（如：MySQL）不支持UPDATE中直接使用窗口函数
UPDATE sales
SET amount = (SELECT AVG(amount) OVER (PARTITION BY city) FROM sales s2 WHERE s2.city = sales.city)
WHERE amount IS NULL;

问题：语法兼容性低，多数数据库不支持UPDATE子查询中的窗口函数；即使支持，窗口函数会为每条空值行重复计算平均值，效率低。

正解：子查询算平均值+UPDATE JOIN（兼容且高效）

-- 步骤1：计算每个城市的非空金额平均值
WITH city_avg AS (
  SELECT
    city,
    AVG(amount) AS avg_amt
  FROM sales
  GROUP BY city
)
-- 步骤2：用JOIN更新空值（兼容MySQL、PostgreSQL、SQL Server等）
UPDATE sales s
JOIN city_avg ca ON s.city = ca.city
SET s.amount = ca.avg_amt
WHERE s.amount IS NULL;

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

场景21：分组内差值计算（每个订单与城市平均值的差额）

模拟数据（sales表，同场景1）

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

需求：我们要查询所有订单，计算每个订单金额与所在城市平均值的差额（正值为高于平均，负值为低于平均）

错解：窗口函数AVG() OVER ()+直接计算

SELECT
  city,
  amount,
  AVG(amount) OVER (PARTITION BY city) AS city_avg,
  amount - AVG(amount) OVER (PARTITION BY city) AS avg_diff -- 每行重复计算平均值，再差值
FROM sales;

结果（北京）：100（avg=150，diff=-50）、200（avg=150，diff=50）（符合预期）。
问题：每行都需计算一次城市平均值，再做差值，重复计算导致资源浪费；数据量达10万行时，CPU消耗比基础SQL高50%+。

正解：GROUP BY算平均值+JOIN+差值

-- 步骤1：计算每个城市的金额平均值
WITH city_avg AS (
  SELECT city, AVG(amount) AS avg_amt FROM sales GROUP BY city -- 仅1次/城市计算
)
-- 步骤2：关联原表计算差额
SELECT
  s.city,
  s.amount,
  ca.avg_amt AS city_avg,
  s.amount - ca.avg_amt AS avg_diff
FROM sales s
JOIN city_avg ca ON s.city = ca.city;

优势：平均值仅计算1次/城市，关联后统一计算差额，无重复计算；大数据量下性能提升60%+，且逻辑更清晰。

场景22：按自定义规则分组聚合（按金额区间分组统计订单数）

模拟数据（sales表，扩展数据量）

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

需求：我们要按金额区间（0-200、201-400、401+）分组，统计每个区间的订单数量

错解：窗口函数CASE+PARTITION BY+DISTINCT

SELECT DISTINCT
  CASE
    WHEN amount BETWEEN 0 AND 200 THEN '0-200'
    WHEN amount BETWEEN 201 AND 400 THEN '201-400'
    ELSE '401+'
  END AS amount_range,
  COUNT(*) OVER (
    PARTITION BY CASE -- 按自定义区间分组的窗口函数
      WHEN amount BETWEEN 0 AND 200 THEN '0-200'
      WHEN amount BETWEEN 201 AND 400 THEN '201-400'
      ELSE '401+'
    END
  ) AS order_count
FROM sales;

结果：0-200（3单）、201-400（3单）、401+（1单）（符合预期）。
问题：需重复写2次CASE逻辑，冗余且易出错；窗口函数分组后需DISTINCT去重，增加额外步骤，效率比基础SQL低。

正解：基础CASE分组+GROUP BY

SELECT
  CASE
    WHEN amount BETWEEN 0 AND 200 THEN '0-200'
    WHEN amount BETWEEN 201 AND 400 THEN '201-400'
    ELSE '401+'
  END AS amount_range,
  COUNT(*) AS order_count -- 直接按自定义区间分组统计
FROM sales
GROUP BY amount_range;

优势：仅写1次CASE逻辑，直接按区间分组聚合，无冗余步骤；执行计划仅含“分组+计数”，效率比窗口函数方案高40%+。

场景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 既能满足业务需求，又高效好维护，再也不用在两种方式里瞎纠结啦！

阅读原文：原文链接

该文章在 2025/9/1 11:33:25 编辑过

关键字查询

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。