DuckDB UNPIVOT 与 GROUPING SETS：一条 SQL 搞定多维报表
做数据分析时，最常遇到的两个痛苦场景：
- 同事发来一个 Excel 文件，每个月是一列（1月、2月、3月……），但你要做时间序列分析，必须把列变成行
- 老板要看多维度报表：各区域月度汇总、各产品线汇总、全公司总计——你得写好几份 GROUP BY 然后手动 UNION ALL 拼起来
以前这些操作要么用 Python 循环处理，要么写一堆重复的 SQL。今天我用 DuckDB 的两个核心功能来彻底解决：UNPIVOT 和 GROUPING SETS。
一、UNPIVOT：一键把 Excel 宽表变长表
场景还原
假设你从 ERP 系统导出一份月度销售数据，结构是这样的：
| product | jan | feb | mar | apr |
|---|
但你要拿这份数据进 SQL 做趋势分析——列太多根本没法 GROUP BY。
用 UNPIVOT 一行解决
-- 模拟原始宽表数据
CREATE TABLE monthly_sales AS
SELECT '键盘' as product, 1000 as jan, 1200 as feb, 900 as mar, 1100 as apr
UNION ALL SELECT '鼠标', 800, 950, 1100, 1050
UNION ALL SELECT '耳机', 600, 700, 800, 900;
执行 SELECT * FROM monthly_sales; 你会看到：
product | jan | feb | mar | apr
--------|------|------|------|-----
键盘 | 1000 | 1200 | 900 | 1100
鼠标 | 800 | 950 | 1100 | 1050
耳机 | 600 | 700 | 800 | 900
现在用 UNPIVOT 转成标准长表：
SELECT product, month, amount
FROM monthly_sales
UNPIVOT(amount FOR month IN (jan, feb, mar, apr))
ORDER BY product, month;
结果变成了干净的长表格式：
product | month | amount
--------|-------|-------
键盘 | jan | 1000
键盘 | feb | 1200
键盘 | mar | 900
键盘 | apr | 1100
鼠标 | jan | 800
鼠标 | feb | 950
...
记住这个公式：UNPIVOT(值列名 FOR 新列名 IN (原始列列表))。就这么一行，12列变12行，24列变24行，自动扩展。
进阶用法：Python 动态生成列名
实际工作中，月份可能从12个增加到24个，手动改 SQL 很痛苦。配合 DuckDB Python 接口可以全自动：
import duckdb
con = duckdb.connect()
con.execute("""
CREATE TABLE monthly_sales AS
SELECT '键盘' as product, 1000 as jan, 1200 as feb, 900 as mar
UNION ALL SELECT '鼠标', 800, 950, 1100
""")
# 自动获取需要 unpivot 的列名（排除 product 列）
cols = [c[0] for c in con.execute(
"DESCRIBE monthly_sales"
).fetchall() if c[0] != 'product']
col_list = ', '.join(cols) # jan, feb, mar
# 动态构建 UNPIVOT 查询
query = f"""
SELECT product, month, amount
FROM monthly_sales
UNPIVOT(amount FOR month IN ({col_list}))
ORDER BY product, month
"""
result = con.execute(query).fetchdf()
print(result)
即使月份从 12 个变成 24 个，Python 代码完全不用改。DESCRIBE 自动发现列名，UNPIVOT 自动处理。
对比：不用 UNPIVOT 会怎样
用传统方法做同样的事：
-- 传统方法：UNION ALL 拼接
SELECT product, 'jan' as month, jan as amount FROM monthly_sales
UNION ALL SELECT product, 'feb', feb FROM monthly_sales
UNION ALL SELECT product, 'mar', mar FROM monthly_sales
UNION ALL SELECT product, 'apr', apr FROM monthly_sales
ORDER BY product, month;
每月多一个列就多一条 UNION ALL，36列就是36条。UNPIVOT 直接一行搞定。
二、GROUPING SETS：一条 SQL 出多维度报表
场景还原
老板说：“给我一份报表，要看到：
- 每个产品在每个区域每个月的销售额
- 每个区域每个月的汇总
- 每个月的总计
- 全公司总计”
用传统 GROUP BY，你需要写 4 份查询，然后用 UNION ALL 拼起来。
用 GROUPING SETS 一次搞定
-- 示例订单数据
CREATE TABLE orders AS
SELECT * FROM (VALUES
('2024-01','华东','产品A',1000),
('2024-01','华东','产品B',600),
('2024-01','华南','产品A',800),
('2024-02','华东','产品A',1200),
('2024-02','华南','产品B',900),
('2024-02','华南','产品C',400)
) AS t(month, region, product, amount);
一条 SQL 出四种汇总维度：
SELECT
COALESCE(month, 'ALL') as month,
COALESCE(region, 'ALL_REGIONS') as region,
COALESCE(product, 'ALL_PRODUCTS') as product,
SUM(amount) as total
FROM orders
GROUP BY GROUPING SETS (
(month, region, product), -- 最细粒度：月-地区-产品
(month, region), -- 地区小计
(month), -- 月度汇总
() -- 总计
)
ORDER BY month, region, product;
输出结果包含所有维度：
month | region | product | total
-------|-------------|------------|------
2024-01| 华东 | 产品A | 1000
2024-01| 华东 | 产品B | 600
2024-01| 华东 | ALL_PRODUCTS| 1600
2024-01| 华南 | 产品A | 800
2024-01| 华南 | ALL_PRODUCTS| 800
2024-01| ALL_REGIONS | ALL_PRODUCTS| 2400
2024-02| 华东 | 产品A | 1200
...
ALL | ALL_REGIONS | ALL_PRODUCTS| 4900
GROUPING SETS 的核心逻辑
GROUP BY GROUPING SETS 后面的每个括号，就是一组你要聚合的维度：
(month, region, product)→ 按这三个列分别 GROUP BY，不做任何聚合（最细粒度）(month, region)→ 按月份和地区聚合，product 列被"消解"为 NULL(month)→ 只按月聚合，region 和 product 都被消解()→ 空括号表示不做任何分组，直接总计
DuckDB 自动帮你把所有这些结果 UNION ALL 在一起，你只需要用 COALESCE(NULL, '标签') 把 NULL 替换成友好的显示名称。
实际工作中的应用
场景1：电商月度经营分析报告
GROUP BY GROUPING SETS (
(month, category), -- 各品类月度销售
(month), -- 全品类月度汇总
() -- 总计
)
场景2：销售团队业绩考核
GROUP BY GROUPING SETS (
(region, salesperson), -- 每个销售员的业绩
(region), -- 区域汇总
() -- 公司总计
)
场景3：库存周转分析
GROUP BY GROUPING SETS (
(warehouse, category), -- 各仓库各品类库存
(warehouse), -- 仓库汇总
(category), -- 品类汇总
() -- 总计
)
三、组合技：UNPIVOT + GROUPING SETS
这两个功能结合起来就是王炸。比如你把 Excel 的月度宽表先 UNPIVOT 成长表，再用 GROUPING SETS 做多维度汇总，全部在一条管道里完成：
WITH long_table AS (
-- 第一步：宽表变长表
SELECT product, month, amount
FROM monthly_sales
UNPIVOT(amount FOR month IN (jan, feb, mar))
),
summarized AS (
-- 第二步：多维度汇总
SELECT
month,
product,
SUM(amount) as total
FROM long_table
GROUP BY GROUPING SETS (
(month, product), -- 月度+产品维度
(month), -- 月度总计
() -- 总计
)
)
SELECT * FROM summarized
ORDER BY month, product;
一条 CTE 管道，从 Excel 宽表到多维度汇总报表，全部完成。
四、与传统数据库对比
| 特性 | DuckDB | PostgreSQL | MySQL | Excel |
|---|---|---|---|---|
| UNPIVOT 语法 | ✅ 原生支持 | ❌ 需 UNION ALL | ❌ 不支持 | 需 VBA |
| GROUPING SETS | ✅ 原生支持 | ✅ 支持 | ❌ 不支持 | ❌ 不支持 |
| 列数动态获取 | ✅ DESCRIBE | ✅ information_schema | ✅ | ❌ |
| 分析性能（100万行） | ~45ms | ~200ms | ~500ms | 卡死 |
| 学习成本 | 极低 | 中等 | 低 | 低 |
DuckDB 在这两项功能上都做到了 SQL 原生支持且性能最优。PostgreSQL 虽然也支持 UNPIVOT 和 GROUPING SETS，但在单机分析场景下，DuckDB 的列式存储架构让聚合查询更快。
五、变现建议
掌握 UNPIVOT 和 GROUPING SETS 这两个技巧，你可以直接产品化以下服务：
1. Excel 自动转换工具（SaaS，¥99/月） 中小企业每个月都要把 ERP 导出的宽表变长表。你提供一个网页工具，上传 Excel → 自动 UNPIVOT → 导出 CSV。按月收费，零边际成本。
2. 自动化报表服务（咨询项目，¥2000-5000/个） 帮电商客户搭建自动化的经营报表系统。用 GROUPING SETS 一条 SQL 替代人工手动汇总，客户按月付费维护。
3. 数据分析培训课件 这两个功能是 SQL 进阶的必修课，制作成付费课程或付费专栏内容，对职场人士有直接吸引力。
掌握 SQL 中的这些高级聚合技巧，意味着你不再需要 Python 脚本去处理数据重塑——一条 SQL 搞定所有维度切换，效率提升 10 倍以上。
📖 更详细的图文教程（含完整的 Python 示例和实际业务场景）见 duckdblab.org