我需要帮助制定一个先进的Postgres查询。我试图用两个相邻的单词来查找句子，直接使用Postgres，而不是一些命令语言扩展。我的表格是： pre $ TABLE字（拼写文本，wordid序列） TABLE语句（sentenceid serial） TABLE item（句子整数，位置smallint，wordid整数）
我有一个简单的查询来查找句子用一个单词：
SELECT DISTINCT sentence.sentenceid FROM商品，单词，句子 WHERE word .spelling ='word1' AND item.wordid = word.wordid AND sentence.sentenceid = item.sentenceid我想依次查询某个其他单词（ word2 ）的结果，该单词的相应项目的 item.sentenceid 等于到当前查询结果（ item 或 sentence ）的 sentenceid ，其中 item.position 等于当前查询结果的 item.position + 1 。如何优化我的查询以实现此目标并以高性能的方式执行？
解决方案更简单的解决方案，但只给出结果，在 item.position s中没有差别：
SELECT DISTINCT句子.sentenceid 从句子加入项目on sentence.sentenceid = item.sentenceid 加入单词on item.wordid = word.wordid 加入项目next_item ON sentence.sentenceid = next_item .sentenceid AND next_item.position = item.position + 1 加入单词AS next_word ON next_item.wordid = next_word.wordid WHERE word.spelling ='word1' AND next_word .spelling ='word2'更通用的解决方案，使用窗口函数：
SELECT DISTINCT句子 FROM（SELECT sentence.sentenceid， word.spelling， lead（word.spelling）OVER（PARTITION BY sentence.sentenceid ORDER BY item.position） FROM句子 JOIN项目on sentence.sentenceid = item。 sentenceid JOIN word ON item.wordid = word.wordid）AS pair WHERE spelling ='word1' AND lead ='word2'编辑：也是一般解决方案（允许空白），但仅限连接：
SELECT DISTINCT sentence.sentenceid FROM句子 JOIN项ON句子.sentenceid = item.sentenceid 加入单词ON item.wordid =单词.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'
I need help crafting an advanced Postgres query. I am trying to find sentences with two words adjacent to each other, using Postgres directly, not some command language extension. My tables are:
TABLE word (spelling text, wordid serial) TABLE sentence (sentenceid serial) TABLE item (sentenceid integer, position smallint, wordid integer)I have a simple query to find sentences with a single word:
SELECT DISTINCT sentence.sentenceid FROM item,word,sentence WHERE word.spelling = 'word1' AND item.wordid = word.wordid AND sentence.sentenceid = item.sentenceidI want to filter the results of that query in turn by some other word (word2) whose corresponding item has an item.sentenceid equal to the current query result's (item or sentence)'s sentenceid and where item.position is equal to the current query result's item.position + 1. How can I refine my query to achieve this goal and in a performant manner?
解决方案Simpler solution, but only gives results, when there are no gaps in item.positions:
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'More general solution, using 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'Edit: Also general solution (gaps allowed), but with joins only:
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'
更多推荐
在Pg中查找彼此相邻的两个单词的句子
发布评论