sql - 在Pg中查找两个词相邻的句子

Question

我需要帮助制作高级 Postgres 查询。我试图直接使用 Postgres，而不是使用一些命令语言扩展来查找两个单词彼此相邻的句子。我的表是:

TABLE word (spelling text, wordid serial)
TABLE sentence (sentenceid serial)
TABLE item (sentenceid integer, position smallint, wordid integer)

我有一个简单的查询来查找包含单个单词的句子:

SELECT DISTINCT sentence.sentenceid 
FROM item,word,sentence 
WHERE word.spelling = 'word1' 
  AND item.wordid = word.wordid 
  AND sentence.sentenceid = item.sentenceid 

我想通过某个其他词 (word2) 依次过滤该查询的结果，其对应项的 item.sentenceid 等于当前查询结果的 ( item or sentence)的sentenceid并且item.position等于当前查询结果的 item.position + 1。我如何优化我的查询以实现此目标并以高效的方式实现？

Answer 1

更简单的解决方案，但仅在 item.position 中没有间隙时给出结果:

SELECT DISTINCT sentence.sentenceid 
  FROM sentence 
  JOIN item ON sentence.sentenceid = item.sentenceid
  JOIN word ON item.wordid = word.wordid
  JOIN item AS next_item ON sentence.sentenceid = next_item.sentenceid
                        AND next_item.position = item.position + 1
  JOIN word AS next_word ON next_item.wordid = next_word.wordid
 WHERE word.spelling = 'word1'
   AND next_word.spelling = 'word2'

更通用的解决方案，使用 window functions :

SELECT DISTINCT sentenceid
FROM (SELECT sentence.sentenceid,
             word.spelling,
             lead(word.spelling) OVER (PARTITION BY sentence.sentenceid
                                           ORDER BY item.position)
        FROM sentence 
        JOIN item ON sentence.sentenceid = item.sentenceid
        JOIN word ON item.wordid = word.wordid) AS pairs
 WHERE spelling = 'word1'
   AND lead = 'word2'

编辑:也是通用解决方案(允许有间隙)，但仅包含连接:

SELECT DISTINCT sentence.sentenceid
  FROM sentence 
  JOIN item ON sentence.sentenceid = item.sentenceid
  JOIN word ON item.wordid = word.wordid
  JOIN item AS next_item ON sentence.sentenceid = next_item.sentenceid
                        AND next_item.position > item.position
  JOIN word AS next_word ON next_item.wordid = next_word.wordid
  LEFT JOIN item AS mediate_word ON sentence.sentenceid = mediate_word.sentenceid
                                AND mediate_word.position > item.position
                                AND mediate_word.position < next_item.position
 WHERE mediate_word.wordid IS NULL
   AND word.spelling = 'word1'
   AND next_word.spelling = 'word2'