【AI大数据工程师特训笔记】第08讲：集合运算与超级函数
目录
1.4 INTERSECT 与 INTERSECT ALL（交集）
2.6 识别小计行：GROUPING（） 与 GROUPING_ID（）
3.2 行转列：静态 CASE WHEN + GROUP BY
3.4 列转行：UNION ALL、LATERAL、hstore、jsonb
第 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 转行。
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
6
0- 0
已为社区贡献23条内容
所有评论(0)