Как объединить ранее сгруппированные строки одной или несколькими общими строками?

В моей базе данных PostgreSQL у меня есть два отдельных запроса (q1, q2), которые объединяются между несколькими таблицами и назначают одни и те же элементы разным группам (я называю эти подгруппы) на основе разных критериев. Я получаю результат запроса 1 и 2 (qr1, qr2).

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

qr1                           qr2
+----------+-------------+    +----------+-------------+
| item     | subgroup1    |   | item     | subgroup2   |
+----------+-------------+    +----------+-------------+
|       1  |           1 |    |        1 |           5 |
|       2  |           1 |    |        5 |           6 |
|       3  |           2 |    |        6 |           6 |
|       4  |           3 |    |        7 |           7 |
|       5  |           3 |    |        8 |           5 |
|       6  |           4 |    |       10 |           5 |
+----------+-------------+    +----------+-------------+
combined (interested in item and group):
+---------+------------+------------+-------+
| item    | subgroup1  | subgroup2  | group |
+---------+------------+------------+-------+
|  1      | 1          | 5          | 1     |
|  2      | 1          | N          | 1     |
|  3      | 2          | N          | 2     |
|  4      | 3          | N          | 3     |
|  5      | 3          | 6          | 3     |
|  6      | 4          | 6          | 3     |
|  7      | N          | 7          | 4     |
|  8      | N          | 5          | 1     |
| 10      | N          | 5          | 1     |
+---------+------------+------------+-------+
  • Подгруппы 1 и 5 будут объединены из-за общего элемента 1. (элементы группы 1, 2, 8, 10)
  • Подгруппа 3, 4! и 6 будут объединены из-за общего элемента 5. (сгруппировать элементы 4, 5, 6)
  • Подгруппы 2 и 7 составляют свою собственную группу и не делят элемент ни с какой другой группой.

Я пытался использовать оконные функции, считать по дублирующемуся элементу и идти оттуда. Но я застрял.

Подгруппа 4 не связана с элементом 5. Она связана с элементом 6, а поскольку подгруппа 6 связана как с элементами 5, так и с элементами 6, вы хотите включить подгруппу 4 с элементами 3 и 6? Это включено косвенной ассоциацией? Я очень сомневаюсь, что один только SQL может достичь этого, и «простой способ» не описывает усилия. Если у вас есть конкретный код с проблемой, опубликуйте вопрос.

June7 19.11.2022 19:16

Решение, скорее всего, будет включать рекурсивное поведение. См.: WITH RECURSIVE ...;

Jon Armstrong 19.11.2022 19:19
Как настроить Tailwind CSS с React.js и Next.js?
Как настроить Tailwind CSS с React.js и Next.js?
Tailwind CSS - единственный фреймворк, который, как я убедился, масштабируется в больших командах. Он легко настраивается, адаптируется к любому...
LeetCode запись решения 2536. Увеличение подматриц на единицу
LeetCode запись решения 2536. Увеличение подматриц на единицу
Увеличение подматриц на единицу - LeetCode
Переключение светлых/темных тем
Переключение светлых/темных тем
В Microsoft Training - Guided Project - Build a simple website with web pages, CSS files and JavaScript files, мы объясняем, как CSS можно...
Отношения "многие ко многим" в Laravel с методами присоединения и отсоединения
Отношения "многие ко многим" в Laravel с методами присоединения и отсоединения
Отношения "многие ко многим" в Laravel могут быть немного сложными, но с помощью Eloquent ORM и его моделей мы можем сделать это с легкостью. В этой...
В PHP
В PHP
В большой кодовой базе с множеством различных компонентов классы, функции и константы могут иметь одинаковые имена. Это может привести к путанице и...
Карта дорог Беладжар PHP Laravel
Карта дорог Беладжар PHP Laravel
Laravel - это PHP-фреймворк, разработанный для облегчения разработки веб-приложений. Laravel предоставляет различные функции, упрощающие разработку...
2
2
63
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Как упоминалось в комментариях к запросу, вам нужен рекурсивный запрос. Рекурсивная часть — это то, что я называю cte в своем запросе. Существует массив с именем items, который я использую, чтобы избежать циклов.

Идея заключается в том, что для каждого элемента я назначаю все остальные элементы, которые прямо или косвенно связаны с одной и той же группой. Затем я агрегирую по элементам и беру наименьший связанный элемент и, таким образом, обнаруживаю все элементы, принадлежащие к одной и той же группе. Я использую DENSE_RANK, чтобы получить последовательные номера групп.

with recursive
  qr1(item, subgroup) as (values (1,1), (2,1), (3,2), (4,3), (5,3), (6,4)),
  qr2(item, subgroup) as (values (1,5), (5,6), (6,6), (7,7), (8,5), (10,5)),
  qr(item, subgroup) as (select * from qr1 union all select * from qr2),
  cte(item, other, items) as
  (
    select item, item, array[item]
    from qr
    union all
    select cte.item, g.item, cte.items || g.item
    from cte
    join qr on qr.item = cte.other
    join qr g on g.subgroup = qr.subgroup
    where g.item <> all (cte.items)
  )
select
  item, 
  min(qr1.subgroup) as sg1,
  min(qr2.subgroup) as sg2,
  dense_rank() over (order by min(other)) as grp
from cte
left join qr1 using (item)
left join qr2 using (item)
group by item
order by item;

Демо: https://dbfiddle.uk/fG6AnX6l

Другие вопросы по теме