Postgres下一个/上一个行SQL查询

Question

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

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。

例如

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|

我可以使用以下查询执行此操作：

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;

您可以帮助解决以下问题：

1）有没有办法让ID包裹，即

• nextitemid列最后一行中的NULL itemid应该为1
• previtemid列第一行中的NULL itemid应该是9

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

注意： 在此示例中，一个用户的itemid是连续的，对于真实数据则不是这种情况，每个用户的itemid都是交错的。

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|

Answer 1

第1季：FIRST_VALUE / LAST_VALUE

问题2：PARTITION BY（正如罗曼·佩卡（Roman Pekar）所建议的那样）

在这里查看

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;