如何在PostgreSQL中将当前行与前一行和后一行进行比较？

Question

如何在PostgreSQL中将当前行与前一行和后一行进行比较？

sqlpostgresqlwindow

37

我想知道如何在SQL查询中使用逻辑比较来检索结果与相邻行。我正在使用PostgreSQL。

示例
假设我在数据库中有一个具有两个属性（排序位置和随机数字）的表，我想检索在偶数之间的奇数。我该怎么做？

实际用途
我想找到在两个具有NAME类别的单词之间的单词（而且该单词不是名称）。排序由句子和位置提供。

编辑我想知道PostgreSQL的窗口函数是否比查询更好地解决了这种问题。我听说过它们，但从未使用过。

- Renato Dinhani

这三个单词应该在同一个句子中，并且它们的位置是按顺序排列的吗？ - Erwin Brandstetter

@ErwinBrandstetter 是的，所有单词必须在同一个句子中按顺序出现。 - Renato Dinhani

3个回答

25

您可以在此网址找到最佳解决方案：

http://blog.sqlauthority.com/2013/09/25/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement-part-4/

适用于SQL Server 2012及其后续版本的查询1：

SELECT
LAG(p.FirstName) OVER(ORDER BY p.BusinessEntityID) PreviousValue,
    p.FirstName,
    LEAD(p.FirstName) OVER(ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

SQL Server 2005+及更高版本的查询2:

WITH CTE AS(
    SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID),
    p.FirstName FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
    CTE.FirstName,
    nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO

- mnshahab

谢谢...我喜欢LEG和LEAD函数。 - khichar.anil

查询2完全符合我的需求。谢谢！ - NINtender

感谢您添加有关支持的 SQL 版本的信息（是的，请不要问）。 - Jan 'splite' K.

6

这应该可以工作：

SELECT w1.word AS word_before, w.word, w2.word AS word_after
FROM   word w
JOIN   word w1 USING (sentence)
JOIN   word w2 USING (sentence)
WHERE  w.category <> 'name'
AND    w1.pos = (w.pos - 1)
AND    w1.category = 'name'
AND    w2.pos = (w.pos + 1)
AND    w2.category = 'name'

使用两个自连接
所有单词必须在同一句子中，并按顺序。
前一个单词和后一个单词必须是“名称”类别。单词本身不是“名称”
这假定类别不为空

~~回答您的附加问题：在这种情况下，窗口函数并不特别有用，自连接是关键词。~~

编辑：
我纠正了自己。Renato展示了一个很酷的解决方案，使用窗口函数lag()和lead()。
请注意微妙的区别：

自连接操作绝对值：如果行pos-1缺失，则行pos不符合条件。
Renatos版本使用lag()和lead()操作由ORDER BY创建的行的相对位置。

在许多情况下（可能包括手头的问题？）两个版本都会导致相同的结果。在id空间中有间隙的情况下，将会有不同的结果。

- Erwin Brandstetter

请看一下我的编辑。我还没有尝试过这个SQL，但我认为它可以解决问题。问题是在此之后我将有更复杂的事情要做。您知道窗口函数是否更适合此任务吗？我从未使用过它们。 - Renato Dinhani

看看我的答案，我使用了WINDOW函数。我不知道它是好还是坏，但我喜欢，现在看起来很容易使用。 - Renato Dinhani

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Renato Dinhani · Accepted Answer

这是我使用WINDOW函数的解决方案。我使用了lag和lead函数。两者都会从当前行的偏移量返回列中的值。lag向后移动，lead向下移动。

SELECT tokcat.text
FROM (
    SELECT text, category, chartype, lag(category,1) OVER w as previousCategory, lead(category,1) OVER w as nextCategory
    FROM token t, textBlockHasToken tb
    WHERE tb.tokenId = t.id
    WINDOW w AS (
        PARTITION BY textBlockId, sentence
        ORDER BY textBlockId, sentence, position
    )
) tokcat
WHERE 'NAME' = ANY(previousCategory)
AND 'NAME' = ANY(nextCategory)
AND 'NAME' <> ANY(category)

Simplified version:

SELECT text
FROM (
    SELECT text
          ,category 
          ,lag(category) OVER w as previous_cat
          ,lead(category) OVER w as next_cat
    FROM   token t
    JOIN   textblockhastoken tb ON tb.tokenid = t.id
    WINDOW w AS (PARTITION BY textblockid, sentence ORDER BY position)
    ) tokcat
WHERE  category <> 'NAME'
AND    previous_cat = 'NAME'
AND    next_cat = 'NAME';

主要要点

= ANY() 不需要使用，窗口函数会返回单个值
子查询中存在一些冗余字段
不需要按列排序，而是按照你的PARTITION BY - ORDER BY 仅适用于分区内
不要在没有引号的情况下使用混合大小写标识符，这只会导致混乱。（更好的做法：在 PostgreSQL 中永远不要使用混合大小写标识符）