Criteri ROWS e RANGE

Al momento, non possiamo essere flessibili nella scelta di quante righe precedenti o successive prendere in considerazione. Ora è possibile farlo con i criteri ROWS e RANGE. Per usarli scriviamo:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

E possiamo specificare le seguenti opzioni:

• CURRENT ROW - la riga corrente
• n PRECEDING - righe precedenti alla riga corrente
• n FOLLOWING - righe successive alla riga corrente 

La differenza tra ROWS & RANGE è che il criterio ROWS non tiene conto dei valori, ma solo delle posizioni, mentre RANGE definisce la finestra in termini di intervalli di valori anziché di posizioni delle righe.

Per RANGE dobbiamo specificare ORDER BY --column_name-- perché altrimenti non saprebbe come scegliere la finestra.

Per esempio:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Qui crea una finestra che include la riga corrente, la riga precedente e la riga successiva.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Qui crea una finestra che include, per ogni livello (ordinato in ordine crescente), il livello attuale, un livello prima e un livello dopo. Se il livello attuale è 5, includerà i livelli 4, 5 e 6.

Nota: L'uso di RANGE BETWEEN potrebbe comportare l'inclusione di più righe nella finestra, perché include tutte le righe che condividono gli stessi valori di quelle nell'intervallo, mentre ROWS BETWEEN includerà sempre lo stesso numero di righe (purché siano disponibili nel set di dati). 

Inoltre RANGE non supporta le colonne di date.

Ecco un semplice esempio per illustrare ROWS rispetto a RANGE:

Usando ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Utilizzo di RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range