/* Extensii SQL pentru procesari analitice in Oracle – ferestre de calcul */

/* EXEMPLE */
-- Exemplu cu totaluri cumulative intr-o fereastra cu offset logic
-- Afisati salariul angajatilor precum si suma salariilor de la primul salariu pana la salariul curent,
-- ordonati crescator dupa salariu.
SELECT ename, sal,
    SUM(sal) OVER (ORDER BY sal RANGE UNBOUNDED PRECEDING) AS SalCumul_logic
FROM emp;
-- fereastra de calcul este: [salariu_angajat_1, salariu_angajat_2, ...,  salariu_angajat_curent]

-- Exemplu cu functii analitce de mutare a liniilor intr-o fereastra cu offset logic
-- Afisati salariul angajatilor precum si media salariului angajatului curent si a celor precedenti
-- care au cu cel mult 200 salariul mai mic, ordonati crescator dupa salariu.
SELECT ename, sal,
    ROUND(AVG(sal) OVER (ORDER BY sal RANGE 200 PRECEDING), 2) AS avgsal_range
FROM emp;
-- fereastra de calcul este: [sal-200, sal]

-- Exemplu de calcul pentru fereastra centrata de date
-- Afisati suma salariilor pentru o fereastra de plus minus 100 la salariu.
SELECT ename, hiredate, sal,
    SUM(sal) OVER (ORDER BY sal RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) sumsal_center
FROM emp;
-- fereastra de calcul este: [sal-100, sal+100]

-- Fereastra de date cu limita mobila (dinamica)
-- Afisati angajatii, salariile si suma salariilor pentru un interval variabil definit de un offset logic
-- dat de o functie utilizator fn

-- Functia fn intoarce numarul de angajati pentru un departament
CREATE OR REPLACE FUNCTION fn(dno NUMBER) RETURN NUMBER
IS
    Result NUMBER;
    res NUMBER;
BEGIN
    SELECT COUNT(*) - 1
    INTO res
    FROM emp
    WHERE deptno = dno;

    Result := res;
    RETURN(Result);

    EXCEPTION
        WHEN OTHERS THEN
            Result := 0;
END fn;

SELECT deptno, ename, sal, fn(deptno),
    SUM(sal) OVER (ORDER BY deptno RANGE fn(deptno) PRECEDING) sumsal_dept
FROM emp;

-- offset fizic = inregistrari / offset logic = deplasare pe functia cumulativa
-- adica daca sunt mai multi angajati cu acelasi salariu, offset-ul logic considera offset 1,
-- iar cel fizic le ia pe toate in considerare ca si inregistrari separate
-- Exemplu cu totaluri cumulative intr-o fereastra cu offset fizic
-- Afisati salariul angajatilor precum si suma salariilor de la primul salariu pana la salariul curent,
-- ordonati crescator dupa salariu.
SELECT ename, sal,
    SUM(sal) OVER (ORDER BY sal ROWS UNBOUNDED PRECEDING) AS SalCumul_fizic
FROM emp;

-- Exemplu fereastra centrata pentru offset fizic
-- Afisati angajatii si suma salariilor pentru intervalul fizic de plus minus 3 angajati, ordonati
-- dupa salarii.
SELECT ename, sal,
    SUM(sal) OVER (ORDER BY sal ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) sum3_fizic
FROM emp;

-- Fereastra de date cu limita mobila (dinamica) si ofs.amount_sold, fset fizic
-- Afisati angajatii, salariile si suma salariilor pentru un interval variabil, definit de un offset fizic
-- dat de o functie utilizator fn.
SELECT deptno, ename, sal, fn(deptno),
    SUM(sal) OVER (ORDER BY deptno ROWS fn(deptno) PRECEDING) sumsal_dept_fizic
FROM emp;
-- Fereastra este definita de la un punct de start pana la linia curenta. Punctul de start este
-- variabil si este dat de numarul de linii fizice precedente calculate de functia utilizator fn.

-- Exemplu de folosire a functiilor pentru aflarea valorii minime si maxime intr-o fereastra
-- de calcul
-- Afisati angajatii, salariile, salariul minim si salariul maxim pentru o fereastra de calcul cu offset
-- fizic de plus minus 3 angajati, ordonati dupa salariu.
SELECT ename, sal,
    FIRST_VALUE(sal) OVER (ORDER BY sal ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) firstval,
    LAST_VALUE(sal) OVER (ORDER BY SAL ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) lastval
FROM emp;

-- Exemplu de folosire a functiilor LAG/LEAD - pentru accesul la inregistrari din vecinatate
-- Afisati angajatii, salariile, precum si salariile precedentilor 2 si urmatorilor 2 angajati.
SELECT ename, sal,
    LAG(sal, 2) OVER (ORDER BY sal) lagval,
    LEAD(sal, 2) OVER (ORDER BY sal) leadval
FROM emp;

-- Exemplu de utilizare CASE
-- Afisati numele, salariul si gradul salariatului, unde gradul se calculeaza astfel: 1 pentru salariu <
-- 1000, 2 pentru [1000,2000), 3 pentru [2000,3000) si 4 pentru salariu mai mare de 4000. Ordonati
-- dupa grad si salariu.
SELECT ename, sal,
    (CASE WHEN e.sal BETWEEN 0 AND 1000 THEN 1
        WHEN e.sal BETWEEN 1000 AND 2000 THEN 2
        WHEN e.sal BETWEEN 2000 AND 3000 THEN 3
        ELSE 4
    END) grad
FROM emp e
ORDER BY grad, sal;

/* EXERCITII */
-- 1
SELECT ename, hiredate, sal,
    SUM(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) AS sumsal_1_month,
    -- pentru ca intervalul e lunar si nu numeric, se foloseste "RANGE BETWEEN INTERVAL x DAY / MONTH / YEAR"
    -- offset logic = RANGE
    FIRST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS maxsal,
    -- valoarea maxima din setul de inregistrari din fereastra = FIRST_VALUE()
    LAST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS minsal
    -- valoarea minima din setul de inregistrari din fereastra = LAST_VALUE()
FROM emp;

-- 2
SELECT deptno, ename, sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset fizic = ROWS
    -- pana la angajatul curent = de la inceput (UNBOUNDED PRECEDING), pana la inregistrarea curent, implicit
FROM emp;

SELECT deptno, ename, sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset logic = RANGE
FROM emp;

-- 3
SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
    SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_job -- cu offset fizic = ROWS
FROM emp;

SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
    SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_job -- cu offset logic = RANGE
FROM emp;

-- 4
CREATE OR REPLACE FUNCTION fn(dno NUMBER) RETURN NUMBER -- functia fn() din laborator
IS
    Result NUMBER;
    res NUMBER;
BEGIN
    SELECT COUNT(*) - 1
    INTO res
    FROM emp
    WHERE deptno = dno;

    Result := res;
    RETURN(Result);

    EXCEPTION
        WHEN OTHERS THEN
            Result := 0;
END fn;

SELECT deptno, ename, sal, fn(deptno),
    SUM(sal) OVER (ORDER BY deptno, sal ROWS BETWEEN CURRENT ROW AND fn(deptno) FOLLOWING) AS sumsal_dept
    -- ROWS = offset fizic, nu folosim offset logic pentru ca in enunt se specifica "linia curenta" si nu "valoarea curenta"
    -- CURRENT ROW = linia curenta
    -- fn(deptno) FOLLOWING = urmatoarele x linii, x = valoarea intoarsa de fn()
FROM emp;

-- schimbam situatia pentru partitionare pe job, modificam functia fn()
CREATE OR REPLACE FUNCTION fnjob(v_job emp.job%TYPE) RETURN NUMBER
IS
    Result NUMBER;
    res NUMBER;
BEGIN
    SELECT COUNT(*) - 1
    INTO res
    FROM emp
    WHERE job = v_job;

    Result := res;
    RETURN(Result);

    EXCEPTION
        WHEN OTHERS THEN
            Result := 0;
END fnjob;

SELECT job, ename, sal, fnjob(job),
    SUM(sal) OVER (ORDER BY job, sal ROWS BETWEEN CURRENT ROW AND fnjob(job) FOLLOWING) AS sumsal_job
    -- acelasi lucru, doar schimbam deptno cu job
FROM emp;

-- 5
SELECT job, ename, sal AS "Salariu curent",
    LAG(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu anterior",
    -- LAG(sal, 1) = valoarea campului "sal" de la "1" inregistrari anterioare inregistrarii curente
    LEAD(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu urmator"
    -- LEAD(sal, 1) = valoarea campului "sal" de la "1" inregistrari posterioare inregistrarii curente
FROM emp;

-- 6
SELECT COUNT(empno) AS "Numar total angajati",
    SUM(CASE WHEN sal BETWEEN 0 AND 1000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 0 si 1000",
    SUM(CASE WHEN sal BETWEEN 1000 AND 2000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 1000 si 2000",
    SUM(CASE WHEN sal BETWEEN 2000 AND 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 2000 si 3000",
    SUM(CASE WHEN sal > 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul peste 3000"
    -- SUM(CASE...) numara cate valori ale campului "sal" se incadreaza in conditiile din CASE WHEN
FROM emp;

-- 7
SELECT cust.cust_first_name AS "Client", t.calendar_quarter_number AS "Trimestru",
    SUM(s.amount_sold) AS "Suma vanzarilor pe trimestrul curent",
    SUM(SUM(s.amount_sold)) OVER (PARTITION BY cust.cust_first_name ORDER BY t.calendar_quarter_number ROWS UNBOUNDED PRECEDING) AS "Suma vanzarilor anterioare"
    -- partitionarea se face in functie de client, deoarece clientul este folosit ca referinta in calcule: client X a vandut cantitatea Y in trimestrul Z
    -- pentru un client fixat, se ordoneaza inregistrarile dupa trimestru, pentru ca se calculeaza suma vanzarilor pe trimestre
    -- ROWS UNBOUNDED PRECEDING = inregistrarile anterioare, de la inceput pana la cea curenta (offset fizic)

    -- explicatia pentru SUM(SUM(...)): ni se cere sa afisam suma vanzarilor anterioare, pentru fiecare trimestru afisat
    -- vanzarile pentru un anumit trimestru se obtin prin a calcula SUM(amount_sold), deci prin a suma tranzactiile efectuate de un anumit client
    -- deci, suma vanzarilor anterioare se calculeaza prin adunarea tuturor vanzarilor trimestrelor anterioare, deci prin a suma sumele facute anterior, deci SUM(SUM(...))
FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN times t ON (s.time_id = t.time_id)
WHERE s.cust_id IN (6380, 6510) -- filtrarile cerute
    AND t.calendar_year = 1999
GROUP BY cust.cust_first_name, t.calendar_quarter_number -- grupare si ordonare dupa cum se cere in enunt, pentru ca folosim functia agregat SUM()
ORDER BY cust.cust_first_name, t.calendar_quarter_number;

-- 8
SELECT cust.cust_first_name, t.calendar_month_number,
    SUM(s.amount_sold) AS "Suma vanzari pe luna",
    ROUND(AVG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_number RANGE 2 PRECEDING), 2) AS "Media vanzarilor anterioare"
    -- ordonarea se face dupa luna, iar fereastra cuprinde si 2 luni anterioare -> atentie, 2 luni, nu 2 inregistrari!
    -- deci se iau in calcul 2 valori anterioare, de aceea folosim offset logic (RANGE) si nu fizic (ROWS)
    -- din cauza offset-ului logic, pot exista 4 inregistrari care fac referire la aceleasi 2 luni si sunt luate in calcul ca facand parte din fereastra, desi sunt in numar de 4

    -- AVG(SUM(...)) -> pentru ca se cere media vanzarilor anterioare, deci media unei sume calculate anterior, de aici AVG(SUM(...))
FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN times t ON (s.time_id = t.time_id)
WHERE s.cust_id = 6380
    AND t.calendar_year = 1999
GROUP BY cust.cust_first_name, t.calendar_month_number
ORDER BY cust.cust_first_name, t.calendar_month_number;

-- 9
SELECT cust.cust_first_name, t.time_id,
    SUM(s.amount_sold) AS "Suma vanzari pe zi",
    ROUND(AVG(SUM(s.amount_sold)) OVER (ORDER BY t.time_id RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING), 2) AS "Media sumei vanzarilor pe 3 zile"
    -- aceeasi logica precum la 7 si la 8 -> media vanzarilor pe o fereastra de 3 zile = medie de suma
    -- offset logic din acelasi motiv, zilele se pot repeta in inregistrari si trebuie luate in considerare
    -- la ferestre cu date calendaristice se foloseste BETWEEN INTERVAL
FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN times t ON (s.time_id = t.time_id)
WHERE s.cust_id IN (6380, 6510)
    AND t.calendar_year = 1999
    AND t.calendar_week_number = 51
GROUP BY cust.cust_first_name, t.time_id
ORDER BY cust.cust_first_name, t.time_id;

-- 10
SELECT t.day_number_in_month, SUM(s.amount_sold) AS "Suma vanzarilor pe ziua curenta",
    LAG(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia anterioara",
    -- pentru valoarea de pe linia anterioara, folosim functia LAG() peste setul ordonat de inregistrari dupa suma vanzarilor
    LEAD(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia urmatoare"
    -- pentru valoarea de pe linia anterioara, folosim functia LEAD() peste setul ordonat de inregistrari dupa suma vanzarilor
FROM sales s JOIN times t ON (s.time_id = t.time_id)
WHERE t.calendar_month_desc = '2000-10'
    AND t.day_number_in_month BETWEEN 10 AND 15 -- intre 10 si 15 octombrie 2000
GROUP BY t.day_number_in_month;