MySQL соединяет таблицы без декартова произведения

avatar
Danil Nikonov
9 августа 2021 в 06:30
100
1
-1

У меня есть такой набор данных

group_id a_id b_id c_id d_id
1 1 нуль нуль нуль
1 нуль 2 нуль нуль
1 нуль нуль 3 нуль
1 нуль нуль нуль 4
1 нуль нуль нуль 5
2 11 нуль нуль нуль
2 нуль 12 нуль нуль
2 нуль нуль 13 нуль

Каждая запись содержит только 1 *_id для каждого group_id. Мне нужно агрегировать наборы данных для каждого group_id.

Результат:

group_id a_id b_id c_id d_id
1 1 2 3 4
1 нуль нуль нуль 5
2 11 12 13 нуль

Некоторые *_id могут быть нулевыми (строка 3 для group_id). И важно избегать декартова произведения (в моем примере строка 2 содержит только d_id = 5, потому что первые 4 строки объединены в одну, а неагрегированных a_id, b_id и c_id с group_id больше нет. =1).

Можно ли сделать это с помощью соединений MySQL?

Источник
Akina
9 августа 2021 в 06:39
0

Почему d_id=4 входит в объединенную группу, а d_id=5 нет, почему не наоборот?

Akina
9 августа 2021 в 06:41
0

Что-то (ограничение CHECK?) обеспечивает строго «три NULL и одно значение в строке» в исходных данных?

Danil Nikonov
9 августа 2021 в 06:56
0

@Akina d_id=5 тоже входит в объединенную группу. Есть 2 объединенные группы для group_id=1 (строки 1 и 2).

Danil Nikonov
9 августа 2021 в 06:56
0

@Akina Akina, мне не нужны проверочные ограничения, потому что нулевой идентификатор - это нормальная ситуация.

matigo
9 августа 2021 в 07:02
0

Форматируется ли набор данных таким образом для внешнего инструмента, который затем будет перебирать данные? Можно ли сгруппировать несколько значений и разделить их запятыми, например: 4,5? 🤔

Danil Nikonov
9 августа 2021 в 07:07
0

@matigo да, этот набор данных подготовлен для использования во внешнем интерфейсе. К сожалению, мне нужно показать строки, как в моем примере (объедините *_id, если это возможно, и покажите пустые ячейки, когда некоторые *_id равны нулю).

Akina
9 августа 2021 в 07:17
0

d_id=5 тоже входит в объединенную группу. Ну почему d_id=4 входит в 1-ю объединенную группу, а d_id=5 во 2-ю, почему не наоборот?

Akina
9 августа 2021 в 07:18
0

мне не нужны проверочные ограничения, потому что null id — это нормальная ситуация. норма?

Danil Nikonov
9 августа 2021 в 07:52
0

@akina 1 - потому что результирующий набор данных сформирован как объединение уникальных a_id, b_id, c_id и d_id. Нам нужно получить списки всех *_id и показать их в таблице пользовательского интерфейса в виде 4 независимых столбцов. Я знаю, что с точки зрения реляционной базы данных это не совсем правильно, но мне нужна эта структура данных в моем результате. 2 - да, это нормально. На самом деле ситуация, которую вы описали (a_id и c_id оба не равны нулю), не встречается в таблице исходных данных. Только один из a/b/c/d_id не равен нулю. Остальные нулевые.

Ответы (1)

avatar
Akina
9 августа 2021 в 07:56
0
WITH 
cte_a AS ( SELECT group_id, a_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn
           FROM test
           WHERE a_id IS NOT NULL ),
cte_b AS ( SELECT group_id, b_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn
           FROM test
           WHERE b_id IS NOT NULL ),
cte_c AS ( SELECT group_id, c_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn
           FROM test
           WHERE c_id IS NOT NULL ),
cte_d AS ( SELECT group_id, d_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn
           FROM test
           WHERE d_id IS NOT NULL ),
cte_n AS ( SELECT group_id, rn FROM cte_a
           UNION
           SELECT group_id, rn FROM cte_b
           UNION
           SELECT group_id, rn FROM cte_c
           UNION
           SELECT group_id, rn FROM cte_d )
SELECT group_id,
       cte_a.a_id,
       cte_b.b_id,
       cte_c.c_id,
       cte_d.d_id
FROM cte_n
LEFT JOIN cte_a USING (group_id, rn)
LEFT JOIN cte_b USING (group_id, rn)
LEFT JOIN cte_c USING (group_id, rn)
LEFT JOIN cte_d USING (group_id, rn)
ORDER BY group_id, rn;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=87281205ad06780b71b0e90355751e88

ПС. Если несколько значений из *_id не равны NULL в строке, возможно, что эти значения появятся в разных выходных строках.