企业官网建设流程全解析

如果说过去一个月我都在跟一张叫“精油功效筛选”的需求清单较劲，那大部分时间其实不是在精油知识上，而是被 MySQL 里那几种翻来覆去的分组排行问题按在地上摩擦。标题看着有点拗口，但说白了就是：一批精油产品，每个产品有多种功效标签、有库存、有价格、有销量记录，然后要按“舒缓”“抗菌”“安神”这些功效去筛选、去排序、去分档，还要保证查询在数据量涨上去之后不崩。

窗口函数在 MySQL 8.0 里已经不是新东西了，但真到业务里用，很多人还是习惯性写子查询、写 GROUP BY 硬凑，最后搞出一坨又慢又难维护的 SQL。这篇文章我就拿精油筛选这个场景做引子，把窗口函数和多条件过滤的搭配方式完整过一遍，包括表怎么建、索引怎么加、慢查询怎么排查，以及那些“你写的时候觉得没问题，一跑就翻车”的细节。

整个内容适合已经在写增删改查、想往高级查询靠一靠的后端开发，也适合被慢 SQL 折腾过的数据运维。全文不扯虚的，SQL 都是可以复制跑一遍的，建议你顺手在本地库上敲一敲。

1. 业务场景与表结构设计

1.1 为什么拿精油场景做窗口函数训练场

很多人学窗口函数卡在“不知道该用在哪”。窗口函数最适合的数据形态是：一表内存着大量事实记录（比如销售流水、操作日志、库存变化），需要在这些记录内部做排行、累计、环比、移动平均。

精油场景恰好满足这个条件。它有典型的多对多标签关系：一瓶油可以同时属于“舒缓”“助眠”“抗菌”多个功效；有连续型数值字段：价格、库存、销量；有天然维度：产地、品牌、销售日期。这意味着查询时既能玩 PARTITION BY 分组，又能在同一组内玩 ORDER BY 排序，还能把多个功效条件叠加起来做过滤。

更重要的是，精油筛选页面的真实需求往往长这样：价格在 300 元以内、必须带“安神”功效、库存不低于 50 瓶、按最近 30 天销量排序，只要前 3 名。这类需求单靠 GROUP BY 很难优雅实现，因为“按销量排序取前 3 名”强制你要在分组内排次序，而窗口函数就是为这件事生的。

1.2 核心表结构与设计思路

我模拟的库一共四张表：精油主表、功效标签表、精油-功效关联表、销售流水表。少量表但足够覆盖绝大多数窗口函数场景。

CREATE TABLE oil ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, oil_name VARCHAR(100) NOT NULL, botanical_name VARCHAR(120) DEFAULT NULL, origin VARCHAR(64) DEFAULT NULL, price DECIMAL(8,2) NOT NULL, stock INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_price (price), KEY idx_origin (origin) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE effect ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, effect_name VARCHAR(50) NOT NULL, effect_code VARCHAR(50) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE oil_effect ( oil_id INT UNSIGNED NOT NULL, effect_id INT UNSIGNED NOT NULL, strength TINYINT NOT NULL DEFAULT 3 COMMENT '功效强度 1-5', PRIMARY KEY (oil_id, effect_id), KEY idx_effect (effect_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE sales ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, oil_id INT UNSIGNED NOT NULL, sale_date DATE NOT NULL, qty INT NOT NULL, amount DECIMAL(10,2) NOT NULL, KEY idx_oil_date (oil_id, sale_date), KEY idx_date (sale_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里有几个设计细节值得说说。oil_effect 表用复合主键，既保证一条油和一个功效不会重复关联，又能直接利用主键加速按 oil_id 或者 effect_id 的检索；额外加一个 idx_effect，是因为后面要频繁从“功效”反查精油，没有这个索引，JOIN 就只能走全表扫描。sales 表不是随便建一张流水表就完事，索引顺序我刻意设计成 (oil_id, sale_date)，后面查“某瓶油近 30 天销量”或者窗口函数按时间排序时，这个复合索引能直接覆盖。

1.3 模拟数据怎么造才能测出效果

数据量太少，窗口函数的排序和索引优化都看不出差别。我这边按 100 款精油、10 个功效标签、每款精油关联 2 到 5 个功效、销售流水 20000 条来做基准测试。

模拟数据不建议手写，适合用递归 CTE 生成，MySQL 8.0 的 WITH RECURSIVE 可以一次插入大量数据，比如批量生成两年内的销售流水，随机指派 oil_id，qty 控制在 1 到 10 之间。造数逻辑越贴近真实业务越好，特别是销售日期要保证连续，不然后面测 LAG、移动平均时会出现“昨天数据不存在”导致的 NULL，干扰你对结果的判断。

造完数据别忘了 ANALYZE TABLE，让统计信息尽量准。很多窗口函数查询慢，不是 SQL 写得不对，而是统计信息缺失导致优化器选择了糟糕的执行计划。

2. 窗口函数在功效分析中的典型应用

2.1 ROW_NUMBER / RANK / DENSE_RANK：功效热销榜的取舍

先说最常见需求：每种功效下，按销量取 Top 3 精油。这类“分组 Top N”问题，没有窗口函数之前只能用变量模拟，又丑又容易错。窗口函数一行搞定。

我先把 ROW_NUMBER、RANK、DENSE_RANK 三者的差异讲透，因为选错函数直接会导致业务数据“看起来差一点”。

SELECT eff.effect_name, o.oil_name, COALESCE(SUM(s.qty), 0) AS total_qty, ROW_NUMBER() OVER (PARTITION BY eff.id ORDER BY COALESCE(SUM(s.qty), 0) DESC) AS rn, RANK() OVER (PARTITION BY eff.id ORDER BY COALESCE(SUM(s.qty), 0) DESC) AS rk, DENSE_RANK() OVER (PARTITION BY eff.id ORDER BY COALESCE(SUM(s.qty), 0) DESC) AS drn FROM oil o LEFT JOIN sales s ON s.oil_id = o.id JOIN oil_effect oe ON oe.oil_id = o.id JOIN effect eff ON eff.id = oe.effect_id GROUP BY eff.id, eff.effect_name, o.id, o.oil_name;

这套 SQL 的坑点在于 SUM(s.qty) 出现在窗口函数里，GROUP BY 之后窗口函数依然可以工作，因为 MySQL 8.0 是在分组聚合结果上再计算窗口的。如果销量用 SUM 汇总后再排序，总销量完全相同的情况会频繁出现。三种函数的区别这时候就暴露了：

实际业务里，榜单要“唯一获奖者”用 ROW_NUMBER；要“并列名次但允许空位”用 RANK；要“并列名次但不想要编号跳变”用 DENSE_RANK。我做过一次需求，运营非要看“并列第一后面是不是第二”，最后发现 RANK 是更贴合业务直觉的方案，因为第二名空缺时他们会理直气壮说“没有第二名只有并列第一”。

取每组前三名的标准写法是套一层子查询：

SELECT * FROM ( SELECT eff.effect_name, o.oil_name, COALESCE(SUM(s.qty), 0) AS total_qty, ROW_NUMBER() OVER (PARTITION BY eff.id ORDER BY COALESCE(SUM(s.qty), 0) DESC) AS rn FROM oil o LEFT JOIN sales s ON s.oil_id = o.id JOIN oil_effect oe ON oe.oil_id = o.id JOIN effect eff ON eff.id = oe.effect_id GROUP BY eff.id, eff.effect_name, o.id, o.oil_name ) t WHERE t.rn <= 3;

这里要注意子查询别一口气 SELECT *，只选需要的字段，窗口函数的排序字段尽量和索引能匹配上，不然分组大时会出现 Using filesort 导致性能雪崩。

2.2 NTILE 分桶：精油价格分档背后的逻辑

功效筛选页面经常有价格区间分布，运营要的往往不是简单的 BETWEEN，而是“按从低到高平均分成 4 档，每档有多少款”。传统做法要写 CASE WHEN 判断区间，一旦档数变了 SQL 就得跟着改。NTILE 函数直接把数据平分成指定数量的桶。

SELECT oil_name, price, NTILE(4) OVER (ORDER BY price) AS price_bucket FROM oil;

这个查询结果里，每款精油会拿到 1 到 4 的桶号，1 代表价格最低的四分之一，4 代表最高的四分之一。底层实现上，NTILE 会先统计整个分区的行数，然后按桶数尽量平均分配，余数会塞给靠前的桶。

我实际使用中更建议在子查询里把桶号算好，再在外面 GROUP BY 桶号做聚合，这样写出来的筛选条件好维护。比如：

SELECT bucket, COUNT(*) AS oil_count, AVG(price) AS avg_price FROM ( SELECT price, NTILE(4) OVER (ORDER BY price) AS bucket FROM oil ) t GROUP BY bucket;

有一点要提：NTILE 的桶数如果大于行数，那多出来的桶就是空桶；如果行数不是桶数的整数倍，MySQL 会把剩余行分配到前面的桶。这个行为不是 bug，但容易让“每档数量一致”的希望落空，上线前最好先用 COUNT(*) 验证一下。

2.3 窗口聚合 SUM / AVG：累计销量与移动平均

窗口函数另一个高频场景是算“累计值”。传统写法要用自连接把之前所有日期加总，数据量一大就爆炸。窗口函数在 OVER 里写 ORDER BY 之后，SUM 自动变成累计逻辑。

比如“每款精油按时间累计销量”：

SELECT oil_id, sale_date, qty, SUM(qty) OVER (PARTITION BY oil_id ORDER BY sale_date) AS cumulative_qty FROM sales ORDER BY oil_id, sale_date;

这个查询的逻辑是：PARTITION BY oil_id 划出每一款精油独立的分区，ORDER BY sale_date 指明顺序，SUM 从分区第一行累加到当前行。如果只想算最近 3 天，要在 ORDER BY 后面加窗口框架子句：

SUM(qty) OVER ( PARTITION BY oil_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_3d

窗口框架子句非常容易被忽略。默认的框架逻辑在 ORDER BY 存在时是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是说只要你写了 ORDER BY，SUM 就是累计模式；如果写了 ORDER BY 又想算整体总和，必须显式写成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，或者干脆不写 ORDER BY。

移动平均也是一样套路，适合做销量趋势平滑。比如每款精油最近 7 天日均销量：

SELECT oil_id, sale_date, qty, AVG(qty) OVER ( PARTITION BY oil_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS avg_7d FROM sales;

用这个做筛选条件时要注意，窗口函数算出来的结果不能在 WHERE 里直接用，得在外面包一层子查询。这是最容易踩的语法坑，新手写 SELECT AVG(...) OVER(...) WHERE avg_7d > 5 就直接报错，因为 MySQL 里 WHERE 在窗口函数执行之前就做了过滤。

2.4 LAG / LEAD：环比与前后值对比

LAG 取当前行前面第 N 行的值，LEAD 取后面第 N 行的值。这两个函数在做“今天比昨天多卖了多少”这类需求时是真正的效率神器。

SELECT oil_id, sale_date, qty, LAG(qty, 1) OVER (PARTITION BY oil_id ORDER BY sale_date) AS prev_qty, qty - LAG(qty, 1) OVER (PARTITION BY oil_id ORDER BY sale_date) AS diff_from_prev FROM sales;

需要注意，第一行往前没有数据时，LAG 返回 NULL。实际写筛选条件要过滤掉 NULL，否则 diff_from_prev 也会是 NULL，NULL 参与计算会导致最终结果全被排除。处理方式基本两种，要么用 COALESCE(prev_qty, 0)，要么在子查询后 WHERE prev_qty IS NOT NULL。

LEAD 在业务里常用于倒推，比如“计算当前销量和下一天销量的变化”，逻辑跟 LAG 方向相反，用法完全一致。窗口函数里你对同一批数据既能取前值又能取后值，这意味着以前需要三四个自连接才能做的“环比列表”，现在一个子查询搞定。

3. 多条件过滤：组合筛选与窗口函数的联合

3.1 AND / OR 优先级：先跑一次再信直觉

多条件过滤最基础也最容易翻车的是 AND 和 OR 混用时的优先级问题。MySQL 中 AND 的优先级高于 OR，这个知识写 SQL 的人都背过，但实际写复杂筛选时还是会犯。

比如业务方要求“筛选舒缓功效或抗菌功效，且价格低于 200 元的精油”，新手很容易写：

SELECT * FROM oil WHERE effect_name = '舒缓' OR effect_name = '抗菌' AND price < 200;

这句实际执行的是“effect_name = 舒缓，或者同时满足抗菌且价格低于 200”，结果会把所有舒缓精油全部带出来，哪怕价格 500 的都算在内。症状轻的话没有歧义，重的话直接屏蔽了一个核心条件。

正确的写法：

SELECT * FROM oil WHERE (effect_name = '舒缓' OR effect_name = '抗菌') AND price < 200;

多条件筛选建议养成两个小习惯。第一，OR 条件必须加括号，哪怕只有两个条件，也不要赌自己和同事的优先级记忆；第二，文本性比较尽量用 IN 代替 OR，上面这个例子可以写成 effect_name IN ('舒缓', '抗菌') AND price < 200，执行效率更高，可读性也更强。

3.2 多对多功效标签：IN、EXISTS、聚合判断的选择

功效和精油是多对多关系，业务上的筛选往往有两种类型：至少具备某几个功效之一，和必须同时具备某几个功效。

“至少具备之一”直接用 EXISTS 或 IN：

SELECT o.id, o.oil_name FROM oil o WHERE o.id IN ( SELECT oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name IN ('舒缓', '抗菌') );

“必须同时具备多个功效”稍微绕一点，思路是先精确匹配每个功效对应的精油集合，再取交集。比如同时要有“舒缓”和“抗菌”：

SELECT o.id, o.oil_name FROM oil o WHERE o.id IN ( SELECT oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name = '舒缓' ) AND o.id IN ( SELECT oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name = '抗菌' );

这种写法比 JOIN 两张关联表然后 GROUP BY oil_id HAVING COUNT(DISTINCT effect_name) = 2 的表现好很多，因为两个 IN 子查询都能走 oil_effect 的 idx_effect 索引，而 HAVING COUNT 的做法需要先把大量关联结果集构建出来再聚合。

有一种特例是“需要具备的功效数量超过 3 个且都是指定标签”，这时候 IN + GROUP BY + HAVING 可能是最优解：

SELECT oe.oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name IN ('舒缓', '抗菌', '安神', '抗炎') GROUP BY oe.oil_id HAVING COUNT(DISTINCT eff.id) = 4;

注意 HAVING 的条件数字要跟 IN 列表里的数量一致，这个逻辑是“四个功效都要匹配上”。

3.3 CASE 加分排序：把“模糊匹配”变成可排名的分数

精油功效筛选页面有一个很常见的体验设计：用户勾选几个倾向，系统不是硬过滤，而是把符合项排到前面。比如用户说“想要洋甘菊相关、舒缓功效、200 元以下”，业务上希望“洋甘菊”这个关键词权重最高，其次是功效，再其次是价格。

纯 WHERE 过滤没法表达这种“权重倾向”，把条件转成 CASE 加分就可以：

SELECT o.id, o.oil_name, o.price, (CASE WHEN o.oil_name LIKE '%洋甘菊%' THEN 50 ELSE 0 END + CASE WHEN EXISTS (SELECT 1 FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE oe.oil_id = o.id AND eff.effect_name = '舒缓') THEN 30 ELSE 0 END + CASE WHEN o.price < 200 THEN 20 ELSE 0 END) AS match_score FROM oil o ORDER BY match_score DESC, o.stock DESC;

这个方案的核心价值在于把多个异构条件映射到同一把尺子上，排序结果就是产品运营口中的“相关度”。实际部署这类 SQL 要留意：CASE 表达式里嵌了相关子查询，数据量大时每行都会触发一遍子查询，性能可能会出问题。稳妥做法是先 JOIN 出功效标签字段，在外层做 CASE，或者把功效匹配先在 CTE 中算好。

3.4 窗口函数之前先过滤，别让窗口背全锅

窗口函数虽然解决了分组内排名的问题，但它运行前得先把结果集拿出来排序。如果数据范围没有提前缩小，窗口函数会成为慢查询的最大元凶。

最典型的反例是：先对全表做窗口排名，再在子查询外过滤只取某几个功效。这样窗口函数会对所有 100 款精油做排名，哪怕最终只要“舒缓”这一个功效下的数据。

正确姿势是把多条件过滤放到窗口函数之前：

SELECT * FROM ( SELECT o.id, o.oil_name, o.price, ROW_NUMBER() OVER (ORDER BY o.stock DESC) AS rn FROM oil o WHERE o.id IN ( SELECT oil_id FROM oil_effect WHERE effect_id = (SELECT id FROM effect WHERE effect_name = '舒缓') ) ) t WHERE t.rn <= 5;

这个版本只对命中“舒缓”的精油做排名，窗口函数处理的行数少了 70% 以上。说白了，窗口函数是个消耗大户，合理安排过滤顺序，比给窗口函数本身优化要有效得多。

4. 优化实测：从 3 秒到 0.08 秒

4.1 索引设计：等值字段在前，范围字段在后

多条件过滤场景里最常被忽略的是联合索引的字段顺序。MySQL 联合索引的最左前缀原则决定了索引能怎么被利用，顺序放错就得回表，SQL 就会从 0.1 秒变成 2 秒。

以“筛出价格在 200 到 300 之间、产地为法国、按最近销量排序”为例，正确的联合索引应该是：

ALTER TABLE oil ADD INDEX idx_origin_price (origin, price);

这里把 origin 放前面是因为它是等值条件（WHERE origin = '法国'），price 是范围条件（BETWEEN 200 AND 300）。查询执行时 MySQL 会先按 origin 精准锁定一个索引分支，再在分支内用 price 的索引排序。顺序反过来，origin = '法国' 就只能过滤已经不存在的字段顺序。

如果查询里还有 stock 的排序，考虑直接用覆盖索引把结果集控制在索引内部。比如销售统计数据，建议建：

ALTER TABLE sales ADD INDEX idx_oil_date_qty (oil_id, sale_date, qty);

这个索引能让按 oil_id 分组、按 sale_date 排序、对 qty 做聚合的查询全部在索引内部完成，不需要回表读额外记录。覆盖索引对窗口函数尤其有价值，因为 OVER 里的 PARTITION BY 和 ORDER BY 字段如果能被索引覆盖，filesort 大概率可以避免。

4.2 EXPLAIN 解读：三秒钟定位慢查询源头

遇到慢 SQL 第一动作不是改 SQL，而是看 EXPLAIN。我举个例子，有次同事写了一段查询，功效表 JOIN 关联表再 JOIN 精油表取 Top 10，跑出来 3 秒多。

EXPLAIN 输出里 type 列和 Extra 列是最有信息量的。ALL 说明全表扫描，ref 说明在用非唯一索引等值匹配，const 说明直接通过主键或唯一索引定位单行。当时那段 SQL 的 EXPLAIN 显示 oil 表 type 是 ALL，rows 是 100，Extra 是 Using where; Using filesort。

问题很清楚：JOIN 顺序里 MySQL 选择了先扫 oil 全表，然后每条记录再查关联表。同时窗口函数里的 ORDER BY 因为没法用索引，只能 filesort。解决方式是先让 filter 走索引缩小结果集，再加一个 EXPLAIN ANALYZE 看时间花在哪个步骤：

EXPLAIN ANALYZE SELECT ...

EXPLAIN ANALYZE 是 MySQL 8.0.18 新增的能力，会输出每个步骤的实际行数和执行时间，比传统 EXPLAIN 的估算值更可信。我调整后的查询 type 变成 ref，Extra 里没有了 Using filesort，执行时间从 3 秒降到 0.08 秒。

4.3 子查询条件下推：先窄后宽是黄金法则

多条件过滤最怕的是 JOIN 一大坨临时结果再过滤。优化核心始终是“先窄后宽”：把能过滤的条件尽量下沉到最早执行的部分。

不推荐的写法：

SELECT tmp.*, sales_agg.total_qty FROM ( SELECT o.id, o.oil_name, o.price, eff.effect_name FROM oil o JOIN oil_effect oe ON oe.oil_id = o.id JOIN effect eff ON eff.id = oe.effect_id ) tmp LEFT JOIN ( SELECT oil_id, SUM(qty) AS total_qty FROM sales GROUP BY oil_id ) sales_agg ON sales_agg.oil_id = tmp.id WHERE tmp.effect_name = '舒缓' AND tmp.price < 200;

这个写法的问题是：即使业务只要“舒缓”功效，内层子查询也把全量 300 多行标签关系 JOIN 出来了，然后再在外面过滤。精油数据量小问题不大，一旦换成千万级流水，这类写法就是数据库性能杀手。

推荐的写法：

WITH filtered_oil AS ( SELECT o.id, o.oil_name, o.price FROM oil o WHERE o.price < 200 AND o.id IN ( SELECT oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name = '舒缓' ) ), sales_agg AS ( SELECT oil_id, SUM(qty) AS total_qty FROM sales WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY oil_id ) SELECT fo.*, COALESCE(sa.total_qty, 0) AS total_qty FROM filtered_oil fo LEFT JOIN sales_agg sa ON sa.oil_id = fo.id;

两者的差距在于过滤时机。第一个版本是全量 JOIN 后过滤，第二个版本是先在每张单表上做最小化，再进行关联。多条件过滤的 SQL 优化思路其实就是一句话：让每一层的参与数据量尽量小，下一层才会轻。

4.4 LIMIT 大偏移与窗口函数的分页问题

窗口函数配合分页时，OFFSET 的写法有个隐蔽的性能陷阱。如果运营页面要展示“舒缓功效下按销量排名的第 10001 到 10020 名”，经典写法：

SELECT * FROM ( SELECT ... ROW_NUMBER() OVER (ORDER BY total_qty DESC) rn ... ) t WHERE t.rn BETWEEN 10001 AND 10020;

这个查询窗口函数已经对所有“舒缓”精油做完排名了，如果总数只有 200 行，那 10000 的偏移根本不成立，全查出来也就 0.01 秒。但如果每款精油的销售流水有几万行，窗口分区又大，大 OFFSET 会拖慢排序。

优化方案是记录上一页的锚点值，用 WHERE 带走过去的值再排名：

SELECT ... FROM ... WHERE rn > 10000 ORDER BY rn LIMIT 20;

要不要优化取决于实际偏移量。偏移超过结果集的 10% 且结果集本身超过百万行时，大 OFFSET 基本都会造成明显延迟，这时候换成锚点分页能拿到指数级的提升。

5. 三个真实业务需求的全流程实现

5.1 需求一：300元内带“安神”功效、库存充足的前三名

这是功效筛选页面最常见的组合查询，要求同时满足价格、功效、库存、销量排名四个条件。

WITH target_effect AS ( SELECT id FROM effect WHERE effect_name = '安神' ), base AS ( SELECT o.id, o.oil_name, o.price, o.stock, COALESCE(SUM(s.qty), 0) AS total_qty FROM oil o LEFT JOIN sales s ON s.oil_id = o.id WHERE o.price <= 300 AND o.stock >= 20 AND o.id IN (SELECT oil_id FROM oil_effect WHERE effect_id = (SELECT id FROM target_effect)) GROUP BY o.id, o.oil_name, o.price, o.stock ) SELECT * FROM ( SELECT b.*, ROW_NUMBER() OVER (ORDER BY b.total_qty DESC) AS rn FROM base b ) t WHERE t.rn <= 3;

执行计划里 GROUP BY 会先聚合出总销量，窗口函数在聚合结果之上做排名。这里 COALESCE 不能省，LEFT JOIN 导致无销量记录的精油 total_qty 为 NULL，如果用 SUM(s.qty) 直接排序，NULL 会被排到前面还是后面取决于排序方向，业务结果会出现“没有销量的油排第一”这种笑话。

5.2 需求二：找出每个产地高于自己产地均价且带“舒缓”功效的油

这个需求必须用窗口函数，否则得对每个产地手动算均价再关联回主表。窗口函数一次解决：

WITH price_with_avg AS ( SELECT o.id, o.oil_name, o.origin, o.price, AVG(o.price) OVER (PARTITION BY o.origin) AS origin_avg_price FROM oil o WHERE o.id IN ( SELECT oil_id FROM oil_effect oe JOIN effect eff ON eff.id = oe.effect_id WHERE eff.effect_name = '舒缓' ) ) SELECT id, oil_name, origin, price, origin_avg_price, ROUND(price - origin_avg_price, 2) AS price_diff FROM price_with_avg WHERE price > origin_avg_price ORDER BY price_diff DESC;

这里需要注意窗口函数的执行顺序。WHERE price > origin_avg_price 在外层子查询执行时，窗口函数已经计算完成，所以可以直接用。如果试图在同一个 SELECT 里写 WHERE price > AVG(price) OVER(...)，一定会报错，因为窗口函数发生在 WHERE 过滤之后。

5.3 需求三：销量环比涨幅超过20%的精油清单

先算每款精油当日销量和前一日销量，再在外层过滤涨幅条件：

WITH daily_with_prev AS ( SELECT oil_id, sale_date, qty, LAG(qty, 1) OVER (PARTITION BY oil_id ORDER BY sale_date) AS prev_qty FROM sales WHERE sale_date >= '2025-01-01' ) SELECT oil_id, sale_date, qty, prev_qty, ROUND((qty - prev_qty) / prev_qty * 100, 2) AS growth_pct FROM daily_with_prev WHERE prev_qty IS NOT NULL AND prev_qty > 0 AND (qty - prev_qty) / prev_qty > 0.2 ORDER BY growth_pct DESC;

这里要特别小心 prev_qty > 0，不写的话，分母为零会出现除零错误。MySQL 中除零会返回 NULL，但同一条记录里 qty - prev_qty 还会继续算，结果就会把“前一天卖 0 瓶、今天卖 10 瓶”的案例算成无穷大，排序出来一坨垃圾。实际还应该对增长率加上限，比如只统计 prev_qty 和 qty 都在 5 以上的记录，避免偶然性数据污染排名。

6. 常见问题与避坑清单

6.1 MySQL 版本不能低于 8.0

窗口函数和 CTE 都是 MySQL 8.0 引入的能力，5.7 里直接报语法错。如果你还在用 5.7，升级到 8.0 是最优先的事，不然这篇文章里一大半 SQL 都跑不动。升级时还要注意 SQL 模式的差异，ONLY_FULL_GROUP_BY 在 8.0 默认开启，老代码里 GROUP BY 字段不全的情况会立刻暴露。

很多本地开发环境用的还是 MariaDB，它对窗口函数的支持跟 MySQL 官方略有差异，部署前要在目标库上做兼容性测试。

6.2 NULL 处理不要偷懒

窗口函数 + 连表中，NULL 是最大隐患。LEFT JOIN 没有匹配记录时，聚合函数 SUM、AVG 会忽略 NULL，但排序时 NULL 的升降序位置和业务预期可能不一致。我在实战中总结了一个原则：凡是最终要展示或者参与排序的数值，一律 COALESCE 兜底；凡是参与计算的 LAG/LEAD 结果，一律先判断 IS NOT NULL 再参与下一步。

6.3 多对多 JOIN 导致的行数翻倍

oil_effect 和 effect 两张表 JOIN 后，如果一款精油关联了 4 个功效，结果集里就会出现 4 行相同精油记录。这时候再对 SUM(qty) 做聚合，销量会被放大 4 倍。规避方式就是在聚合之前先把精油过滤成唯一集合，或者用相关子查询 IN / EXISTS 代替直接 JOIN。

6.4 HAVING 远比你想象中更晚执行

HAVING 在窗口函数之前还是之后执行？答案是窗口函数之后。很多人误以为 HAVING 只是“GROUP BY 之后的 WHERE”，结果在 HAVING 里引用窗口函数别名，直接报错。正确做法是包一层子查询再过滤别名，这是 MySQL 8.0 的语法限制，不要指望它能自动识别。

6.5 复合排序别忽略第二排序字段

窗口函数 ORDER BY 后面只写一个字段时，并列数据会按随机顺序分配排名，下一次跑结果可能就变了。所有需要保证可复现的排名场景，都要补上唯一性兜底字段，比如 ORDER BY total_qty DESC, o.id ASC。特别是在做分页或者分桶的时候，排名不稳定会导致同一页数据刷新后变化，运营会直接判定为 bug。

我在这个项目里踩过最深的坑是窗口函数别名在外层不能用，一度以为是数据库连接池把连接重置了，排查半天才发现是 SQL 模式导致的语义误解。后来我把所有窗口查询都强制写成“子查询 + 外层过滤”的结构，再也没被这类问题咬过。你如果刚开始接触窗口函数，建议也按这种标准化套路写，先用子查询包一层，外层做条件过滤，虽然多几行代码，但排查起来省太多时间。

这套方案后续还能扩展，比如把功效标签改成向量化的关键词权重，配合全文索引做更精细的匹配排序，或者把销售流水放到分区表里提升日期范围查询的效率。只要数据模型保持合理，窗口函数能撑起的业务复杂度远比你想的要多。