Блог

Каждая строка в моей базе данных представляет собой данные JSON. Мне нужно объединить строки на основе поля ‘Match’, что, как я полагаю, означает самосоединение к таблице. Будет ровно 10 строк с одинаковым полем ‘Match’, так что это означает, что мне придется объединяться 10 раз. Это невероятно неэффективно. Есть ли более эффективный способ выполнить приведенный ниже запрос?

 SELECT
P1.data->>'Reference_Name',
P2.data->>'Reference_Name',
P3.data->>'Reference_Name',
P4.data->>'Reference_Name',
P5.data->>'Reference_Name',
P6.data->>'Reference_Name',
P7.data->>'Reference_Name',
P8.data->>'Reference_Name',
P9.data->>'Reference_Name',
P10.data->>'Reference_Name'
from match_player_data P1
INNER JOIN match_player_data P2
on P1.data->>'Match' = P2.data->>'Match'
and P1.data->>'Reference_Name' <> P2.data->>'Reference_Name'
INNER JOIN match_player_data P3
on P1.data->>'Match' = P3.data->>'Match'
    and P1.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P2.data->>'Reference_Name' <> P3.data->>'Reference_Name'
INNER JOIN match_player_data P4
on P1.data->>'Match' = P4.data->>'Match'
    and P4.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P4.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P4.data->>'Reference_Name' <> P3.data->>'Reference_Name'
INNER JOIN match_player_data P5
on P1.data->>'Match' = P5.data->>'Match'
    and P5.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P5.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P5.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P5.data->>'Reference_Name' <> P4.data->>'Reference_Name'
INNER JOIN match_player_data P6
on P1.data->>'Match' = P6.data->>'Match'
    and P6.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P6.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P6.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P6.data->>'Reference_Name' <> P4.data->>'Reference_Name'
    and P6.data->>'Reference_Name' <> P5.data->>'Reference_Name'
INNER JOIN match_player_data P7
on P1.data->>'Match' = P7.data->>'Match'
    and P7.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P7.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P7.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P7.data->>'Reference_Name' <> P4.data->>'Reference_Name'
    and P7.data->>'Reference_Name' <> P5.data->>'Reference_Name'
    and P7.data->>'Reference_Name' <> P6.data->>'Reference_Name'
INNER JOIN match_player_data P8
on P1.data->>'Match' = P8.data->>'Match'
    and P8.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P4.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P5.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P6.data->>'Reference_Name'
    and P8.data->>'Reference_Name' <> P7.data->>'Reference_Name'
INNER JOIN match_player_data P9
on P1.data->>'Match' = P9.data->>'Match'
    and P9.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P4.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P5.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P6.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P7.data->>'Reference_Name'
    and P9.data->>'Reference_Name' <> P8.data->>'Reference_Name'
INNER JOIN match_player_data P10
on P1.data->>'Match' = P10.data->>'Match'
    and P10.data->>'Reference_Name' <> P1.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P2.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P3.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P4.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P5.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P6.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P7.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P8.data->>'Reference_Name'
    and P10.data->>'Reference_Name' <> P9.data->>'Reference_Name'
  

Пример ввода:

 {'Match':'1', 'Reference_Name":"a"}
{'Match':'1', 'Reference_Name":"b"}
{'Match':'1', 'Reference_Name":"c"}
{'Match':'1', 'Reference_Name":"d"}
{'Match':'1', 'Reference_Name":"e"}
{'Match':'1', 'Reference_Name":"f"}
{'Match':'1', 'Reference_Name":"g"}
{'Match':'1', 'Reference_Name":"h"}
{'Match':'1', 'Reference_Name":"j"}
{'Match':'1', 'Reference_Name":"k"}
{'Match':'2', 'Reference_Name":"b"}
  

Ожидаемый результат:

 {'Match':'1', 'Reference_Name":"a"}, {'Match':'1', 'Reference_Name":"b"}, {'Match':'1', 'Reference_Name":"c"}, {'Match':'1', 'Reference_Name":"d"},{'Match':'1', 'Reference_Name":"e"}, {'Match':'1', 'Reference_Name":"f"},{'Match':'1', 'Reference_Name":"g"}, {'Match':'1', 'Reference_Name":"h"},{'Match':'1', 'Reference_Name":"j"}, {'Match':'1', 'Reference_Name":"k"}
{'Match':'2', 'Reference_Name":"b"}
  

Вы можете объединить все в массив:

 select jsonb_agg(data) as matches
from match_player_data
group by data ->> 'Match'
  

Если вам действительно нужно каждое «совпадение» в виде отдельного столбца, просто извлеките элементы из массива:

 select matches -> 0 ->> 'Match' as match_number,
       matches -> 0 ->> 'Reference_Name' as reference_name_1,
       matches -> 1 ->> 'Reference_Name' as reference_name_2,
       matches -> 2 ->> 'Reference_Name' as reference_name_3,
       ....
from (
  select jsonb_agg(data) as matches
  from match_player_data
  group by data ->> 'Match'
) t
  

Или просто соберите имена, если вас не интересует остальное:

 select match_number,
       names[1] as reference_name_1,
       names[2] as reference_name_2,
       names[3] as reference_name_3,
       ....
from (
  select data ->> 'Match' as match_number, 
         array_agg(data ->> 'Reference_Name') as names
  from match_player_data
  group by data ->> 'Match'
) t
  

Онлайн-пример