Come confrontare la riga corrente con la riga successiva e precedente in PostgreSQL?

Voglio sapere come recuperare i risultati in una query SQL eseguendo un confronto logico con le righe successive o precedenti. Sto usando PostgreSQL.

Esempio
Supponendo di avere una tabella nel mio database con due attributi (posizione ordinata e numeri casuali), voglio recuperare i numeri dispari che si trovano tra i numeri pari. Come posso fare questo?

L’uso reale
Voglio trovare parole che sono tra due altre parole che hanno la categoria NOME (e la parola non è un nome). L’ordine è fornito da frase e posizione.

Modifica Voglio sapere se la funzione Window di PostgreSQL è la soluzione migliore per questo tipo di problema rispetto alle query. Ne ho sentito parlare, ma mai usato.

Questa è la mia soluzione usando le WINDOW functions . Ho usato le funzioni lag e lead . Entrambi restituiscono un valore da una colonna di una riga in offset rispetto alla riga corrente. lag torna indietro e il lead va avanti nell’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) 

Versione semplificata:

 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'; 

Punti principali

  • = ANY() non è necessario, la funzione window restituisce un singolo valore
  • alcuni campi ridondanti nella sottoquery
  • non è necessario ordinare per colonne, che si PARTITION BY – l’ORDINE BY si applica all’interno delle partizioni
  • Non utilizzare identificatori di maiuscole e minuscole senza citazioni, ma solo confusione. (Meglio ancora: non usare mai identificatori di maiuscole e minuscole in PostgreSQL)

Questo dovrebbe funzionare:

 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' 
  • Usa due auto-join
  • Tutte le parole devono essere nella stessa frase (?) E in ordine.
  • Parola prima e parola dopo devono essere di categoria ‘nome’. Word stessa non “nome”
  • Questo presuppone che la categoria IS NOT NULL

Per rispondere alla tua domanda aggiuntiva: no, una funzione finestra non sarebbe particolarmente utile in questo caso, l’ autoaggiunta è la parola magica qui.

Modificare:
Sono corretto. Renato dimostra una soluzione interessante con le funzioni della finestra lag () e lead () .
Nota le sottili differenze:

  • i self join operano su valori assoluti : se manca la riga con pos -1 , la riga con pos non si qualifica.
  • La versione di Renatos con lag() e lead() opera sulla posizione relativa delle righe create da ORDER BY .

In molti casi (come probabilmente in quello a portata di mano?) Entrambe le versioni portano a risultati identici. Con lacune nello spazio ID ci saranno risultati diversi.

Puoi trovare la migliore soluzione in questo indirizzo:

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

Query 1 per SQL Server 2012 e versioni successive:

 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 

Query 2 per SQL Server 2005 e versioni successive:

 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