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

Danil Nikonov

9 августа 2021 в 06:30

100

-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

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

Akina

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

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

Danil Nikonov

9 августа 2021 в 06:56

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

Danil Nikonov

9 августа 2021 в 06:56

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

matigo

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

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

Danil Nikonov

9 августа 2021 в 07:07

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

Akina

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

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

Akina

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

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

Danil Nikonov

9 августа 2021 в 07:52

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

Ответы (1)

Akina

9 августа 2021 в 07:56

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 в строке, возможно, что эти значения появятся в разных выходных строках.

Поделиться Скопировать ссылку Скопировано