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;
[[ column ]] |
---|
[[ value ]] |
Здесь мы ограничились только квадратами с номерами в диапазоне 12-16, чтобы, с одной стороны, уменьшить вывод, а, с другой стороны, сделать вывод презентативным. Сортировка по цветам выполнена в порядке RGB. Вот результат:
b_q_id | v_color | Qty |
---|---|---|
12 | R | 255 |
12 | G | 255 |
12 | B | 255 |
13 | B | 123 |
14 | R | 50 |
14 | B | 111 |
15 | R | 100 |
15 | G | 100 |
16 | G | 100 |
16 | B | 150 |
В терминологии CROSSTAB номера баллонов являются идентификаторами строк, а цвета - категориями. Результат поворота должен быть следующим:
square | R | G | B |
---|---|---|---|
12 | 255 | 255 | 255 |
13 | 123 | ||
14 | 50 | 111 | |
15 | 100 | 100 | |
16 | 100 | 150 |
Теперь с помощью 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):
square | R | G | B |
---|---|---|---|
12 | 255 | 255 | 255 |
13 | 123 | ||
14 | 50 | 111 | |
15 | 100 | 100 | |
16 | 100 | 150 |
Этот результат не вполне совпадает с ожидаемым. Напомним, что здесь важен только порядок. Если квадрат окрашивался только одним цветом, то значение (суммарный объем краски) попадет в первую категорию (у нас она называется 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;
[[ column ]] |
---|
[[ value ]] |
b_q_id | v_color | Qty |
---|---|---|
12 | R | 255 |
12 | G | 255 |
12 | B | 255 |
13 | R | |
13 | G | |
13 | B | 123 |
14 | R | 50 |
14 | G | |
14 | B | 111 |
15 | R | 100 |
15 | G | 100 |
15 | B | |
16 | R | |
16 | G | 100 |
16 | B | 150 |
Теперь ниже представленный запрос даст требуемый результат.
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 и выполнить его.