postgresql 获取表的子集的按列排序的下一行和上一行

wmtdaxz3 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(1)|浏览(217)

我有一个表的子集：

-- 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列排序）？

-- 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 |
+---------------------------------+---------+-----------+--+

最后一行的查询应该返回子集中每行之前的行，按date_time排序，对应子集中的行animal_id。

-- 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|
+---------------------------------+---------+-----------+--+

与前一行相同，但在后一行。
我找到了一个simmilar question with an answer，但最终未能将其集成到我的用例中，因为那里描述的方法适用于单行（或者我认为如此）。
表：

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
);

(animal_id, date_time)上还没有唯一的约束。（将创建一个！）
如果没有下一行/上一行，则可以省略该行（即，结果输出中没有行）
DB schema
Subset query
PG版本：15.2

postgresql

来源：https://stackoverflow.com/questions/76039178/get-next-and-previous-rows-ordered-by-column-for-a-subset-of-table

1条答案

按热度按时间

rdlzhqv91#

连接到LATERAL子查询应该可以做到这一点。
上一页：

SELECT prev.*
FROM   "subset" t
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time < t.date_time
   ORDER  BY t1.date_time DESC
   LIMIT  1
   ) prev;

SELECT next.*
FROM   "subset" t
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time > t.date_time
   ORDER  BY t1.date_time
   LIMIT  1
   ) next;

fiddle
存在各种可能的极端情况：
1.如果可以有null值。--〉所有相关列NOT NULL。
1.如果可以有相同的(animal_id, date_time). --〉用新的UNIQUE约束排除。
1.如果没有下一行/上一行。--〉没有行。这就是我的查询所做的。
你需要在(animal_id, date_time)上创建一个索引来加快速度。在(animal_id, date_time)上创建一个UNIQUE约束，它排除了大小写 2.，并隐式地提供索引。
相关：

Multicolumn index and performance
PostgreSQL中的LATERAL JOIN和subquery有什么区别？
优化GROUP BY查询以检索每个用户的最新行

赞(0）回复(0）举报 2023-04-20

我来回答

postgresql 获取表的子集的按列排序的下一行和上一行

1条答案

相关问题

热门标签

最新问答