Funkcje okna: rankingi i obliczenia w grupach

Funkcja okienkowa to funkcja działająca na „grupie wierszy” (okno), ale — w przeciwieństwie do GROUP BY — nie zmniejsza liczby wiersze: wartość obliczona w oknie jest dołączona do każdego wiersza pliku wynik końcowy.

To właściwe narzędzie do rankingów, numeracji progresywnej, wartości procentowych w sumie w grupie i wiele innych operacji, które są niewygodne lub niemożliwe z GROUP BY.

Składnia: `function() OVER (…)`

SQL

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Komponenty:

PARTITION BY — definiuje „grupy” (jak GROUP BY, ale wiersze trzymajcie się osobno);
ORDER BY wewnątrz OVER(…) — określa porządek wewnątrz partycja.

OVER () (puste nawiasy) oznacza „całą tabelę jako pojedyncze okno”. Przydatne do obliczeń takich jak „procent całości”.

Funkcje rankingowe

SQL

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Różnice (z wartościami 100, 100, 90, 80):

Funkcja	Pozycje
KODEF0	1, 2, 3, 4 (zawsze unikalne)
KODEF1	1, 1, 3, 4 (remisy mają tę samą pozycję, pozostawia „luki”)
KODEF2	1, 1, 2, 3 (remisy mają tę samą pozycję, bez przerw)

Przykład: ranking produktów według kategorii

SQL

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

Dla każdego category_id, RANK rozpoczyna się od 1 i porządkuje w dół cena: otrzymujesz ranking produktów w każdej kategorii, w jednym strzał.

Numeracja progresywna

SQL

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

Pierwsze zamówienie każdego klienta będzie miało row_num = 1, drugie 2 itd.

Funkcja okna jest obliczana po WHERE i GROUP BY. Więc nie możesz filtrować za pomocą WHERE rank = 1: zawiń zapytanie w CTE i filtruj na zewnątrz.

SQL

WITH ranked AS (
  SELECT name, price, RANK() OVER (ORDER BY price DESC) AS r
  FROM   products
)
SELECT * FROM ranked WHERE r <= 3;

Spróbuj

Ćwiczenie#sql.m7.l2.e1

Próby: 0Ładowanie...

Dla każdego produktu pokaż nazwę, identyfikator kategorii, cenę i rangę (ranking według malejącej ceny WEWNĄTRZ kategorii; użyj RANK). Sortuj według rosnącego identyfikatora kategorii i rosnącej rangi.

Ładowanie edytora...

Pokaż wskazówkę

PARTYCJA WG grup id kategorii, ZAMÓW WG ceny DESC zamówień w obrębie grupy.

Rozwiązanie dostępne po 3 próbach

Przejrzyj ćwiczenie

Ćwiczenie#sql.m7.l2.e2