/* Extensii SQL pentru procesari analitice in Oracle */

/* EXEMPLE */
-- FUNCTIILE RANK() SI DENSE_RANK()
-- clasamentul angajatilor dupa salarii
SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) position
FROM emp;

SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) position
FROM emp;

-- clasificare pe partitii
-- afisarea angajatilor pe departamente
SELECT deptno, ename, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) posdept
FROM emp;

-- clasificare dupa expresii multiple
-- clasamentul salariatilor dupa salariu si dupa data angajarii
SELECT deptno, ename, sal, hiredate, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, hiredate ASC) posdept
FROM emp;

-- clasificare dupa partitii multiple
-- clasamentul angajatilor dupa salarii, clasament grupat dupa departament si dupa job
SELECT deptno, job, ename, sal,
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) posdept,
    RANK() OVER (PARTITION BY job ORDER BY sal DESC) posjob
FROM emp;

-- FUNCTIA CUME_DIST()
-- distributia salariilor angajatilor
SELECT ename, sal,
    ROUND(CUME_DIST() OVER (ORDER BY sal DESC), 2) cume_dist_sal
FROM emp;

-- distributia salariilor pe departamente
SELECT deptno, ename, sal,
    ROUND(CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal DESC), 2) cume_dist_sal_dept
FROM emp;

-- FUNCTIA PERCENT_RANK()
-- distributiile pozitionale ale salariilor pe departamente
SELECT deptno, ename, sal,
    ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC), 2) percent_rank_sal_dept
FROM emp;

-- FUNCTIILE NTILE() SI ROW_NUMBER()
-- salariile descrescator, impartite in 4 categorii
SELECT deptno, ename, sal,
    NTILE(4) OVER (ORDER BY sal DESC) ntile4
FROM emp;

-- salariile descrescator, partitionate dupa departament si impartite in cate 2 buchete
SELECT deptno, ename, sal,
    NTILE(2) OVER (PARTITION BY deptno ORDER BY sal DESC) ntile2_deptno
FROM emp;

-- salariile angajatilor partitionate dupa departamente, impreuna cu o coloana de numerotare
-- care se reseteaza dupa fiecare partitie
SELECT deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) nrcrt
FROM emp;

/* EXERCITII */
-- 1
SELECT d.dname, e.ename, e.hiredate, e.job,
    RANK() OVER (ORDER BY e.hiredate ASC) clasament -- pozitia se afla ordonand dupa data angajarii si calculand "RANK()"-ul
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- 2
SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal",
    ROUND(AVG(sal), 2) AS "AvgSal",
    RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept -- pozitia in cadrul departamentului
    -- folosim "PARTITION BY deptno" pentru a face clasamentul mediilor salariale pe departamente, si nu global
    -- adica "pozitie_dept" este relativa la numarul departamentului = campul DeptAvgSal
FROM emp
GROUP BY ROLLUP(deptno, job); -- linii totalizatoare pentru departament si job

-- 3
SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal",
    ROUND(AVG(sal), 2) AS "AvgSal",
    RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept,
    RANK() OVER (PARTITION BY job ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_job -- pozitia in cadrul job-ului
    -- partitionam dupa job, iar pentru fiecare "job" fixat, se ordoneaza mediile salariale si se calculeaza pozitia in ierarhie
FROM emp
GROUP BY ROLLUP(deptno, job);

-- 4
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
        -- ordonam dupa venit (salariu + comision) si calculam DENSE_RANK(), si nu RANK()
        -- pentru ca vrem ca duplicatele sa fie tratate ca aflandu-se pe acelasi nivel ierarhic
        -- (SCOTT si FORD sunt pe acelasi loc, nu ocupa 2 locuri diferite in ierarhie)
        ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
        FROM emp)
-- dorim primele 10 inregistrari: folosim o subinterogare, din care preluam tot, cu conditia ca pozitia calculata sa fie <= 10
-- nu avem cum folosi conditia din WHERE-ul exterior in SELECT-ul din interior, deoarece alias-ul "pozitie_venit" nu este cunoscut decat in exterior!
WHERE pozitie_venit <= 10;

-- 5
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
        ROUND(PERCENT_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC), 2) distributie_venit,
        -- distributia veniturilor = functia PERCENT_RANK() in loc de RANK() / DENSE_RANK()
        ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
        FROM emp)
WHERE pozitie_venit <= 10;

-- 6
SELECT NTILE(4) OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) nr_crt, -- numarul buchetului
    -- impartim in 4 buchete folosind functia NTILE()
    DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit, -- pozitia nivelului de venit
    ename, sal, sal + NVL(comm, 0) * sal AS "Venit"
FROM emp;

-- 7
SELECT ch.channel_desc, SUM(s.amount_sold) AS "Vanzari canal de distributie",
    RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    -- JOIN-uri pentru a afla informatiile suplimentare necesare la filtrare si afisare
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
WHERE t.calendar_month_desc IN ('2000-09', '2000-10') -- filtrarile din enunt
    AND cust.country_id = 'US'
GROUP BY ch.channel_desc; -- vanzarile trebuie grupate pe canale de distributie

-- 8
SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie
    -- partitionam dupa canalul de distributie, apoi pentru fiecare canal fixat, ordonam dupa valoarea vanzarilor ca sa aflam pozitia relativa
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
GROUP BY ch.channel_desc, t.calendar_month_desc;

-- 9
SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie,
    RANK() OVER(PARTITION BY t.calendar_month_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_luna
    -- pozitia in cadrul lunii = partitionam dupa luna si ordonam partitiile rezultate dupa valoarea vanzarilor, pentru a afla pozitia relativa din luna respectiva
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
GROUP BY ch.channel_desc, t.calendar_month_desc;

-- 10
SELECT ch.channel_desc, cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_grup
    -- pozitionare in cadrul canalului de distributie = facem partitionare dupa canalul de distributie
    -- si pentru fiecare inregistrare pentru un canal fixat se calculeaza rangul in acea partitie
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- filtrarile cerute
    AND t.calendar_month_desc = '2000-09'
    AND cust.country_id IN ('UK', 'US', 'JP')
GROUP BY CUBE(ch.channel_desc, cust.country_id);
    -- totalul vanzarilor pe canalul de distributie si pe tara + linii de subtotaluri pentru combinarea celor 2 dimensiuni
    -- CUBE() in loc de ROLLUP() pentru ca trebuie toate combinatiile intre dimensiuni

-- 11
SELECT *
FROM (
    SELECT cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
        RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
        -- top vanzari = ordonare descrescatoare in functie de suma valorilor vanzarilor
        -- apoi calcul de rang pe aceasta ordonare
    FROM sales s JOIN times t ON (s.time_id = t.time_id)
        JOIN customers cust ON (s.cust_id = cust.cust_id)
    WHERE t.calendar_month_desc = '2000-09'
    GROUP BY cust.country_id -- trebuie grupate rezultatele pentru ca folosim functia agregat SUM(),
        -- iar afisarea trebuie facuta in functie de tara
    -- din interogarea interioara rezulta o lista cu toate tarile ordonate dupa vanzari
    -- nu putem afisa doar primele 5 inregistrari in acest SELECT, deoarece alias-ul "pozitie" nu este vizibil in acelasi
    -- SELECT in care este creat
)
-- se face un SELECT exterior din tabelul rezultat, din care selectam toate coloanele si filtram dupa coloana alias "pozitie",
-- care devine vizibila de aceasta data
WHERE pozitie <= 5;

-- 12
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
    NTILE(4) OVER (ORDER BY SUM(s.amount_sold) DESC) pozitie_buchet
    -- impartire in 4 buchete = functia NTILE(), dupa suma vanzarilor = ordonare dupa SUM(...)
FROM sales s JOIN times t ON (s.time_id = t.time_id)
    JOIN products p ON (s.prod_id = p.prod_id)
WHERE t.calendar_year = '1999' -- filtrarile cerute
    AND p.prod_category = 'Men'
GROUP BY t.calendar_month_desc; -- se grupeaza din acelasi motiv ca la 11, folosim o functie agregat SUM()