如何结合CASE WHEN/COALESCE与子查询获取字段最新更新/创建时间

我有一张日志表log_table，每次数据发生变更时，会向ts列新增一个时间戳，同时将上一次变更的时间戳写入prevts列。需要获取指定字段的最新更新时间；若某字段（如示例中的lastname）自创建后从未发生变更，则返回其创建时间。

日志表数据 #

现有查询问题 #

我可以通过以下查询获取有变更字段的更新时间，但无法处理像lastname这类从未变更的字段（因curr.firstname!=prev.firstname or prev.firstname is null条件不满足），此时希望返回其创建时间。

现有查询代码：

SELECT 
DISTINCT ON (curr.id)
curr.id,
prev.ts   AS curr_firstname_ts,
curr.prevts
FROM log_table curr
JOIN log_table prev 
  ON curr.prevts=prev.ts AND prev.id=curr.id
WHERE curr.firstname is not null
AND curr.firstname!='' 
AND (curr.firstname!=prev.firstname or prev.firstname is null)
ORDER BY curr.kolid, curr.prevts DESC NULLS LAST, prev.ts;

当前查询结果：

期望结果 #

可以通过子查询结合CASE WHEN和COALESCE函数实现需求，具体SQL如下：

WITH create_info AS (
    -- 提取每条记录的创建时间
    SELECT id, ts AS create_ts
    FROM log_table
    WHERE operation = 'create'
),
field_change_records AS (
    -- 找出每个字段的最后变更时间
    SELECT 
        curr.id,
        MAX(CASE WHEN curr.firstname IS DISTINCT FROM prev.firstname THEN curr.ts END) AS firstname_last_update,
        MAX(CASE WHEN curr.middlename IS DISTINCT FROM prev.middlename THEN curr.ts END) AS middlename_last_update,
        MAX(CASE WHEN curr.lastname IS DISTINCT FROM prev.lastname THEN curr.ts END) AS lastname_last_update
    FROM log_table curr
    JOIN log_table prev 
        ON curr.prevts = prev.ts AND curr.id = prev.id
    GROUP BY curr.id
)
SELECT 
    fcr.id,
    COALESCE(fcr.firstname_last_update, ci.create_ts) AS curr_firstname_ts,
    COALESCE(fcr.middlename_last_update, ci.create_ts) AS curr_middlename_ts,
    COALESCE(fcr.lastname_last_update, ci.create_ts) AS curr_lastname_ts
FROM field_change_records fcr
JOIN create_info ci ON fcr.id = ci.id;

逻辑说明 #

1. create_info CTE：先筛选出每个id对应的创建时间，作为未变更字段的默认返回值。
2. field_change_records CTE：通过自连接对比当前记录与上一条记录的字段值，用CASE WHEN标记字段发生变更的记录，再通过MAX函数取每个字段的最后变更时间（从未变更的字段会返回NULL）。
3. 主查询：使用COALESCE函数将字段的最后变更时间和创建时间结合，若字段从未变更（最后变更时间为NULL），则返回创建时间，否则返回最后变更时间。

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