Kryterium ROWS i RANGE

Na razie nie możemy elastycznie wybierać, ile wierszy przed lub po 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 też 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, a jedynie pozycje, podczas gdy RANGE definiuje okno za pomocą 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

Tutaj tworzy okno obejmujące bieżący wiersz, poprzedni wiersz i następny wiersz.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

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

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

Ponadto w przypadku dat przechowywanych jako tekst (takich jak '2024-01-05') przesunięcie RANGE nie wykonuje żadnych obliczeń na datach: przy 1 PRECEDING okno obejmuje tylko wiersze z tą samą datą. Aby zliczać dni, sortuj według liczby, na przykład julianday(date).

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

Użycie 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