Intereting Posts
вычислить сумму времени типа с помощью sql ORA-00918: столбец, неоднозначно определенный в SELECT * Эффективный запрос SQL 2000 для выбора предпочтительной конфеты Проблема с SQLite Удалить строку JOIN Issue: Исправьте SQL-запрос для решения: ORA-01799: столбец не может быть внешним соединен с подзапросом Попытка понять () и разделить на Два индекса с одним столбцом против одного двухколоночного индекса в MySQL? Как использовать count и группу с помощью одного и того же оператора select Получить уязвимые строки в ExecuteNonQuery BigQuery – объединение нескольких условий с использованием подзапросов и операторов OR Почему я не могу ввести эту дату в таблицу, используя sql? как запросить в sqlite для разных форматов даты Лучший тип поля базы данных для URL-адреса ssis импортирует два файла excel с различным количеством столбцов в базу данных В SQL, как написать запрос, чтобы вернуть 1 запись из отношения 1 ко многим?

PostgreSQL 9.3: Динамическая сводная таблица

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

Таблица : матрица

 create table matrix ( cola varchar(10), colb varchar(10) ); 

Вставка строк :

 insert into matrix values('A1','B1'),('A2','B2'),('A3','B3'),('A4','B4'), ('A5','B5'),('A6','B6'),('A7','B7'),('A8','B8'), ('A9','B9'),('A10','B10'),('A11','B11'),('A12','B12'), ('A13','B13'),('A14','B14'),('A15','B15'),('A16','B16'), ('A17','B17'),('A18','B18'),('A19','B19'),('A20','B20'), ('A21','B21'),('A22','B22'),('A23','B23'),('A24','B24'), ('A25','B25'),('A26','B26'),('A27','B27'),('A28','B28'), ('A29','B29'),('A30','B30'); 

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

Ожидаемый результат :

  A1 A2 A3 A4 A5 A6 ................ A30 ------------------------------------------------------------------ B1 | 1 0 0 0 0 0 0 | B2 | 0 1 0 0 0 0 0 | B3 | 0 0 1 0 0 0 0 | B4 | 0 0 0 1 0 0 0 | B5 | 0 0 0 0 1 0 0 | B6 | 0 0 0 0 0 1 0 . | . | . | . | B30| 0 0 0 0 0 0 1 

Вы можете сделать это с помощью crosstab() из дополнительного модуля tablefunc :

 SELECT b , COALESCE(a1, 0) AS "A1" , COALESCE(a2, 0) AS "A2" , COALESCE(a3, 0) AS "A3" , ... -- all the way up to "A30" FROM crosstab( 'SELECT colb, cola, 1 AS val FROM matrix ORDER BY 1,2' , $$SELECT 'A'::text || g FROM generate_series(1,30) g$$ ) AS t (b text , a1 int, a2 int, a3 int, a4 int, a5 int, a6 int , a7 int, a8 int, a9 int, a10 int, a11 int, a12 int , a13 int, a14 int, a15 int, a16 int, a17 int, a18 int , a19 int, a20 int, a21 int, a22 int, a23 int, a24 int , a25 int, a26 int, a27 int, a28 int, a29 int, a30 int); 

Если NULL вместо 0 работает, это может быть просто SELECT * во внешнем запросе.
Детальное объяснение:

  • Запрос кросс-таблицы PostgreSQL

Особая «трудность» здесь: никакой реальной «ценности». Поэтому добавьте 1 AS val качестве последнего столбца.

Неизвестное количество категорий

Полностью динамический запрос (с неизвестным типом результата) невозможен в одном запросе. Вам нужны два запроса. Сначала создайте оператор, как указано выше, и выполните его. Детали:

  • Выбор нескольких значений max () с использованием одного оператора SQL

  • PostgreSQL конвертирует столбцы в строки? Транспонирование?

  • Динамически создавать столбцы для кросс-таблицы в PostgreSQL

  • Динамическая альтернатива повороту с помощью CASE и GROUP BY

Слишком много категорий

Если вы превысите максимальное количество столбцов (1600) , классическая кросс-таблица невозможна, потому что результат не может быть представлен отдельными столбцами. (Кроме того, человеческие глаза вряд ли смогут прочитать таблицу с таким количеством столбцов)

Массивы или типы документов, такие как hstore или jsonb являются альтернативой. Вот решение с массивами:

 SELECT colb, array_agg(cola) AS colas FROM ( SELECT colb, right(colb, -1)::int AS sortb , CASE WHEN m.cola IS NULL THEN 0 ELSE 1 END AS cola FROM (SELECT DISTINCT colb FROM matrix) b CROSS JOIN (SELECT DISTINCT cola FROM matrix) a LEFT JOIN matrix m USING (colb, cola) ORDER BY sortb, right(cola, -1)::int ) sub GROUP BY 1, sortb ORDER BY sortb; 
  • Создайте полную сетку значений с помощью:

      (SELECT DISTINCT colb FROM matrix) b CROSS JOIN (SELECT DISTINCT cola FROM matrix) a 
  • LEFT JOIN существующие комбинации, порядок по числовой части имени и агрегата в массивы.

    • right(colb, -1)::int обрезает ведущий символ из «A3» и отличает цифры до целого, чтобы мы получили правильный порядок сортировки.

Базовая матрица

Если вам просто нужна таблица 0 a 1 где x = y , это может быть дешевле:

 SELECT x, array_agg((x = y)::int) AS y_arr FROM generate_series(1,10) x , generate_series(1,10) y GROUP BY 1 ORDER BY 1; 

SQL Fiddle на основе того, который вы указали в комментариях.

Обратите внимание, что sqlfiddle.com в настоящее время имеет ошибку, которая убивает отображение значений массива. Поэтому я бросил туда text чтобы обойти его.