Распространение таблицы Postgres & ldquo; Wide & rdquo; [Дубликат]

В вашем случае, я думаю, массив хорош. SQL Fiddle

select
    bar,
    feh || array_fill(null::int, array[c - array_length(feh, 1)]) feh
from
    (
        select bar, array_agg(feh) feh
        from foo
        group by bar
    ) s
    cross join (
        select count(*)::int c
        from foo
        group by bar
        order by c desc limit 1
    ) c(c)
;
 bar |      feh      
-----+---------------
 A   | {10,20,NULL}
 B   | {3,4,NULL}
 C   | {5,NULL,NULL}
 D   | {6,7,8}

Это для завершения @Damian хорошего ответа. Я уже предлагал подход JSON в других ответах до удобной функции json_object_agg 9.6. Это просто требует больше работы с предыдущим набором инструментов.

Два из приведенных возможных недостатков на самом деле нет. При необходимости случайный порядок ключей трижды корректируется. Отсутствующие ключи, если необходимо, занимают почти тривиальное количество кода, подлежащего устранению:

select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as data
from
    tbl
    right join
    (
        (select distinct row_name from tbl) a
        cross join
        (select distinct attrib from tbl) b
    ) c using (row_name, attrib)
group by row_name
order by row_name
;
 bar |                     data                     
-----+----------------------------------------------
 a   | { "val1" : 10, "val2" : 20, "val3" : null }
 b   | { "val1" : 3, "val2" : 4, "val3" : null }
 c   | { "val1" : 5, "val2" : null, "val3" : null }
 d   | { "val1" : 6, "val2" : 7, "val3" : 8 }

Для конечного пользователя запроса, который понимает JSON, нет недостатков. Единственное, что он не может быть использован в качестве источника таблицы.

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

Чтобы проиллюстрировать, вы попросили метод переноса данных со следующей структурой:

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

в этот формат:

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

традиционное решение является умным (и невероятно осведомленным) подходом к созданию динамических кросс-табличных запросов, которые объясняются в изящной детализации в ответе Эрвина Брандстретера.

Однако, если ваш конкретный вариант использования достаточно гибкий, чтобы принять несколько иной результат формат, тогда возможно другое решение, которое красиво обрабатывает динамические повороты. Этот метод, о котором я узнал здесь

• Динамические сводные таблицы с JSON и PostgreSQL

использует новый PostgreSQL jsonb_object_agg для создания поворотных данных «на лету» в виде объекта JSON.

Я буду использовать «более простой тестовый пример» г-на Брандстретера, чтобы проиллюстрировать:

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

Используя функцию jsonb_object_agg, мы можем создать необходимый поворотный результирующий набор с этой прекрасной красотой:

SELECT
  row_name AS bar,
  json_object_agg(attrib, val) AS data
FROM tbl
GROUP BY row_name
ORDER BY row_name;

Какие выходы:

 bar |                  data                  
-----+----------------------------------------
 A   | { "val1" : 10, "val2" : 20 }
 B   | { "val1" : 3, "val2" : 4 }
 C   | { "val1" : 5 }
 D   | { "val3" : 8, "val1" : 6, "val2" : 7 }

Как вы можете видеть , эта функция работает путем создания пар ключ / значение в объекте JSON из столбцов attrib и value в образцах данных, все сгруппированы по row_name.

Хотя этот набор результатов явно выглядит по-разному , Я считаю, что он действительно удовлетворит многие (если не большинство) случаи использования в реальном мире, особенно те, где для данных требуется динамически сгенерированный опорный стержень, или когда результирующие данные потребляются родительским приложением (например, требуется переформатировать для передача в HTTP-ответе).

Преимущества этого pproach:

• Синтаксис чистых. Я думаю, что все согласятся с тем, что синтаксис этого подхода намного понятнее и понятнее, чем даже самые основные примеры кросс-таблицы.
• Полностью динамический. Не нужно заранее указывать информацию о базовых данных. Ни имена столбцов, ни их типы данных не должны быть известны заранее.
• Обрабатывает большое количество столбцов. Поскольку скользящие данные сохраняются как один столбец jsonb, вы не столкнетесь с лимитом столбца PostgreSQL (я полагаю, что ≤1 600 столбцов). По-прежнему существует предел, но я считаю, что это то же самое, что и для текстовых полей: 1 ГБ на созданный объект JSON (пожалуйста, поправьте меня, если я ошибаюсь). Это много пар ключ / значение!
• Упрощенная обработка данных. Я считаю, что создание данных JSON в БД упростит (и, вероятно, ускорит) процесс преобразования данных в родительских приложениях. (Вы заметите, что целочисленные данные в нашем примере тестового теста были правильно сохранены как таковые в результирующих объектах JSON. PostgreSQL обрабатывает это путем автоматического преобразования его внутренних типов данных в JSON в соответствии со спецификацией JSON.) Это эффективно устранит необходимость вручную передавать данные, переданные родительским приложениям: все они могут быть делегированы в собственный парсер JSON приложения.

Различия (и возможные недостатки):

• It выглядит иначе. Нельзя отрицать, что результаты этого подхода выглядят иначе. Объект JSON не так хорош, как набор результатов кросс-таблицы; однако различия являются чисто косметическими. Такая же информация создается - и в формате, который, вероятно, больше удобен для потребления родительскими приложениями.
• Отсутствующие ключи. Отсутствующие значения в подходе кросс-таблицы заполняются нулями, в то время как объекты JSON просто пропускают соответствующие ключи. Вам придется решать за себя, если это приемлемый компромисс для вашего прецедента. Мне кажется, что любая попытка решить эту проблему в PostgreSQL значительно усложнит процесс и, вероятно, включит некоторую интроспекцию в виде дополнительных запросов.
• Порядок ключей не сохраняется. Я не знаю, может ли это быть рассмотрено в PostgreSQL, но эта проблема в основном косметическая, так как любые родительские приложения либо вряд ли будут полагаться на порядок клавиш, либо иметь возможность определять правильный порядок клавиш другими способами. В худшем случае, вероятно, потребуется только запрос на добавление базы данных.

Заключение

Мне очень любопытно услышать мнения других (особенно @ ErwinBrandstetter's) об этом подход, особенно в том, что касается производительности. Когда я обнаружил этот подход в блоге Эндрю Бендера, это было похоже на попадание в сторону головы. Какой прекрасный способ сделать новый подход к сложной проблеме в PostrgeSQL. Он отлично решил мой прецедент, и я считаю, что он также будет служить многим другим.

Прошу прощения за возвращение в прошлом, но решение «Динамический кросс-лист» возвращает ошибочную таблицу результатов. Таким образом, значения valN ошибочно «выравниваются влево», и они не соответствуют именам столбцов. Когда входная таблица имеет «отверстия» в значениях, например. «C» имеет val1 и val3, но не val2. Это приводит к ошибке: значение val3 будет располагаться в столбце val2 (т.е. следующем свободном столбце) в финальной таблице.

CREATE TEMP TABLE tbl (row_name text, attrib text, val int); 
INSERT INTO tbl (row_name, attrib, val) VALUES ('C', 'val1', 5) ('C', 'val3', 7);

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl 
ORDER BY 1,2') AS ct (row_name text, val1 int, val2 int, val3 int);

row_name|val1|val2|val3
 C      |   5|  7 |

Чтобы вернуть правильные ячейки с «отверстиями» в правом столбце , запрос кросс-таблицы требует 2-го SELECT в кросс-таблице, что-то вроде этого "crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2', 'select distinct row_name from tbl order by 1')"