Разворачивание таблиц

#sql #interbase

Есть три таблицы


main (id, f1, f2, f3, ...)
detail (id, main_id)
sub_detail (id, param, val)


Связь main -> detail один ко многим, detail -> sub_detail один к одному (по полям id)

На выходе нужно получить таблицу с полями

main_id, f1, f2, f3, value1, value2, value3

где valueN это значение поля val из таблицы sub_detail для записи с param = N. Не
все valueN могут присутствовать в базе.

Базовый запрос выглядит так

SELECT
  m.id AS main_id,
  m.f1,
  m.f2,
  m.f3,
  s1.val AS value1,
  s2.val AS value2,
  s3.val AS value3
FROM
  main m
  LEFT JOIN detail d ON (
    d.main_id = m.id
  )
  LEFT JOIN sub_detail s1 ON (
    s1.id = d.id AND
    s1.param = 1
  )
  LEFT JOIN sub_detail s2 ON (
    s2.id = d.id AND
    s2.param = 2
  )
  LEFT JOIN sub_detail s3 ON (
    s3.id = d.id AND
    s3.param = 3
  )
WHERE
  m.id = 1;


Но он вместо одной записи возвращает столько записей, сколько есть дочерних записей
в detail.

Поставить группировку по main.id и вытаскивать MAX(s.val) я не могу, т.к. в запросе
есть еще поля f1, f2

Есть вариант на основании detail и sub_detail построить view

CREATE VIEW vw_detail (
  main_id,
  param,
  val
) AS
SELECT
  d.main_id,
  s.param,
  s.val
FROM
  detail d
  JOIN sub_detail s ON (
    d.id = s.id
  )


и вызывать ее в основном запросе

SELECT
  m.id AS main_id,
  m.f1,
  m.f2,
  m.f3,
  s1.val AS value1,
  s2.val AS value2,
  s3.val AS value3
FROM
  main m
  LEFT JOIN vw_detail s1 ON (
    s1.main_id = m.id AND
    s1.param = 1
  )
  LEFT JOIN vw_detail s2 ON (
    s2.main_id = m.id AND
    s2.param = 2
  )
  LEFT JOIN vw_detail s3 ON (
    s3.main_id = m.id AND
    s3.param = 3
  )
WHERE
  m.id = 1;


но в Interbase есть старый баг из-за которого при вызове LEFT JOIN view начинают
игнорироваться индексы и происходит полное сканирование таблицы. А писать JOIN vw_detail
нельзя, потому что некоторые (или даже все) параметры могут отсутствовать.

Пробовал писать так

CREATE VIEW vw_values (
  main_id,
  value1,
  value2,
  value3
) AS
SELECT
  m.id,
  MAX(s1.val),
  MAX(s2.val),
  MAX(s3.val),
FROM
  main m
  LEFT JOIN detail d ON (
    d.main_id = m.id
  )
  LEFT JOIN sub_detail s1 ON (
    s1.id = d.id AND
    s1.param = 1
  )
  LEFT JOIN sub_detail s2 ON (
    s2.id = d.id AND
    s2.param = 2
  )
  LEFT JOIN sub_detail s3 ON (
    s3.id = d.id AND
    s3.param = 3
  )
GROUP BY
  m.id;


и потом в основном запросе

SELECT
  m.id AS main_id,
  m.f1,
  m.f2,
  m.f3,
  v.value1,
  v.value2,
  v.value3
FROM
  main m
  JOIN vw_values v ON (
    v.main_id = m.id
  )
WHERE
  m.id = 1;


но тогда вначале идет построение VIEW с группировкой, а потом накладывание условия
v.main_id = m.id. План получается дикий.

Вариант заменить vw_values селективной процедурой тоже не подходит. Т.к. основной
запрос выполняется в другой view, а синтаксис Interbase не позволяет внутри view использовать
процедуры

P.S. Вариант перейти на Firebird не предлагать

Ссылка на DB Fiddle.
    

Ответы

Ответ 1

SELECT
  m.id AS main_id,
  m.f1,
  m.f2,
  m.f3,
  MAX(s1.val) AS value1,
  MAX(s2.val) AS value2,
  MAX(s3.val) AS value3
FROM
  main m
  LEFT JOIN detail d ON (
    d.main_id = m.id
  )
  LEFT JOIN sub_detail s1 ON (
    s1.id = d.id AND
    s1.param = 1
  )
  LEFT JOIN sub_detail s2 ON (
    s2.id = d.id AND
    s2.param = 2
  )
  LEFT JOIN sub_detail s3 ON (
    s3.id = d.id AND
    s3.param = 3
  )
WHERE
  m.id = 1
GROUP BY   m.id,
           m.f1,
           m.f2,
           m.f3


fiddle


Ответ 2

Денормализовал таблицу detail и скопировал туда поле param. После чего запрос переписался
в такой вид

SELECT
  m.id AS main_id,
  m.f1,
  m.f2,
  m.f3,
  s1.val AS value1,
  s2.val AS value2,
  s3.val AS value3
FROM
  main m
  LEFT JOIN detail d1 ON (
    d1.main_id = m.id AND
    d1.param = 1
  )
  LEFT JOIN sub_detail s1 ON (
    s1.id = d1.id
  )
  LEFT JOIN detail d2 ON (
    d2.main_id = m.id AND
    d2.param = 2
  )
  LEFT JOIN sub_detail s2 ON (
    s2.id = d2.id
  )
  LEFT JOIN detail d3 ON (
    d3.main_id = m.id AND
    d3.param = 3
  )
  LEFT JOIN sub_detail s3 ON (
    s3.id = d3.id
  )
WHERE
  m.id = 1;


Если кто подскажет решение без денормализации - буду рад



Update

Причем поля detail.main_id и detail.param должны входить в общий индекс. Если завести
два отдельных, то хоть и план строится с использованием индексов, но выполнение очень
долгое