CROSSTAB в PostgreSQL

Повернуть таблицу в 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;

Здесь мы ограничились только квадратами с номерами в диапазоне 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 * 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;

для конкретной базы данных. Для версий, предшествующих 9.1, достаточно загрузить в pgadmin следующий файл share/contrib/tablefunc.sql и выполнить его.