SQL 如何将当前行与 PostgreSQL 中的下一行和上一行进行比较？

How to compare the current row with next and previous row in PostgreSQL?

提问by Renato Dinhani

I want to know how to retrieve results in a SQL query doing some logic comparison with the next or previous rows. I'm using PostgreSQL.

我想知道如何在 SQL 查询中检索结果，与下一行或前一行进行一些逻辑比较。我正在使用 PostgreSQL。

Example
Supposing I have a table in my database with two attributes (ordered position and random numbers), I want to retrieve the odd numbers that are between even numbers. How can I do this?

示例
假设我的数据库中有一个具有两个属性（有序位置和随机数）的表，我想检索偶数之间的奇数。我怎样才能做到这一点？

The real usage
I want to find words that are between two another words which have the category NAME (and the word is not a name). The ordering is provided by sentence and position.

真正的用法
我想找到两个具有类别名称（并且该词不是名称）的词之间的词。排序由句子和位置提供。

EditI want to know if the Window function of PostgreSQL are best solution for this kind of problem than doing queries. I heard about them, but never used.

编辑我想知道 PostgreSQL 的 Window 函数是否是解决此类问题的最佳解决方案，而不是进行查询。我听说过它们，但从未使用过。

回答by Renato Dinhani

This is my solution using WINDOW functions. I used the lagand leadfunctions. Both returns a value from a column from a row in offset from the current row. laggoes back and leadgoes next in the offset.

这是我使用WINDOW functions. 我使用了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';

Major points

要点

• = ANY()is not needed, the window function returns a single value
• some redundant fields in the subquery
• no need to order by columns, that you PARTITION BY- the ORDER BY applies withinpartitions
• Don't use mixed case identifiers without quoting, it only leads to confusion. (Better yet: don't use mixed case identifiers in PostgreSQL ever)

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

回答by mnshahab

You can find the best solution in this address:

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

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

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

Query 1 for SQL Server 2012 and later version:

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

Query 2 for SQL Server 2005+ and later version:

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

回答by Erwin Brandstetter

This should work:

这应该有效：

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'

• Use two self-joins
• All words must be in the same sentence (?) and in order.
• Word before and word after have to be of category 'name'. Word itself not 'name'
• This assumes that category IS NOT NULL

• 使用两个自联接
• 所有单词必须在同一个句子中 (?) 并按顺序排列。
• 单词之前和之后的单词必须属于“名称”类别。单词本身不是“名称”
• 这假设类别 IS NOT NULL

To answer your additional question: no, a window function would not be particularly useful in this case, self-joinis the magic word here.

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

Edit:
I stand corrected. Renato demonstrates a cool solution with the window functions lag() and lead().
Note the subtle differences:

编辑：
我站纠正。Renato 使用窗口函数lag() 和 lead()演示了一个很酷的解决方案。
注意细微的差别：

• the self joins operate on absolute values: if the row with pos -1is missing, then the row with posdoes not qualify.
• Renatos version with lag()and lead()operates on the relative position of rowscreated by ORDER BY.

• 自连接对绝对值进行操作：如果pos -1缺少行，则行pos不符合条件。
• Renatos版本lag()，并lead()在运行行的相对位置所创造ORDER BY。

In many cases (like probably in the one at hand?) both versions lead to identical results. With gaps in the id space there will be different results.

在许多情况下（比如可能在手头的那个？）两个版本都会导致相同的结果。id 空间的间隙会有不同的结果。