Użycie PARTITION BY do dzielenia danych na grupy

Wyobraź sobie, że pracujesz jako kelner (albo barista, jeśli wolisz kawę) w dużej restauracji. Każdego dnia podliczasz napiwki, które zarobiłeś. Ale jest pewien haczyk: restauracja jest podzielona na strefy i interesuje cię, ile napiwków zarobiłeś w każdej strefie osobno. PARTITION BY — to właśnie to, czego SQL używa, żeby "podzielić restaurację na strefy".

Bardziej formalnie, PARTITION BY jest używane w funkcjach okiennych do dzielenia wszystkich wierszy tabeli na osobne grupy (albo "partycje"). W każdej grupie funkcja okienna działa od nowa. To tak, jakbyś stosował funkcję osobno w każdej "partycji".

Przykład: jak to działa

Załóżmy, że mamy tabelę sales z danymi o sprzedaży:

Jeśli chcemy policzyć, ile pieniędzy zarobił każdy sprzedawca, ale osobno dla każdego regionu, PARTITION BY — to właśnie to, czego potrzebujemy.

Składnia PARTITION BY

Składnia jest całkiem prosta:

funkcja_okienna() OVER (PARTITION BY kolumna_lub_kolumny)

• funkcja_okienna() — na przykład SUM(), AVG(), ROW_NUMBER() i tak dalej.
• PARTITION BY kolumna — wskazuje, według której kolumny dzielić wiersze.
• OVER() — to operator, który mówi SQL: "Zrób coś w ramach określonego okna".

Przykład: suma według grup

Policzmy sumę sprzedaży dla każdego regionu:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

Wynik będzie taki:

Co się dzieje? SQL dzieli wiersze na grupy według wartości w kolumnie region (North i South), a potem stosuje funkcję SUM() osobno dla każdej grupy. W efekcie wiersze w grupie "North" dostają tę samą sumę, a wiersze w grupie "South" — inną.

Przykłady użycia PARTITION BY

Zobaczmy, jak PARTITION BY może się przydać w zadaniach z życia wziętych.

Przykład 1: Ranking w grupie

Załóżmy, że chcemy zrobić ranking sprzedawców w każdym regionie według ilości sprzedaży. Do tego można użyć kombinacji PARTITION BY i funkcji RANK():

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

Wynik:

Funkcja RANK() nadaje ranking w każdej grupie region, zaczynając od 1. Zwróć uwagę, że dla każdej grupy rankingi zaczynają się od jedynki.

Przykład 2: Porównanie każdej wartości ze średnią w grupie

Załóżmy, że chcemy zobaczyć, ile każdy sprzedawca zarobił w porównaniu do średniej w swoim regionie. Użyjemy AVG():

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

Wynik:

Najpierw SQL dzieli wiersze na grupy według region. Potem liczy średnią AVG(amount) dla każdej grupy. Na końcu dla każdego wiersza liczy różnicę między jego wartością a średnią.

Przykład 3: Numerowanie wierszy w grupie

Powiedzmy, że chcesz ponumerować wszystkie transakcje w każdej grupie regionu. Użyjemy ROW_NUMBER():

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

Wynik:

Porównanie z GROUP BY

Często pojawia się zamieszanie między PARTITION BY a GROUP BY. Porównajmy je:

GROUP BY

GROUP BY zmienia strukturę wyniku — zamienia wiersze tabeli na agregaty. Na przykład:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Wynik:

Tutaj tracimy info o sprzedawcach, bo dane są agregowane.

PARTITION BY

PARTITION BY z kolei nie zmienia struktury. Nadal widzimy każdy wiersz, ale mamy dodatkowe wartości policzone według grup. Czyli PARTITION BY pozwala agregować bez utraty szczegółów.

Częste błędy przy użyciu PARTITION BY

Błąd 1: Zapomniano o PARTITION BY

Czasem chcesz grupować dane, ale zapominasz użyć PARTITION BY. Na przykład:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

Wynik:

Tutaj SUM(amount) jest liczona dla całej tabeli, a nie osobno dla każdego regionu. Jeśli chcesz uwzględnić regiony, nie zapomnij dodać PARTITION BY region.

Błąd 2: Zła kolejność w ORDER BY

Kolejność wierszy w oknie jest ważna dla funkcji takich jak RANK() czy ROW_NUMBER(). Bądź uważny, kiedy używasz ORDER BY w środku OVER().