Funkce okna SQL

Začínáte zde? Tato lekce je součástí úplného kurzu používání SQL pro analýzu dat. Podívejte se na začátek.

V této lekci pojednáme:

• Úvod do funkcí okna
• Základní syntaxe oken
• Obvyklí podezřelí: SUM, COUNT a AVG
• ROW_NUMBER ()
• RANK () a DENSE_RANK ()
• NTILE
• MAS and LEAD
• Definování alias okna
• Pokročilé techniky vytváření oken

Tato lekce využívá data z programu Capital Bikeshare ve Washingtonu DC, který publikuje podrobné historické údaje o úrovni výletu na jejich webových stránkách. Data byla stažena v únoru 2014, ale jsou omezena na data shromážděná během prvního čtvrtletí roku 2012. Každý řádek představuje jednu jízdu. Většina polí je samozřejmostí, kromě rider_type: „Registrovaný“ označuje měsíční členství v programu spolujízdy, „Neformální“ uvádí, že jezdec koupil třídenní vstupenku. Pole start_time a end_time byla vyčištěna z původních formulářů, aby vyhovovaly formátování data SQL – jsou v této tabulce uložena jako časová razítka.

Úvod do funkcí okna

Dokumentace PostgreSQL dělá skvělou práci při zavádění konceptu funkcí oken:

Funkce okna provádí výpočet napříč sadou řádků tabulky, které nějak souvisí s aktuálním řádkem. To je srovnatelné s typem výpočtu, který lze provést agregační funkcí. Na rozdíl od běžných agregačních funkcí však použití okna funkce nezpůsobí seskupení řádků do jednoho výstupního řádku – řádky si zachovají svou samostatnou identitu. V zákulisí je funkce okna přístupná nejen k aktuálnímu řádku výsledku dotazu.

Nejpraktičtějším příkladem je průběžný součet:

Vidíte, že výše uvedený dotaz vytváří agregaci (running_total) bez použití GROUP BY. Pojďme rozdělit syntaxi a uvidíme, jak to funguje.

Základní syntaxe oken

První část výše uvedené agregace, SUM(duration_seconds) , vypadá hodně jako každá jiná agregace. Přidání OVER ji označuje jako funkci okna. Výše uvedenou agregaci můžete přečíst jako „vezměte součet duration_seconds přes celou sadu výsledků, v pořadí podle start_time. „

Pokud byste chtěli zúžit okno z celé datové sady na jednotlivé skupiny v rámci datovou sadu, můžete k tomu použít PARTITION BY:

Výše uvedený dotaz seskupuje a objednává dotaz podle start_terminal . V rámci každé hodnoty start_terminal je seřazeno podle start_time a průběžného součtu přes aktuální řádek a všechny předchozí řádky duration_seconds. Posouvejte se dolů, dokud se hodnota start_terminal nezmění, a všimnete si, že running_total začíná znovu. To se stane, když seskupujete pomocí PARTITION BY. V případě, že vás stále untík ORDER BY přeruší, jednoduše si objedná podle určeného sloupce (s) stejným způsobem jako klauzule ORDER BY, kromě toho, že s každým oddílem zachází jako s oddělenými. Vytvoří také průběžný součet – bez ORDER BY bude každá hodnota jednoduše součtem všech duration_seconds hodnot v příslušných start_terminal. Zkuste spustit výše uvedený dotaz bez ORDER BY, abyste získali představu:

ORDER a definujte, co se označuje jako„ okno “- seřazená podmnožina dat, na základě kterých se provádějí výpočty.

Poznámka: Funkce okna a standardní agregace nelze používat současně konkrétněji, nelze funkce okna zahrnout do klauzule GROUP BY.

Obvyklí podezřelí: SUM, COUNT a AVG

Při použití funkcí okna můžete použít stejné agregáty, které byste použili za normálních okolností – SUM, COUNT a AVG. Nejjednodušší způsob, jak tomu porozumět, je znovu spustit předchozí příklad s některými dalšími funkcemi. Proveďte

Alternativně stejné funkce s ORDER BY:

Nezapomeňte tyto dva předchozí dotazy připojit do režimu a spustit je. Tento další praktický problém je velmi podobný příkladům, proto zkuste upravit výše uvedený kód, místo toho, abyste začínali od nuly.

ROW_NUMBER ()

ROW_NUMBER() dělá přesně to, co zní – zobrazuje číslo daného řádku. Začíná to na 1 a čísluje řádky podle ORDER BY části výpisu okna. ROW_NUMBER() nevyžaduje, abyste v závorce uvedli proměnnou:

Použití klauzule PARTITION BY vám umožní začněte znovu počítat 1 v každém oddílu. Následující dotaz spustí počítání znovu pro každý terminál:

RANK () a DENSE_RANK ()

RANK() se mírně liší od ROW_NUMBER(). Pokud například objednáváte prostřednictvím start_time, může se stát, že některé terminály budou jezdit se dvěma stejnými počátečními časy. V tomto případě mají stejnou hodnost, zatímco ROW_NUMBER() jim dává různá čísla. V následujícím dotazu si všimnete 4. a 5. pozorování pro start_terminal 31000 – obě mají hodnost 4 a následující výsledek získá hodnost 6:

Místo RANK() můžete také použít DENSE_RANK() v závislosti na vaší aplikaci. Představte si situaci, kdy tři položky mají stejnou hodnotu. Pomocí obou příkazů získají všichni stejnou hodnost. Kvůli tomuto příkladu řekněme „s“ 2. „ Takto by oba příkazy vyhodnotily další výsledky odlišně:

• RANK() dá stejným řádkům pořadí 2, poté přeskočí řady 3 a 4, takže další výsledek by byl 5
• DENSE_RANK() by stále dal všem identickým řádkům hodnost 2, ale následující řádek by byl 3 – ne řady by byly přeskočeny.

NTILE

Pomocí funkcí okna můžete určit, do jakého percentilu (nebo kvartilu nebo jakéhokoli jiného pododdělení) spadá daný řádek. Syntaxe je NTILE(*# of buckets*). V tomto případě ORDER BY určuje, který sloupec se použije k určení kvartilů (nebo jakéhokoli počtu upřesněte). Například:

Při pohledu na výsledky z výše uvedeného dotazu můžete vidět, že sloupec percentile se nepočítá přesně tak, jak byste očekávali. Pokud pouze měl dva záznamy a měřili jste percentily, očekávali byste, že jeden záznam definuje 1. percentil a druhý záznam definuje 100. percentil. Pomocí funkce NTILE ve skutečnosti uvidíte jeden záznam v 1. percentilu a jeden ve 2. percentilu. Vidíte to ve výsledcích pro start_terminal 31000 – sloupec percentile vypadá jako číselné hodnocení. Pokud přejdete dolů na start_terminal 31007, je vidět, že správně vypočítává percentily, protože o tom existuje více než 100 záznamů start_terminal. Pokud pracujete s velmi malými okny, pamatujte na to a zvažte použití kvartilů nebo podobně malých pásma.

MAS a VEDOUT

Často může být užitečné porovnat řádky s předchozími nebo následujícími řádky, zejména pokud máte data v pořadí, které dává smysl. Pomocí LAG nebo LEAD můžete vytvořit sloupce, které získávají hodnoty z jiných řádky – vše, co musíte udělat, je zadat, ze kterého sloupce se má vytáhnout a o kolik řádků dál byste chtěli tahat. LAG táhne z předchozích řádků a LEAD táhne z následujících řádků:

To je zvláště užitečné, pokud chcete vypočítat rozdíly mezi řádky:

První řádek ve sloupci difference má hodnotu null, protože neexistuje žádný předchozí řádek, ze kterého lze táhnout. Podobně použití LEAD vytvoří nuly na konci datové sady. Pokud byste chtěli výsledky trochu čistit, můžete je zabalit do vnějšího dotazu a odstranit tak nuly:

Definování aliasu okna

Pokud plánujete psát několik funkcí okna do stejného dotazu, pomocí stejného okna můžete vytvořit alias.Vezměte NTILE příklad výše:

Lze jej přepsat jako:

WINDOW klauzule, pokud je zahrnuta, by měla vždy následovat po klauzuli WHERE.

Pokročilé techniky vytváření oken

Můžete si prohlédnout kompletní seznam oken funkce v Postgresu (používá režim syntaxe) v dokumentaci Postgres. Pokud používáte funkce okna v připojené databázi, měli byste se podívat na příslušného průvodce syntaxí pro váš systém.