Join со значением поля в качестве имени поля в результате

имхо нужно играть с case-when-ами

Не надо такого делать на sql, потом концов не найдешь в своих портянках. У меня так коллега делал, до сих пор перед глазами как живой.

Но если так хочешь надо узнать, какая у тебя субд. Такие извращения делаются у каждого по-своему.

Можно ли каким-то образом

Можно. Переделать структуру бд. Не надо натягивать такое извращение на реляционную бд.

Там походу, как и у нас, кому-то пришла в голову светлая идея запилить key-value таблицу.

запилить key-value таблицу

О, отличная идея!

select
       t1.item_id
     , t1.param1
     , t1.param2
     , (select max(t2.param3) from t2 where t2.id = t1.id) as param3
     , (select max(t2.param4) from t2 where t2.id = t1.id) as param4
  from t1

max - это на случай если в t2 встретится больше одной записи, т.е. ошибки в этом случае не произойдет, но остальные значения останутся за бортом. Вообще если t2.item_id и param3 (param4) не уникальны нужны дополнительные вводные.

Число запросов = число столбцов в key-value + 1. Внушает, неистово одобряю.
Вообще в ракле есть pivot и там с помощью чёрной магии и такой-то матери оно разворачивает kv в таблицу. Но то ракл.

ТС, ты где? Что у тебя за субд? Ты троллишь что-ли? Тот тред про си хорош.

Кстати с нодой я вообще не страдаю от этого и легко разворачиваю эти самые kv в удобоваримые объекты.

Это прямой ответ на поставленный вопрос. Такая конструкция будет работать во всех СУБД поддерживающих SQL. Количество select в конструкции больше одного не означает что конструкция неэффективна. Можно транспонировать вторую таблицу (если позволяет СУБД) и обойтись одной конструкцией select, но ниоткуда не следует что такой способ будет эффективнее

В постгре для таких штук есть полный фарш из json[b]_* функций. По факту очень удобно и довольно быстро. И никакие kv таблицы не страшны.

В постгре для таких штук есть полный фарш из json_* функций.

Да это уже не sql, а читерство. Еще бы монгу какую-нибудь туда запихали.
Впрочем выглядит вкусно, но у нас все равно никто не будет pg ставить. Там же пакетов нет.

Не троллю, просто пару раз была идея сделать такое для чего-то вроде динамической схемы. Чистый key-value-то распаковывается легко, в python, например, можно dict'у сразу скормить список с парами.

Я предполагал, вдруг есть что-то в стандартном SQL, а как выясняется есть либо вариант с монструозными запросами с захардкоженым набором столбцов, либо непереносимые штуки вроде PIVOT (в MS и Oracle) и CROSSTAB (в postgres).

Исходя из ответов, как я понял, так лучше не делать, а если делать, то разворачивать нормально выйдет только в коде?

Да это уже не sql, а читерство.

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

P.S.: подскажи кейворд на тему «пакетов», чтоб я нашёл что это такое.

Либо я что-то не понял, либо должно быть

select
       t1.item_id
     , t1.param1
     , t1.param2
     , (select max(t2.value) from t2 where t2.id = t1.id and t2.name = 'param3') as param3
     , (select max(t2.value) from t2 where t2.id = t1.id and t2.name = 'param4') as param4
  from t1

Итт умники и умницы, поясните, почему не подойдет

select
  ...,
  t3.value as param3,
  t4.value as param4
from t
left join t2 as t3
  on t3.item_id = t.item_id and
     t3.name = "param3"
left join t2 as t4
  on t4.item_id = t.item_id and
     t4.name = "param4"

t2.name = 'param3'

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

С left join оптимизатору сложнее работать будет, и hint-ы сложнее расставлять (если будет необходимость).

А где читать об этом? Вот так вот прямолинейное самое что ни есть реляционное решение, и оптимизатор уже споткнулся? Что это за оптимизатор такой.

Зависит от общего объема данных и объема который хотите выбрать за один запрос. Например если в одном запросе выбираются данные для одного Id, то КV таблица с двумя столбцами будет удобнее, в один заход выбираете нужное количество строк и разворачиваете. Если например параметров сотни, то выбирать из сотни нормализованных таблиц будет дороже., но если нужно выбрать данные за один раз для всех Id, то с нормализованное сотней таблиц будет дешевле.

Я и не хотел хардкодить. Просто я не понял, откуда в запросе, на который отвечал, само по себе возникло поле t2.param3 или транспонировалась таблица.

Где читать вообще для абстрактного оптимизатора не знаю. Решение, то реляционное, таблица только не реляционная. Откуда оптимизатор будет брать статистику?

А если конкретного? А если построить unique(item_id, name), который по логике должен быть? Варианты с субселектом кстати реляционные?

.. по логике он вообще pk.

Понятно, т.е. можно сделать

db.execute('select item_id from t1')
items = db.fetchall()
for item in items:
    db.execute('select name, value from t2 where item_id = ?', (item.item_id))
    item.update(dict(db.fetchall()))

select
       t1.item_id
     , t1.param1
     , t1.param2
     , (select max(t2.value) from t2 where t2.id = t1.id and t2.name = 'param3') as param3
     , ....
     , (select max(t2.value) from t2 where t2.id = t1.id and t2.name = 'paramN') as paramN
  from t1

max - это на случай если в t2 встретится больше одной записи

Лучше наверное просто делать select ... limit 1, чем на каждый запрос вычислять max.

Если построить unique(item_id, name) который по логике должен быть, и взять идеальный оптимизатор, то по логике оба варианта запросов должны преобразоваться в один и тот-же план. На практике (на моей) на join-ах (особенно left, right) оптимизатор спотыкается чаще. И еще мне не понятен термин реляционный применительно к запросу?

Набор столбцов в запросе для каждого t1.item_id видимо будет свой?

По логике вариант последовательной выборки выглядит дешевле, но опять-же объема/распределения данных, вполне может быть ситуация, когда СУБД быстрее сделать два лишних обращения к таблице, чем пересылать N раз текст запроса от клиента к серверу.

Тут вердикт может вынести только эксперимент.

Наверно лучше. MAX написал потому, что во всех СУБД одинаково пишется (а также min avg тоже одинаково но тоже дороже). Я обычно пишу rownum < 2. Опять-же для Оракла если поле индексировано то max будет не медленнее.

pivot.
но это говно.

Непонятно в каких случаях, тем более тут в тривиальном. А есть какие-то простые примеры спотыков?

не понятен термин реляционный

t1 (item_id) и t2 (item_id, :name) это простое отношение. Что имеется ввиду под «таблица только не реляционная» - это и я хотел узнать.

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

И еще мне не понятен термин реляционный применительно к запросу?

Вот так вот прямолинейное самое что ни есть реляционное решение

Что такое реляционное решение?

Набор столбцов в запросе для каждого t1.item_id видимо будет свой?

Не совсем понял о чём речь. Когда я говорил о том, что надо получить набор столбцов, я имел в виду набор всех допустимых значений t2.name, т.к. мы должны их явно указать в подзапросах.

Неточно выразился. Речь о том что для каждого t1.item_d в таблице t2 может быть разное количество записей. Соответственно не все подзапросы (количество которых будет равно максимально допустимому) будут возвращать какие-то значения.

Походу, вот еще что пришло в голову: В оракле есть агрегатная функция сложения (сцепления) строк, т.е. из второй таблицы результаты можно выгрести в один проход, будет иметь вид:

select
       t2.item_id
       listagg(t2.name||' = '||to_char(value), '; ') within group (order by t2.name) l

  from t2
 group by t2.item_id

На выходе получим что-то вроде

  item_id | l
----------------------------------------------
  1       |  param_1=v1; param_2=v2; param5=v5
....

В СУБД в которой вы работаете, может тоже есть что-то похожее.

Это которое использует сопоставление значений в колонках таблиц, а не конкатенацию или жсоны. Давай не будем как в детсаде шланговать, либо скажи ответ на [1], либо я пошел дальше. Какие-то где-то у кого-то там представления о терминах в вакууме мне неинтересны, как и играть целый день в поймай ***добола.

[1] Join со значением поля в качестве имени поля в результате (комментарий)

стоит ли заменять левые джойны на субселекты в перспективе?
На каких субд?

Правильно, иди дальше. С хамами разговаривать желания никакого.

Итт умники и умницы, поясните, почему не подойдет

Научись разговаривать, тогда и будем обсуждать

P.S.: подскажи кейворд на тему «пакетов», чтоб я нашёл что это такое.

Так они и называются. Вот, например http://www.firststeps.ru/sql/oracle/r.php?96
По сути это неймспейс для набора функций/процедур.

json-вещи это просто сказка, эти фичи влюбили меня в эту субд.

Жаль что, на этой работе мне это точно не грозит.

Исходя из ответов, как я понял, так лучше не делать, а если делать, то разворачивать нормально выйдет только в коде?

Классическая РСУБД очень плохо натягивается на всё, что сильно отличается от таблицы со связями. Даже попытки сделать иерархию или бинарное дерево превращают запросы в ссаные простынки не говоря уже про key-value. Для трёхзвенки это, конечно, не проблема.