我们写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; -- 过滤条件后置，先计算窗口再过滤

问题：我们先对全表数据执行窗口函数分组聚合，再过滤“北京2025年”数据，无效计算了其他城市/年份的聚合值，浪费资源。

正解：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%+。