Postgres - перенос строк в столбцы

У меня есть следующая таблица, которая дает несколько адресов электронной почты для каждого пользователя.

Мне нужно сгладить это в столбцах пользовательского запроса. Чтобы дать мне "новейшие" 3 адреса электронной почты на основе даты создания.

user.name | user.id | email1          | email2           | email3**

Mary      | 123     | [email protected]  | [email protected] | [email protected]

Joe       | 345     | [email protected]   | [NULL]           | [NULL]

Ответ 1

Используйте crosstab() из модуля tablefunc.

SELECT * FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn < 4
     ORDER  BY user_id
   $$
  , 'VALUES (1),(2),(3)'
   ) AS t (user_id int, user_name text, email1 text, email2 text, email3 text);

Я использовал долларовое цитирование для первого параметра, который не имеет особого значения. Это просто удобно, если вам нужно избежать одиночных кавычек в строке запроса, которая является обычным случаем:

Вставить текст с одинарными кавычками в PostgreSQL

Подробное объяснение и инструкции здесь:

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

И, в частности, для "дополнительных столбцов":

Поворот на нескольких столбцах с помощью Tablefunc

Особые трудности здесь:

Отсутствие имен ключей.
- > Мы подставляем row_number() в подзапрос.
Различное количество писем.
- > Ограничимся максимумом. из трех во внешнем SELECT
и используйте crosstab() с двумя параметрами, предоставляя список возможных ключей.

Обратите внимание на NULLS LAST в ORDER BY.

Ответ 2

Если кто-либо еще найдет этот вопрос и нуждается в динамическом решении для этого, где у вас есть undefined количество столбцов для транспонирования, а не ровно 3, вы можете найти здесь приятное решение: https://github.com/jumpstarter-io/colpivot

Ответ 3

приведенный выше код действительно помог мне. Теперь я пытаюсь добавить больше полей в сводную таблицу.

    SELECT * INTO temp_pivot_table FROM crosstab(
    $$SELECT lead_id, magazine_id, days_between_mailers, rn
    FROM  (
    SELECT u.lead_id, u.magazine_id, u.days_between_mailers
         , row_number() OVER (PARTITION BY u.lead_id
                        ORDER BY u.id ASC NULLS LAST) AS rn
    FROM   temp_mailer_stats u
    ) sub
    WHERE  rn <= 3
    ORDER  BY lead_id
   $$

  , 'VALUES (1),(2),(3),(4),(5),(6)'
  ) AS t (lead_id int, magazine1 text, magazine2 text, magazine3 text, 
   days_between1 integer, days_between2 integer, days_between3 integer);

Конечно, я могу создавать отдельные таблицы, а затем соединять их вместе, но если я смогу сохранить шаг, который был бы идеальным.

Заранее спасибо за помощь.