PostgreSQL 时序连接：准确取得相关记录-未完纪

原作者在 2008 年第一次看到时序连接时，首先想到的是：“这个查询为什么这么复杂？”当时所在公司为高校提供 CRM 和学生发展跟踪系统，因此大量依赖数据库查询。查询需要返回筛选后的用户列表，以及第二张表中每位用户最后一条关联记录。难点并不是返回最后的时间戳，也不是执行连接，而是只返回第二张表中最后的那条关联记录。

2008 年时，作者使用的环境没有窗口函数或 CTE，因此查询算法由一系列嵌套表组成：

SELECT
    *
FROM users, ( -- find the record for the last second_table by created_at and user_id
                SELECT
                    second_table.*
                FROM second_table, ( -- find the last second_table created_at per user_id
                                        SELECT
                                            user_id,
                                            max(created_at) AS created_at
                                        FROM second_table
                                        GROUP BY 1
                                    ) AS last_second_table_at
                WHERE
                    last_second_table_at.user_id = second_table.user_id
                    AND second_table.created_at = last_second_table_at.created_at
            ) AS last_second_table
WHERE users.id = last_second_table.user_id;

运行这些查询所需的表结构和数据，见后面的“示例代码”。

但这个查询仍然是错的，因为第二张表可能存在 created_at 相同的记录。这正是 2008 年那个缺陷的根源，最终导致结果中出现重复行。

当时使用的显然不是 Postgres，因为 Postgres 一直提供更简单的 DISTINCT ON 写法：

SELECT DISTINCT ON (u.id)
    u.id,
    u.name,
    s.created_at AS last_action_time,
    s.action_type
FROM users u
JOIN second_table s ON u.id = s.user_id
ORDER BY u.id, s.created_at DESC, s.id DESC;

时序连接需要注意细节。

稳健方案：CTE 与窗口函数

继续深入之前，先为正在解决实际问题的读者给出一种现在会采用的写法，适用于并非只查找序列中第一条或最后一条记录的场景。使用 CTE 与窗口函数，可以将查询抽象成职责更清晰的部分，不必层层嵌套。下面是无法直接使用 DISTINCT ON 时的时序连接模板：

WITH max_second_table AS (
    SELECT
        *
    FROM (
            SELECT
                *,
                -- Use ROW_NUMBER() window function to return the latest record:
                -- The ORDER BY clause is critical:
                -- 1. ORDER BY created_at DESC finds the latest time.
                -- 2. ORDER BY id DESC serves as a reliable tie-breaker
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS row_order
            FROM second_table
        ) AS ordered_second_table
    WHERE row_order = 2
)

SELECT
    *
FROM users
LEFT JOIN max_second_table ON users.id = max_second_table.user_id;

此例连接的是用户在 second_table 中的第二次出现，即 WHERE row_order = 2。在高校案例中，作者使用这类查询显示事件的第 1、2、3 次等发生情况，从而报告随时间变化的进展。

这比第一个例子的代码更少吗？并没有，但代码被分成了目的更明确的部分。

另外，在 ORDER BY 中加入主键 id，为相同排序值提供了必要的决胜规则。开头提到的 SQL 问题就是这样修复的。

ORM 带来的问题

由于查询复杂，ORM 通常很难在不进行复杂处理的情况下完成时序连接。作者最熟悉的是 Ruby on Rails 中的 ActiveRecord。Rails 开发者遇到时序连接时，通常会从应用代码中使用这样的 N+1 查询模式：

users = User.all

users.each do |user|
  last_action = user.second_table.last
end

如果查询不频繁，用户记录也不多，性能通常还够用。但随着用户列表增大，这种方式会影响应用性能，因为每次循环都需要与数据库进行一次网络往返，并在应用中初始化对象。虽然也可以让 ActiveRecord 原生完成这项工作，但对典型场景而言，生成的代码通常更难阅读和维护。其他 ORM 也有类似现象。

示例代码

下面的 SQL 可以将示例数据载入数据库，供你尝试这些查询。注意，Alice 有两个动作的时间戳完全相同，用于重现最初的缺陷场景。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE second_table (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    action_type TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- Alice has two actions at the exact same timestamp (The 2008 bug scenario)
INSERT INTO second_table (user_id, action_type, created_at) VALUES
(1, 'login', '2023-10-01 10:00:00'),
(1, 'page_view', '2023-10-01 10:00:00'),
(2, 'purchase', '2023-10-02 11:00:00'),
(3, 'registration', '2023-10-03 12:00:00'),
(3, 'profile_update', '2023-10-04 13:00:00');