Как сделать выборку из таблицы и развернуть в строку?

Question

[Webber]

Есть такая структура:



У айтема много айтем филдов. У филда есть поле key (уникальное на айтем) которое нужно сделать столбцом. У item_fields есть языки en,ru,ko нужно выбрать только 1н узазанный.
Нужно сделать запрос к items чтобы все item_fields вместить столбцами. Как пример столбцов (если у нас 3 item_fields с ключами):
id, priority, key1, key2, key3
поле en или ru или ko должно стать значением для key1, key2, key3

--

Запрос вышел примерно такой, но с будет с пост-обработкой - можно ли улучшить? Набор филдов заранее известен.

select items.id,
       items.priority,
       i1.en as f1,
       i2.en as f2,
       i3.en as f3
from items
left join item_fields as i1 on items.id = i1.item_id and i1.field_id = 218
left join item_fields as i2 on items.id = i2.item_id and i2.field_id = 219
left join item_fields as i3 on items.id = i3.item_id and i3.field_id = 220

затем сделаю замену f1,2,3 на ключи

Answer 1

[Slava Rozhnev]

Pivot можно сделать следующим образом: (с одним join-ом):

select 
	i.name,
	min(ru) filter (where fi.id = 218) key1,
	min(ru) filter (where fi.id = 219) key2,
	min(ru) filter (where fi.id = 220) key3
from items i
left join item_fields fi on i.id = fi.item_id and fi.id in (218, 219, 220)
group by i.name, item_id

Проверить SQL запрос онлайн

Результат:

+=======+========+========+========+
| name  | key1   | key2   | key3   |
+=======+========+========+========+
| Item1 | Поле 1 | Поле 2 | Поле 3 |
+-------+--------+--------+--------+

Comments

[Михаил Е]

Я так понимаю это работает аналогично оконным функциям ? (т.е. на уровне движка это будут доп. запросы к таблице источнику ?
"from items i
left join item_fields fi on i.id = fi.item_id and fi.id in (218, 219, 220)"
получается это не совсем Pivot ? т.е. результат сопоставимый, но по производительности медленней ?

[Slava Rozhnev]

Это стандартная имплементация фенкции пайвот в базах где это не имплементировано в движке базы (MySQL, PostgreSQL).
Под капотом это обычный джойн с группировкой, так что никаких "дополнительных запросов" не будет.
Если итересно план SQL запроса

[Webber]

Спасибо, буду пробовать

[Михаил Е]

Slava Rozhnev спасибо, не сталкивался с подобным синтаксисом, расширил кругозор.

[Webber]

Slava Rozhnev, Спасибо работает. Скажите как сделать условие здесь "min(ru) filter (where fi.id = 218) key1", если поле ru == null, то брать значение en столбца?

[Slava Rozhnev]

Webber, смотри здесь: SQL запрос

Answer 2

[Михаил Е]

Насколько понял, вам нужно попробовать реализовать "Pivot Table", но в PGSQL этой функции по умолчанию нет (я ей пользовался в MSSQL как встроенной). Если погуглить есть реализации этой функции на PGSQL через функцию "crosstab". Посмотрите, если устроит можете один раз организовать функцию и пользовать в подобных случаях. Но насколько это актуально конкретно для вас (может у вас всего 3 поля и имеет смысл просто запросом как в вашем примере реализовать) решать вам.
Про языки, чтобы выбрать один указанный, оптимальнее использовать "coalesce( t.en, t.ru, t,ko)"

UPD: т.к. имена полей у вас находятся в промежуточной таблице, если вы хотите чтобы значения "coalesce( t.en, t.ru, t,ko)" были в заголовках колонок, скорее всего придётся использовать "Динамический Pivot table" он несколько сложнее в организации, но производительней с точки зрения использования данных. Если же вас устроит "Fieidid" как заголовок колонки, можно воспользоваться обычным Pivot table.

В MSSQL это выглядело бы примерно так:

Select ItemId as ItemId,
[fieldid1], [fieldid2], [fieldid3], [fieldid4], [fieldid5] 
From
(Select 
	items.id as ItemId,
	Coalesce(itemfields.en,itemfields.ru,itemfields.ko) as FieldName,
	itemfields.fieldid as fieldid,
	fields.key as value
From
	from items as items
	left join item_fields as item_fields on items.id = item_fields.item_id
		left join fields as fields On item_fields.fieldid = fields.id) SourceTable
pivot
(
Max(value) for fieldid in ([fieldid1], [fieldid2], [fieldid3], [fieldid4], [fieldid5] )
) as PivotTable;

Comments

[Webber]

Спасибо, буду пробовать