PostgreSQL: Cosa sono le clausole OVER e PARTITION

Piccolo esercizio di SQL: un vostro cliente vi chiede di estrarre i prezzi più alti degli articoli, partendo da dei documenti. Ora avete due possibilità:

1. Utilizzare il classico GROUP BY, con le funzioni di aggregazione, impazzendo tra MAX, GROUP BY, subquery, CROSS JOIN nella stessa tabella
2. Utilizzare le funzioni di partizionamento fornite da SQL Server, PostgreSQL, Oracle e qualche altro RDBMS (no, quella chiavica di MySQL non le supporta essendo non compatibile con lo standard SQL2003)

Ecco qui un esempio di cosa stiamo parlando, sotto PostgreSQL 9.3.5:

ed ecco una query veloce, veloce, che vi ha risolto il problema in poco tempo:


SELECT *
FROM
  (SELECT *,
   row_number() OVER (PARTITION BY codicearticolo ORDER BY  codicearticolo ASC,valorearticolo DESC) AS pos
   FROM articoli
  ) AS max_prezzi

con relativo risultato:

Spiegazioni:
La query in PostgreSQL ha:
* Creato una colonna pos ( dense_rank() ), contenente l'ordinamento delle tuple, la quale viene resettata ogni volta che:
*  il partizionamento dei dati dato da codicearticolo e ordinato da codicearticolo e valorearticolo cambiano.

Quindi ora basta prendere le tuple con pos = 1 e abbiamo gli articoli con i prezzi più alti.

Fonti per PostgreSQL:
Window Functions (es: dense_rank) 
Esempi sulla guida di PostgreSQL 

EDIT: Aggiornata la query in quanto usava una funzione errata. Dense_rank() serve per fare il ranking delle tuple.