Funkcje okna w języku SQL

SQL (ang. Structured Query Language) to język spotykany praktycznie w większości aplikacji backendowych, niezależnie od użytej technologii. Jeśli projekt wymaga integralności danych i możliwości zaawansowanego dostępu do nich, to oczywistym wyborem staje się właśnie baza SQL. Na rynku dostępnych jest wiele baz danych SQL. Najpopularniejsze z nich to MySQL, MSSQL, Oracle czy otwartoźródłowy PostgreSQL, którego użyjemy w tym artykule.

Często w aplikacjach backendowych wykorzystywane są mechanizmy ORM (ang. Object-Relational Mapping), dzięki którym do korzystania z bazy danych praktycznie nie potrzebujemy znajomości języka SQL. Są one jednak dość mocno ograniczone i nie pozwalają w pełni korzystać z funkcjonalności, jakie oferuje silnik bazodanowy.

Jedną z takich funkcjonalności są właśnie funkcje okna, które przybliżę w artykule.

Czym są funkcje okna w SQL?

Funkcje okna (ang. window functions lub analytic functions) w SQL to specjalne rodzaje funkcji, które pozwalają na wykonywanie obliczeń na zestawie wierszy z wyniku zapytania, przy zachowaniu relacji między wierszami. Są to funkcje, które pozwalają na realizację skomplikowanych operacji analitycznych i agregacyjnych w obrębie zbioru wynikowego zapytania, bez konieczności korzystania z podzapytań, samodzielnej obróbki wyniku czy tworzenia tymczasowych tabel czy widoków.

Jeśli ta definicja wydaje Ci się skomplikowana, nie przejmuj się. W dalszej części artykułu pojawią się przykłady użycia.

Popularne silniki bazodanowe, które posiadają implementację funkcji okna, to:

• PostgreSQL(pełna implementacja),
• SQLite (implementacja okrojona),
• Oracle od wersji 11 wzwyż,
• MS SQL od wersji 2012 wzwyż,
• MySQL od wersji 8.

Konstrukcja funkcji okna

1. Okno (ang. Window) – okno to zbiór wierszy z wyniku zapytania, na którym wykonywane są obliczenia. Jest definiowane poprzez klauzulę OVER, pozwalającą określić kolejność sortowania (ORDER BY) oraz warunki partycjonowania (PARTITION BY). Partycjonowanie dzieli wynik na grupy, w ramach których wykonywane są obliczenia.
2. Funkcje Agregujące – funkcje okna obejmują funkcje agregujące takie jak: SUM, AVG, MIN, MAX, które są wykonywane na zestawie wierszy w obrębie okna. To pozwala na uzyskanie wyników agregacji na poziomie okna, a nie całego zestawu wynikowego.
3. Funkcje Analityczne – oprócz funkcji agregujących, funkcje okna umożliwiają wykonywanie funkcji analitycznych, takich jak: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, LAST_VALUE itp. Służą one do przypisywania numerów, rankingów, uzyskiwania wartości poprzednich i następnych wierszy w obrębie okna.
4. Klauzula porządkowania – funkcje okna działają w oparciu o porządek wierszy, który możesz zdefiniować przy użyciu klauzuli ORDER BY. To pozwala na kontrolowanie, na jakiej podstawie wykonywane są obliczenia.

Wydaje się skomplikowane? Nie przejmuj się, poniższe przykłady powinny rozwiać wszelkie wątpliwości.

Popularne funkcje okna

1. AVG:

SELECT
    worker_id,
    department_name,
    salary,
    AVG(salary) OVER(PARTITION BY department_name ORDER BY salary) AS avg_salary
FROM
    sii.workers;

Wynik:

W tym przykładzie funkcja

AVG(salary) OVER(PARTITION BY department_id ORDER BY salary)

oblicza średnią pensję w obrębie każdego działu, zachowując porządek rosnący pensji. Dzięki temu, można zobaczyć, jak pensje pracowników w danym dziale porównują się do średniej w ich dziale.

2. ROW_NUMBER: Skorzystajmy z tabeli workers z przykładu użytego powyżej. Przykład będzie dość banalny – zakładamy, że chcemy nadać numery pracownikom, uwzględniając wysokość ich zarobków.

SELECT
    worker_id,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_number
FROM
    sii.workers;

Wynikiem tego zapytania będą kolejne numery nadawane rosnąco, dla kolejnych pracowników posortowanych po wysokości zarobków.

3. LAG/LEAD: obie te funkcje działają podobnie (LEAD jest odwróceniem funkcji LAG), zaprezentuję więc przykład użycia na LAG. Załóżmy, że w tabeli revenues trzymane są kwartalne wyniki przychodów firmy. Naszym celem jest pobranie danych i wskazanie w każdym rekordzie, jaki był wynik z poprzedniego kwartału danego roku, oraz jaka była zmiana względem obecnego rekordu.

SELECT
    year,
    quarter,
    revenue,
    LAG(revenue, 1) OVER(PARTITION BY year ORDER BY quarter) AS previous_quarter_result,
    revenue- LAG(revenue, 1) OVER(PARTITION BY year ORDER BY quarter) AS change
FROM
    sii.revenues;

Wynik jaki otrzymamy:

Jak można zauważyć, przy każdym kwartale danego roku widzimy wynik z poprzedniego, a także to, o ile zmienił się przychód względem poprzedniego kwartału.

4. RANK: A co, gdybyśmy z tej samej tabeli zechcieli pobrać informacje o wynikach kwartalnych z przydzielonym rankingiem w oparciu o przychody? Z pomocą przyjdzie funkcja RANK().

SELECT
    year,
    quarter,
    revenue,
    RANK() OVER(PARTITION BY year ORDER BY revenue DESC) AS q_revenue_rank
FROM
    sii.revenues;

Wynik zapytania:

Od razu rzuca się w oczy, że funkcja obsługuje sytuację, gdy mamy dwie identyczne wartości, nadając im ten sam ranking i pomijając kolejną wartość. Gdybyśmy nie chcieli, aby owa przerwa w numeracji się pojawiała, zamiast funkcji RANK powinniśmy użyć DENSE_RANK.

Inne funkcje okna

1. FIRST_VALUE – funkcja jest używana do zwracania pierwszej wartości w obrębie okna wierszy. Wartość ta jest wybrana na podstawie określonego porządku sortowania. Jest to przydatne, gdy chcemy uzyskać pierwszą wartość w danym zestawie w kontekście określonej kolejności.
2. SUM – oblicza sumę wartości w obrębie okna wierszy. Dzięki partycjonowaniu i porządkowi można kontrolować, na jakich danych funkcja ma działać.
3. MAX – oblicza maksymalną wartość w obrębie okna wierszy. Podobnie jak w przypadku innych funkcji okna, partycjonowanie i porządek mają wpływ na działanie funkcji. Odwrotnością tej funkcji jest funkcja MIN.

Podsumowanie

Funkcje okna w SQL są niezwykle przydatne w wielu kontekstach, szczególnie w analizie danych i generowaniu bardziej zaawansowanych raportów. Pozwalają one na wykonanie skomplikowanych operacji na zestawach wierszy w wyniku zapytania, przy zachowaniu relacji między wierszami.

Gdzie warto ich używać? Szczególnie tam, gdzie wymagane jest wykonywanie skomplikowanych analiz danych, uwzględniając relacje między poszczególnymi wierszami w tabeli.

***

Jeśli interesuje Cię tematyka związana z SQL, zajrzyj również do innych artykułów naszych ekspertów.