CTE i Window Funkcije

📖 Evolucija T-SQL Upita

Pisanje upita u 2025. godini se drastično razlikuje od onog prije 15 godina. Zamorne podupite (subqueries) i kursore zamijenili smo Common Table Expressions (CTE) i Window funkcijama. Ovi alati ne samo da čine kod čitljivijim, već omogućavaju SQL Serveru da bolje optimizuje plan izvršenja.

📑 Common Table Expressions (CTE): Privremeni Pogledi

CTE je privremeni set rezultata koji možete definisati unutar scope-a jednog SELECT, INSERT, UPDATE ili DELETE upita.

WITH EconomicData_CTE AS (
    -- Definišemo "virtuelnu tabelu"
    SELECT r.RegionName, SUM(ed.GDP) as TotalGDP
    FROM Stats.EconomicData ed
    INNER JOIN Stats.Regions r ON ed.RegionID = r.RegionID
    WHERE ed.Year = 2023
    GROUP BY r.RegionName
)
-- Koristimo je odmah ispod
SELECT * FROM EconomicData_CTE WHERE TotalGDP > 1000000000;

Rekurzivni CTE: Snaga Stabala

Ovo je jedini način da bez kursora prođete kroz hijerarhije (npr. menadžment struktura ili folderi).

WITH DepartmentHierarchy_CTE AS (
    -- Anchor member: Odakle počinjemo (odjeljenja bez parent-a)
    SELECT DepartmentID, DepartmentName, ManagerID, 0 AS Level
    FROM Stats.Departments WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member: Kako se povezujemo dalje (zaposlenici po odjeljenjima)
    SELECT d.DepartmentID, d.DepartmentName, d.ManagerID, dh.Level + 1
    FROM Stats.Departments d
    INNER JOIN Stats.Employees e ON d.ManagerID = e.EmployeeID
    INNER JOIN DepartmentHierarchy_CTE dh ON e.DepartmentID = dh.DepartmentID
)
SELECT * FROM DepartmentHierarchy_CTE ORDER BY Level;

🪟 Window Funkcije: Analitika u letu

Window funkcije omogućavaju da vršite agregacije (SUM, AVG) i rangiranje, a da pri tome ne gubite individualne redove (za razliku od GROUP BY).

1. Klauzula OVER(PARTITION BY ...)

PARTITION BY dijeli redove u grupe, a funkcija se primjenjuje na svaku grupu posebno.

SELECT 
    d.DepartmentName, 
    p.Budget,
    -- Prikazuje total budžeta po odjeljenju pored SVAKOG projekta
    SUM(p.Budget) OVER(PARTITION BY d.DepartmentName) as TotalBudgetByDepartment,
    -- Prikazuje procenat ovog projekta u odnosu na total budžet odjeljenja
    CAST(p.Budget * 100.0 / SUM(p.Budget) OVER(PARTITION BY d.DepartmentName) AS DECIMAL(5,2)) as PctOfTotal
FROM Stats.Projects p
INNER JOIN Stats.Departments d ON p.DepartmentID = d.DepartmentID;

2. Rangiranje: ROW_NUMBER, RANK, DENSE_RANK

3. Time Travel u redovima: LEAD i LAG

Ove funkcije vam omogućavaju da pristupite vrijednosti prethodnog ili sljedećeg reda.

SELECT 
    ed.Year, 
    ed.GDP,
    r.RegionName,
    -- Vrijednost GDP-a od PROŠLE GODINE za istu regiju
    LAG(ed.GDP) OVER(PARTITION BY ed.RegionID ORDER BY ed.Year) as PreviousGDP,
    -- Razlika u odnosu na prošlu godinu
    ed.GDP - LAG(ed.GDP) OVER(PARTITION BY ed.RegionID ORDER BY ed.Year) as GDPGrowth
FROM Stats.EconomicData ed
INNER JOIN Stats.Regions r ON ed.RegionID = r.RegionID;

🎯 Praktična Vježba: Detekcija Duplikata

WITH DuplicateFinder AS (
    SELECT 
        ReportDataID,
        ReportID,
        RegionID,
        CreatedDate,
        ROW_NUMBER() OVER(
            PARTITION BY ReportID, RegionID 
            ORDER BY CreatedDate DESC
        ) as RowNum
    FROM Stats.ReportData
)
-- Brišemo sve što nije broj 1 (najnoviji)
DELETE FROM Stats.ReportData 
WHERE ReportDataID IN (
    SELECT ReportDataID FROM DuplicateFinder WHERE RowNum > 1
);
GO

✅ Zaključak

CTE i Window funkcije su "švicarski nož" modernog SQL developera:

• ✅ CTE zamjenjuje kompleksne podupite i omogućava rekurziju.
• ✅ Window Funkcije rade analitiku bez agregacije redova.
• ✅ LEAD/LAG omogućavaju poređenje podataka kroz vrijeme bez kompleksnih JOIN-ova.
• ✅ Vaš kod je 5x čitljiviji i lakši za održavanje.