SQL OVER() s PARTITION BY- Klauzule pro fajnšmekry + příklady

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í:

1. Agregační funkce (Aggregate functions)
2. Pořadníkové funkce (Ranking functions)
3. 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].

Data

Ú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);

Příklad použítí příkazu

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í:

Použítí příkazu s agregační funkcí a specifikací pravidla pro agregaci