Оператор PIVOT

Оператор PIVOT позволяет разделять различные значения в столбце на отдельные столбцы. Значения в этих новых столбцах вычисляются с помощью агрегатной функции для подмножества строк, соответствующих каждому уникальному значению.

DuckDB реализует как синтаксис SQL Standard PIVOT, так и упрощённый синтаксис PIVOT, который автоматически определяет столбцы для создания при операциях поворота. PIVOT_WIDER также можно использовать вместо ключевого слова PIVOT.

Подробности реализации оператора PIVOT см. на странице Внутренние механизмы Pivot.

Оператор UNPIVOT является обратным оператору PIVOT.

Упрощённый синтаксис PIVOT

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

PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩
ORDER BY ⟨columns_with_order_directions⟩
LIMIT ⟨number_of_rows⟩;

Операторы ON, USING, и GROUP BY являются необязательными, но не могут быть опущены все одновременно.

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

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

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

SELECT *
FROM cities;

PIVOT ON и USING

Используйте оператор PIVOT ниже, чтобы создать отдельный столбец для каждого года и вычислить общее население в каждом из них. Оператор ON указывает, какие столбцы следует разделить на отдельные столбцы. Он эквивалентен параметру столбцов в сводной таблице электронных таблиц.

Оператор USING определяет, как агрегировать значения, которые разделяются на отдельные столбцы. Это эквивалентно параметру значений в сводной таблице электронных таблиц. Если оператор USING не указан, он по умолчанию принимает значение count(*).

PIVOT cities
ON year
USING sum(population);

В приведённом примере агрегатная функция sum всегда работает с одним значением. Если нужно только изменить ориентацию отображения данных без агрегации, используйте агрегатную функцию first. В этом примере мы поворачиваем числовые значения, но функция first отлично подходит для поворота текстового столбца. (Это сложно сделать в сводной таблице электронных таблиц, но легко в DuckDB!)

Этот запрос даёт такой же результат, как и предыдущий:

PIVOT cities
ON year
USING first(population);

Примечание. Синтаксис SQL допускает FILTER операторы с агрегатными функциями в операторе USING. В DuckDB оператор PIVOT в настоящее время не поддерживает их, и они будут проигнорированы.

PIVOT ON, USING, и GROUP BY

По умолчанию, оператор PIVOT сохраняет все столбцы, не указанные в операторах ON или USING. Чтобы включить только определённые столбцы и выполнить дальнейшую агрегацию, укажите столбцы в операторе GROUP BY. Это эквивалентно параметру строк в сводной таблице электронных таблиц.

В приведённом ниже примере столбец name больше не включён в вывод, а данные агрегируются на уровне country.

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

Фильтр по IN для ON

Чтобы создать отдельный столбец только для определённых значений в столбце в операторе ON, используйте необязательное выражение IN. Предположим, например, что по какой-то причине мы хотим забыть о 2020 году…

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;

Несколько выражений в операторе

Несколько столбцов можно указать в операторах ON и GROUP BY, а несколько агрегатных выражений можно включить в оператор USING.

Несколько столбцов ON и выражений ON

Несколько столбцов можно повернуть в отдельные столбцы. DuckDB найдёт уникальные значения в каждом столбце оператора ON и создаст один новый столбец для всех комбинаций этих значений (декартово произведение).

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

PIVOT cities
ON country, name
USING sum(population);

Чтобы повернуть только комбинации значений, присутствующих в исходных данных, используйте выражение в операторе ON. Можно указать несколько выражений и/или столбцов.

Здесь country и name конкатенируются, и получившиеся конкатенации получают каждый свой столбец. Может быть использовано любое произвольное неагрегирующее выражение. В данном случае используется конкатенация с нижним подчёркиванием, чтобы имитировать правила именования, которые использует оператор PIVOT при указании нескольких столбцов ON (как в предыдущем примере).

PIVOT cities
ON country || '_' || name
USING sum(population);

Несколько выражений USING

Для каждого выражения в операторе USING также может быть указан псевдоним. Он будет добавлен к сгенерированным именам столбцов после нижнего подчёркивания (_). Это значительно улучшает согласованность именования столбцов при включении нескольких выражений в оператор USING.

В этом примере и sum и max столбца населения вычисляются для каждого года и разделяются на отдельные столбцы.

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;

Несколько столбцов GROUP BY

Также могут быть предоставлены несколько столбцов GROUP BY. Обратите внимание, что должны использоваться имена столбцов, а не позиции столбцов (1, 2 и т. д.), и что выражения не поддерживаются в GROUP BY-запросе.

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

Использование PIVOT в операторе SELECT

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

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

WITH pivot_alias AS (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
)
SELECT * FROM pivot_alias;

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

SELECT *
FROM (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
) pivot_alias;

Несколько операторов PIVOT

Каждый оператор PIVOT можно рассматривать как узел SELECT, поэтому их можно объединить или обработать другими способами.

Например, если два оператора PIVOT используют одно и то же выражение GROUP BY, их можно объединить, используя столбцы в GROUP BY-запросе в более широкий сводный запрос.

SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);

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

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

Синтаксис оператора SQL Standard PIVOT

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

SELECT *
FROM ⟨dataset⟩
PIVOT (
    ⟨values⟩
    FOR
        ⟨column_1⟩ IN (⟨in_list⟩)
        ⟨column_2⟩ IN (⟨in_list⟩)
        ...
    GROUP BY ⟨rows⟩
);

В отличие от упрощенного синтаксиса, для каждого столбца, который необходимо преобразовать в сводный вид, необходимо указать IN-запрос. Если вам нужна динамическая обработка в сводной форме, рекомендуется использовать упрощенный синтаксис.

Обратите внимание, что в FOR-запросе выражения не разделяются запятыми, но выражения value и GROUP BY должны быть разделены запятыми!

Примеры

В этом примере используется одно выражение, одно выражение с одним столбцом и одно выражение с одной строкой:

SELECT *
FROM cities
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
);

Этот пример несколько искусственный, но служит примером использования нескольких выражений со значениями и нескольких столбцов в FOR-запросе.

SELECT *
FROM cities
PIVOT (
    sum(population) AS total,
    count(population) AS count
    FOR
        year IN (2000, 2010)
        country IN ('NL', 'US')
);

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

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

Ограничения

PIVOT в настоящее время принимает только агрегатную функцию, выражения не допускаются. Например, следующий запрос пытается получить численность населения как количество человек, а не в тысячах (т. е. вместо 564 получить 564000):

PIVOT cities
ON year
USING sum(population) * 1000;

Однако он терпит неудачу со следующей ошибкой:

Catalog Error: * is not an aggregate function

Чтобы обойти это ограничение, выполните PIVOT с агрегацией только, а затем используйте COLUMNS выражение:

SELECT country, name, 1000 * COLUMNS(* EXCLUDE (country, name))
FROM (
    PIVOT cities
    ON year
    USING sum(population)
);