我有一张包含大量数据的表，可以把其中的数据简化成一组非唯一的可选名称和索引：

SELECT *
FROM unnest(ARRAY[
    NULL, NULL,
    'foo', 'foo', 'foo',
    NULL, NULL, NULL,
    'bar', 'bar',
    NULL,
    'foo', 'foo'
]) WITH ORDINALITY _(name, index);

我想把名称分组，生成一个带有区间的索引，同时保留中间的空缺。最终的目标大概是这样的：

到目前为止，我已经能够使用 LEAD 和 LAG 函数来检测区间的起点和终点，但还没能让它们真正实现分组。我的想法是将 index 列与 ARRAY_AGG 聚合，然后用首尾元素来创建一个区间。我的当前查询是：

WITH dat AS (
  SELECT *
  FROM unnest(ARRAY[
        NULL, NULL,
        'foo', 'foo', 'foo',
        NULL, NULL, NULL,
        'bar', 'bar',
        NULL,
        'foo',
        'foo'
    ]) WITH ORDINALITY _(name, index)
)
SELECT *,
    CASE
         WHEN (
            LEAD(index, 1) OVER (ORDER BY name, index) = index + 1
            AND LAG(index, 1) OVER (ORDER BY name, index) = index - 1
         ) THEN 'middle'
         WHEN LEAD(index, 1) OVER (ORDER BY name, index) = index + 1 THEN 'start'
         WHEN LAG(index, 1) OVER (ORDER BY name, index) = index - 1 THEN 'end'
         ELSE 'solo'
    END pos
FROM dat
ORDER BY INDEX;

我另一种想法是做一个简单的聚合（SELECT name, array_agg(index) FROM data GROUP BY name;），再用一个函数生成结果，但如果可能的话，我更愿意用一个普通查询来完成。

解决方案

内置 range_agg() 可以自行处理缺口与岛屿的问题，内部：

select name,unnest(range_agg(int8range(index,index+1)))
from dat
group by 1
order by 2

每一行都会变成一个单元素的区间，这些区间会合并成每个 name 的一个大多重区间，然后 unnest() 将连续的部分输出。将其保留为一个真正的区间类型，就能解锁原生的区间函数和运算符，例如包含性检查 <@。

如果你确实想要自定义的文本表示形式，可以在 concat_ws() 中提取区间边界：

select name, concat_ws('-',lower(r),nullif(upper(r)-1,lower(r))) as range
from(select name, range_agg(int8range(index,index+1))as mr
     from dat
     group by 1)_
cross join unnest(mr) as r
order by r;

在没有索引的情况下对3 万行的执行时间： demo1 at db<>fiddle

它不会使用索引，因此在这些条件存在时，基于索引的方法的扩展性更好： demo2 at db<>fiddle