Как сравнить текущую строку со следующей и предыдущей строкой в PostgreSQL?

Я хочу знать, как получить результаты в SQL-запросе, проводя некоторые логические сравнения со следующими или предыдущими строками. Я использую PostgreSQL.

Пример
Предположим, у меня есть таблица в моей базе данных с двумя атрибутами (упорядоченная позиция и случайные числа), я хочу получить нечетные числа, которые находятся между четными числами. Как я могу это сделать?

Реальное использование
Я хочу найти слова, которые находятся между двумя другими словами, которые имеют категорию NAME (а слово не имя). Заказ предоставляется предложением и позицией.

Edit Я хочу знать, является ли функция Window PostgreSQL лучшим решением для такого рода проблем, чем выполнение запросов. Я слышал о них, но никогда не использовал.

Ответ 1

Это мое решение, используя 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)

Упрощенная версия:

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 когда-либо)

Ответ 2

Это должно работать:

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'

Используйте два самостоятельных объединения
Все слова должны быть в одном и том же предложении (?) и в порядке.
Слово до и после слова должно быть категории "имя". Слово само не "имя"
Это предполагает, что категория IS NOT NULL

Чтобы ответить на ваш дополнительный вопрос: нет, функция окна не будет особенно полезна в этом случае, self-join - это волшебное слово здесь.

Edit:
Я стою исправлено. Ренато демонстрирует прекрасное решение с функциями окна lag() и lead().
Обратите внимание на незначительные отличия:

self-join работает с абсолютными значениями: если строка с pos -1 отсутствует, то строка с pos не подходит.
Версия Renatos с lag() и lead() работает с относительным положением строк, созданным ORDER BY.

Во многих случаях (например, возможно, в одной руке?) обе версии приводят к одинаковым результатам. С пробелами в пространстве пробелов будут разные результаты.

Ответ 3

Вы можете найти лучшее решение по этому адресу:

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

Запрос 1 для SQL Server 2012 и более поздняя версия:

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

Запрос 2 для SQL Server 2005+ и более поздней версии:

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