在我的 PostgreSQL 資料庫中，我有兩個獨立的查詢（q1、q2）連接多個表，根據不同的標準將相同的專案分配給不同的組（我稱之為子組）。我得到查詢結果 1 和 2 (qr1, qr2)。
一個專案可能出現在一個或兩個結果中，但在結果中它是唯一的。我想根據兩個子組分配一個新的組 ID，并在子組共享一個或多個專案時分配相同的組 ID。
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，子組 1 和 5 將合并。（組專案 1、2、8、10）
- 第 3、4 組！和 6 將因共享專案 5 而合并。（組專案 4、5、6）
- 子組 2 和 7 構成自己的組，不與任何其他組共享專案。
我嘗試使用視窗函式，按重復專案計數，然后從那里開始。但是我卡住了。
uj5u.com熱心網友回復：
正如請求評論中提到的，您需要一個遞回查詢。遞回部分就是我所說的 cte im 我的查詢。有一個名為 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/537275.html