Teraz jesteś gotowy, żeby zanurzyć się w świat praktycznych przykładów i zobaczyć, jak to wszystko działa w realnych zadaniach!
Przykład: obliczanie rangi sprzedaży według regionów
Wyobraź sobie, że mamy tabelę sales, która zawiera dane o sprzedaży w różnych regionach. Musimy określić rangi sprzedaży dla każdego regionu.
| id | region | sales_amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | North | 3000 |
| 3 | North | 7000 |
| 4 | South | 2000 |
| 5 | South | 4000 |
| 6 | East | 8000 |
| 7 | East | 6000 |
Zadanie: znajdź rangę (RANK) sprzedaży dla każdego regionu
SELECT
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;
Wynik:
| region | sales_amount | sales_rank |
|---|---|---|
| North | 7000 | 1 |
| North | 5000 | 2 |
| North | 3000 | 3 |
| South | 4000 | 1 |
| South | 2000 | 2 |
| East | 8000 | 1 |
| East | 6000 | 2 |
Zwróć uwagę, że użyliśmy PARTITION BY region, żeby policzyć rangi osobno dla każdego regionu. Gdybyśmy nie użyli PARTITION BY, ranga byłaby liczona globalnie dla całej tabeli.
Przykład: obliczanie sumy skumulowanej przychodu
Teraz ogarniemy tabelę transactions, żeby policzyć skumulowaną sumę przychodu dla każdego klienta.
| id | customer_id | purchase_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 100 |
| 2 | 101 | 2023-01-03 | 50 |
| 3 | 102 | 2023-01-02 | 200 |
| 4 | 101 | 2023-01-05 | 150 |
| 5 | 102 | 2023-01-04 | 100 |
Zadanie: policz sumę skumulowaną dla każdego klienta
SELECT
customer_id,
purchase_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM
transactions;
Wynik:
| customer_id | purchase_date | amount | cumulative_sum |
|---|---|---|---|
| 101 | 2023-01-01 | 100 | 100 |
| 101 | 2023-01-03 | 50 | 150 |
| 101 | 2023-01-05 | 150 | 300 |
| 102 | 2023-01-02 | 200 | 200 |
| 102 | 2023-01-04 | 100 | 300 |
Tu kluczowy moment — użycie ORDER BY purchase_date wewnątrz OVER(), żeby suma skumulowana była liczona w kolejności chronologicznej.
Przykład: podział danych na kwartyle
Wyobraź sobie, że mamy tabelę students, gdzie są imiona i wyniki testów. Chcemy podzielić uczniów na 4 grupy według ich wyników.
| id | name | test_score |
|---|---|---|
| 1 | Alice | 85 |
| 2 | Bob | 95 |
| 3 | Charlie | 75 |
| 4 | Diana | 88 |
| 5 | Edward | 65 |
| 6 | Fiona | 70 |
Zadanie: podziel studentów na 4 grupy używając NTILE()
SELECT
name,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM
students;
Wynik zapytania będzie taki:
| name | test_score | quartile |
|---|---|---|
| Bob | 95 | 1 |
| Diana | 88 | 1 |
| Alice | 85 | 2 |
| Charlie | 75 | 3 |
| Fiona | 70 | 3 |
| Edward | 65 | 4 |
NTILE(4) dzieli dane na 4 grupy. Studenci z najwyższymi wynikami trafiają do pierwszej grupy, z najniższymi — do ostatniej.
Przykład: analiza danych czasowych
W tabeli site_visits są dane o liczbie odwiedzin strony w poszczególne dni. Musimy policzyć różnicę w liczbie odwiedzin między dniami dla każdej strony.
| site_id | visit_date | visits |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 1 | 2023-01-02 | 120 |
| 1 | 2023-01-03 | 110 |
| 2 | 2023-01-01 | 50 |
| 2 | 2023-01-02 | 60 |
| 2 | 2023-01-03 | 70 |
Nasze zadanie — policzyć różnicę w odwiedzinach między dniami
SELECT
site_id,
visit_date,
visits,
visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM
site_visits;
Wynik:
| site_id | visit_date | visits | visit_diff |
|---|---|---|---|
| 1 | 2023-01-01 | 100 | NULL |
| 1 | 2023-01-02 | 120 | 20 |
| 1 | 2023-01-03 | 110 | -10 |
| 2 | 2023-01-01 | 50 | NULL |
| 2 | 2023-01-02 | 60 | 10 |
| 2 | 2023-01-03 | 70 | 10 |
Funkcja LAG() pozwala pobrać wartość z poprzedniego wiersza. Jeśli nie ma danych dla poprzedniego wiersza, wynik będzie NULL. Więcej o niej dowiesz się w kolejnych lekcjach :P
GO TO FULL VERSION