Ramki ROWS a RANGE

Gdy potrafią już Państwo napisać sumę narastającą, naturalne pytanie dodatkowe brzmi: „Jaka jest różnica między ROWS a RANGE w ramce okna?” To precyzyjny wyznacznik poziomu średniozaawansowanego. Wielu kandydatów codziennie używa ramek, nie zauważając, że te dwa słowa kluczowe działają inaczej.

Oba określają zbiór wierszy widocznych dla agregatu, ale zasadniczo różnią się sposobem wyznaczania tego zbioru. Poprawna odpowiedź pozwala się wyróżnić.

ROWS działa pozycyjnie. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW oznacza dosłownie bieżący wiersz oraz dwa fizyczne wiersze znajdujące się bezpośrednio przed nim w uporządkowanej sekwencji.

Nie ma znaczenia, czy sąsiednie wiersze mają tę samą wartość ORDER BY. Trzy wiersze to trzy wiersze, kropka. Taką ramkę niemal zawsze warto stosować w przypadku średnich kroczących i ścisłych sum narastających.

RANGE opiera się na wartościach. Obejmuje każdy wiersz, którego wartość ORDER BY mieści się w logicznym zakresie wartości bieżącego wiersza, a nie określoną liczbę wierszy.

Najważniejsza konsekwencja jest taka, że w przypadku RANGE wszystkie wiersze, które remisują pod względem wartości ORDER BY, są traktowane jako jedna grupa wierszy równorzędnych. Wszystkie otrzymują tę samą ramkę, a zatem również ten sam wynik.

Załóżmy, że dwie sprzedaże miały miejsce 2024-03-02 i opiewały na kwoty 30 oraz 40, a wcześniej, 2024-03-01, odnotowano sprzedaż na kwotę 100.

• Przy RANGE do bieżącego wiersza: oba wiersze z 03-02 są równorzędne, więc w obu widnieje 100 + 30 + 40 = 170.
• Przy ROWS do bieżącego wiersza: pierwszy wiersz z 03-02 pokazuje 130, a drugi 170, ponieważ każdy wiersz rozszerza ramkę o jedną pozycję.

Te same dane, inne liczby. Ta rozbieżność przy remisach jest sednem tego pytania.

Przypomnijmy, że wyrażenie OVER (ORDER BY ...) bez jawnie określonej ramki domyślnie używa RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Oznacza to, że naiwna suma narastająca po cichu korzysta z RANGE. Przy unikalnych kluczach sortowania jest ono identyczne z ROWS, więc błąd pozostaje ukryty. Gdy tylko pojawią się zduplikowane wartości sortowania, suma narastająca po cichu łączy wiersze z remisem. Dlatego doświadczeni inżynierowie jawnie zapisują ROWS.

RANGE może przyjmować przesunięcie wartości, a nie tylko UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW zastosowane do kolumny datowej lub numerycznej obejmuje każdy wiersz, którego wartość znajduje się w odległości nie większej niż 7 jednostek od bieżącej wartości.

W przypadku dat daje to rzeczywiste okno „ostatnich 7 dni”, które poprawnie pomija brakujące dni, podczas gdy ROWS 7 PRECEDING pobrałoby poprzednie 7 wierszy, niezależnie od luk. Znajomość tego niuansu to mocny element odpowiedzi.

Druga strona medalu jest taka, że ROWS nie uwzględnia luk w danych. Jeśli w tabeli sprzedaży brakuje weekendów, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW obejmuje 7 zarejestrowanych dni, co w rzeczywistości może oznaczać dwa tygodnie kalendarzowe.

Wybór zależy więc od intencji: ostatnie N rekordów oznacza ROWS, a ostatnie N jednostek wartości (dni, dolarów) oznacza RANGE z przesunięciem.

W rzetelnej odpowiedzi na rozmowie kwalifikacyjnej warto wspomnieć o różnicach w obsłudze:

• PostgreSQL obsługuje RANGE z przesunięciami (od wersji 11) oraz w pełni obsługuje ROWS.
• SQL Server obsługuje ROWS i RANGE, ale RANGE działa tylko z UNBOUNDED/CURRENT ROW, a nie z przesunięciami liczbowymi.
• MySQL 8 obsługuje oba tryby, ale typy przesunięć dla RANGE są ograniczone.

Jeśli przesunięcie liczbowe w RANGE nie jest dostępne, można je zasymulować za pomocą złączenia tabeli z samą sobą albo wygenerowanego kalendarza. Wspomnienie o tym pokazuje świadomość realiów środowiska produkcyjnego.

Istnieje jeszcze mniej znany, trzeci tryb definiowania ramki: GROUPS. Zlicza on grupy równorzędnych wierszy, a nie wiersze ani wartości. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW obejmuje bieżącą grupę równorzędnych wierszy oraz grupę bezpośrednio przed nią.

Rzadko jest wymagany, ale wymienienie go w odpowiedzi na pytanie „czy istnieją inne tryby ramki?” pokazuje rzeczywistą biegłość. Jest obsługiwany w PostgreSQL 11+ i kilku innych systemach.

Sprowadźmy cały temat do jednego zdania, które można wypowiedzieć na rozmowie kwalifikacyjnej:

„Używam ROWS, gdy mam na myśli stałą liczbę fizycznych wierszy, RANGE, gdy mam na myśli logiczny zakres wartości sortowania, i pamiętam, że powtarzające się wartości powodują różnicę między nimi, ponieważ domyślne RANGE grupuje równe wartości.”

To jedno zdanie wyczerpująco i pewnie odpowiada na pytanie.

Umieszczenie obu sposobów definiowania ramki w jednym zapytaniu uwidacznia różnicę. Należy uruchomić je na danych z powtarzającymi się datami i porównać obie kolumny wiersz po wierszu.

Przy unikalnych datach kolumny są identyczne, natomiast przy takich samych datach kolumna RANGE powtarza tę samą sumę dla równorzędnych wierszy, podczas gdy kolumna ROWS rośnie za każdym razem o jeden krok.

Dwa wiersze mają taką samą wartość używaną w ORDER BY, a ramka jest domyślna. Co się stanie?

ROWS zlicza fizyczne wiersze, a RANGE działa na podstawie logicznej wartości kolumny sortowania i traktuje powtarzające się wartości jako grupę równorzędnych wierszy. Domyślna ramka OVER (ORDER BY ...) to RANGE, dlatego prosta suma narastająca może łączyć wiersze o takich samych wartościach sortowania.

Należy wybrać ROWS dla „ostatnich N rekordów” oraz odpornych na luki okien kroczących, a RANGE z przesunięciem dla „ostatnich N dni/dolarów”, gdy luki mają być uwzględniane. Sama znajomość istnienia GROUPS jest dodatkowym atutem. Następnie zastosujemy ramkę ROWS do obliczania średnich kroczących.