Funkce okna v SQL. co jsou?
Účelem tohoto článku je představit vám funkce okna SQL a některé z konceptů na vysoké úrovni. V SQL se funkce okna týká funkce, jako je součet nebo průměr, která působí na řádky sady výsledků vzhledem k aktuálnímu řádku.
Existuje mnoho podrobností, ale uvidíte, že je pokrýváme v pozdějších článcích. Prozatím se kopeme do toho, jak fungují funkce okna a opravdu se dozvíte více o rámečcích.
Všechny příklady této lekce jsou založeny na Studio Microsoft SQL Server Management Studio a The AdventureWorks Database. Tyto nástroje zdarma můžete začít používat pomocí mého průvodce. Začínáme používat SQL Server.
Шкідливі розширення Microsoft VSCode, Вакансії з ШІ в Apple, Вживлення чіпа в людину
Úvod do funkcí okna SQL
Funkce okna jsou z velké části známé. Co je činí zvláštními, je, že jsou nastaveny tak, aby fungovaly na sadě řádků vzhledem k aktuální řadě procesoru dotazu
[1] Уроки по WinAPI?! Создание окна.
Klauzule over může být složitá, jak představíte objednávku a oddíl podle parametrů, ale uděláme to po druhém. Na konci tohoto článku uvidíte, jak každá z nich ovlivňuje výpočet funkce okna, a buďte připraveni řešit další analytické funkce a možnosti rozdělení.
Následující diagram spojuje všechny koncepty, které v tomto článku projdeme. Když čtete následující sekce, doporučuji vás, abyste se vrátili k této ilustraci, takže pochopíte, jak se hodí do celkového obrazu.
SQL Over Clause
Jak jsme již zmínili dříve, je to klauzule over, která činí agregátu funkci okna SQL. Zde uvidíte jednoduchou funkci okna, kterou chcete vypočítat na celkové saderytd pro všechny prodejní osoby.
Select BusinestentityId, TerritoriyId, Salerytd, Sum (Salerytd) Over As TotalsaleSytd z prodeje.Prodejce
Všimněte si, že TotalsaleSytd je stejná hodnota, jako bychom spustili dotaz
Vyberte součet (salerytd) z prodeje.Prodavač
Ve skutečnosti, pokud bychom si přáli, mohli bychom vytvořit stejný výsledek pomocí tohoto poddotčáku:
Select BusinestentityId, TerritoriyId, Salerytd, (Vyberte SUM (SaleryTD) z prodeje.Prodejce) jako totalsalesytd z prodeje.Prodejce
Uvidíte však, jak se ponoříme do funkcí oken SQL, jejich schopnosti pro agregaci dat rychle převyšují to, čeho je dosaženo pouze pomocí podvazek pouze.
Sql přes oddíl od
Oddíl SQL rozděluje řádky do skupin, kterými funkce okna vypočítává svůj výsledek. Funkce okna se vypočítává přes tyto řádky.
Je to podobné tomu, jak funguje klauzule skupiny by; Výsledkem klauzule skupiny je však souhrnný řádek. S funkcí okna zůstává původní řádek neporušený. Oddíl je logický a používá se pouze pro účely výpočtu.
Zde je pokračování našeho předchozího příkladu. V tomto případě jsme výsledek rozdělovali na území.
Select BusinestentityId, TerritoriyId, Salerytd, Sum (Salerytd) nad (oddíl TerritoriyId) jako totalsalesytd z prodeje.Prodavač
Všimněte si, že TotalsaleSytd nyní představuje totalsalesytd pro každý teritorium.
Mohli bychom provést stejný dotaz pomocí poddokryje? Tak určitě! Mohli jsme použít korelovaný dílčí dotaz podobný níže uvedenému
Select BusinestentityId, TerritoriyId, Salerytd, (Vyberte SUM (SaleryTD) z prodeje.Prodejce, kde se spojí (území.1) = Coalesce (s.Území.1)) jako totalsalesytd z prodeje.Prodejce jako řádek od Territoriyid;
Ale jak vidíte, podmnožina je stále těžkopádnější psát; Zatímco funkce okna SQL je elegantnější.
Možná jste na to zjistili, ale pokud není stanoven žádný oddíl, pak je celý výsledek považován za oddíl. To je důležité vědět, když přecházíme k dalšímu tématu, které má co do činění s tím, jak objednáváme řádky v rámci oddílu pro výpočet.
Sql over s objednávkou od
Když je v okně použita klauzule o objednávce, nastaví řádky objednávek v oddílu. Mějte na paměti, že pokud není stanoven žádný oddíl, oddíl obsahuje každý řádek.
Všechny řádky od začátku oddílu do aktuálního řádku nebo řádků rovnající se aktuální pořadí řádků podle hodnoty jsou považovány za součást okenního rámce. Mnoho okenních funkcí, jako je součet, pracuje v tomto rámci, což znamená, že se rovni vpřed k nastavení součtů, průměrů atd. Podle pořadí okna.
Pojďme se podívat na příklad, kde vypočítáme běžící celkový nebo totalsalesytd od prodejce. Uděláme to tím, že objednáme okno podnikatelkou obchodní osoby:
Select BusinestentityId, TerritoriyId, Salerytd, Sum (Salerytd) Over (Order by BusinestentityId) jako totalsalesytd z prodeje.Prodavač
Všimněte si, že se TotalsaleSyTD progresivně zvětšuje a nakonec se rovná dříve vypočítanému TotalsaleSytd.
Výsledky třídění funkce okna
Takže nyní možná přemýšlíte, co se stane, když se pokusíme objednat výsledky něčím jiným než BusinessEntityId?
Objednávání podle území zničí celkový výpočet běhu?
Vlastně to nebude! Důvodem jsou výpočty funkce okna SQL během výběrové fáze pořadí provádění, která je před uspořádáním výsledku. Pokud si toho nejste jisti, podívejte se na toto srovnání vedle sebe:
Všimněte si, že ačkoli výsledky vlevo jsou tříděny odlišně než výsledky napravo, součet na každém podnikatelství zůstává stejný. Můžete vidět, kde to poukazuji na dva případy (viz zvýrazněné řádky).
Klíčem odjměte to, že když funkce okna SQL provádějí své výpočty, dělají tak na logickém uspořádání řádků, což je nezávislé na konečném výsledku. Tyto řádky jsou označovány jako rám, protože jsou podmnožinou okna.
Používání dvou okenních funkcí
Pojďme pokračovat v příkladu kombinací funkcí našich oken do jediného dotazu. Níže uvidíte, kde máte dvě funkce okna: jeden, který počítač je celkem a druhý celkový běh:
Select BusinestentityId, TerritoriyId, Salerytd, Sum (Salerytd) Over (Order by BusinestentityId) jako RunningTalAlsaleSytd, Sum (Salerytd) přes GrandTolAlSaleSytd z prodeje.Objednávka prodavače od BusinessEntityId
To ukazuje, že můžete mít dva snímky ve stejném dotazu.
Rámy RunningTalsaleSytd jsou definovány uspořádáním aktuálního řádku prostřednictvím BusinessEntityId. Rámec GrandTolAlsaleSytd, protože neexistuje žádná objednávka, pokrývá celý oddíl.
.
SQL Over s oddílem a objednávkou
Používání partace a pořadí podle koncertu v rámci oproti klauzule poskytuje výkonnou kombinaci. To nám umožňuje vytvářet rámce pro provádění výpočtů, jako jsou běžící součty, uvnitř skupin.
Zvažte dotaz použitý pro výpočet běžného celkového počtu prodejní osobou na každém prodejním území.
Select BusinestentityId, TerritoriyId, Salerytd, Sum (Salerytd) Over (Oddíl podle TerritoriyId Order by BusinestentityId) jako RunningTalAlSaleSytdterritory z prodeje.Prodavač
Ve výsledcích můžete vidět běžící celkové resetování, když se setká s novými územími (oddíl).
Pojďme se zabalit tím, že se vrátíme přes některé z klíčových konceptů.
Koncepty klíčových klíčů SQL Okno
Vím, že toho bylo hodně pokryto, ale je důležité, abyste pochopili koncepty a žargon, než se přesunete dál.
Over. Toto je váš signál, předchozí funkce je funkce okna. Položky obsažené v doložce Over Over, například pořadí nebo rozdělením diktátem, jak je okno orámováno. To znamená, jak je vypočtena funkce okna.
Objednejte. to je to, co dělá funkci, aby se stal celkem běžící. Určuje pořadí, že řádky jsou zpracovány na funkci v rámci.
Oddíl. to je to, co dělá seskupení. Partion může být jeden nebo více sloupců. Jeden z nových oddílů ukončí výsledek funkce okna je reset.
Některá pozorování:
Pokud nemáte objednávku, dostanete celkem celkově běh.
Můžete mít různá okna ve stejném dotazu. Díky tomu je opravdu snadné zahrnout například běžení součtů podle dne, měsíce a roku!
Jedním z klíčových bodů, které si můžete zapamatovat, je rámec definovaný pořadí a rozdělením by je zcela oddělen od pořadí třídění. Nemá to nic společného s okny, která definujete v rámci vašich okenních funkcí. Neovlivňují výsledky, protože výsledek se nejprve vypočítá.
Tento web používá Akismet ke snížení spamu. Zjistěte, jak se zpracovávají data komentáře.
Jak definovat rám okna ve funkcích okna SQL
Funkce okna je super výkonným zdrojem jazyka SQL. V jádru jakékoli funkce okna je sada záznamů, která se nazývá okenní rám, definovaný pomocí klauzule Over Over V tomto článku budeme analyzovat a vysvětlit příklady, jak můžete definovat různé typy okenních rámců. Čtěte dále a udělejte důležitý krok v růstu vašich dovedností SQL!
Použití oddílu pro definování rámečku okna
Funkce okna SQL provádějí výpočty na základě sady záznamů. Například možná budete chtít vypočítat průměrný plat konkrétní skupiny zaměstnanců. Tato skupina záznamů se nazývá rám okna a její definice je ústřední pro pochopení toho, jak fungují funkce okna a jak je můžeme využít.
Okno je sada řádků souvisejících s aktuálním řádkem, kde se pro výpočet používá funkce okna. Rám okna může být jinou sadou řádků pro další řádek ve výsledku dotazu, protože záleží na zpracování aktuálního řádku. Každý řádek ve výsledkové sadě dotazu má svůj vlastní okenní rám.
Ve zbytku tohoto článku ukážeme příklady dotazů založené na databázi skupiny prodejců automobilů. Skupina ukládá informace o prodeji seskupené podle měsíce do tabulky s názvem MOUNKLY_CAR_SALES. Níže je tabulka s některými ukázkami:
| 2021 | 01 | Brod | F100 | Vyzvednout | 40 | 2500000 |
| 2021 | 01 | Brod | Mustang | Auto | 9 | 1010000 |
| 2021 | 01 | Renault | Auto | 20 | 9000000 | |
| 2021 | 02 | Renault | Fuego | Auto | 50 | 23000000 |
| 2021 | 02 | Brod | F100 | Vyzvednout | 20 | 1200000 |
| 2021 | 02 | Brod | Mustang | Auto | 10 | 1050000 |
| 2021 | 03 | Renault | Megane | Auto | 50 | 20000000 |
| 2021 | 03 | Renault | Koleos | Auto | 15 | 1004000 |
| 2021 | 03 | Brod | Mustang | Auto | 20 | 2080000 |
| 2021 | 04 | Renault | Megane | Auto | 50 | 20000000 |
| 2021 | 04 | Renault | Koleos | Auto | 15 | 1004000 |
| 2021 | 04 | Brod | Mustang | Auto | 25 | 2520000 |
Jednoduchý způsob, jak vytvořit rám okna, je použití klauzule over s oddílem podle uzavření. V následujícím příkladu SQL vygenerujeme zprávu o příjmech pomocí automobilu za rok 2021.
Vyberte značku, součet (příjmy) nad (oddíl podle značky) jako total_revenue od měsíce_car_sales, kde rok = 2021
Všechny záznamy se stejnou hodnotou ve sloupci Make (řádky jako barevně kódované níže) patří do stejného okenního rámce. Protože ve sloupci Make máme pouze dvě různé hodnoty, máme dva rámy oken.
| 2021 | 01 | Brod | F100 | Vyzvednout | ||
| 2021 | Brod | Mustang | Auto | 9 | 1010000 | |
| 2021 | 01 | Renault | Fuego | Auto | 20 | 9000000 |
| 2021 | 02 | Renault | Fuego | Auto | 50 | 23000000 |
| 2021 | 02 | Brod | F100 | Vyzvednout | 20 | 1200000 |
| 2021 | 02 | Brod | Mustang | Auto | 10 | 1050000 |
| 2021 | 03 | Renault | Megane | Auto | 50 | 20000000 |
| 2021 | 03 | Renault | Koleos | Auto | 15 | 1004000 |
| 2021 | 03 | Brod | Mustang | Auto | 20 | 2080000 |
| 2021 | 04 | Megane | Auto | 40 | 15000000 | |
| 2021 | 04 | Koleos | Auto | 20 | 1504000 | |
| 2021 | 04 | Brod | Mustang | Auto | 25 |
Výsledek dotazu je:
| Brod | 10360000 |
| 69508000 |
Chtěl bych navrhnout 2 články, kde najdete spoustu úvodních informací o funkcích oken SQL: „Kurz SQL měsíce. Funkce okna “a„ Kdy používám funkce okna SQL?“.
Objednávání řádků v rámci okna s objednávkou
ZPOŽDĚNÍ. a první_value. mezi ostatními.
Například, pokud chceme získat rozdíl příjmů mezi po sobě následujícími měsíci, můžeme si objednat rám okna podle měsíce. Poté, vzhledem k jakémukoli aktuálnímu řádku, může funkce okna zpoždění vrátit jakýkoli sloupec předchozího měsíce. Podívejme se na příklad, který získá rozdíl příjmů mezi každou dvojicí po sobě jdoucích měsíců.
Vyberte značku, model, měsíc, příjmy jako proud_month_revenue, zpoždění (příjmy) přes (objednávka podle měsíce) jako předchozí_month_revenue, příjmy.
Výsledek předchozího dotazu je:
| Brod | Mustang | 1 | 1010000 | NULA | NULA |
| Brod | Mustang | 2 | 1050000 | 1010000 | 4000 |
| Brod | Mustang | 3 | 1050000 | 103000 | |
| Brod | Mustang | 4 | 2080000 | 440000 |
První řádek ve výsledku má nulové hodnoty ve sloupcích předchozích_month_revenue a delta_revenue. Je to proto, že na leden neexistuje žádný předchozí měsíc. Sloupec delta_revenue se vypočítá mezi příjmy z aktuálního měsíce a příjmy z předchozího měsíce, z nichž druhý se získá s funkcí okna zpoždění.
Když použijeme pořadí v doložce Over Over, je třeba zvážit nový prvek: hranice rámce okna Když však použijeme objednávku podle subjektu, aktuální řádek se stává horní hranou rámu okna V další části budeme podrobně pokrývat koncept hranic okenních rámců.
Tyto hranice lze specifikovat pomocí jedné ze dvou subclauses v klauzuli over: řádky nebo rozsah. V této části vysvětlíme, jak používat dílčí založení řádků z klauzule.
Okno rám je sada řádků, které nějak souvisejí s aktuálním řádkem. Jejich hranice lze definovat pro každý řádek v výsledku dotazu s řádky, které má následující syntaxi:
Řádky mezi dolním a horním_bound
Jak jsme již zmínili v předchozí části, je důležité vědět, jaké jsou výchozí hranice rámce okna. Když zadáme objednávku podle subjektu, aktuální řádek je ve výchozím nastavení horní hranou rámečku okna ve výchozím nastavení. V některých případech však musíme změnit tuto horní hranici (nebo dolní mez), jak uvidíme níže.
Podívejme se na příklad, kde musíme určit hranice rámce okna. Předpokládejme, že chceme zprávu s celkovým prodejem za aktuální měsíc, celkovým prodejem za předchozí měsíc a maximálním prodejem v jakémkoli individuálním měsíci po celý rok, vše podle Make and Model. Dotaz k získání takové zprávy je:
Vyberte značku, model, model, měsíc, příjmy jako proud_month_revenue, zpoždění (příjmy) nad (oddíl podle značky, pořadí modelu podle měsíce) jako předběžné, max (příjmy) nad (oddíl podle značky, pořadí modelu podle měsíce řádky mezi neomezenými a neomezenými sledováním ) Jako max_year_revenue od měsíce_car_sales, kde rok = 2021
| Brod | F100 | 1 | 2500000 | NULA | 2500000 |
| Brod | F100 | 2 | 1200000 | 2500000 | 2500000 |
| Brod | Mustang | 1 | 1010000 | NULA | 2520000 |
| Brod | Mustang | 2 | 1050000 | 1010000 | 2520000 |
| Brod | Mustang | 3 | 2080000 | 1050000 | 2520000 |
| Brod | Mustang | 4 | 2520000 | 2080000 | 2520000 |
| Renault | Fuego | 1 | 9000000 | NULA | 23000000 |
| Renault | Fuego | 2 | 23000000 | 9000000 | 23000000 |
| Renault | Koleos | 3 | 1004000 | NULA | 1504000 |
| Renault | Koleos | 4 | 1504000 | 1004000 | 1504000 |
| Renault | Megane | 3 | 20000000 | NULA | 20000000 |
| Renault | Megane | 4 | 15000000 | 20000000 | 20000000 |
Kdybychom vynechali řádky mezi neomezeným předchozím a neomezeným po podložce ve funkci maximálního okna v předchozím dotazu, získali bychom maximum mezi prvním měsícem a aktuálním měsícem. To je špatné, protože chceme maximální měsíční příjmy s ohledem na celý rok (včetně měsíců po aktuálním měsíci). Musíme tedy do tabulky zahrnout všechny dostupné měsíce. Děláme to přidáním řádků podložení mezi neomezenými předchozím a neomezenými doložením do klauzule.
Další obrázek ukazuje všechny možné hranice, které můžeme specifikovat pro definování spodní a horní hranice rámce okna:
Možnosti pro nižší a horní hranice v klauzuli Over Over jsou:
- Neomezené předchozí
- N předchozí
- Aktuální řádek
- N následovat
- Neomezené následování
Musíme se ujistit, že dolní hranice je menší než horní hranice.
Nakonec bych rád navrhl článek, proč se učit funkce okna SQL v roce 2021? kde se můžete naučit, jak používat funkce okna.
Definování hranic rámu okna s dosahem
V předchozí části jsme definovali hranice okenního rámce z hlediska řádků. V této části vysvětlíme, jak používat rozsah dílčího zadržení k určení hranic rámečku oken v rozsazích řádků. Syntaxe podložení rozsahu je následující:
Rozsah mezi dolním a horním_bound
Rozsah je sada řádků se stejnou hodnotou pro oddíl podle kritérií. Například, pokud máme oddíl podle měsíce, můžeme vidět rozdíl v dalším obrázku, když používáme řádky nebo rozsah k definování rámce okna:
Over (oddíl od…. Objednejte do měsíce řádky mezi 1 předchozí a 1)
Over (oddíl od…. Objednávka podle měsíce se pohybuje mezi 1 předchozí a 1 následující)
Pokud chceme zprávu o příjmech podle Make pro aktuální měsíc a pro každý z posledních tří měsíců, můžeme použít následující dotaz:
Vyberte značku, model, měsíc, měsíc, příjmy jako model_revenue_current_month, součet (příjmy) nad (oddíl na příkaz podle měsíce se pohybuje mezi 0 předchozími a aktuální řádek) jako make_current_month, součet (příjmy) nad (oddíl pořadí na měsíc mezi 1. předcházejícím předcházejícím předcházejícím předcházejícím předcházejícím předcházejícím pořadí. A aktuální řádek) jako make_last_2_months, součet (příjmy) nad (oddíl na objednávku podle měsíce se pohybuje mezi 2 předchozími a aktuální řádek) jako make_last_3_months z měsíce_car_sales, kde rok = 2021 objednávka o 1,3,2
Předchozí dotaz používá podložku rozsahu k zadání rámce oken se všemi záznamy aktuálního rozsahu N měsíců. Například:
SUM (příjmy) přes (oddíl na příkaz podle měsíce se pohybuje mezi 1 předchozí a aktuální řádek) jako make_last_2_months
Předchozí rozsah podložení mezi 1 předchozí a aktuální řádek určuje rám okna, který zahrnuje předchozí měsíc a aktuální měsíc. Poté funkce SUM vrátí celkové příjmy za poslední dva měsíce.
Podobně můžeme použít následující ustanovení k získání celkových příjmů za poslední tři měsíce.
SUM (příjmy) přes (rozdělení příkazem na objednávku podle měsíce se pohybuje mezi 2 předchozími a aktuálními řádek) jako make_last_3_months
K dispozici je několik zkratů, aby se syntaxe usnadnila těmto ohraničujícím ustanovením:
| Neomezené předchozí | Mezi neomezeným předchozím a současným řádkem |
| n předchozí | Mezi n předchozí a aktuální řádek |
| Aktuální řádek | Mezi aktuálním řádkem a aktuálním řádkem |
| n následovat | Mezi a současnou řadou a n následným |
| Neomezené následování | Mezi současným řádkem a neomezenými |
V neposlední řadě je zde můj preferovaný cheat list na okenních funkcích se spoustou podrobností syntaxe: SQL Okno Functions Cheat Sheet.
Využijte funkce oken!
Ústřední myšlenkou ve funkci okna je rám okna, tj. Skupina záznamů, nad nimiž funkce okna funguje. V tomto článku jsme vysvětlili, že rám okna závisí na aktuálním řádku a je definován klauzulí over Over. Ukázali jsme také několik příkladů pro definování, které záznamy mají zahrnout do rámu okna, objednat řádky v něm a definovat jeho hranice.
Pro ty, kteří chtějí jít hlouběji, navrhuji funkce okna SQL online, se spoustou příkladů používajících různé funkce okna. Rozvíjejte své dovednosti a zvyšte vaše aktiva!!
Funkce okna SQL vysvětlily [kniha]
Funkce okna jsou pravděpodobně nejasnější částí SQL. Možná si pomyslete: „Tak co? Právě přišli s některými dalšími funkcemi “. Spíš ne. „Funkce okna“ je samostatný jazyk zabudovaný do běžného SQL. A je to komplikovanější než všechno, co víte o výběru.
Stručně řečeno, funkce okna pomáhají při vytváření skvělých analytických zpráv bez Excelu. Možná chcete v průběhu roku vypočítat měsíční procenta prodeje? Funkce okna. Rozdělit marketingové kanály na efektivní a neefektivní? Funkce okna. Vyberte 10 nejlepších klientů pro každý segment? Stejný.
Četl jsem několik desítek článků vysvětlujících funkce oken SQL. Všichni trpěli jedním ze dvou problémů:
- Snadné čtení bez velké praktické hodnoty, popisující 10% funkcí.
- Je obtížné pochopit. Kdybych tento předmět neznal. nerozuměl bych věci.
Chci, aby lidé lépe porozuměli oknům SQL. Takže jsem napsal knihu. vysvětlené funkce okna SQL.
O knize
Je to jasný a vizuální úvod do okenních funkcí. Jasné. protože mohu popsat složitá témata čitelným způsobem. Vizuální. protože jsem připravil sto obrázků a gifů, které vám pomohou pochopit okna SQL.
Funkce okna jsou složité téma. Kniha tedy učí jen trochu najednou. Dává to jen dost teorie a spoustu praxe, protože je to jediný způsob, jak proměnit abstraktní znalosti v dovednosti.
Vyprávění krok za krokem kombinované s mnoha vizuálními prvky pomáhá čtenářům pochopit materiál. Padesát šest interaktivních cvičení s řešeními zajišťujete, že v praxi ovládáte funkce okna.
Knihu můžete použít s jakoukoli z následujících DBMSS:
- MySQL 8.0.2 (Mariadb 10.2)
- PostgreSQL 11
- Sqlite 3.28
- MS SQL 2012
- Oracle 11g
Co se naučíš
Část 1. Okna a funkce
Část 1. Okna a funkce
- Funkce okna
- Order okna vs. Objednávání výsledků
- Třídění jedinečnosti
- Více oken
- Oddíly
- Skupiny
- Funkce hodnocení
- Ve srovnání se sousedy
- Ve srovnání s hranicemi
- Okno, oddíl, rám
- Ve srovnání s hranicemi revidovaným
- Funkce offsetu
- Rozdělené agregáty
- Filtrování a pořadí provádění
- Definice okna
- Agregační funkce
- Klouzavý průměr
- Rám
- Kumulativní celková částka
- Výchozí rámec
- Funkce válcování agregátů
- Kumulativní rozdělení
- Relativní hodnost
- Shrnutí statistiky
- Percentil
- Percentil jako funkce okna
- Statistické funkce
Část 2. Rámečky
- Rámový rám
- Specifika rozsahu
- Hranice rámu
- Výchozí rámec
- Rámečky FAQ
Část 3. Praxe
- Agregát nejprve, Windows později
- Nejprve Windows, filtr později
- Agregace a null
- Prodej zlatého plánu
- Prodej podle plánu na Q1
- Platina plán klouzavý průměr
- Srovnání s prosincem
- Příspěvek plánů
- Vysoké/střední/nízké příjmy
- 2020 vs 2019
- Měsíce zařazené podle příjmů
- Identifikace ostrovů
- Ostrovy s duplikáty
- Datum ostrovy
- Hodnotové klastry
- Datum klastry
Kniha začíná základy SQL Windows Basics (část 1) a přechází skrz nuance snímků (část 2) k některým docela pokročilým věcem (část 3).
Chcete.li zobrazit náhled prvních několika kapitol, navštivte odkazy ve výše uvedené tabulce.
O autorovi
Jsem Anton Zhiyanov, vývojář Python/Golang a nadšenec Sqlite. Pracuji na open source, učím kurzy a blog o programování.
V roce 2021 jsem spustil kurz funkcí oken SQL. Nyní má 1000 absolventů a průměrné hodnocení 5 hvězdiček na základě 300 studentských recenzí.
V roce 2023 jsem se rozhodl napsat knihu založenou na původním kurzu. Tady to je.
Podrobnosti o knize
Sledujte @ohmypy on a držte krok s novými příspěvky
Funkce okna v SQL
V SQL, funkce okna, známé také jako analytické funkce, fungují podobně jako agregované funkce, které vypočítají všechny řádky tabulky v sadě. Funkce okna se však liší v tom, že se agregují všechny řádky do jednoho výstupního řádku. Místo toho tyto funkce vrací všechny řádky v tabulce, přičemž vypočítaná sloupec ukazuje konkrétní funkci aplikovanou na každý řádek. Mezi běžné příklady funkcí oken patří Row_number. HODNOST. Huse_rank. VÉST. a zpoždění.
Jaké jsou funkce okna v SQL?
Funkce okna se používají k provádění určité operace na skupině řádků a poskytují výslednou hodnotu pro každý řádek v tabulce. Okno Slova v termínu „funkce okna“ se týká sady řádků v tabulce, na které funkce bude spustit. Tím, že známe fungování funkcí oken v SQL, můžeme snadno vyřešit většinu složitých problémů SQL.
Například, pokud potřebujete načíst nejvyšší výdělky z každého oddělení ve vašem stole zaměstnance nebo ukázat minimální a maximální plat v každém oddělení odpovídající každému záznamu zaměstnanců, budete pravděpodobně muset použít funkce okna. Bez těchto funkcí by mohly být takové dotazy obtížné psát a efektivně provádět. Tento článek prozkoumá různé příklady dotazů SQL, které těží z použití okenních funkcí.
Nejprve se podívejme na typy funkcí, na kterých můžeme aplikovat okno v SQL:
- Agregované funkce okna v SQL
- SOUČET
- Min
- Max
- POČET
- AVG
- Row_number
- HODNOST
- Huse_rank
- VÉST
- ZPOŽDĚNÍ
Syntax
Zatímco dotazy SQL pomocí konvenčních metod, jako jsou jednoduché funkce agregátu nebo hodnocení, mohou být přímé k psaní, použití funkcí oken vyžaduje mírně odlišnou syntaxi. V této části prozkoumáme přesnou syntaxi pro použití funkcí oken na dotazy SQL.
VYBRAT. window_function_name (column_name) přes (oddíl od pole_name a objednávka od field_name) jako new_column_name from Table_name;
Vrací: Tabulka s dalším sloupcem, který je reprezentován názvem a obsahuje výsledné hodnoty podle použité funkce okna.
Argumenty
| Window_function_name | Určuje název funkce okna. Je to povinný argument. |
| column_name | Určuje název sloupce, na kterém by měl být výpočet proveden. |
| PŘES | Používá se s funkcemi oken k určení oddílů a pořadí řádků, ve kterých by měl být výsledek získán. Je také povinným argumentem používat vlastnost funkce okna. |
| Oddíl od | Používá se k definování okna pro funkce okna, skupinu řádků, na nichž funguje funkce okna. Pokud není oddíl dáno, budou data agregována na celé tabulce. Field_name Určuje sloupec, ve kterém chceme použít rozdělení. Je to také povinný argument. |
| SEŘADIT PODLE | Řádky uvnitř každé skupiny/oddílu jsou tříděny pomocí objednávky. Field_name Určuje sloupec, kterým chceme třídit rozdělení. Je to volitelný argument a ve výchozím nastavení bude dodržován náhodný řád. |
Používáme předem vytvořenou tabulku pro výpočetní účely (pomocí funkcí oken).
Tabulka zaměstnanců:
VYBRAT. Max (plat) nad (oddíl od Dept_name) jako max_salary od zaměstnanců;
Vysvětlení: Funkce maximálního agregátu okna ve výše uvedeném dotazu SQL se používá pro výpočet maximálního platu pro každé oddělení ve skupině z tabulky zaměstnanců. Výše uvedený dotaz vrátil další sloupec Max_salary, který představuje maximální plat v určité skupině oddělení. Over (oddíl od Dept_name) seskupuje názvy oddělení a aplikuje okno na skupině/oddílu.
Příklady
Ve všech níže uvedených příkladech používáme stejnou tabulku zaměstnanců.
Agregované funkce okna
Pojďme načíst celkový plat zaměstnanců ve skupině oddělení pomocí funkce agregovaného okna součtu.
VYBRAT. Součet (plat) nad (oddíl od Dept_name) jako total_salary od zaměstnance;
Pojďme načíst minimální plat zaměstnance ve skupině oddělení pomocí funkce agregovaného okna Min.
VYBRAT. Min (plat) nad (oddíl od Dept_name) jako total_salary od zaměstnance;
- Funkce počtu: Tato funkce vrací počet řádků přítomných ve sloupci a splňuje podmínky v dotazu SQL.
Pojďme načíst celkový počet zaměstnanců ve skupině oddělení pomocí funkce agregovaného okna Count.
VYBRAT. Count (emp_id) přes (oddíl podle dept_name) jako emp_count od zaměstnance;
Pojďme načíst průměrný plat zaměstnanců ve skupině oddělení pomocí funkce AVG agregované okna.
VYBRAT. Avg (plat) přes (oddíl od Dept_name) jako AVG_SALARY od zaměstnance;
Funkce okna hodnocení
- Funkce Row_number: Tato funkce přidělí každému z záznamů jedinečnou sekvenční hodnotu v tabulce.
Pojďme přiřadit jedinečné číslo řádku každému zaměstnanci ve skupině oddělení pomocí funkce okna hodnocení Row_number.
VYBRAT. ROW_NUMBER Over (oddíl podle dept_name pořadí emp_id) jako row_number od zaměstnance;
- Funkce pořadí: Funkce pořadí přiřadí každému záznamu jedinečnou hodnotu v tabulce se stejnou hodnotou přiřazenou více záznamům, pokud sdílejí stejné hodnoty. Pokud má více záznamů stejné hodnoty, funkce funkce přeskočí podle toho.
Pojďme načíst nejvyšší zaměstnance v každém oddělení, kteří vydělávají maximální plat pomocí funkce okna hodnocení hodnocení.
VYBRAT. Rank Over (rozdělení podle rozkazu DEPT_NAME od platu DESC) jako hodnost od zaměstnance;
- Funkce DENSE_RANK: Tato funkce přiřazuje jedinečnou hodnost záznamům v tabulce. Je to podobné funkci pořadí, ale nevynechává řady, pokud mají dva nebo více záznamů stejné řady.
Načíst nejvyšší zaměstnance v každém oddělení, kteří vydělávají maximální plat bez přeskočení v řadách pomocí funkce Okno Hunse_Rank.
VYBRAT. Rank Over (Oddíl podle rozkazu dept_name od platu desc) jako hodnost, huse_rank over (rozdělení podle rozkazu dept_name od platu desc) jako huse_rank od zaměstnance;
Funkce okna hodnoty
- Funkce zpoždění: Tato funkce nám umožňuje načíst data z předchozího řádku ve stejné sadě výsledků, bez použití spojů SQL. Jak vidíte v níže uvedeném příkladu, používáme funkci zpoždění k nalezení platu předchozího zaměstnance.
Pojďme načíst plat předchozího zaměstnance s ohledem na EMP_ID pomocí funkce okna hodnoty lag.
VYBRAT. Zpoždění (plat) nad (oddíl podle odkladu od Dept_name od Emp_id) jako predur_emp_salary od zaměstnance;
- Funkce olova: Tato funkce nám umožňuje načíst data z dalšího řádku ve stejné sadě výsledků, bez použití spojů SQL. Jak vidíte v níže uvedeném příkladu, používáme hlavní funkci k nalezení platu dalšího zaměstnance.
Pojďme načíst plat příštím zaměstnancem s ohledem na EMP_ID pomocí funkce okna olova hodnoty.
VYBRAT. Zpoždění (plat) nad (oddíl od Dept_Name řády od Emp_Id) jako predur_emp_salary, olovo (plat) nad (oddíl podle pořadí dept_name emp_id) jako next_emp_salary od zaměstnance;
