Postgres下一行/上一行SQL查询

Question

我有一个Postgres 9.1数据库，其中包含以下表结构，但最理想的解决方案应该是DB可移植的（如果可能的话）:

Table: users
|id|username|
|1 |one     |
|2 |two     |
|3 |three   |
Table: items
|id|userid|itemname|created  |
|1 |1     |a       |时间戳   |
|2 |1     |b       |时间戳   |
|3 |1     |c       |时间戳   |
|4 |2     |d       |时间戳   |
|5 |2     |e       |时间戳   |
|6 |2     |f       |时间戳   |
|7 |3     |g       |时间戳   |
|8 |3     |h       |时间戳   |
|9 |3     |i       |时间戳   |

我有一个查询（对于视图），它提供了下一个和上一个 item.id。

例如:

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

我可以使用以下查询实现此操作:

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换行，即

• 下一个项目ID列中最后一行的NULL itemid应该是1
• 前一个项目ID列中第一行的NULL itemid应该是9

2）是否有一种高效的方法按userid分组next和previous itemids，例如

NB：在此示例中，用户的itemids是连续的，但在实际数据中并非如此，每个用户的itemids是交错的。

视图：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 |

- davec

Answer 1

问题1：FIRST_VALUE / LAST_VALUE

问题2：PARTITION BY（正如Roman Pekar已经建议的那样）

在此处查看SQLFiddle示例

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;

- dnoeth

---

做得很好，非常感谢大家。你也可以删除未经测试的评论http://sqlfiddle.com/#!1/58d92/28 - davec

Answer 2

更新 我忘记了PostgreSQL中的first_value和last_value函数，感谢dnoeth提醒我。然而，他的查询不起作用，因为last_value是使用默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW工作的，并且不会返回正确的结果，所以你要么必须在over子句中更改范围，要么使用带有order by asc的first_value：

select
    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)
    ) as nextitemid,
    coalesce(
        lag(i.id) over(order by i.created desc),
        first_value(i.id) over(order by i.created asc)
    ) 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)
    ) as nextuseritemid,
    coalesce(
        lag(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 asc)
    ) as prevuseritemid,
    i.created as created
from items as i
   left outer join users as u on u.id = i.userid
order by i.created desc

SQL Fiddle演示

先前版本
我认为你可以这样做：

SELECT
  i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  coalesce(
      LEAD(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created desc limit 1)
  ) AS nextitemid,
  coalesce(
      LAG(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created asc limit 1)
  ) AS previtemid,
  coalesce(
      LEAD(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created desc limit 1)
  ) AS nextuseritemid,
  coalesce(
      LAG(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created asc limit 1)
  ) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

sql fiddle demo

- Roman Pekar

---

是的，我忘记了ROWS。但是，我不会改变排序顺序，而是将ROWS更改为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这应该允许优化器在单个步骤中使用相同的PARTITION BY和ORDER BY执行所有OLAP函数。 - dnoeth

好的，您可以这样做，我会点赞的 :) 我想给它点赞，但是它不起作用，所以您必须先编辑它 :) - Roman Pekar

好的，我修改了我的回答 :-) - dnoeth