透视表内部原理 – DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / 内部结构

透视表内部原理

`PIVOT`

透视表 (Pivoting) 的实现结合了 SQL 查询重写和用于提高性能的专用 PhysicalPivot 运算符。每个 PIVOT 都通过一组聚合为列表来实现，然后专用的 PhysicalPivot 运算符将这些列表转换为列名和值。如果动态检测透视时要创建的列（当未使用 IN 子句时会发生这种情况），则需要额外的预处理步骤。

DuckDB 与大多数 SQL 引擎一样，要求在查询开始时知道所有列名和类型。为了自动检测 PIVOT 语句结果中应创建的列，它必须被转换为多个查询。ENUM 类型用于查找应成为列的不同值。然后，每个 ENUM 被注入到 PIVOT 语句的一个 IN 子句中。

在 IN 子句填充了 ENUM 后，查询再次被重写为一组聚合为列表的查询。

例如

PIVOT cities
ON year
USING sum(population);

最初翻译为

CREATE TEMPORARY TYPE __pivot_enum_0_0 AS ENUM (
    SELECT DISTINCT
        year::VARCHAR
    FROM cities
    ORDER BY
        year
    );
PIVOT cities
ON year IN __pivot_enum_0_0
USING sum(population);

并最终翻译为

SELECT country, name, list(year), list(population_sum)
FROM (
    SELECT country, name, year, sum(population) AS population_sum
    FROM cities
    GROUP BY ALL
)
GROUP BY ALL;

这会产生结果

国家	名称	list("year")	list(population_sum)
荷兰	阿姆斯特丹	[2000, 2010, 2020]	[1005, 1065, 1158]
美国	西雅图	[2000, 2010, 2020]	[564, 608, 738]
美国	纽约市	[2000, 2010, 2020]	[8015, 8175, 8772]

PhysicalPivot 运算符将这些列表转换为列名和值，以返回此结果

国家	名称	2000	2010	2020
荷兰	阿姆斯特丹	1005	1065	1158
美国	西雅图	564	608	738
美国	纽约市	8015	8175	8772

`UNPIVOT`

内部原理

逆透视完全通过重写为 SQL 查询来实现。每个 UNPIVOT 都通过一组 unnest 函数来实现，这些函数作用于列名列表和列值列表。如果是动态逆透视，则首先评估 COLUMNS 表达式以计算列列表。

例如

UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
    NAME month
    VALUE sales;

被翻译为

SELECT
    empid,
    dept,
    unnest(['jan', 'feb', 'mar', 'apr', 'may', 'jun']) AS month,
    unnest(["jan", "feb", "mar", "apr", "may", "jun"]) AS sales
FROM monthly_sales;

请注意用于构建文本字符串列表以填充 month 的单引号，以及用于提取列值以用于 sales 的双引号。这会产生与初始示例相同的结果

员工ID	部门	月份	销售额
1	电子产品	一月	1
1	电子产品	二月	2
1	电子产品	三月	3
1	电子产品	四月	4
1	电子产品	五月	5
1	电子产品	六月	6
2	服装	一月	10
2	服装	二月	20
2	服装	三月	30
2	服装	四月	40
2	服装	五月	50
2	服装	六月	60
3	汽车	一月	100
3	汽车	二月	200
3	汽车	三月	300
3	汽车	四月	400
3	汽车	五月	500
3	汽车	六月	600

`PIVOT`

`UNPIVOT`

内部原理

关于此页面

本文中