By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 'Alice' as name, 'New-York' as city, 25 as age, 13782749 as id, 12 as foo union all
select 'Eve', 'Chicago', 23, 1938679, 34 union all
select 'Bob', 'New-York', 25, 824697624, 56 union all
select 'Jack', 'Denver', 30, 239679163, 12 union all
select 'Simone', 'Denver', 30, 1687631, 99
✓
with recursive edges as (
select distinct t1.name as name1, t2.name as name2
from t t1 join
t t2
on t1.city = t2.city and t1.age = t2.age or
t1.foo = t2.foo
),
cte as (
select name1, name2, min(name1, name2) as first_name,
',' || name1 || ',' || name2 || ',' as visited
from edges
union all
select cte.name1, e.name2, min(cte.first_name, e.name2),
visited || e.name2 || ','
from cte join
edges e
on e.name1 = cte.name2
where visited not like '%,' || e.name2 || ',%'
)
select t.*, cte.grpnum
from t join
(select name1, min(first_name), dense_rank() over (order by min(first_name)) as grpnum
from cte
group by name1
) cte
on t.name = cte.name1
| name | city | age | id | foo | grpnum |
|---|---|---|---|---|---|
| Alice | New-York | 25 | 13782749 | 12 | 1 |
| Eve | Chicago | 23 | 1938679 | 34 | 2 |
| Bob | New-York | 25 | 824697624 | 56 | 1 |
| Jack | Denver | 30 | 239679163 | 12 | 1 |
| Simone | Denver | 30 | 1687631 | 99 | 1 |