本教程不会只扔代码。每一个知识点都会从真实的“业务问题”出发先弄清楚为什么要用再讲明白底层怎么运作最后给出可运行的 SQL并指出新手最容易掉进去的坑。一、 窗口函数——不是“高大上”是解决问题的利器窗口函数最让人困惑的地方不是语法而是“它到底看到了哪些行”。这一节会花较多笔墨把“窗口帧”讲透。1.1 累计计算YTD / MTD——老板想看“截至本月今年一共卖了多少” 业务场景销售表里记录着每个月的销量。老板想要一张报表每个产品、每个月都要显示从1月累计到当月的总销量YTD。你不能只在最后一行加个总和因为每个月都要看到累计值。 原理解析核心就是SUM(quantity) OVER (...)但窗口函数的秘密藏在OVER()里的三个要素要素作用本例写法PARTITION BY把数据分成几个“小世界”互不干扰PARTITION BY product_id, year(按产品年份)ORDER BY在每个小世界里按什么顺序排列ORDER BY year_month窗口帧每一行计算时能看到小世界里的哪些行ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW重点解释窗口帧如果只写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;运行逻辑示意假设某产品year_monthquantity窗口包含的行从开始到当前ytd_qty202501100只有第1行10020250280第1~2行180202503120第1~3行300⚠️ 常见坑忘了ORDER BY导致每个月的ytd_qty都变成全年总计完全失去累计意义。在WHERE里直接用ytd_qty过滤因为窗口函数在SELECT阶段才计算WHERE看不到它。必须套一层子查询。1.2 排名与占比帕累托分析——找出贡献80%销售额的核心产品 业务场景“二八法则”分析我们想找出哪些产品合起来贡献了 80% 的销售额好把资源集中到它们身上。 原理解析需要两步按销售额从高到低排列产品。计算每个产品的累计销售额占总销售额的百分比。关键技术点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:50productamt排名(row_number)cum_amt 计算cum_pctD300130037.5%B200250062.5%E150365081.25%A100475093.75%C505800100%取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~101是最高 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让函数只处理符合条件的行不符合的返回NULLCOUNT会忽略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 month01 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; 如果想要动态列不固定月份需用存储过程或在应用层拼 SQLDWS 的crosstab可参考官方文档。⚠️ 常见坑必须用聚合函数MAX/SUM包裹CASE因为GROUP BY后每列只能有一个值。如果一个产品在某个月份有多条记录要先在外面汇总好或者用SUM汇总。2.3 列转行Unpivot——把宽表变回干净的长表 业务场景有人给了你一张列是各月份的表如m01, m02...你需要分析趋势就必须先把列转回行。 原理解析可以用UNION ALL将每一列单独查出后摞起来也可以用UNNEST同时拆解多个数组。✍️ SQL 示例-- 方法1UNION 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 ...; -- 方法2UNNEST更简洁 SELECT product_id, unnest(ARRAY[202501,202502,202503]) AS month, unnest(ARRAY[m01, m02, m03]) AS amount FROM wide_table;三、 递归 CTE——处理树形结构BOM 展开的灵魂 业务场景制造业的物料清单BOM是典型的多层树一个成品由多个半成品组成半成品又由原料组成…… 我们想要从某个成品出发展示它所有的子物料及层级。 原理解析递归 CTE 分为两部分种子查询锚点不递归的部分通常是顶层节点。递归查询引用 CTE 自身每次迭代都找出下一层子节点。使用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 里跑只需根据实际表名和字段微调。