Выражение UNPIVOT

Выражение UNPIVOT позволяет объединить несколько столбцов в меньшее количество столбцов. В базовом случае несколько столбцов объединяются в два столбца: столбец NAME (который содержит имя исходного столбца) и столбец VALUE (который содержит значение из исходного столбца).

DuckDB реализует синтаксис SQL-стандарта UNPIVOT и упрощенный синтаксис UNPIVOT. Оба могут использовать выражение COLUMNS для автоматического определения столбцов для преобразования. PIVOT_LONGER может быть использовано вместо ключевого слова UNPIVOT.

Подробную информацию о реализации выражения UNPIVOT см. на сайте Внутреннее устройство Pivot.

Выражение PIVOT является обратным выражению UNPIVOT

Упрощенный синтаксис UNPIVOT

Полная диаграмма синтаксиса приведена ниже, но упрощенный синтаксис UNPIVOT можно обобщить, используя соглашения об именовании сводных таблиц в электронных таблицах:

UNPIVOT ⟨dataset⟩
ON ⟨column(s)⟩
INTO
    NAME ⟨name-column-name⟩
    VALUE ⟨value-column-name(s)⟩
ORDER BY ⟨column(s)-with-order-direction(s)⟩
LIMIT ⟨number-of-rows⟩;

Пример данных

Все примеры используют набор данных, полученный из запросов ниже:

CREATE OR REPLACE TABLE monthly_sales
    (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
INSERT INTO monthly_sales VALUES
    (1, 'electronics', 1, 2, 3, 4, 5, 6),
    (2, 'clothes', 10, 20, 30, 40, 50, 60),
    (3, 'cars', 100, 200, 300, 400, 500, 600);

FROM monthly_sales;

UNPIVOT Ручная операция

Наиболее типичное UNPIVOT преобразование — это взятие уже сводных данных и повторное объединение их в столбцы для имени и значения. В этом случае все месяцы будут объединены в столбец month и столбец sales.

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

UNPIVOT Динамическое использование выражения столбцов

Во многих случаях количество столбцов для преобразования UNPIVOT заранее трудно определить. В случае этого набора данных запрос нужно будет изменять каждый раз, когда добавляется новый месяц. Выражение COLUMNS можно использовать для выбора всех столбцов, которые не являются empid или dept. Это позволяет динамически выполнять преобразование UNPIVOT, которое будет работать независимо от количества добавляемых месяцев. Запрос ниже возвращает идентичные результаты предыдущему.

UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
    NAME month
    VALUE sales;

UNPIVOT в несколько столбцов значений

Выражение UNPIVOT имеет дополнительную гибкость: поддерживается более 2 столбцов назначения. Это может быть полезно, когда цель состоит в сокращении объема сводки данных, но не в полном объединении всех сводных столбцов. Чтобы продемонстрировать это, запрос ниже сгенерирует набор данных с отдельным столбцом для номера каждого месяца в квартале (месяц 1, 2 или 3) и отдельной строкой для каждого квартала. Поскольку кварталов меньше, чем месяцев, это сделает набор данных длиннее, но не так длинным, как предыдущий.

Для достижения этого в ON клаузуле включены несколько наборов столбцов. Псевдонимы q1 и q2 необязательны. Количество столбцов в каждом наборе столбцов в ON клаузе должно соответствовать количеству столбцов в VALUE клаузе.

UNPIVOT monthly_sales
    ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
    INTO
        NAME quarter
        VALUE month_1_sales, month_2_sales, month_3_sales;

Использование UNPIVOT в выражении SELECT

Выражение UNPIVOT может быть включено в выражение SELECT как CTE (Общее табличное выражение, или клаузула WITH) или подзапрос. Это позволяет использовать выражение UNPIVOT наряду с другой логикой SQL, а также использовать несколько выражений UNPIVOT в одном запросе.

В CTE не требуется SELECT, ключевое слово UNPIVOT можно рассматривать как заменяющее его.

WITH unpivot_alias AS (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
)
SELECT * FROM unpivot_alias;

Можно использовать UNPIVOT в подзапросе, обязательно заключив его в скобки. Обратите внимание, что это поведение отличается от стандартного SQL Unpivot, как показано в последующих примерах.

SELECT *
FROM (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
) unpivot_alias;

Выражения в операторах UNPIVOT

DuckDB позволяет использовать выражения в операторах UNPIVOT при условии, что они затрагивают только один столбец. Их можно использовать для выполнения вычислений и явного приведения типов. Например:

UNPIVOT
    (SELECT 42 AS col1, 'woot' AS col2)
    ON
        (col1 * 2)::VARCHAR,
        col2;

Упрощенная диаграмма синтаксиса UNPIVOT

Ниже приведена полная диаграмма синтаксиса оператора UNPIVOT.

Синтаксис стандартного SQL UNPIVOT

Полная диаграмма синтаксиса представлена ниже, но синтаксис стандартного SQL UNPIVOT можно обобщить следующим образом:

FROM [dataset]
UNPIVOT [INCLUDE NULLS] (
    [value-column-name(s)]
    FOR [name-column-name] IN [column(s)]
);

Обратите внимание, что в выражении name-column-name может быть включён только один столбец.

Ручная реализация стандартного SQL UNPIVOT

Для выполнения базовой операции UNPIVOT с использованием стандартного синтаксиса SQL необходимы лишь несколько дополнений.

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
);

Динамическое использование стандартного SQL UNPIVOT выражения COLUMNS

Выражение COLUMNS может использоваться для динамического определения списка IN столбцов. Это будет работать даже при добавлении дополнительных month столбцов в набор данных. Результат будет аналогичным результату запроса выше.

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (columns(* EXCLUDE (empid, dept)))
);

Стандартный SQL UNPIVOT в несколько столбцов значений

Оператор UNPIVOT имеет дополнительную гибкость: поддерживается более 2 столбцов назначения. Это полезно, когда цель состоит в уменьшении степени сворачивания данных, но не в полной укладке всех сгруппированных столбцов. Чтобы продемонстрировать это, запрос ниже создаст набор данных с отдельным столбцом для каждого месяца в квартале (месяц 1, 2 или 3) и отдельной строкой для каждого квартала. Поскольку кварталов меньше, чем месяцев, это делает набор данных длиннее, но не так длинным, как в примере выше.

Для этого в части value-column-name оператора UNPIVOT включаются несколько столбцов. В части IN включаются несколько наборов столбцов. Алиасы q1 и q2 являются необязательными. Количество столбцов в каждом наборе столбцов в части IN должно совпадать с количеством столбцов в части value-column-name.

FROM monthly_sales
UNPIVOT (
    (month_1_sales, month_2_sales, month_3_sales)
    FOR quarter IN (
        (jan, feb, mar) AS q1,
        (apr, may, jun) AS q2
    )
);

Полная диаграмма синтаксиса стандартного SQL UNPIVOT оператора UNPIVOT

Ниже приведена полная диаграмма синтаксиса стандартного SQL оператора UNPIVOT.