目录
第 1 章 集合运算基础
1.1 什么是集合运算
1.2 电力行业数据准备
1.3 UNION 与 UNION ALL（并集）
1.4 INTERSECT 与 INTERSECT ALL（交集）
1.5 EXCEPT 与 EXCEPT ALL（差集）
1.6 集合运算的注意事项
1.7 性能优化与常见错误
第 2 章 超级函数（多维聚合）
2.1 为什么需要多维聚合
2.2 电力行业多维数据准备
2.3 GROUPING SETS：自定义维度组合
2.4 ROLLUP：层级递进汇总
2.5 CUBE：全交叉汇总
2.6 识别小计行：GROUPING（） 与 GROUPING_ID（）
2.7 性能对比
第 3 章 行列转换
3.1 什么是行转列与列转行
3.2 行转列：静态 CASE WHEN + GROUP BY
3.3 半动态行转列：crosstab 扩展
3.4 列转行：UNION ALL、LATERAL、hstore、jsonb
3.5 超宽表处理策略
第 4 章 补充知识点与综合练习
4.1 GROUPING（） 深度解析（区分汇总 NULL 与原始 NULL）
4.2 电力行业综合练习题
第 5 章 总结
第 1 章 集合运算基础
1.1 什么是集合运算
理论讲解：
集合运算是指将两个SELECT查询的结果集看作数学中的集合，然后进行并集（UNION）、交集（INTERSECT）、差集（EXCEPT）操作。不同于JOIN是在横向（增加列）上连接表，集合运算是在纵向（增加行）上拼接结果。
并集：把两个结果集上下堆叠在一起（列数必须相同）。
交集：取出同时出现在两个结果集中的行。
差集：取出出现在第一个结果集但不出现在第二个结果集中的行。
与 JOIN 的本质区别：
方向：集合运算是纵向（行数增加），
JOIN是横向（列数增加）。列要求：集合运算要求两个
SELECT输出的列数相同且对应列类型兼容；JOIN无此限制。重复行：集合运算默认会去重（除非使用
ALL）；JOIN默认保留所有行。
口诀：纵向拼、列对齐、去重看 ALL。
1.2 电力行业数据准备
为了演示，我们创建一个电力行业的数据库模式，包含发电厂信息表、发电记录表、售电记录表等。
-- 创建电力行业模式 CREATE SCHEMA IF NOT EXISTS power; -- 1. 发电厂表 CREATE TABLE power.plants ( plant_id SERIAL PRIMARY KEY, plant_name VARCHAR(50) NOT NULL, region VARCHAR(20), -- 区域：华东、华北、华南等 capacity NUMERIC(10,2) -- 装机容量（MW） ); -- 2. 发电记录表（每日每厂发电量） CREATE TABLE power.generation ( gen_id SERIAL PRIMARY KEY, plant_id INTEGER REFERENCES power.plants(plant_id), gen_date DATE NOT NULL, gen_mwh NUMERIC(12,2) -- 发电量（兆瓦时） ); -- 3. 售电记录表（每日每厂售电量） CREATE TABLE power.sales ( sale_id SERIAL PRIMARY KEY, plant_id INTEGER REFERENCES power.plants(plant_id), sale_date DATE NOT NULL, sale_mwh NUMERIC(12,2) -- 售电量（兆瓦时） ); -- 插入发电厂数据 INSERT INTO power.plants (plant_name, region, capacity) VALUES ('华能电厂', '华东', 1200), ('大唐电厂', '华北', 800), ('华电电厂', '华东', 600), ('国电电厂', '华南', 1000); -- 插入发电记录（部分日期） INSERT INTO power.generation (plant_id, gen_date, gen_mwh) VALUES (1, '2025-01-01', 950), (1, '2025-01-02', 1020), (2, '2025-01-01', 700), (2, '2025-01-02', 680), (3, '2025-01-01', 500), (4, '2025-01-01', 880), (4, '2025-01-02', 910); -- 插入售电记录 INSERT INTO power.sales (plant_id, sale_date, sale_mwh) VALUES (1, '2025-01-01', 900), (1, '2025-01-02', 980), (2, '2025-01-01', 650), (3, '2025-01-01', 480), (4, '2025-01-01', 800), (4, '2025-01-02', 850);说明：上面数据中，generation表有 7 行，sales表有 6 行。注意电厂 2 在 1 月 2 日有发电但无售电记录，电厂 3 在 1 月 2 日无任何记录等，便于演示集合运算。
1.3 UNION 与 UNION ALL（并集）
理论讲解：
UNION将两个查询的结果上下拼接，并自动去除重复行（按照所有列比较）。UNION ALL则保留所有行（包括重复行），性能更好。
使用场景：合并多个来源的同类数据，比如合并不同月份或不同电厂的报表。
案例：查询所有在2025-01-01有发电或售电记录的日期（不去重 vs 去重）。
-- UNION：去重 SELECT gen_date AS activity_date FROM power.generation WHERE gen_date = '2025-01-01' union SELECT sale_date FROM power.sales WHERE sale_date = '2025-01-01'; -- 结果：只有一行 '2025-01-01'（重复被合并） --------------------------------------------------------------- -- UNION ALL：保留重复 SELECT gen_date FROM power.generation WHERE gen_date = '2025-01-01' UNION all SELECT sale_date FROM power.sales WHERE sale_date = '2025-01-01'; -- 结果：4行（发电4条 + 售电4条，共8条，但示例数据只有4+3? 实际发电4条（电厂1,2,3,4），售电4条（电厂1,2,3,4），共8行）注意：UNION ALL不排序、不去重，速度最快。
1.4 INTERSECT 与 INTERSECT ALL（交集）
理论讲解：
INTERSECT返回同时出现在两个结果集中的行（去重）。INTERSECT ALL保留重复次数（取两个结果集中出现次数的最小值）。
使用场景：找出既发电又售电的日期或电厂。
案例：找出在2025-01-01既发电又售电的电厂 ID。
-- INTERSECT：去重 SELECT plant_id FROM power.generation WHERE gen_date = '2025-01-01' intersect SELECT plant_id FROM power.sales WHERE sale_date = '2025-01-01'; -- 结果：电厂1,2,4（电厂3只有发电，没有售电；电厂2有发电也有售电，被包含）1.5 EXCEPT 与 EXCEPT ALL（差集）
理论讲解：
EXCEPT返回出现在第一个结果集但不出现在第二个结果集的行（去重）。EXCEPT ALL会考虑重复次数（减去第二个结果集出现的次数）。
注意：A EXCEPT B与B EXCEPT A结果完全不同。
案例：找出在2025-01-01发电但没有售电的电厂 ID。
SELECT plant_id FROM power.generation WHERE gen_date = '2025-01-01' except SELECT plant_id FROM power.sales WHERE sale_date = '2025-01-01'; -- 结果：电厂3（它只有发电，没有售电记录）1.6 集合运算的注意事项
理论讲解：
（1）列对齐：两个SELECT的列数必须相同，对应列的数据类型必须兼容（可隐式转换）。结果集的列名以第一个SELECT为准。
（2）NULL处理：在集合运算中，NULL = NULL被视为真（与普通比较不同）。因此包含 NULL 的行会正常参与并、交、差。
（3）排序与分页：ORDER BY和LIMIT只能出现在整个集合运算的最后（不能单独对子查询排序，除非子查询加括号）。PostgreSQL 14+允许子查询内部排序，但对外层整体排序仍只能写在末尾。
（4）性能：默认的去重操作会引入排序或哈希，如果业务允许重复行，尽量使用ALL变体提速。
案例：演示列不对齐的错误及修复。
-- 错误：列数不同 SELECT plant_id FROM power.generation union SELECT plant_id FROM power.sales; -- 报错 -- 修复：补 NULL 使列数对齐 SELECT plant_id ,NULL::numeric AS dummy FROM power.generation union SELECT plant_id ,sale_mwh FROM power.sales;1.7 性能优化与常见错误
技巧 | 说明 |
|---|---|
优先用 | 不需要去重时， |
提前过滤 | 在子查询中用 |
并行 | PostgreSQL 11+ 会自动并行集合运算，可调整 |
统计信息 | 定期 |
常见错误速查：
each UNION query must have the same number of columns→ 列数不一致，补NULL。UNION types text and integer cannot be matched→ 类型不兼容，显式转换（如::text）。ORDER BY position 3 does not exist→ 外层ORDER BY使用了第二个SELECT的列索引，应使用第一个SELECT的列名或序号。
第 2 章 超级函数（多维聚合）
2.1 为什么需要多维聚合
理论讲解：
在实际报表中，我们常常需要同时查看不同维度组合的汇总结果，例如：
全年总销售额
各区域销售额
各季度销售额
各区域各季度销售额
传统做法是写多条SELECT再用UNION ALL拼起来，这样数据库会扫描多次表，性能差且代码冗长。
超级函数（GROUPING SETS、ROLLUP、CUBE）允许一次扫描表，同时计算出多种粒度的汇总，代码量减少 90%，性能提升数倍。
2.2 电力行业多维数据准备
我们创建一张发电销售事实表，包含时间、区域、电厂类型、发电量、售电量等维度。
-- 电力销售事实表 CREATE TABLE power.fact_electricity ( year INTEGER, quarter INTEGER, month INTEGER, region TEXT, plant_type TEXT, -- 火电、水电、风电等 gen_mwh NUMERIC, sale_mwh NUMERIC ); INSERT INTO power.fact_electricity VALUES (2025,1,1,'华东','火电',1000,950), (2025,1,1,'华东','风电',400,380), (2025,1,2,'华北','火电',800,750), (2025,1,2,'华北','水电',300,290), (2025,2,3,'华东','火电',1100,1050), (2025,2,3,'华南','风电',500,480);2.3 GROUPING SETS：自定义维度组合
理论讲解：
GROUPING SETS允许你显式列出想要的汇总维度组合，每个组合用括号括起来。空括号()表示总计。数据库会对每个组合分别聚合，然后合并结果（一次扫描）。
案例：按年合计、按区域合计、按年+区域合计，以及总计。
SELECT year ,region ,SUM(gen_mwh) AS total_gen FROM power.fact_electricity GROUP BY GROUPING SETS ( (year), -- 年合计 (region), -- 区域合计 (year, region), -- 年+区域 () -- 总计 ) ORDER BY year NULLS LAST, region NULLS LAST;结果解释：NULL出现在某个维度时表示该行是该维度的汇总（例如year为NULL且region为NULL的行是总计）。
2.4 ROLLUP：层级递进汇总
理论讲解：
ROLLUP是对GROUP BY列的一个层级降维过程。假设ROLLUP(a,b,c)会生成：(a,b,c)→(a,b)→(a)→()，即从最细粒度逐步向上汇总，顺序敏感。
案例：按年、季度、月递进汇总发电量。
SELECT year ,quarter ,month ,SUM(gen_mwh) AS total_gen FROM power.fact_electricity GROUP BY ROLLUP(year, quarter, month) ORDER BY year, quarter, month;输出会包含：
每月明细（year， quarter， month 都有值）
每季度小计（month 为 NULL）
每年小计（quarter 和 month 为 NULL）
总计（全 NULL）
2.5 CUBE：全交叉汇总
理论讲解：
CUBE(a,b)会生成所有可能的维度组合：(a,b)、(a)、(b)、()，共2^n种。与ROLLUP不同，CUBE不要求层级顺序，是对称的。
案例：按区域和电厂类型全交叉汇总售电量。
SELECT region, plant_type, SUM(sale_mwh) AS total_sale FROM power.fact_electricity GROUP BY CUBE(region, plant_type);结果包含：区域+类型明细、区域合计、类型合计、总计。非常适合交叉分析。
2.6 识别小计行：GROUPING（） 与 GROUPING_ID（）
理论讲解：
当汇总结果中出现NULL时，我们无法区分这个NULL是原始数据还是汇总占位符。GROUPING()函数返回1表示该列是汇总生成的NULL，返回0表示是原始值（包括真正的NULL）。
在 PostgreSQL 中，GROUPING_ID()不存在，但可以通过多个GROUPING()组合成二进制数来模拟。
案例：给上述ROLLUP结果添加标签，区分年小计、月小计等。
SELECT year ,quarter ,month ,SUM(gen_mwh) AS total_gen ,CASE WHEN GROUPING(year) = 1 THEN '总计' ELSE year::text END AS year_label ,CASE WHEN GROUPING(quarter) = 1 THEN '全年' ELSE quarter::text END AS quarter_label ,CASE WHEN GROUPING(month) = 1 THEN '全季' ELSE month::text END AS month_label FROM power.fact_electricity GROUP BY ROLLUP(year, quarter, month);模拟 GROUPING_ID：手工计算GROUPING(year)*2^2 + GROUPING(quarter)*2^1 + GROUPING(month)*2^0得到一个整数，7表示全汇总行。
2.7 性能对比
方式 | 扫描次数 | 执行时间（参考） |
|---|---|---|
多条 | 多次 | 慢 |
| 1 次 | 快（6～10 倍提升） |
第 3 章 行列转换
3.1 什么是行转列与列转行
行转列：将一列中的不同值（如月份）变成多个列头，使数据更便于阅读（宽表）。
列转行：将多个列合并为一列（长表），便于后续聚合或导入其他系统。
3.2 行转列：静态 CASE WHEN + GROUP BY
场景：按月展示每个电厂的发电量（列头为 1 月、2 月…）。
SELECT plant_id ,SUM(CASE WHEN EXTRACT(MONTH FROM gen_date)=1 THEN gen_mwh ELSE 0 END) AS jan ,SUM(CASE WHEN EXTRACT(MONTH FROM gen_date)=2 THEN gen_mwh ELSE 0 END) AS feb FROM power.generation WHERE EXTRACT(YEAR FROM gen_date)=2025 GROUP BY plant_id;优化：可用FILTER子句替代CASE，更清晰。
SUM(gen_mwh) FILTER (WHERE EXTRACT(MONTH FROM gen_date)=1) AS jan3.3 半动态行转列：crosstab 扩展
当列值不固定（比如产品种类未知）时，可以使用tablefunc扩展中的crosstab函数。需要预先知道列的类型。
CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM crosstab( 'SELECT plant_id ,EXTRACT(MONTH FROM gen_date)::text ,gen_mwh FROM power.generation WHERE EXTRACT(YEAR FROM gen_date)=2025 ORDER BY 1,2', 'SELECT generate_series(1,12)::text' ) AS ct(plant_id int, "1" numeric, "2" numeric, ...);3.4 列转行：UNION ALL、LATERAL、hstore、jsonb
方法 1：UNION ALL（最通用但冗长）
SELECT plant_id ,1 AS month ,jan AS gen_mwh FROM monthly_gen WHERE jan IS NOT NULL UNION ALL SELECT plant_id ,2 ,feb FROM monthly_gen WHERE feb IS NOT NULL;方法 2：LATERAL + VALUES（一次扫描，性能更好）
SELECT plant_id ,month ,gen_mwh FROM monthly_gen, LATERAL (VALUES (1, jan), (2, feb), (3, mar)) AS t(month, gen_mwh) WHERE gen_mwh IS NOT NULL;方法 3：hstore（一键展开所有列）
SELECT plant_id ,(each(hstore(monthly_gen))).key AS month ,(each(...)).value AS gen_mwh FROM monthly_gen;方法 4：jsonb_each_text（最灵活，支持动态列名）
SELECT plant_id ,key AS month ,value::numeric AS gen_mwh FROM monthly_gen,jsonb_each_text(to_jsonb(monthly_gen) - 'plant_id');3.5 超宽表处理策略
当列数超过 1600（PostgreSQL 单表列上限），或者列值不确定时，不应强行转列，而应使用JSONB或hstore存储键值对，并通过 GIN 索引加速查询。
CREATE MATERIALIZED VIEW power.gen_json AS SELECT plant_id ,jsonb_object_agg(to_char(gen_date,'YYYY-MM-DD'), gen_mwh) AS daily_gen FROM power.generation GROUP BY plant_id; -- 查询某电厂特定日期的发电量 SELECT daily_gen->>'2025-01-01' FROM power.gen_json WHERE plant_id=1;第 4 章 补充知识点与综合练习
4.1 GROUPING（） 深度解析（区分汇总 NULL 与原始 NULL）
生活例子：超市小票汇总表里出现的 NULL 可能是“某天所有水果”的汇总占位符，也可能是水果名本身缺失。GROUPING()函数返回1表示汇总占位符，0表示原始数据（包含真实 NULL）。在电力行业，用GROUPING()可以将汇总行中的 NULL 替换为有意义的文字（如“总计”）。
4.2 电力行业综合练习题
基于power模式，请完成以下题目：
（1）集合运算：查询2025-01-01既发电又售电的电厂名称（使用INTERSECT）。
（2）ROLLUP：按区域、电厂类型、年份汇总发电量，要求输出每个层级的小计。
（3）行转列：将2025年各电厂的月发电量转换为宽表（列名为 1～12 月），未发电月份填 0。
（4）列转行：将上一步生成的宽表再转回长表（plant_id， month， gen_mwh）。
（5）GROUPING（）：在 2 的结果中增加一列，标明当前行是“明细”、“区域小计”、“类型小计”还是“总计”。
第 5 章 总结
知识点 | 核心要点 | 电力行业应用示例 |
|---|---|---|
集合运算 | 纵向合并，列对齐，默认去重 | 合并各电厂发电与售电记录 |
GROUPING SETS | 一次扫描，多维度组合 | 年/季/区域任意组合汇总 |
ROLLUP | 层级递进，顺序敏感 | 年→季→月逐级汇总 |
CUBE | 全交叉，对称 | 区域×类型所有组合分析 |
GROUPING() | 识别汇总 NULL | 将 NULL 替换为“总计” |
行转列 |
| 月发电量变成 12 列 |
列转行 |
| 宽表变成长表便于存储 |
最后口诀：
集合运算上下拼，列对齐，ALL 去重要分清。
多维聚合一次扫，SETS/ROLLUP/CUBE，性能好。
NULL 真假难分辨，GROUPING 函数来帮忙。
行列转换宽窄变，CASE 转列，LATERAL 转行。
