连续 ID block 上的 PostgresQL 窗口函数

Question

我有一个包含部分连续整数 ID 的表，即有像 1,2,3, 6,7,8, 10, 23,24,25,26 这样的 block .

• 间隙大小是动态的
• block 的长度是动态的

我正在为从表中选择的简单解决方案而烦恼并包含一列，其中的值对应于相应 block 的第一个 id。

即像这样

select id, first(id) over <what goes here?> first from table;

结果应该如下所示

| id | first |
|----|-------|
| 1  | 1     |
| 2  | 1     |
| 3  | 1     |
| 6  | 6     |
| 7  | 6     |
| 8  | 6     |
| 10 | 10    |
| 23 | 23    |
| 24 | 23    |
| 25 | 23    |
| 26 | 23    |

之后，我可以将此专栏与 partition by 很好地结合使用窗口函数子句。

到目前为止我想出的总是看起来与此相似但没有成功:

WITH foo AS (
    SELECT LAG(id) OVER (ORDER BY id)  AS previous_id,
           id                          AS id,
           id - LAG(id, 1, id) OVER (ORDER BY id) AS first_in_sequence
    FROM table)
SELECT *,
       FIRST_VALUE(id) OVER (ORDER BY id) AS first
FROM foo
ORDER BY id;

定义一个自定义的 postgres 函数也是一个可以接受的解决方案。

谢谢你的建议，

马蒂

Answer 1

在 Postgres 中你可以 create a custom aggregate.示例:

create or replace function first_in_series_func(int[], int)
returns int[] language sql immutable
as $$ 
    select case 
        when $1[2] is distinct from $2- 1 then array[$2, $2]
        else array[$1[1], $2] end; 
$$;

create or replace function first_in_series_final(int[])
returns int language sql immutable
as $$
    select $1[1]
$$;

create aggregate first_in_series(int) (
    sfunc = first_in_series_func,
    finalfunc = first_in_series_final,
    stype = int[]
);

Db<>fiddle.

阅读文档:User-Defined Aggregates