SQL OVER() – Klauzule pro fajnšmekry

Poslední aktualizace:

OVER 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].

SQL OVER()

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

SQL OVER()

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

SQL OVER()

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

SQL OVER() – Klauzule pro fajnšmekry
5 (100%) 3 vote[s]

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: SQL příkazy

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.