loading..
Русский    English
17:27
листать

CROSSTAB в PostgreSQL

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

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

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

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

Консоль
Выполнить
  1. SELECT b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
  2. WHERE b_q_id BETWEEN 12 AND 16
  3. GROUP BY b_q_id, v_color
  4. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

Здесь мы ограничились только квадратами с номерами в диапазоне 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 попытаемся написать запрос, который бы дал требуемый результат:

  1. SELECT * FROM
  2. crosstab(
  3. $$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
  4. WHERE b_q_id BETWEEN 12 AND 16
  5. GROUP BY b_q_id, v_color
  6. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$
  7. ) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
  8. 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:

Консоль
Выполнить
  1. SELECT b_q_id, v_color, SUM(b_vol) qty FROM(
  2. SELECT b_q_id, v_color, b_vol FROM utb
  3. JOIN utv ON b_v_id = v_id
  4. UNION ALL --вот эта добавка
  5. SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X
  6. CROSS JOIN (SELECT 'R' color, NULL::smallint vol
  7.                 UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y
  8. ) X
  9. WHERE b_q_id BETWEEN 12 AND 16
  10. GROUP BY b_q_id, v_color
  11. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

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

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

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

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

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

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

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

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

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

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

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

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
продать bitcoin . Подробности и запчасти iPhone здесь.
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100