Pivot Internals

Search Shortcut cmd + k | ctrl + k

Documentation / Internals

Pivot Internals

`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)
NL	阿姆斯特丹	[2000, 2010, 2020]	[1005, 1065, 1158]
美国	西雅图	[2000, 2010, 2020]	[564, 608, 738]
美国	纽约市	[2000, 2010, 2020]	[8015, 8175, 8772]

PhysicalPivot 操作符将这些列表转换为列名和值以返回此结果：

国家	名称	2000	2010	2020
NL	阿姆斯特丹	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。这将产生与初始示例相同的结果：

员工编号	部门	月份	销售额
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`

内部结构

关于此页面

本文内容