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

Question

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

例子
假设我的数据库中有一个表有两个属性(有序位置和随机数)，我想检索偶数之间的奇数。我该怎么做？

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

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

Answer 1

这是我使用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)

---

简化版:

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 中使用大小写混合的标识符永远)