PIVOT

Pivoting（数据透视）的实现结合了 SQL 查询重写和专门的 PhysicalPivot 算子，以实现更高的性能。每个 PIVOT 都被实现为一组聚合到列表（lists）的操作，然后由专门的 PhysicalPivot 算子将这些列表转换为列名和值。如果透视时需要创建的列是动态检测的（当未使用 IN 子句时会发生这种情况），则需要额外的预处理步骤。

与大多数 SQL 引擎一样，DuckDB 要求在查询开始时就确定所有列的名称和类型。为了自动检测 PIVOT 语句执行后应该创建的列，它必须被转换为多个查询。ENUM 类型被用于查找应该成为列的不同值（distinct values）。然后，每个 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;

这产生的结果为

PhysicalPivot 算子将这些列表转换为列名和值，以返回以下结果

UNPIVOT

内部原理

Unpivoting（逆透视）完全通过重写为 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。这产生的结果与最初的示例相同