Внутреннее устройство Pivot

PIVOT

Перегруппировка реализована как комбинация переписывания 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;

Это даёт результат:

Оператор PhysicalPivot преобразует эти списки в имена и значения столбцов, чтобы вернуть этот результат:

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. Это даёт тот же результат, что и в исходном примере: