📈 Funkcje Analityczne

funkcja_analityczna() OVER (
    [PARTITION BY kolumny]     -- podział na grupy (opcjonalny)
    [ORDER BY kolumny]         -- kolejność w ramach grupy
    [ROWS/RANGE BETWEEN ...]   -- ramka okna (opcjonalna)
)

SELECT first_name, last_name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM hr.employees WHERE department_id = 60;

-- TOP 3 zarabiający w każdym dziale (RANK - z lukami):
SELECT * FROM (
    SELECT first_name, last_name, salary, department_id,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM hr.employees
)
WHERE rnk <= 3;

-- Podziel pracowników na 4 grupy (kwartyle) wg pensji
SELECT first_name, last_name, salary,
       NTILE(4) OVER (ORDER BY salary) AS kwartal
FROM hr.employees;
-- Wynik: każdy pracownik dostaje numer 1, 2, 3 lub 4

SELECT first_name, salary,
    CUME_DIST()    OVER (ORDER BY salary) AS cume_dist,   -- frakcja [0,1] <= wartość
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank     -- frakcja [0,1] bez bieżącej
FROM hr.employees;

-- Pracownicy w górnych 20% pensji:
SELECT first_name, salary FROM (
    SELECT first_name, salary,
           CUME_DIST() OVER (ORDER BY salary) AS cd
    FROM hr.employees
)
WHERE cd >= 0.8;

-- LAG(kolumna, offset, default) — poprzedni wiersz
-- LEAD(kolumna, offset, default) — następny wiersz
SELECT employee_id, hire_date, salary,
    LAG(salary, 1, 0)  OVER (ORDER BY hire_date) AS poprzednia_pensja,
    LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS nastepna_pensja,
    salary - LAG(salary, 1, salary) OVER (ORDER BY hire_date) AS roznica
FROM hr.employees;

-- Suma bieżąca (running total):
SELECT hire_date, salary,
    SUM(salary) OVER (ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS suma_biezaca
FROM hr.employees;

-- Średnia krocząca 3-miesięczna:
SUM(wartosc) OVER (ORDER BY data ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Dostępne zakresy:
-- UNBOUNDED PRECEDING  — od początku partycji
-- N PRECEDING          — N wierszy wstecz
-- CURRENT ROW
-- N FOLLOWING          — N wierszy naprzód
-- UNBOUNDED FOLLOWING  — do końca partycji

-- Najwyższa pensja w dziale (dla każdego wiersza)
SELECT first_name, salary, department_id,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_w_dziale,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS min_w_dziale
FROM hr.employees;

-- Udział pensji pracownika w sumie pensji działu
SELECT first_name, salary, department_id,
    RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS udzial
FROM hr.employees;
-- Wynik: liczba z zakresu [0, 1], suma w grupie = 1.0

-- Podziel pensje na 5 równych przedziałów (2000–15000)
SELECT first_name, salary,
    WIDTH_BUCKET(salary, 2000, 15000, 5) AS bucket
FROM hr.employees;
-- bucket 0 = poniżej min, bucket 6 = powyżej max
-- bucket 1 = [2000, 4600), bucket 2 = [4600, 7200), itd.

-- Jaką pozycję miałby pracownik z pensją 10000?
SELECT RANK(10000) WITHIN GROUP (ORDER BY salary) AS pozycja
FROM hr.employees;
-- Wynik: np. 85 — 84 pracowników zarabia mniej niż 10000

-- DENSE_RANK — bez luk w rankingu:
SELECT DENSE_RANK(10000) WITHIN GROUP (ORDER BY salary) AS dense_poz
FROM hr.employees;

-- PERCENT_RANK — ile procent wyników jest poniżej 10000?
SELECT PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary) AS pct
FROM hr.employees;

-- CUME_DIST — ile procent wyników jest <= 10000?
SELECT CUME_DIST(10000) WITHIN GROUP (ORDER BY salary) AS cd
FROM hr.employees;

-- Na zagregowanych danych — na której pozycji byłby dział z sumą 50000?
SELECT RANK(50000) WITHIN GROUP (ORDER BY SUM(salary)) AS pozycja_dzialu,
       PERCENT_RANK(50000) WITHIN GROUP (ORDER BY SUM(salary)) AS procent
FROM hr.employees
GROUP BY department_id;

-- Mediana pensji (percentyl 50%):
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS mediana
FROM hr.employees;

-- Percentyl 90%:
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90
FROM hr.employees;

-- LISTAGG — agregacja do stringa:
SELECT department_id,
    LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS pracownicy
FROM hr.employees
GROUP BY department_id;

-- Globalna suma sprzedaży (jeden wiersz na cały wynik):
SELECT channel_id, SUM(amount_sold) AS sprzedaz,
    SUM(SUM(amount_sold)) OVER () AS suma_globalna
FROM sh.sales
GROUP BY channel_id;

-- Udział kanału w globalnej sprzedaży:
SELECT channel_id, SUM(amount_sold) AS sprzedaz,
    ROUND(SUM(amount_sold) / SUM(SUM(amount_sold)) OVER () * 100, 2) AS udzial_pct
FROM sh.sales
GROUP BY channel_id
ORDER BY sprzedaz DESC;

-- Ranking kanałów wg sprzedaży + suma bieżąca (od największego):
SELECT channel_id,
    SUM(amount_sold) AS sprzedaz,
    RANK() OVER (ORDER BY SUM(amount_sold) DESC) AS rank_kanal,
    SUM(SUM(amount_sold)) OVER (ORDER BY SUM(amount_sold) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS suma_biezaca
FROM sh.sales
GROUP BY channel_id
ORDER BY sprzedaz DESC;

-- TOP 5 produktów globalnie wg przychodów:
SELECT prod_id, sprzedaz, rn FROM (
    SELECT prod_id,
        SUM(amount_sold) AS sprzedaz,
        ROW_NUMBER() OVER (ORDER BY SUM(amount_sold) DESC) AS rn
    FROM sh.sales
    GROUP BY prod_id
)
WHERE rn <= 5;

-- TOP 5 produktów w każdym kanale sprzedaży:
SELECT * FROM (
    SELECT s.prod_id, s.channel_id,
        SUM(s.amount_sold) AS sprzedaz,
        RANK() OVER (PARTITION BY s.channel_id
                     ORDER BY SUM(s.amount_sold) DESC) AS rank_w_kanale
    FROM sh.sales s
    GROUP BY s.prod_id, s.channel_id
)
WHERE rank_w_kanale <= 5
ORDER BY channel_id, rank_w_kanale;

-- Sprzedaż miesięczna 2001: zmiana vs poprzedni miesiąc:
SELECT t.calendar_month_number AS miesiac,
    SUM(s.amount_sold) AS sprzedaz,
    LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_number) AS poprzedni_mies,
    SUM(s.amount_sold) - LAG(SUM(s.amount_sold), 1, 0)
        OVER (ORDER BY t.calendar_month_number) AS zmiana
FROM sh.sales s
JOIN sh.times t ON s.time_id = t.time_id
WHERE t.calendar_year = 2001
GROUP BY t.calendar_month_number
ORDER BY t.calendar_month_number;

-- Każdy produkt vs średnia sprzedaż w jego kategorii:
SELECT p.prod_name, p.prod_category,
    SUM(s.amount_sold) AS sprzedaz,
    AVG(SUM(s.amount_sold)) OVER (PARTITION BY p.prod_category) AS sr_w_kat,
    SUM(s.amount_sold) - AVG(SUM(s.amount_sold))
        OVER (PARTITION BY p.prod_category) AS odchylenie
FROM sh.sales s
JOIN sh.products p ON s.prod_id = p.prod_id
GROUP BY p.prod_name, p.prod_category
ORDER BY p.prod_category, sprzedaz DESC;

-- Udział każdego kanału w całkowitej sprzedaży:
SELECT channel_id,
    SUM(amount_sold) AS sprzedaz,
    ROUND(RATIO_TO_REPORT(SUM(amount_sold)) OVER () * 100, 2) AS udzial_pct
FROM sh.sales
GROUP BY channel_id
ORDER BY sprzedaz DESC;

-- TOP 5 produktów wg sprzedaży w każdej kategorii (z nazwami):
SELECT * FROM (
    SELECT p.prod_category, p.prod_name,
           SUM(s.amount_sold) AS sprzedaz,
           RANK() OVER (PARTITION BY p.prod_category
                        ORDER BY SUM(s.amount_sold) DESC) AS rnk
    FROM sh.sales s
    JOIN sh.products p ON s.prod_id = p.prod_id
    GROUP BY p.prod_category, p.prod_name
)
WHERE rnk <= 5
ORDER BY prod_category, rnk;

-- Ranking produktów w kanale z nazwą kanału:
SELECT * FROM (
    SELECT ch.channel_desc, p.prod_name,
           SUM(s.amount_sold) AS sprzedaz,
           RANK() OVER (PARTITION BY s.channel_id
                        ORDER BY SUM(s.amount_sold) DESC) AS rank_w_kanale
    FROM sh.sales s
    JOIN sh.products p   ON s.prod_id   = p.prod_id
    JOIN sh.channels ch  ON s.channel_id = ch.channel_id
    GROUP BY ch.channel_desc, p.prod_name, s.channel_id
)
WHERE rank_w_kanale <= 3;

-- Podziel klientów na 4 kwartyle wg sumy zakupów:
SELECT cust_id,
    SUM(amount_sold) AS suma_zakupow,
    NTILE(4) OVER (ORDER BY SUM(amount_sold) DESC) AS segment
FROM sh.sales
GROUP BY cust_id
ORDER BY suma_zakupow DESC;

-- Średnia sprzedaż w 5 segmentach per miasto (z joinami):
SELECT c.cust_city, seg, AVG(suma_zakupow) AS sr_w_segmencie FROM (
    SELECT s.cust_id, c.cust_city,
        SUM(s.amount_sold) AS suma_zakupow,
        NTILE(5) OVER (PARTITION BY c.cust_city
                       ORDER BY SUM(s.amount_sold) DESC) AS seg
    FROM sh.sales s
    JOIN sh.customers c ON s.cust_id = c.cust_id
    GROUP BY s.cust_id, c.cust_city
)
GROUP BY cust_city, seg
ORDER BY cust_city, seg;

-- Średnia krocząca 7 dni (7 poprzednich wierszy):
SELECT time_id, SUM(amount_sold) AS dzienna_sprzedaz,
    ROUND(AVG(SUM(amount_sold)) OVER (
        ORDER BY time_id
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS srednia_7_dni
FROM sh.sales
GROUP BY time_id
ORDER BY time_id;

-- Średnia krocząca 7 dni wg wartości daty (RANGE):
SELECT time_id, SUM(amount_sold) AS dzienna_sprzedaz,
    ROUND(AVG(SUM(amount_sold)) OVER (
        ORDER BY time_id
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ), 2) AS srednia_zakres_7d
FROM sh.sales
GROUP BY time_id
ORDER BY time_id;

-- Sprzedaż w każdym roku vs rok z najwyższą i najniższą sprzedażą:
SELECT t.calendar_year AS rok,
    SUM(s.amount_sold) AS sprzedaz,
    FIRST_VALUE(SUM(s.amount_sold)) OVER (
        ORDER BY SUM(s.amount_sold) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_rok_sprzedaz,
    LAST_VALUE(SUM(s.amount_sold)) OVER (
        ORDER BY SUM(s.amount_sold) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS min_rok_sprzedaz
FROM sh.sales s
JOIN sh.times t ON s.time_id = t.time_id
GROUP BY t.calendar_year
ORDER BY rok;

-- Sprzedaż roczna z trendem i rankingiem:
SELECT t.calendar_year AS rok,
    SUM(s.amount_sold) AS sprzedaz,
    LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_year) AS rok_poprzedni,
    SUM(s.amount_sold) - LAG(SUM(s.amount_sold), 1, 0)
        OVER (ORDER BY t.calendar_year) AS zmiana_rdr,
    RANK() OVER (ORDER BY SUM(s.amount_sold) DESC) AS rank_roku
FROM sh.sales s
JOIN sh.times t ON s.time_id = t.time_id
GROUP BY t.calendar_year
ORDER BY rok;

-- Które produkty odpowiadają za 80% przychodów?
SELECT prod_id, sprzedaz, suma_biezaca, suma_total,
    ROUND(suma_biezaca / suma_total * 100, 2) AS procent_skumulowany
FROM (
    SELECT prod_id,
        SUM(amount_sold) AS sprzedaz,
        SUM(SUM(amount_sold)) OVER (ORDER BY SUM(amount_sold) DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS suma_biezaca,
        SUM(SUM(amount_sold)) OVER () AS suma_total
    FROM sh.sales
    GROUP BY prod_id
)
WHERE suma_biezaca / suma_total <= 0.8   -- pierwsze 80% przychodów
ORDER BY sprzedaz DESC;

-- VIP klienci (top 10% wg wydatków):
SELECT cust_id, suma_zakupow, dr FROM (
    SELECT cust_id,
        SUM(amount_sold) AS suma_zakupow,
        DENSE_RANK() OVER (ORDER BY SUM(amount_sold) DESC) AS dr
    FROM sh.sales
    GROUP BY cust_id
)
WHERE dr <= (SELECT COUNT(DISTINCT cust_id) * 0.1 FROM sh.sales);

-- Efektywność kanału: sprzedaż vs koszty:
SELECT s.channel_id,
    SUM(s.amount_sold) AS przychod,
    SUM(c.unit_cost * s.quantity_sold) AS koszt,
    SUM(s.amount_sold) - SUM(c.unit_cost * s.quantity_sold) AS zysk,
    RANK() OVER (ORDER BY SUM(s.amount_sold) -
        SUM(c.unit_cost * s.quantity_sold) DESC) AS rank_zysku
FROM sh.sales s
JOIN sh.costs c ON s.prod_id = c.prod_id AND s.time_id = c.time_id
                AND s.channel_id = c.channel_id
GROUP BY s.channel_id
ORDER BY rank_zysku;