Новое в стандарте и реализациях языка SQL

Операторы PIVOT и UNPIVOT

Повернуть таблицу в PostgreSQL можно при помощи функции CROSSTAB. Эта функция принимает в качестве текстового параметра SQL-запрос, который возвращает 3 столбца:

• идентификатор строки - т.е. этот столбец содержит значения, определяющие результирующую (повернутую) строку;
• категорию - уникальные значения из этого столбца образуют столбцы повернутой таблицы. Нужно отметить, что в отличие от PIVOT сами значения роли не играют; важно лишь их количество, которое определяет максимально допустимое количество столбцов;
• значение категории - собственно значения категорий. Размещение значений по столбцам производится слева направо, и имена категорий роли не играют, а только их порядок, определяемый сортировкой запроса.

Поясним сказанное на примере базы данных "Окраска".

Давайте для каждого квадрата просуммируем количество краски каждого цвета:

Консоль

Выполнить

SELECT b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

select b_q_id, v_color, sum(b_vol) qty from utb join utv on b_v_id = v_id where b_q_id between 12 and 16 group by b_q_id, v_color order by b_q_id, case v_color when 'R' then 1 when 'G' then 2 else 3 end;

    Схема

Нет схемы

Здесь мы ограничились только квадратами с номерами в диапазоне 12-16, чтобы, с одной стороны, уменьшить вывод, а, с другой стороны, сделать вывод презентативным. Сортировка по цветам выполнена в порядке RGB. Вот результат:

В терминологии CROSSTAB номера баллонов являются идентификаторами строк, а цвета - категориями. Результат поворота должен быть следующим:

Теперь с помощью CROSSTAB попытаемся написать запрос, который бы дал требуемый результат:

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

Здесь мы должны перечислить список столбцов с указанием их типа. При этом столбцы категорий могут быть перечислены не все. Посмотрим на результат (вы можете проверять запросы в консоли, выбрав для исполнения PostgreSQL):

Этот результат не вполне совпадает с ожидаемым. Напомним, что здесь важен только порядок. Если квадрат окрашивался только одним цветом, то значение (суммарный объем краски) попадет в первую категорию (у нас она называется R), каким бы этот единственный цвет ни был. Давайте перепишем запрос таким образом, чтобы он давал значения для всех цветов причем в нужном порядке. При этом отсутствующий цвет будем заменять NULL-значением. Чтобы добиться этого, добавим для каждого квадрата по одной строке каждого цвета со значением объема краски равным NULL:

Консоль

Выполнить

SELECT b_q_id, v_color, SUM(b_vol) qty FROM(
SELECT b_q_id, v_color, b_vol FROM utb 
JOIN utv ON b_v_id = v_id 
UNION ALL --вот эта добавка
SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X 
CROSS JOIN (SELECT 'R' color, NULL::smallint vol 
                UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y 
) X
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

select b_q_id, v_color, sum(b_vol) qty from( select b_q_id, v_color, b_vol from utb join utv on b_v_id = v_id union all --вот эта добавка select * from (select distinct b_q_id from utb) X cross join (select 'R' color, null::smallint vol                 union all select 'G', null union all select 'B', null) Y ) X where b_q_id between 12 and 16 group by b_q_id, v_color order by b_q_id, case v_color when 'R' then 1 when 'G' then 2 else 3 end;

    Схема

Нет схемы

Теперь ниже представленный запрос даст требуемый результат.

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM(
SELECT b_q_id, v_color, b_vol FROM utb 
JOIN utv ON b_v_id = v_id 
UNION ALL
SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X 
CROSS JOIN (SELECT 'R' color, NULL::smallint vol 
               UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y 
) X
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;$$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

Наверное, у вас уже возник вопрос: а нельзя ли это сделать как-нибудь проще?

Ответ положительный. Оказывается, у функции CROSSTAB есть второй необязательный параметр - запрос, возвращающий список категорий в том же порядке, в котором выводятся столбцы. Тогда первый запрос, чтобы он давал правильный результат, мы можем переписать следующим образом:

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$,
$$select 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';$$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

Поскольку PostgreSQL допускает использование конструктора таблиц, запрос

SELECT 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';

можно заменить на более короткий:

VALUES('R'),('G'),('B');

Если вы при выполнении запроса получаете ошибку, что функции crosstab не существует, это означает, что у вас не установлен модуль tablefunc. Он устанавливается простой командой (начиная с версии 9.1)

CREATE EXTENSION IF NOT EXISTS tablefunc;