Объединение данных главной таблицы с атрибутами ключ-значение формирует другую таблицу, используя ключи в качестве столбцов

#sql #postgresql #key-value #crosstab

#sql #postgresql #ключ-значение #перекрестная таблица

Вопрос:

У меня есть таблица PostgreSQL, называемая Events следующим образом:

  ---- ---------- ------------ 
| id | event    | created_at |
 ---- ---------- ------------ 
| 1  | pageview | 2019-03-29 |
 ---- ---------- ------------ 
| 2  | purchase | 2019-03-28 |
 ---- ---------- ------------

И другая таблица называется EventAttributes

  ---------- --------- ------------------------------- 
| event_id | key     | value                         |
 ---------- --------- ------------------------------- 
| 1        | url     | https://www.stackoverflow.com |
 ---------- --------- ------------------------------- 
| 2        | product | Apple                         |
 ---------- --------- ------------------------------- 
| 2        | user    | Nick                          |
 ---------- --------- -------------------------------

Я хочу получить все события вместе с соответствующими атрибутами в виде столбцов, вот так:

  ---- ---------- ------------ ------------------------------- --------- ------ 
| id | event    | created_at | url                           | product | user |
 ---- ---------- ------------ ------------------------------- --------- ------ 
| 1  | pageview | 2019-03-29 | https://www.stackoverflow.com | null    | null |
 ---- ---------- ------------ ------------------------------- --------- ------ 
| 2  | purchase | 2019-03-29 | null                          | Apple   | Nick |
 ---- ---------- ------------ ------------------------------- --------- ------

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

Приветствуется любая помощь или связанная статья по теме.

Ответ №1:

Используйте условную агрегацию:

 SELECT
    e.id, 
    e.event, 
    e.created_at,
    MAX(CASE WHEN ea.key = 'url' THEN ea.value END) url,
    MAX(CASE WHEN ea.key = 'product' THEN ea.value END) product,
    MAX(CASE WHEN ea.key = 'user ' THEN ea.value END) user 
FROM Events e
INNER JOIN EventAttributes ea ON ea.event_id  = e.id
GROUP BY 
    e.id, 
    e.event, 
    e.created_at

1. Спасибо за ваш ответ! Я ищу что-то более динамичное.. Возможно ли это вообще, чтобы избежать необходимости жестко кодировать ключи в запросе?

2. @gastongreco: добро пожаловать! SQL-запрос должен возвращать фиксированный набор столбцов, это не может быть динамическим…

Ответ №2:

Вы можете выполнить условную агрегацию :

 select e.id, e.event, e.created_at, 
       max(case when ea.key = 'url' then ea.value end) as url,
       max(case when ea.key = 'product' then ea.value end) as product,
       max(case when ea.key = 'user' then ea.value end) as user
from Events e inner join
     EventAttributes ea
     on ea.event_id = e.id
group by e.id, e.event, e.created_at;