如何在PostgreSQL中实现自定义窗口函数？

PostgreSQL执行窗口函数时，会把每个分区保存成独立的窗口对象，然后逐行调用注册好的窗口函数。像 row_number、rank、lag 这些内置函数虽然灵活，但它们的计算方式已经固定。如果需求是“从当前行往前找到第一个非空值”或者“按照自定义状态机推进结果”，只用SQL就比较吃力。数据库本身并没有提供 CREATE WINDOW FUNCTION 这样的SQL级命令，真正意义上有能力访问分区任意行的自定义窗口函数，需要借助C语言扩展和 WindowObject 接口来实现。下面梳理完整的实现过程。

窗口函数与普通聚合函数的边界在哪里

把 sum、count 这类聚合函数直接加上 OVER 子句，确实能解决累计销售额、分组计数等需求。但这种做法有一个前提：计算逻辑仍然遵循聚合函数自身的固定规则，执行器只是把窗口帧中的行喂给聚合状态。真正的窗口函数在C接口里拿到的不是单行输入，而是一个 WindowObject，它包含当前分区的行集合、当前行位置以及可以定位到任意一行的取值函数。

这个差异决定了自定义能力。比如 lag(value, 3) 只能按物理偏移往前取固定位置，不能在取数时跳过空值或只取满足某条件的行。再比如需要根据事件类型维护一个分区内的状态机，普通窗口聚合也可能通过复杂SQL勉强实现，但性能和可读性都会下降。此时就需要编写自己的窗口函数，让它直接利用分区内所有行信息。

先看一个内置窗口函数的简单用法：

SELECT depname,
       salary,
       sum(salary) OVER (PARTITION BY depname ORDER BY salary) AS running_total
FROM empsalary;

这里 sum 被窗口化，逐行返回部门内排序后的累计薪资。它本质上还是聚合逻辑，只是帧是“分区头部到当前行”。如果累计方式不是简单相加，而是遇到NULL要跳过、遇到特殊值要重置，SQL就会迅速膨胀。这正是自定义窗口函数的用武之地。

用C语言和WindowObject接口写一个真正可用的窗口函数

PostgreSQL自定义窗口函数需要用一个C源文件实现，并调用 windowapi.h 提供的函数。窗口函数在C层面的调用过程分两层：执行器先为每个分区建立 WindowObject，再对分区内的每一行调用同一个Datum函数。函数内部通常先用 PG_WINDOW_OBJECT 拿到分区对象，再通过 WinGetCurrentPosition 获取当前行位置。如果需要访问任意行的参数值，可以使用 WinGetFuncArgInPartition。

下面实现一个 last_non_null_int 函数，作用是返回当前行往前遇到的第一个非空整数值。它没有复杂状态，适合用来演示窗口对象API。

#include "postgres.h"
#include "fmgr.h"
#include "windowapi.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(last_non_null_int);

Datum
last_non_null_int(PG_FUNCTION_ARGS)
{
    WindowObject winobj = PG_WINDOW_OBJECT();
    int64 curpos = WinGetCurrentPosition(winobj);
    int64 pos;
    bool isnull;
    bool isout;
    Datum result = (Datum) 0;

    for (pos = curpos; pos >= 0; pos--)
    {
        result = WinGetFuncArgInPartition(winobj,
                                          0,
                                          pos,
                                          WINDOW_SEEK_HEAD,
                                          -1,
                                          &isnull,
                                          &isout);
        if (!isnull)
            PG_RETURN_DATUM(result);
    }

    PG_RETURN_NULL();
}

这里的 WinGetFuncArgInPartition 第一个参数是窗口对象，第二个参数表示取SQL函数参数列表里的第0个参数，也就是传入的 integer 值。第四个参数 WINDOW_SEEK_HEAD 表示从分区头部开始计算偏移量，pos 就是当前行往前的位置。第五个参数 markpos 传 -1 表示不设置标记。返回的 isnull 用来说明该位置的值是否为SQL NULL，isout 用来判断是否超出分区范围。由于我们逐行向前扫描，不需要额外保存状态，所以这个函数没有使用 WinGetPartitionLocalMemory。

接着在数据库中创建该函数，注意一定要加上 WINDOW 关键字：

CREATE FUNCTION last_non_null_int(integer)
RETURNS integer
AS '$libdir/last_non_null_int', 'last_non_null_int'
LANGUAGE C WINDOW;

如果这里只写 LANGUAGE C 而没有 WINDOW，执行器会把它当成普通C函数处理，放在 OVER 子句里会直接报错。LANGUAGE C WINDOW 告诉 PostgreSQL，这个函数要通过窗口函数管理接口调用，调用时不再传普通单行参数，而是传入窗口上下文。

编译安装并用SQL验证结果

将C文件保存为 last_non_null_int.c，然后编写一个简单的 PGXS Makefile，利用 pg_config 找到头文件和库路径。PGXS 是 PostgreSQL 官方推荐的扩展编译工具，能自动处理版本差异和安装目录。

MODULES = last_non_null_int
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

执行 make 后会生成 last_non_null_int.so，再通过 sudo make install 安装到 PostgreSQL 的 lib 目录。安装完成后，就可以在目标数据库中执行前面的 CREATE FUNCTION 语句。以传感器读数为例，建表并写入一些包含NULL值的数据：

CREATE TABLE sensor_readings (
    device_id int,
    read_at timestamptz,
    value int
);

INSERT INTO sensor_readings VALUES
(1, '2024-01-01 00:00', 10),
(1, '2024-01-01 00:01', NULL),
(1, '2024-01-01 00:02', 20),
(2, '2024-01-01 00:00', NULL),
(2, '2024-01-01 00:01', 15);

查询时按 device_id 分区、按 read_at 排序，调用自定义函数：

SELECT device_id,
       read_at,
       value,
       last_non_null_int(value) OVER (
           PARTITION BY device_id
           ORDER BY read_at
       ) AS last_value
FROM sensor_readings
ORDER BY device_id, read_at;

结果第一行是 10，因为当前行本身非空；第二行仍是 10，因为它向前找到第一行；第三行是 20，当前行自己就是非空值。设备2的第一行返回 NULL，因为分区内第一行是空值且前面没有行；第二行返回 15。这个行为如果用 lag 加条件判断会很别扭，而自定义窗口函数直接表达了业务规则。

使用C语言扩展时要注意，错误的内存访问可能导致数据库进程崩溃，因此需要在测试环境充分验证。窗口函数在每一行都被调用，如果函数内部反复扫描之前的行，时间复杂度会上升。对于需要维护累计状态的场景，最好用 WinGetPartitionLocalMemory 分配分区级内存，把中间结果保存起来，避免每次 O(n) 扫描。

不想写C时的替代方案：用自定义聚合充当窗口函数

如果需求只是“自定义累加逻辑”，不要求访问任意行，那么用 CREATE AGGREGATE 创建一个自定义聚合函数也可以放进 OVER 子句。PostgreSQL的窗口执行器允许聚合函数作为窗口函数使用，开发成本远低于C扩展。

比如统计分区内到当前行为止出现了多少个非空读数：

CREATE FUNCTION int_notnull_accum(bigint, integer)
RETURNS bigint
LANGUAGE SQL
IMMUTABLE
AS 'SELECT $1 + CASE WHEN $2 IS NULL THEN 0 ELSE 1 END';

CREATE AGGREGATE count_notnull(integer) (
    SFUNC = int_notnull_accum,
    STYPE = bigint,
    INITCOND = '0'
);

SELECT device_id,
       read_at,
       count_notnull(value) OVER (
           PARTITION BY device_id
           ORDER BY read_at
       ) AS non_null_count
FROM sensor_readings;

这种方案的特点是简单、容易维护，但本质上还是聚合函数，无法获取窗口帧之外的行，也不能直接知道当前行在分区内的位置。适合移动计数、加权累计等场景；如果业务逻辑需要像 lag、lead 那样跨行取数，或者需要访问分区内任意第N行，就必须回到C语言窗口函数方案。

总结来说，PostgreSQL没有提供SQL级创建窗口函数的命令，但通过 WindowObject 和 C 扩展可以实现灵活的自定义逻辑。规划需求时先判断是否能用自定义聚合代替，确实需要访问任意行或维护复杂分区状态时，再投入C开发，这样能最小化扩展维护成本。

PostgreSQL自定义窗口函数窗口函数WindowObject修改时间：2026-09-26 19:15:17