从 PostgreSQL 中同一列中以其他值开头的列中检索所有值的有效方法

为了简单起见，假设您有一个包含如下数字的表：

| number |
----------
|123     |
|1234    |
|12345   |
|123456  |
|111     |
|1111    |
|2       |
|700     |

检索最短数字的有效方法是什么（称它们为roots或其他）和所有值derived来自他们，例如：

| root   | derivatives         |
--------------------------------
| 123    | 1234, 12345, 123456 |
| 111    | 1111                |

Numbers 2 & 700被排除在列表之外，因为它们是unique，因此没有导数。

上面的输出是理想的，但由于它可能很难实现，下一个最好的结果将是如下所示，然后我可以对其进行后期处理：

| root   | derivative |
-----------------------
| 123    | 1234       |
| 123    | 12345      |
| 123    | 123456     |
| 111    | 1111       |

My naive初步尝试至少确定roots（见下文）现在已经运行了 4 小时，数据集约有 50 万个项目，但我必须检查的真实项目包含数百万个项目。

select number
from numbers n1
where exists(
              select number
              from numbers n2
              where n2.number <> n1.number
                and n2.number like n1.number || '_%'
          );

这有效，如果number is an integer or bigint:

select min(a.number) as root, b.number as derivative
  from nums a
       cross join lateral generate_series(1, 18) as gs(power)
       join nums b 
         on b.number / (10^gs.power)::bigint = a.number
 group by b.number
 order by root, derivative;

编辑：我将一个不起作用的查询移动到底部。由于 @Morfic 在评论中概述的原因，它失败了。

我们可以使用以下方法进行类似且更简单的连接like对于字符类型：

select min(a.number) as root, b.number as derivative
  from numchar a
       join numchar b on b.number like a.number||'%'
        and b.number != a.number
 group by b.number
 order by root, derivative;

Updated

错误的解决方案如下

如果数字是字符类型，请尝试以下操作：

with groupings as (
  select number, 
         case
           when number like (lag(number) over (order by number))||'%' then 0
           else 1
         end as newgroup
    from numchar
), groupnums as (
  select number, sum(newgroup) over (order by number) as groupnum
    from groupings
), matches as (
  select min(number) over (partition by groupnum) as root,
         number as derivative
    from groupnums
)
select *
  from matches
 where root != derivative;

应该只有一个排序groupnum在此执行中，因为该列是表的主键。

数据库小提琴