OVER příkaz můžeme v SQL zařadit do příkazů pracujících s tzv. Window functions. Tyto typy funkcí nám umožňují na data pohlížet nejen agregovaně (přes GROUP BY), ale můžeme se na funkční operace dívat přes více atributů => vlastně si vytváříme taková okénka a nad těmito ohraničenými daty aplikujeme různé výpočetní operace. Zní to složitě, ale na příkladu později uvidíte, že je to vlastně jednoduchá věc.
Příkaz můžeme aplikovat na tyto typy funkcí:
- Agregační funkce (Aggregate functions)
- Pořadníkové funkce (Ranking functions)
- Stránkovací funkce (Offset functions)
Praktický příklad OVER (PARTITION BY)
Zdroj dat bude vypadat takto:
--Tabulky se kterýmy pracujeme (Adventureworks)
SELECT c.CustomerKey, SalesOrderNumber, d.CalendarYear, SalesAmount
FROM FactInternetSales a
INNER JOIN DimCustomer c ON a.CustomerKey=c.CustomerKey
INNER JOIN DimDate d ON a.OrderDateKey=d.DateKey
WHERE a.CustomerKey IN (14718, 19383);
Zobrazujeme si data pro 2 zákazníky [CustomerKey] (číslo 14718 a 19383), tito zákazníci mají několik různých objednávek [SalesOrderNumber] a utržili jsme za ně peníze [SalesAmount].
Úkolem je napsat skript, který dá stejný výsledek jako na obrázku (7 řádků) a navíc přidá 2 sloupce:
- [CustomerTotal] – kolik jsme utržili za každého zákazníka
- [TotalSales] – kolik jsme utržili celkově
Řešení:
SELECT c.CustomerKey, SalesOrderNumber, d.CalendarYear, SalesAmount,
SUM(a.SalesAmount) OVER (PARTITION BY a.CustomerKey) AS Customertotal,
SUM(a.SalesAmount) OVER () TotalSales
FROM FactInternetSales a
INNER JOIN DimSalesTerritory b ON a.SalesTerritoryKey=b.SalesTerritoryKey
INNER JOIN DimCustomer c ON a.CustomerKey=c.CustomerKey
INNER JOIN DimDate d ON a.OrderDateKey=d.DateKey
WHERE a.CustomerKey IN (14718, 19383);
Praktický příklad na ukázce kumulace v SQL
Zkusíme si ještě do předchozí úlohy přidat jeden sloupec [RunningTotal], který spočítá kumulaci do daného řádku přes zákazníky tak, aby s přibývajícími řádky se hodnota [SalesAmount] přičetla k sumě hodnot z předešlých řádků v rámci stejného zákazníka.
--Výsledek OVER(PARTITION BY|ORDER BY|ROW)
SELECT c.CustomerKey, SalesOrderNumber, d.CalendarYear, SalesAmount,
SUM(a.SalesAmount) OVER (PARTITION BY a.CustomerKey) AS Customertotal,
SUM(a.SalesAmount) OVER () TotalSales,
SUM(SalesAmount) OVER (PARTITION BY c.CustomerKey ORDER BY SalesOrderNumber
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM FactInternetSales a
JOIN DimSalesTerritory b ON a.SalesTerritoryKey=b.SalesTerritoryKey
JOIN DimCustomer c ON a.CustomerKey=c.CustomerKey
JOIN DimDate d ON a.OrderDateKey=d.DateKey
WHERE a.CustomerKey IN (14718, 19383)
Výsledek je následující:
