Redgate Hub (Čeština)

strávil jsem posledních šest let cestování po USA říká, databáze odborníků o T-SQL Okno Funkce v SQL sobotu a jiných akcí. Jsem ohromen tím, jak málo lidí slyšelo o těchto funkcích a ještě méně, kteří je používají. Na konci každé prezentace, jeden nebo více lidé říkají, že si přejí, aby se dozvěděli o těchto funkcích let dříve, protože by mohly být prospěšné pro mnoho dotazů.,

tyto funkce byly podporovány ke zlepšení výkonu oproti jiným, tradičnějším metodám. Částečně souhlasím. Usnadňují psaní mnoha dotazů a někdy zlepšují výkon.

nemá nic společného s operačním systémem Windows

tyto funkce jsou součástí standardů ANSI SQL 2003 a v případě serveru SQL jsou funkce T-SQL používané k zápisu dotazů. Nemají nic společného s operačním systémem Windows nebo žádnými hovory API. Jiné databázové systémy, jako je Oracle, je také zahrnovaly jako součást svého vlastního jazyka SQL.,funkce

Window (také, windowing nebo windowed) provádějí výpočet přes sadu řádků. Rád přemýšlím o „pohledu oknem“ na řádky, které se vracejí, a mám poslední šanci provést výpočet. Okno je definováno klauzulí OVER, která určuje, zda jsou řádky rozděleny do menších sad a zda jsou uspořádány. Ve skutečnosti, pokud používáte funkci okna, budete vždy používat klauzuli OVER. Klauzule OVER je také součástí další hodnoty syntaxe požadované pro objekt sekvence, ale jinak se používá s funkcemi okna.,

klauzule OVER může obsahovat Oddíl podle volby. Tím se řádky rozdělí na menší sady. Možná si myslíte, že je to stejné jako skupina, ale není to. Při seskupení se vrátí jeden řádek na jedinečnou skupinu. Při použití PARTITION BY se všechny řádky detailů vrátí spolu s výpočty. Pokud máte doma okno, které je rozděleno na tabule, každý panel je okno. Při přemýšlení o funkcích okna je celá sada výsledků oddíl, ale při použití PARTITION BY lze každý oddíl také považovat za okno., Oddíl by je podporován-a volitelně – pro všechny funkce okenního okna.

klauzule OVER může také obsahovat příkaz podle volby. To je nezávislé na pořadí podle klauzule dotazu. Některé funkce vyžadují pořadí podle, a to není podporován ostatními. Je-li pořadí řádků důležité při použití výpočtu, vyžaduje se pořadí podle.funkce okna

lze použít pouze v položce SELECT a ORDER podle doložek dotazu. Používají se po každém spojení, filtrování nebo seskupení.,

funkce hodnocení

nejčastěji používané funkce oken, funkce hodnocení, jsou k dispozici od roku 2005. Tehdy Microsoft představil ROW_NUMBER, RANK, DENSE_RANK a NTILE. ROW_NUMBER se používá velmi často, k přidání jedinečných čísel řádků do oddílu nebo do celé sady výsledků. Přidání čísla řádku nebo jedné z dalších funkcí hodnocení není obvykle cílem, ale je to krok po cestě k řešení.

ORDER BY je vyžadován v klauzuli OVER při použití ROW_NUMBER a dalších funkcí v této skupině., To říká databázovému enginu pořadí, ve kterém by měla být čísla použita. Pokud hodnoty sloupců nebo výrazů používaných v pořadí podle nejsou jedinečné, pak RANK a DENSE_RANK se budou zabývat vazbami, zatímco ROW_NUMBER se nestará o vazby. NTILE se používá k rozdělení řádků do kbelíků na základě pořadí podle.

jednou z výhod ROW_NUMBER je schopnost přeměnit neopakovatelné řádky na jedinečné řádky. To by mohlo být použito například k odstranění duplicitních řádků.

Chcete-li zobrazit, jak to funguje, začněte tabulkou temp obsahující duplicitní řádky., Prvním krokem je vytvoření tabulky a její naplnění.

přidání ROW_NUMBER a rozdělení podle každého sloupce restartuje čísla řádků pro každou jedinečnou sadu řádků. Jedinečné řádky můžete identifikovat tím, že najdete řádky s číslem řádku rovným jednomu.,

Teď, vše, co musíte udělat, je odstranit všechny řádky, které mají řádek číslo větší než jedna., Problém je v tom, že do klauzule WHERE nelze přidat funkce okna.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Řádky pak můžete odstranit přímo z CTE.

úspěch! Další řádky byly odstraněny a zůstává jedinečná sada řádků.

vidět rozdíl mezi ROW_NUMBER, RANK a DENSE_RANK, spuštění tohoto dotazu:

ABY pro každý KONEC klauzule je Datumobjednávky, který není jedinečný. Tento zákazník zadal dvě objednávky na 2013-10-24. ROW_NUMBER jen pokračoval v přiřazování čísel a neudělal nic jiného, i když existuje duplicitní Datum., RANK přiřazen 6 k oběma řadám a pak dohnal ROW_NUMBER s 8 na další řádek. DENSE_RANK také přiřazen 6 do dvou řádků, ale přiřazen 7 do následujícího řádku.

dva vysvětlují rozdíl, považují ROW_NUMBER za poziční. Hodnost je poziční i logická. Tyto dva řádky jsou řazeny logicky stejně, ale další řádek je seřazen podle pozice v sadě. DENSE_RANK je řadí logicky. Objednávka 2013-11-04 je 7. jedinečné datum.

konečná funkce v této skupině se nazývá NTILE. Přiřazuje čísla lopatek k řádkům namísto čísel řádků nebo řad., Zde je příklad:

NTILE má parametr, v tomto případě 4, což je počet segmentů chcete vidět ve výsledcích. Pořadí podle se vztahuje na součet prodejů. Řádky s nejnižšími 25% jsou přiřazeny 1, řádky s nejvyššími 25% jsou přiřazeny 4. Nakonec jsou výsledky NTILE vynásobeny 1000, aby přišly s bonusovou částkou. Vzhledem k tomu, že 14 nelze rovnoměrně rozdělit na 4, do každé z prvních dvou kbelíků jde další řádek.

agregáty oken

agregáty oken byly také zavedeny s SQL Serverem 2005., Díky tomu je psaní některých složitých dotazů snadné, ale často bude horší než starší techniky. Umožňují vám přidat svou oblíbenou funkci agregátu do neagregovaného dotazu. Například byste chtěli zobrazit všechny objednávky zákazníků spolu s mezisoučtem pro každého zákazníka., Přidáním SOUČTU použití klauzule OVER, můžete dosáhnout velmi snadno:

přidáním ROZDĚLENÍ, mezisoučet se vypočítá pro každého zákazníka. Lze použít jakoukoli souhrnnou funkci a pořadí podle klauzule OVER není podporováno.

Okno Souhrnné Vylepšení v roce 2012

Počínaje 2012, můžete přidat, ABY se klauzule OVER okna agregáty na výrobu běží úhrny a klouzavé průměry, například. Současně společnost Microsoft představila koncept rámování. Přidání oddílu je jako rozdělení okna na tabule., Přidání rámování je jako vytvoření okna z barevného skla. Každý řádek má individuální okno, kde bude použit výraz.

s tímto vylepšením můžete vytvářet běžící součty i bez přidání syntaxe rámování., Here is an example that returns a running total by customer:

výchozí rámu, který je použit v případě, že rám není uvedeno, je ROZMEZÍ MEZI NESPOUTANÝ PŘEDCHOZÍ A AKTUÁLNÍ ŘÁDEK. Bohužel to nebude fungovat tak dobře, jako kdybyste místo toho zadali tento rámeček: řádky mezi neohraničeným předcházejícím a aktuálním řádkem. Rozdíl je slovo řádky. Rozsah je v tuto chvíli implementován pouze částečně a je určen pro práci s časovými obdobími, zatímco řádky jsou polohové., Rám, řádky mezi neohraničeným předcházejícím a aktuálním řádkem, znamená, že okno se skládá z prvního řádku oddílu a všech řádků až po aktuální řádek. Každý výpočet se provádí přes jinou sadu řádků. Například při výpočtu pro řádek 4 se používají řádky 1 až 4.

při výpočtu řádku 5 jsou řádky 1 až 5. Okno se zvětšuje, když se pohybujete z jednoho řádku do druhého.,

můžete také použít řádky syntaxe mezi N Předcházející a aktuální řádek nebo řádky mezi aktuální řádek a N následující. To by mohlo být užitečné například pro výpočet tříměsíčního klouzavého průměru. Následující obrázek představuje řádky mezi 2 Předcházející a aktuální řádek.

když je 5 aktuální řádek, okno se pohybuje;nemění velikost.,

Zde je seznam termínů, které byste měli vědět při psaní rámování možnosti:

přiznám se, že tato syntaxe je trochu matoucí, ale pomocí SQL Prompt umožňuje psaní rámování možnost jednodušší!

Offset Funkce

Také součástí vydání SQL Server 2012 jsou čtyři funkce, které vám umožní zahrnout hodnoty z jiných řádků – bez self-join. Společnost Microsoft tyto „analytické funkce“ nazývá, ale při prezentaci na toto téma je vždy označuji jako „offsetové funkce“., Dvě z funkcí umožňují vytáhnout sloupce nebo výrazy z řádku před (zpožděním) nebo po (vedení) aktuálního řádku. Další dvě funkce umožňují vrátit hodnoty z prvního řádku oddílu (FIRST_VALUE) nebo posledního řádku oddílu (LAST_VALUE). FIRST_VALUE a LAST_VALUE také vyžadují rámování, takže při použití těchto funkcí nezapomeňte zahrnout rámeček. Všechny čtyři funkce vyžadují pořadí podle volby klauzule OVER. To dává smysl, protože databázový motor musí znát pořadí řádků, aby zjistil, který řádek obsahuje hodnotu, kterou chcete vrátit.,

někteří lidé mají oblíbenou kapelu; někteří lidé mají oblíbený film. Mám oblíbenou funkci-zpoždění. Je to snadné použití (bez rámu!) a vystupuje skvěle., Here is an example:

MAS a VÉST vyžadují argument – sloupec nebo výraz, který chcete vrátit. Ve výchozím nastavení mas vrátí hodnotu z předchozího řádku a LEAD vrátí hodnotu z následujícího řádku. Můžete to upravit tak, že zadáte hodnotu parametru OFFSET, který je ve výchozím nastavení 1. Všimněte si, že první řádek oddílu vrátí NULL. Pokud chcete přepsat null, můžete zadat výchozí hodnotu., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE a LAST_VALUE může být použit k nalezení hodnoty z prvního řádku, nebo poslední řádek oddílu. Nezapomeňte zadat rámeček nejen z důvodů výkonu, ale proto, že výchozí rámeček nefunguje tak, jak byste očekávali u LAST_VALUE. Výchozí rámeček, rozsah mezi neohraničeným předcházejícím a aktuálním řádkem, jde pouze do aktuálního řádku. Poslední řádek oddílu není zahrnut., Chcete-li získat očekávané výsledky, nezapomeňte při použití LAST_VALUE zadat řádky mezi aktuálním řádkem a neohraničeným následujícím., Zde je příklad použití FIRST_VALUE:

Statistické Funkce

Microsoft skupin tyto čtyři funkce PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – spolu s odsazení funkce volání do všech osm analytické funkce. Protože je rád rozlišuji od offsetových funkcí, nazývám je statistickými.

PROCENT_RANK a CUME_DIST poskytují pořadí pro každý řádek přes oddíl. Mírně se liší. PROCENT_RANK vrací procento řádků, které jsou nižší než aktuální řádek., „Moje skóre je vyšší než 90% skóre.“CUME_DIST, nebo kumulativní distribuce, vrací přesnou hodnost. „Moje skóre je na 90% skóre.“Zde je příklad použití průměrné vysoké teploty v St. Louis za každý měsíc. Všimněte si, že řady byly určeny teplotou Fahrenheita.

hodnosti nejsou určeny relativními hodnotami, ale pozicemi řádků. Všimněte si, že Březen a listopad mají stejnou průměrnou vysokou teplotu, takže byly hodnoceny stejně.

možná vás zajímá, jak vypočítat PERCENT_RANK a CUME_DIST., Tady jsou vzorce:

PERCENTILE_DISC a PERCENTILE_CONT pracovat v opačném směru. Vzhledem k procentuální hodnosti najděte hodnotu v této hodnosti., Liší se v tom, PERCENTILE_DISC vrátí hodnotu, která existuje v sadě, zatímco PERCENTILE_CONT bude vypočítat přesnou hodnotu, pokud se žádná z hodnot v sadě padá přesně v té pozici. PERCENTILE_CONT můžete použít k výpočtu mediánu tím, že dodáte 0.5 jako hodnost procenta. Například, která teplota se řadí na 50% v St. Louis?

PERCENTILE_CONT funkce bere průměr ze dvou hodnot nejblíže středu, 67 a 69, a průměry. PERCENTILE_DISC vrací přesnou hodnotu, 67., Všimněte si také, že tyto dvě funkce mají další klauzuli, která není vidět v ostatních funkcích v rámci skupiny, která obsahuje pořadí podle namísto v klauzuli OVER.

shrnutí

Tento článek je velmi rychlý přehled funkcí okna T-SQL. S SQL Server 2005 byly vydány dva typy funkcí, funkce hodnocení a agregáty oken. S 2012, máte vylepšené okno agregát s rámování a analytické funkce. Rád odděluji analytické funkce do dvou skupin, ofsetové a statistické funkce., Funkce okna usnadňují psaní mnoha dotazů a věřím, že to je hlavní výhoda. V některých případech budou dotazy fungovat lépe, ale to je diskuse na další den.

doufám, že tento článek vás inspiroval, abyste se dozvěděli více o těchto fantastických funkcích!