如何用PostgreSQL中最后一个非NULL值填充列中的NULL值

我有一个PostgreSQL表，结构及数据如下：

CREATE TABLE cte1 (
    entity_id INT,
    assignedtogroup INT,
    time BIGINT
);

INSERT INTO cte1 (entity_id, assignedtogroup, time)
VALUES
    (1, 435198, 1687863949740),
    (1, 435198, 1687863949741),
    (1, NULL, 1687863949742),
    (1, NULL, 1687863949743),
    (1, 435224, 1687863949744),
    (1, 435224, 1687863949745),
    (1, 435143, 1687863949746),
    (1, 435143, 1687863949747),
    (1, 435191, 1687863949748),
    (1, NULL, 1687863949749),
    (2, 435143, 1690452125291),
    (2, 435143, 1690452125292),
    (2, 435191, 1690452125293),
    (2, NULL, 1690452125294);

我需要用同一entity_id下时间上紧邻当前行的前一行非NULL值，填充assignedtogroup列中的空值，预期结果如下：

请问仅通过SELECT语句能否实现该需求？

我尝试了用LAG函数，但结果仍存在NULL值，且entity_id为2的数据出现混乱：

SELECT
    entity_id,
    COALESCE(
        assignedtogroup,
        LAG(assignedtogroup) OVER (PARTITION BY entity_id ORDER BY time)
    ) AS filled_assignedtogroup
FROM cte1;

可以仅通过SELECT语句实现，以下是两种可行方案：

方法1：使用LAST_VALUE + IGNORE NULLS（PostgreSQL 11+支持） #

SELECT
    entity_id,
    LAST_VALUE(assignedtogroup) OVER (
        PARTITION BY entity_id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS assignedtogroup,
    time
FROM cte1
ORDER BY entity_id, time;

方法2：使用MAX()窗口函数（兼容旧版本PostgreSQL） #

SELECT
    entity_id,
    MAX(assignedtogroup) OVER (
        PARTITION BY entity_id, grp
    ) AS assignedtogroup,
    time
FROM (
    SELECT
        *,
        COUNT(assignedtogroup) OVER (
            PARTITION BY entity_id 
            ORDER BY time
        ) AS grp
    FROM cte1
) t
ORDER BY entity_id, time;

方案说明 #

• 方法1：LAST_VALUE(assignedtogroup) ... IGNORE NULLS会在窗口范围内（从当前分组开头到当前行）忽略NULL值，自动取最后一个非NULL值，完美匹配用最近前序非NULL值填充的需求。
• 方法2：先通过COUNT(assignedtogroup)生成分组标识grp，每遇到一个非NULL值，grp就递增，连续的NULL值会和前面的非NULL值归为同一分组，再用MAX()提取分组内的非NULL值完成填充。

内容的提问来源于stack exchange，提问作者executable