Postgres下/上一行SQL查询 [英] Postgres Next/Previous row SQL Query

问题描述

我在Postgres 9.1数据库中具有以下表结构，但是理想的解决方案应该是与数据库无关的：

I have the following table structures in a Postgres 9.1 database but the ideal solution should be DB agnostic if possible:

Table: users
|id|username|
|1 |one     |
|2 |two     |
|3 |three   |

Table: items
|id|userid|itemname|created  |
|1 |1     |a       |timestamp|
|2 |1     |b       |timestamp|
|3 |1     |c       |timestamp|
|4 |2     |d       |timestamp|
|5 |2     |e       |timestamp|
|6 |2     |f       |timestamp|
|7 |3     |g       |timestamp|
|8 |3     |h       |timestamp|
|9 |3     |i       |timestamp|

我有一个查询（用于查看），该查询提供了下一个和上一个item.id。

I have a query (for a view) which provides the next and previous item.id.

eg

View: UserItems
|id|userid|itemname|nextitemid|previtemid|created  |
|1 |1     |a       |2         |null      |timestamp|
|2 |1     |b       |3         |1         |timestamp|
|3 |1     |c       |4         |2         |timestamp|
|4 |2     |d       |5         |3         |timestamp|
|5 |2     |e       |6         |4         |timestamp|
|6 |2     |f       |7         |5         |timestamp|
|7 |3     |g       |8         |6         |timestamp|
|8 |3     |h       |9         |7         |timestamp|
|9 |3     |i       |null      |8         |timestamp|

我可以通过以下查询来做到这一点：

I can do this with the following query:

SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  LEAD(i.id) OVER (ORDER BY i.created DESC) AS nextitemid,
  LAG(i.id) OVER (ORDER BY i.created DESC) AS previtemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

您能帮助解决以下问题吗？

Can you help to solve the following problems:

1）有没有办法使id换行，即

1) Is there a way to make the ids wrap i.e.

• nextitemid列最后一行的NULL itemid应该是1


• previtemid列第一行中的NULL itemid应该为9

2 ）是否有一种按用户ID分组下一个和上一个itemid的有效方式，例如

2) is there a performant way to group the next and previous itemids by userid e.g.

NB：用户是顺序的，对于实际数据则不是这种情况，每个用户的itemid都是交错的。

NB: in this example the itemids for a user are sequential, this is not the case for real data, the itemids for each user are interleaved.

View: UserItems
|id|userid|itemname|nextitemid|previtemid|nextuseritemid|prevuseritemid|created  |
|1 |1     |a       |2         |9         |2             |3             |timestamp|
|2 |1     |b       |3         |1         |3             |1             |timestamp|
|3 |1     |c       |4         |2         |1             |2             |timestamp|
|4 |2     |d       |5         |3         |5             |6             |timestamp|
|5 |2     |e       |6         |4         |6             |4             |timestamp|
|6 |2     |f       |7         |5         |4             |5             |timestamp|
|7 |3     |g       |8         |6         |8             |9             |timestamp|
|8 |3     |h       |9         |7         |9             |7             |timestamp|
|9 |3     |i       |1         |8         |7             |8             |timestamp|

推荐答案

Q1：FIRST_VALUE / LAST_VALUE

Q1: FIRST_VALUE/LAST_VALUE

第2季度：划分依据（如罗曼·佩卡（Roman Pekar）所建议的那样）

Q2: PARTITION BY (as Roman Pekar already suggested)

在此处查看

SEE FIDDLE HERE

SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  COALESCE(LEAD(i.id)        OVER (ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextitemid,
  COALESCE(LAG(i.id)         OVER (ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS previtemid,
  COALESCE(LEAD(i.id)        OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextuseritemid,
  COALESCE(LAG(i.id)         OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

这篇关于Postgres下/上一行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！