Re: how to group by similarity ? - Mailing list pgsql-general

Andreas <maps.on@gmx.net> writes:

> How would I group the table so that it shows groups that have
> similarity () > x ?
>
> Lets say the table looks like this:
>
> id,  txt
> 1,   aa1
> 2,   bb1
> 3,   cc1
> 4,   bb2
> 5,   bb3
> 6,   aa2
> ...
>
> How would a select look like that shows:
>
> id,  txt,      group_id
> 1,   aa1,   1,
> 6,   aa2,   1,
> 2,   bb1,   2,
> 4,   bb2,   2,
> 5,   bb3,   2,
> 3,   cc1,   3

The following query will do that, but it's convoluted:

WITH grp (t1, id, t2) AS (
  SELECT t1.txt, t1.id, t2.txt
  FROM tbt t1
  LEFT JOIN tbt t2 ON t2.txt > t1.txt
  WHERE t2.txt IS NULL OR similarity(t1.txt, t2.txt) > 0
)
SELECT t1, min(id)
FROM (
    SELECT t1, id
    FROM grp
  UNION ALL
    SELECT t2, id
    FROM grp
    WHERE t2 IS NOT NULL
  ) dummy
GROUP BY t1
ORDER BY t1