如何将当前行与PostgreSQL中的下一行和上一行进行比较? [英] How to compare the current row with next and previous row in PostgreSQL?
问题描述
我想知道如何在SQL查询中与下一行或上一行进行逻辑比较来检索结果.我正在使用PostgreSQL.
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.
示例
假设我的数据库中有一个具有两个属性(有序位置和随机数)的表,我想检索介于偶数之间的奇数.我该怎么办?
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?
实际用法
我想找到两个其他单词之间的单词,它们之间的名称为NAME(并且该单词不是名称).排序由句子和位置提供.
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.
修改 我想知道PostgreSQL的Window函数是否比查询更能解决此类问题.我听说过它们,但从未使用过.
Edit I 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.
推荐答案
这是我使用WINDOW functions
的解决方案.我使用了lag
和lead
函数.两者都从与当前行偏移的行中的某一列返回一个值. lag
返回,并且lead
在偏移量中移至下一个.
This is my solution using WINDOW functions
. I used the lag
and lead
functions. Both returns a value from a column from a row in offset from the current row. lag
goes back and lead
goes next in the offset.
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()
,window函数将返回单个值 - 子查询中的一些冗余字段
- 无需按列排序,即您
PARTITION BY
-ORDER BY在 个分区内应用 - 请勿在不加引号的情况下使用大小写混合的标识符,这只会导致混乱. (更好的是:不要在PostgreSQL的 中使用大小写混合的标识符)
= 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 within partitions - Don't use mixed case identifiers without quoting, it only leads to confusion. (Better yet: don't use mixed case identifiers in PostgreSQL ever)
Major points
这篇关于如何将当前行与PostgreSQL中的下一行和上一行进行比较?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!