如何获取表子集按date_time排序的前后行（PostgreSQL）

问题说明 #

现有animals__locations表，结构如下：

create table animals__locations (
    date_time   timestamptz default CURRENT_TIMESTAMP not null,
    animal_id   integer     not null,
    location_id integer     not null,
    id          serial      primary key
);

当前有该表的一个子集：

-- Subset
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 11:11:11.000000 +00:00|43       |55         |11|
|2023-04-17 12:12:12.000000 +00:00|44       |57         |12|
+---------------------------------+---------+-----------+--+

需要为该子集的每一行，获取：

• 前序行：相同animal_id下，按date_time排序的上一行（即date_time小于当前行的最新记录）
• 后序行：相同animal_id下，按date_time排序的下一行（即date_time大于当前行的最早记录）
  若无对应行则省略该行。

解决方案 #

1. 获取前序行 #

通过LATERAL JOIN为子集每行单独查询对应前序记录：

SELECT prev.*
FROM (
    -- 定义目标子集（可根据实际需求修改筛选条件）
    SELECT date_time, animal_id
    FROM animals__locations
    WHERE id IN (11, 12)
) AS subset
JOIN LATERAL (
    SELECT *
    FROM animals__locations
    WHERE animal_id = subset.animal_id
      AND date_time < subset.date_time
    ORDER BY date_time DESC
    LIMIT 1
) AS prev ON true;

返回结果：

-- Previous
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 01:01:01.000000 +00:00|43       |45         |1 |
|2023-04-17 02:02:02.000000 +00:00|44       |47         |2 |
+---------------------------------+---------+-----------+--+

2. 获取后序行 #

类似逻辑，筛选date_time大于当前行的最早记录：

SELECT next.*
FROM (
    SELECT date_time, animal_id
    FROM animals__locations
    WHERE id IN (11, 12)
) AS subset
JOIN LATERAL (
    SELECT *
    FROM animals__locations
    WHERE animal_id = subset.animal_id
      AND date_time > subset.date_time
    ORDER BY date_time ASC
    LIMIT 1
) AS next ON true;

返回结果：

-- Next
+---------------------------------+---------+-----------+--+
|date_time                        |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 21:21:21.000000 +00:00|43       |65         |21|
|2023-04-17 22:22:22.000000 +00:00|44       |67         |22|
+---------------------------------+---------+-----------+--+

关键说明 #

• LATERAL JOIN会为子集的每一行独立执行关联查询，完美适配多行场景；
• 后续添加(animal_id, date_time)唯一约束后，查询逻辑无需修改，且性能会进一步提升；
• 子集的筛选条件可灵活调整（比如用date_time范围而非id）；
• 若无对应前序/后序行，LATERAL JOIN会自动排除该行，符合需求。

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