Sql joins 组合多个重复数据删除策略

我有一个数据集，我正在try 同时以两种方式清理和消除重复数据.

例如，如果我们忽略标点符号(包括空格)、词序和缩写，这些公司名称都是相同的:

 company_id |    company_name    |        words        | words_wo_spaces |    words_sorted     | sorted_words_wo_spaces 
------------+--------------------+---------------------+-----------------+---------------------+------------------------
         17 | the usa co         | {THE,USA,COMPANY}   | THEUSACOMPANY   | {COMPANY,THE,USA}   | COMPANYTHEUSA     
         18 | the u s a company  | {THE,U,S,A,COMPANY} | THEUSACOMPANY   | {A,COMPANY,S,THE,U} | ACOMPANYSTHEU     
         19 | usa company, the   | {USA,COMPANY,THE}   | USACOMPANYTHE   | {COMPANY,THE,USA}   | COMPANYTHEUSA     
         20 | u s a company, the | {U,S,A,COMPANY,THE} | USACOMPANYTHE   | {A,COMPANY,S,THE,U} | ACOMPANYSTHEU     

我有一些助手函数来创建除company_id和company_name之外的所有列-拆分和排序单词以及重新编码单词以删除常见缩写的函数(例如，将‘co’替换为‘Company’).我要把那些帮助器功能排除在这个问题之外.

如果我们查看列words_wo_spaces，则有两对匹配的公司:17/18和19/20. 如果我们按sorted_words_wo_spaces分组，则有两对不同的匹配公司:17/19和18/20.

我真正想要的是一个查询(或两个或三个)，它可以使用这两种方法对公司进行分组，以确定它们都是相同的.

查询1

SELECT 
  array_agg(company_id) ids
  , array_agg(company_name) names
FROM companies
GROUP BY words_wo_spaces

-- Returns

--    ids   | words_wo_spaces 
-- ---------+-----------------
--  {19,20} | USACOMPANYTHE
--  {17,18} | THEUSACOMPANY

问题2

SELECT 
  array_agg(company_id) ids
  , array_agg(company_name) names
FROM companies
GROUP BY sorted_words_wo_spaces

-- Returns

--    ids   | sorted_words_wo_spaces 
-- ---------+------------------------
--  {18,20} | ACOMPANYSTHEU
--  {17,19} | COMPANYTHEUSA

但我想不出如何将两者结合起来.如果17=18，18=20，17=19，那么它们都是一样的.可以将此逻辑放入SQL查询中吗？

我确实try 了连接这两个查询的结果，但这没有任何帮助(正如预期的那样，一旦我找到了答案).

WITH
  unsorted_groups AS <query 1>
  , sorted_groups AS <query 2>
SELECT 
  array_cat(sorted_groups.ids, unsorted_groups.ids) ids
FROM sorted_groups 
JOIN unsorted_groups 
  ON unsorted_groups.ids && sorted_groups.ids;

-- Returns

--      ids   
-- ---------------
--  {18,20,19,20}
--  {18,20,17,18}
--  {17,19,19,20}
--  {17,19,17,18}

would有用的是一个查询，它将返回这样一个表，这样ID数组就可以被排序，然后用来将所有行组合成一个(这个理想的结果是每个ID都在每行的ids个数组中).

--      ids   
-- ---------------
--  {18,17,19,20}
--  {18,20,17,19}
--  {17,19,18,20}
--  {17,19,20,18}