高级 SQL 实战教程（华为云 DWS / PostgreSQL 版）

📌 本教程不会只扔代码。

每一个知识点都会从真实的“业务问题”出发，先弄清楚为什么要用，再讲明白底层怎么运作，最后给出可运行的 SQL，并指出新手最容易掉进去的坑。

一、📊 窗口函数——不是“高大上”，是解决问题的利器

窗口函数最让人困惑的地方不是语法，而是“它到底看到了哪些行”。这一节会花较多笔墨把“窗口帧”讲透。

1.1 累计计算（YTD / MTD）——老板想看“截至本月，今年一共卖了多少？”

🧠 业务场景

销售表里记录着每个月的销量。老板想要一张报表：每个产品、每个月，都要显示从1月累计到当月的总销量（YTD）。你不能只在最后一行加个总和，因为每个月都要看到累计值。

📖 原理解析

核心就是SUM(quantity) OVER (...)，但窗口函数的秘密藏在OVER()里的三个要素：

重点解释窗口帧：

如果只写ORDER BY而不写帧，SQL 标准会默认RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。但在实际中我们常显式写成ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，含义是：从分组内的第一行开始，一直累加到当前行。

如果不写ORDER BY，那么帧默认是整个分区，SUM会变成全量总和，而不再有累计效果。

✍️ SQL 示例

-- 按产品累计年初至今（YTD）销量（假设 year_month 格式 '202501' 等） SELECT year_month, product_id, quantity, SUM(quantity) OVER ( PARTITION BY product_id, SUBSTR(year_month, 1, 4) -- 按产品+年份分区 ORDER BY year_month -- 按月排序 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从年初第一行累加到当前月 ) AS ytd_qty FROM orders WHERE year_month BETWEEN '202501' AND '202505';

运行逻辑示意（假设某产品）：

⚠️ 常见坑

• 忘了ORDER BY，导致每个月的ytd_qty都变成全年总计，完全失去累计意义。
• 在WHERE里直接用ytd_qty过滤，因为窗口函数在SELECT阶段才计算，WHERE看不到它。必须套一层子查询。

1.2 排名与占比（帕累托分析）——找出贡献80%销售额的核心产品

🧠 业务场景

“二八法则”分析：我们想找出哪些产品合起来贡献了 80% 的销售额，好把资源集中到它们身上。

📖 原理解析

需要两步：

1. 按销售额从高到低排列产品。
2. 计算每个产品的累计销售额占总销售额的百分比。

关键技术点：

• SUM(amt) OVER (ORDER BY amt DESC)为什么会产生累计？

因为ORDER BY存在时，窗口帧默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（或我们理解为ROWS ...）。于是它会把从第一名加到当前名的金额加起来。

• 为了得到占比，再除以SUM(amt) OVER ()，后者没有ORDER BY，帧是整个分区，即总金额。
• ROW_NUMBER()/RANK()/DENSE_RANK()的区别在于处理并列值时的编号方式。

✍️ SQL 示例

WITH product_amt AS ( SELECT product_id, SUM(amount) AS amt FROM orders WHERE year_month BETWEEN '202501' AND '202505' GROUP BY product_id ), ranked AS ( SELECT product_id, amt, RANK() OVER (ORDER BY amt DESC) AS rank_num, -- 有并列会跳号 DENSE_RANK() OVER (ORDER BY amt DESC) AS dense_rank, -- 不跳号 ROW_NUMBER() OVER (ORDER BY amt DESC) AS row_num, -- 绝对唯一 SUM(amt) OVER (ORDER BY amt DESC) AS cum_amt, -- 累计金额（默认帧导致） SUM(amt) OVER () AS total_amt, -- 全量总金额 ROUND( SUM(amt) OVER (ORDER BY amt DESC) * 100.0 / SUM(amt) OVER (), 2 ) AS cum_pct FROM product_amt ) SELECT * FROM ranked WHERE cum_pct <= 80; -- 只拿累计占比前80%的产品

🔍 过程数据模拟

假设几个产品的金额：D:300, B:200, E:150, A:100, C:50

取cum_pct <= 80会得到 D、B 两个产品，它们合计贡献了 62.5%，未到 80% 则再加上 E 就到 81.25%，所以最终可能还要微调逻辑，但这里演示了核心机制。

⚠️ 常见坑

• ORDER BY列有重复值时，累计算出可能“跳变”：因为RANGE模式会将相同值的行视为同一帧边界，导致它们的累计值一致。如果必须按物理行严格累计，请显式加ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，并且排序字段加上唯一键（如ORDER BY amt DESC, product_id）。
• 使用(cum_amt / total_amt)时，记得total_amt要用SUM(...) OVER ()，不能用普通聚合，否则会报错或结果错误。

1.3 前后行取值（环比 / 同比）——这个月比上个月涨了多少？

🧠 业务场景

月度销售报表里，经常要计算环比增长（与上月比）和同比增长（与去年同月比）。

📖 原理解析

窗口函数LAG(列, 偏移量, 默认值)可以从当前行往回看若干行；LEAD则是向后看。配合ORDER BY按时间排序，就能轻松取到上月、去年同月的值。

与自连接相比，LAG/LEAD不需要多次扫描表，性能更好，语法更简洁。

✍️ SQL 示例

SELECT year_month, SUM(amount) AS total_amt, LAG(SUM(amount), 1) OVER (ORDER BY year_month) AS last_month_amt, LAG(SUM(amount), 12) OVER (ORDER BY year_month) AS last_year_same_month_amt, SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY year_month) AS mom_diff, ROUND( (SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY year_month)) * 100.0 / NULLIF(LAG(SUM(amount), 1) OVER (ORDER BY year_month), 0), 2 ) AS mom_pct FROM orders GROUP BY year_month ORDER BY year_month;

⚠️ 常见坑

• 第一行（最早的月份）没有上一行，LAG返回NULL，计算差值结果也是NULL，这符合业务常识。
• 注意除零错误：用NULLIF(分母, 0)避免。
• 同比偏移量要根据实际数据跨度设置，这里是 12 个月。

1.4 分箱与分级（NTILE, PERCENT_RANK）——给产品打上“头部/腰部/尾部”标签

🧠 业务场景

运营想把产品按销售额分成 A、B、C 三级，或者分成 10 个等频段来观察分布。

📖 原理解析

• NTILE(N)把数据按ORDER BY排序后尽量均匀地切成 N 个桶，每桶行数大致相等。
• PERCENT_RANK()计算相对排名百分比，方便按比例划分（如前 5% 为 A 级）。
• 这两种函数都依赖ORDER BY，但不需指定窗口帧，因为它们本身就是排名类函数，会自行处理全分区。

✍️ SQL 示例

SELECT product_id, amount, NTILE(10) OVER (ORDER BY amount DESC) AS decile, -- 1~10，1是最高 NTILE(4) OVER (ORDER BY amount DESC) AS quartile, CASE WHEN PERCENT_RANK() OVER (ORDER BY amount DESC) <= 0.05 THEN 'A' WHEN PERCENT_RANK() OVER (ORDER BY amount DESC) <= 0.20 THEN 'B' ELSE 'C' END AS abc_class FROM product_summary;

二、🧮 高级聚合与透视——把报表“掰弯”再“拉直”

2.1 条件聚合——一次分组算出多种指标，避免多次全表扫描

🧠 业务场景

你需要一张报表，按品类列出：

• 销量在 0~500 的产品数量、销售额
• 500~1000 的、1000 以上的……

如果用多个子查询分别算再 JOIN，会多次扫表。条件聚合用一个查询就能搞定。

📖 原理解析

在聚合函数（COUNT、SUM等）内部使用CASE WHEN，让函数只处理符合条件的行，不符合的返回NULL（COUNT会忽略NULL）或0（对SUM无害）。这样就能在一次GROUP BY中同时产出多个分层指标。

✍️ SQL 示例

SELECT category_code, category_name, COUNT(DISTINCT CASE WHEN ytd_qty < 500 THEN product_id END) AS cnt_0_500, COUNT(DISTINCT CASE WHEN ytd_qty BETWEEN 500 AND 1000 THEN product_id END) AS cnt_500_1000, COUNT(DISTINCT CASE WHEN ytd_qty > 1000 THEN product_id END) AS cnt_1000_plus, SUM(CASE WHEN ytd_qty < 500 THEN ytd_amt ELSE 0 END) AS amt_0_500, SUM(CASE WHEN ytd_qty BETWEEN 500 AND 1000 THEN ytd_amt ELSE 0 END) AS amt_500_1000, SUM(CASE WHEN ytd_qty > 1000 THEN ytd_amt ELSE 0 END) AS amt_1000_plus FROM product_ytd GROUP BY category_code, category_name;

⚠️ 常见坑

• COUNT里如果不加DISTINCT，可能会在一个产品出现多次时重复计数（如果数据源已去重则问题不大，但加DISTINCT更安全）。
• ELSE 0对SUM是必须的，否则NULL会导致整个SUM变成NULL。

2.2 行转列（Pivot）——把月份从行变成列，做成二维报表

🧠 业务场景

给老板看的 Excel 表喜欢这样的格式：一行一个产品，后面跟着 1月销售额、2月销售额…… 但数据库里是每月一行。我们需要行转列。

📖 原理解析

通用方法是条件聚合：用MAX(CASE WHEN month='01' THEN amount END)等把多行压缩成单行，GROUP BY产品。

PostgreSQL / 华为云 DWS 还支持crosstab函数，能更灵活地动态生成列，但需要安装tablefunc扩展。

✍️ SQL 示例（条件聚合）

SELECT product_id, MAX(CASE WHEN year_month = '202501' THEN amount END) AS m01, MAX(CASE WHEN year_month = '202502' THEN amount END) AS m02, MAX(CASE WHEN year_month = '202503' THEN amount END) AS m03, MAX(CASE WHEN year_month = '202504' THEN amount END) AS m04, MAX(CASE WHEN year_month = '202505' THEN amount END) AS m05 FROM orders WHERE year_month BETWEEN '202501' AND '202505' GROUP BY product_id;

💡 如果想要动态列（不固定月份），需用存储过程或在应用层拼 SQL，DWS 的crosstab可参考官方文档。

⚠️ 常见坑

• 必须用聚合函数（MAX/SUM）包裹CASE，因为GROUP BY后每列只能有一个值。
• 如果一个产品在某个月份有多条记录，要先在外面汇总好，或者用SUM汇总。

2.3 列转行（Unpivot）——把宽表变回干净的长表

🧠 业务场景

有人给了你一张列是各月份的表（如m01, m02...），你需要分析趋势，就必须先把列转回行。

📖 原理解析

可以用UNION ALL将每一列单独查出后摞起来，也可以用UNNEST同时拆解多个数组。

✍️ SQL 示例

-- 方法1：UNION ALL SELECT product_id, '202501' AS month, m01 AS amount FROM wide_table WHERE m01 IS NOT NULL UNION ALL SELECT product_id, '202502', m02 FROM wide_table WHERE m02 IS NOT NULL ...; -- 方法2：UNNEST（更简洁） SELECT product_id, unnest(ARRAY['202501','202502','202503']) AS month, unnest(ARRAY[m01, m02, m03]) AS amount FROM wide_table;

三、🌳 递归 CTE——处理树形结构，BOM 展开的灵魂

🧠 业务场景

制造业的物料清单（BOM）是典型的多层树：一个成品由多个半成品组成，半成品又由原料组成…… 我们想要从某个成品出发，展示它所有的子物料及层级。

📖 原理解析

递归 CTE 分为两部分：

1. 种子查询（锚点）：不递归的部分，通常是顶层节点。
2. 递归查询：引用 CTE 自身，每次迭代都找出下一层子节点。
3. 使用UNION ALL连接两部分，需加终止条件（如限制层级）防止死循环。

华为云 DWS 使用WITH RECURSIVE语法。

✍️ SQL 示例

WITH RECURSIVE bom_tree (parent_id, child_id, qty, lvl) AS ( -- 锚点：成品 SELECT parent_id, child_id, qty, 1 FROM product_bom WHERE parent_id = 'FG-001' UNION ALL -- 递归：找下一层 SELECT b.parent_id, b.child_id, b.qty, t.lvl + 1 FROM product_bom b JOIN bom_tree t ON b.parent_id = t.child_id WHERE t.lvl < 10 -- 安全出口 ) SELECT * FROM bom_tree ORDER BY lvl, parent_id;

⚠️ 常见坑

• 如果不加层级限制，数据中存在闭环时会无限递归，导致查询失败。
• 递归部分的JOIN条件要写对：父物料 = 上一层查出的子物料。
• 递归 CTE 内部不支持聚合和DISTINCT，所以不能在里面直接做汇总。

四、🤝 高级 JOIN 技巧

4.1 不等值 JOIN——把数值匹配到区间（折扣档位）

🧠 业务场景

促销折扣按购买数量分档：0-100 件无折扣，100-500 件 5% 折扣，500 以上 8% 折扣。我们要给每条订单匹配到对应的折扣。

📖 原理解析

不能用等值连接，要用>=和<组合成区间条件。

✍️ SQL 示例

SELECT o.order_id, o.quantity, d.discount_pct FROM orders o JOIN discount_tier d ON o.quantity >= d.min_qty AND o.quantity < d.max_qty;

⚠️ 常见坑

• 区间必须设计得无缝覆盖，且无重叠，否则可能一行匹配多条。
• 如果某数量没有匹配到折扣（低于最小门槛），需要使用LEFT JOIN并设置默认值。

4.2 自 JOIN——找出连续三个月销量下滑的产品

🧠 业务场景

库存管理需要警惕“连续下滑”的产品，及时调整采购。

📖 原理解析

如果数据库不支持窗口函数，可通过自 JOIN 将相邻月份的记录对齐。但有了LAG后，用窗口函数更优雅。我们展示两种方式。

✍️ SQL 示例（窗口函数法，推荐）

WITH monthly AS ( SELECT product_id, year_month, SUM(quantity) AS qty FROM orders GROUP BY product_id, year_month ), lagged AS ( SELECT *, LAG(qty, 1) OVER (PARTITION BY product_id ORDER BY year_month) AS prev_qty, LAG(qty, 2) OVER (PARTITION BY product_id ORDER BY year_month) AS prev2_qty FROM monthly ) SELECT DISTINCT product_id FROM lagged WHERE qty < prev_qty AND prev_qty < prev2_qty;

自 JOIN 版本（兼容旧系统）

SELECT a.product_id FROM monthly a JOIN monthly b ON a.product_id = b.product_id AND b.year_month = to_char(to_date(a.year_month,'YYYYMM') - interval '1 month', 'YYYYMM') JOIN monthly c ON a.product_id = c.product_id AND c.year_month = to_char(to_date(a.year_month,'YYYYMM') - interval '2 month', 'YYYYMM') WHERE a.qty < b.qty AND b.qty < c.qty;

五、⚡ 性能优化——不止让代码跑通，还要跑得快

5.1 SQL 执行顺序（必背）

FROM → WHERE → GROUP BY → HAVING → SELECT → WINDOW → ORDER BY → LIMIT

核心影响：

• WHERE看不见窗口函数的结果，因为窗口在SELECT阶段才计算。
• GROUP BY里不能直接写窗口函数。
• 如果需要对窗口函数结果再过滤，必须用子查询。

5.2 常见性能陷阱与解法

• 窗口函数+大表无索引：确保ORDER BY和PARTITION BY的列上有索引或分布键设计良好。
• 条件聚合优于多次子查询 JOIN：一个GROUP BY搞定多个分层。
• 避免在ORDER BY中使用非唯一字段导致窗口帧行为异常：加上主键或唯一业务键使排序稳定。

5.3 DWS 特殊建议

• 利用分布键让需要关联或分区内计算的数据落在同一节点，减少数据重分布。
• 对于超大表，按时间分区，查询时带上分区键可以裁剪大量数据。

六、🧪 完整实战：产品多维分层分析

需求：按商品类别，统计 YTD 销量在 0-500、500-1000 的产品数和销售额；同时按销售额从高到低，找出贡献前 5%、5%-10% 的产品数和销售额。

WITH monthly_agg AS ( -- 基础月度汇总 SELECT year_month, category_code, category_name, product_id, SUM(quantity) AS qty, SUM(amount) AS amt FROM orders WHERE year_month BETWEEN '202501' AND '202505' GROUP BY year_month, category_code, category_name, product_id ), ytd_agg AS ( -- 产品YTD累计 SELECT category_code, category_name, product_id, SUM(qty) AS ytd_qty, SUM(amt) AS ytd_amt FROM monthly_agg GROUP BY category_code, category_name, product_id ), ranked AS ( -- 计算累计金额占比 SELECT *, -- 注意：为了防止相同金额导致累计占比异常，ORDER BY 加上 product_id 保证唯一 SUM(ytd_amt) OVER ( PARTITION BY category_code ORDER BY ytd_amt DESC, product_id ) / NULLIF(SUM(ytd_amt) OVER (PARTITION BY category_code), 0) AS cum_pct FROM ytd_agg ) SELECT category_code, category_name, -- 按销量分层 COUNT(CASE WHEN ytd_qty < 500 THEN 1 END) AS cnt_qty_0_500, COUNT(CASE WHEN ytd_qty BETWEEN 500 AND 1000 THEN 1 END) AS cnt_qty_500_1000, SUM(CASE WHEN ytd_qty < 500 THEN ytd_amt ELSE 0 END) AS amt_qty_0_500, SUM(CASE WHEN ytd_qty BETWEEN 500 AND 1000 THEN ytd_amt ELSE 0 END) AS amt_qty_500_1000, -- 按销售额分层 COUNT(CASE WHEN cum_pct <= 0.05 THEN 1 END) AS cnt_top5, COUNT(CASE WHEN cum_pct > 0.05 AND cum_pct <= 0.10 THEN 1 END) AS cnt_top5_10, SUM(CASE WHEN cum_pct <= 0.05 THEN ytd_amt ELSE 0 END) AS amt_top5, SUM(CASE WHEN cum_pct > 0.05 AND cum_pct <= 0.10 THEN ytd_amt ELSE 0 END) AS amt_top5_10 FROM ranked GROUP BY category_code, category_name;

这段 SQL 演示了前面几乎所有知识点的组合：条件聚合、累计窗口、分层统计。

你可以直接拿到 DWS 里跑，只需根据实际表名和字段微调。