SQL查询：忽略NULL值分组获取各列最新非空值

存储订单更新数据时，每次订单状态发生变更就插入一条新记录，用于追踪全链路状态更新轨迹，event_order表的示例数据如下：

+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+
| event_id |   state   | amount |        address        |         notes         |            timestamp             |
+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+
| order123 | fulfilled | NULL   | NULL                  | NULL                  | 2022-07-01T17:08:12.032316+00:00 |
| order123 | NULL      | NULL   | NULL                  | Delivered to customer | 2022-07-01T17:07:12.032316+00:00 |
| order123 | NULL      | NULL   | 300 Post St, CA 94108 | NULL                  | 2022-07-01T17:06:12.032316+00:00 |
| order123 | accepted  | NULL   | NULL                  | NULL                  | 2022-07-01T17:05:12.032316+00:00 |
| order123 | pending   | 100    | NULL                  | NULL                  | 2022-07-01T17:04:12.032316+00:00 |
+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+

需求为编写查询语句，提取每列的最新值且自动忽略NULL值，期望得到的查询结果如下：

+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+
| event_id |   state   | amount |        address        |         notes         |            timestamp             |
+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+
| order123 | fulfilled | 100    | 300 Post St, CA 94108 | Delivered to customer | 2022-07-01T17:08:12.032316+00:00 |
+----------+-----------+--------+-----------------------+-----------------------+----------------------------------+

现有SQL仅能获取每个event_id对应的最新一条记录，无法填充该条记录中为NULL的字段的历史最新非空值，现有代码如下：

SELECT DISTINCT ON (event_id)
    event_id, state, amount, address, notes, timestamp
    FROM event_order
ORDER BY event_id, timestamp DESC;

已查阅到的LAST_VALUE相关方案仅支持整数类型，无法适配字符串等其他数据类型，需要通用可行的解决方案。

核心逻辑为：按event_id分组，对每个业务字段取分组内按时间排序的最后一个非NULL值，即可得到该字段的最新有效值。以下两种方案均支持所有数据类型（字符串、数值、时间类型等），无类型限制。

方案一：窗口函数法（推荐，PostgreSQL 11+ 支持） #

使用LAST_VALUE窗口函数搭配IGNORE NULLS标准参数，自动跳过空值取窗口内最后一个有效值，窗口范围覆盖当前分组所有行，SQL代码如下：

SELECT DISTINCT ON (event_id)
    event_id,
    LAST_VALUE(state) IGNORE NULLS OVER (
        PARTITION BY event_id 
        ORDER BY timestamp ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS state,
    LAST_VALUE(amount) IGNORE NULLS OVER (
        PARTITION BY event_id 
        ORDER BY timestamp ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS amount,
    LAST_VALUE(address) IGNORE NULLS OVER (
        PARTITION BY event_id 
        ORDER BY timestamp ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS address,
    LAST_VALUE(notes) IGNORE NULLS OVER (
        PARTITION BY event_id 
        ORDER BY timestamp ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS notes,
    MAX(timestamp) OVER (PARTITION BY event_id) AS timestamp
FROM event_order
ORDER BY event_id, timestamp DESC;

说明：之前找到的仅支持整数的LAST_VALUE方案，本质是未使用标准的IGNORE NULLS参数，通过数值类的差值/累加逻辑跳过空值，所以只适配数值类型，使用标准IGNORE NULLS语法不存在类型限制。

方案二：数组聚合法（兼容所有PostgreSQL版本） #

如果使用的PostgreSQL版本低于11、不支持IGNORE NULLS参数，可以通过ARRAY_AGG聚合函数搭配FILTER子句，过滤掉空值后按时间排序，取数组最后一个元素即为最新非空值，SQL代码如下：

SELECT
    event_id,
    (ARRAY_AGG(state ORDER BY timestamp ASC) FILTER (WHERE state IS NOT NULL))[array_upper(ARRAY_AGG(state ORDER BY timestamp ASC) FILTER (WHERE state IS NOT NULL), 1)] AS state,
    (ARRAY_AGG(amount ORDER BY timestamp ASC) FILTER (WHERE amount IS NOT NULL))[array_upper(ARRAY_AGG(amount ORDER BY timestamp ASC) FILTER (WHERE amount IS NOT NULL), 1)] AS amount,
    (ARRAY_AGG(address ORDER BY timestamp ASC) FILTER (WHERE address IS NOT NULL))[array_upper(ARRAY_AGG(address ORDER BY timestamp ASC) FILTER (WHERE address IS NOT NULL), 1)] AS address,
    (ARRAY_AGG(notes ORDER BY timestamp ASC) FILTER (WHERE notes IS NOT NULL))[array_upper(ARRAY_AGG(notes ORDER BY timestamp ASC) FILTER (WHERE notes IS NOT NULL), 1)] AS notes,
    MAX(timestamp) AS timestamp
FROM event_order
GROUP BY event_id;

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