PostgreSQL多列排序时如何判定生效排序列及生成时间最大值列

问题 #

我用以下SQL从schedule表创建物化视图schedule_state：

CREATE MATERIALIZED VIEW schedule_state AS (
    WITH schedule_latest_events AS (
        SELECT
            *,
            row_number() over (
                PARTITION BY key_id
                ORDER BY
                    header_event_timestamp DESC,
                    ktimestamp DESC,
                    raw_load_timestamp DESC,
                    update_timestamp DESC
            ) AS row_number
        FROM
            schedule
    )
    SELECT
        *
    FROM
        schedule_latest_events
    WHERE
        row_number = 1
);

当前用多列排序，我有两个需求：

1. 如何确定row_number为1的记录是由哪一列作为最终排序依据的？
2. 能否将上述四个时间列的最大值，赋值给物化视图中的一个新列？

示例数据 #

原表schedule

物化视图schedule_state（当前结果）

在示例数据中：

• key_id k1：首个排序参数header_event_timestamp有差异，最新时间的记录被标记为row_number=1；
• key_id k2：首个参数值相同，排序依据第二个参数ktimestamp，最新时间的记录被标记为row_number=1；
• key_id k3：前两个参数值均相同，排序依据第三个参数raw_load_timestamp，最新时间的记录被标记为row_number=1。

解决方案 #

需求1：确定row_number=1记录的最终排序依据列 #

可以通过对比当前记录与同key_id分组内其他记录的排序列值，从第一个排序列开始依次排查，找到第一个存在差异的列作为最终排序依据。具体实现时，在CTE中新增字段标记该列：

CREATE MATERIALIZED VIEW schedule_state AS (
    WITH schedule_latest_events AS (
        SELECT
            *,
            row_number() over (
                PARTITION BY key_id
                ORDER BY
                    header_event_timestamp DESC,
                    ktimestamp DESC,
                    raw_load_timestamp DESC,
                    update_timestamp DESC
            ) AS row_number,
            -- 标记最终排序依据列
            CASE
                -- 检查当前记录的header_event_timestamp是否为分组最大值
                WHEN header_event_timestamp = MAX(header_event_timestamp) OVER (PARTITION BY key_id) THEN
                    -- 分组内该列存在不同值，则最终依据为该列
                    CASE WHEN MIN(header_event_timestamp) OVER (PARTITION BY key_id) < MAX(header_event_timestamp) OVER (PARTITION BY key_id)
                         THEN 'header_event_timestamp'
                         ELSE
                             -- 检查ktimestamp列
                             CASE WHEN ktimestamp = MAX(ktimestamp) OVER (PARTITION BY key_id) THEN
                                  CASE WHEN MIN(ktimestamp) OVER (PARTITION BY key_id) < MAX(ktimestamp) OVER (PARTITION BY key_id)
                                       THEN 'ktimestamp'
                                       ELSE
                                           -- 检查raw_load_timestamp列
                                           CASE WHEN raw_load_timestamp = MAX(raw_load_timestamp) OVER (PARTITION BY key_id) THEN
                                                CASE WHEN MIN(raw_load_timestamp) OVER (PARTITION BY key_id) < MAX(raw_load_timestamp) OVER (PARTITION BY key_id)
                                                     THEN 'raw_load_timestamp'
                                                     ELSE 'update_timestamp'
                                                END
                                           ELSE 'update_timestamp'
                                           END
                                  END
                             END
                    END
                ELSE 'header_event_timestamp'
            END AS final_sort_column
        FROM
            schedule
    )
    SELECT
        key_id,
        header_event_timestamp,
        ktimestamp,
        raw_load_timestamp,
        update_timestamp,
        row_number,
        final_sort_column
    FROM
        schedule_latest_events
    WHERE
        row_number = 1
);

逻辑说明：

1. 从第一个排序列开始，判断当前记录的列值是否为分组内最大值；
2. 如果该列在分组内存在不同值（最大值≠最小值），则此列就是最终排序依据；
3. 若该列分组内值完全相同，则继续检查下一个排序列，直到找到第一个存在差异的列；
4. 若所有列分组内值都相同，则最终依据为最后一个排序列update_timestamp。

针对示例数据，生成的final_sort_column结果如下：

需求2：新增四列时间的最大值列 #

可以使用GREATEST()函数直接取四个时间列的最大值，新增到物化视图中。结合需求1的实现，最终SQL如下：

CREATE MATERIALIZED VIEW schedule_state AS (
    WITH schedule_latest_events AS (
        SELECT
            *,
            row_number() over (
                PARTITION BY key_id
                ORDER BY
                    header_event_timestamp DESC,
                    ktimestamp DESC,
                    raw_load_timestamp DESC,
                    update_timestamp DESC
            ) AS row_number,
            -- 标记最终排序依据列
            CASE
                WHEN header_event_timestamp = MAX(header_event_timestamp) OVER (PARTITION BY key_id) THEN
                    CASE WHEN MIN(header_event_timestamp) OVER (PARTITION BY key_id) < MAX(header_event_timestamp) OVER (PARTITION BY key_id)
                         THEN 'header_event_timestamp'
                         ELSE
                             CASE WHEN ktimestamp = MAX(ktimestamp) OVER (PARTITION BY key_id) THEN
                                  CASE WHEN MIN(ktimestamp) OVER (PARTITION BY key_id) < MAX(ktimestamp) OVER (PARTITION BY key_id)
                                       THEN 'ktimestamp'
                                       ELSE
                                           CASE WHEN raw_load_timestamp = MAX(raw_load_timestamp) OVER (PARTITION BY key_id) THEN
                                                CASE WHEN MIN(raw_load_timestamp) OVER (PARTITION BY key_id) < MAX(raw_load_timestamp) OVER (PARTITION BY key_id)
                                                     THEN 'raw_load_timestamp'
                                                     ELSE 'update_timestamp'
                                                END
                                           ELSE 'update_timestamp'
                                           END
                                  END
                             END
                    END
                ELSE 'header_event_timestamp'
            END AS final_sort_column,
            -- 新增四列时间的最大值列
            GREATEST(header_event_timestamp, ktimestamp, raw_load_timestamp, update_timestamp) AS max_time
        FROM
            schedule
    )
    SELECT
        key_id,
        header_event_timestamp,
        ktimestamp,
        raw_load_timestamp,
        update_timestamp,
        row_number,
        final_sort_column,
        max_time
    FROM
        schedule_latest_events
    WHERE
        row_number = 1
);

说明：

• GREATEST()函数支持直接对比时间类型的值，返回其中最大的时间；
• 以示例数据中的k1为例，max_time值为2023-12-22 08:52:36.960000，即四列中的最大值。

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