Kryterium ROWS i RANGE

Na razie nie możemy elastycznie wybierać, ile wierszy przed lub po danym wierszu uwzględnić. Teraz jest to możliwe dzięki kryteriom ROWS i RANGE. Aby ich użyć, piszemy:

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

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

Możemy określić następujące opcje:

• CURRENT ROW – bieżący wiersz
• n PRECEDING – wiersze przed bieżącym wierszem
• n FOLLOWING – wiersze po bieżącym wierszu 

Różnica między ROWS a RANGE polega na tym, że kryterium ROWS nie uwzględnia wartości, tylko pozycje, podczas gdy RANGE definiuje okno w kategoriach zakresów wartości, a nie pozycji wierszy.

W przypadku RANGE musimy określić ORDER BY --column_name--, ponieważ w przeciwnym razie nie wiedziałoby, jak wybrać okno.

Na przykład:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

W tym miejscu tworzy okno, które obejmuje bieżący wiersz, poprzedni wiersz i następny wiersz.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Tworzy tu okno, które dla każdego poziomu (posortowanego rosnąco) obejmuje bieżący poziom, poziom przed nim i poziom po nim. Jeśli bieżący poziom to 5, obejmie poziomy 4, 5 i 6.

Uwaga: Użycie RANGE BETWEEN może spowodować uwzględnienie większej liczby wierszy w Twoim oknie, ponieważ obejmuje wszystkie wiersze, które mają takie same wartości jak te w zakresie, podczas gdy ROWS BETWEEN zawsze uwzględni tę samą liczbę wierszy (o ile są dostępne w zbiorze danych). 

Ponadto RANGE nie obsługuje kolumn z datami.

Oto prosty przykład ilustrujący różnicę między ROWS a RANGE:

Używając ROWS:

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

Korzystanie z RANGE:

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