Čísla řádků v nedeterministickém pořadí

Funkce okna ROW_NUMBER má řadu praktických aplikací, které přesahují pouhé zjevné potřeby hodnocení. Většinu času, když počítáte čísla řádků, musíte je počítat na základě nějaké objednávky a v klauzuli pořadí okna funkce zadáte požadovanou specifikaci řazení. Existují však případy, kdy potřebujete vypočítat čísla řádků v žádném konkrétním pořadí; jinými slovy na základě nedeterministického řádu. Může to být napříč celým výsledkem dotazu nebo v rámci oddílů. Mezi příklady patří přiřazení jedinečných hodnot řádkům výsledků, deduplikace dat a vrácení libovolného řádku za skupinu.

Pamatujte, že potřeba přiřadit čísla řádků na základě nedeterministického pořadí se liší od nutnosti přiřadit je na základě náhodného pořadí. U prvního vám prostě nezáleží na tom, v jakém pořadí jsou přiřazeny, a zda opakované provádění dotazu stále přiřazuje stejná čísla řádků stejným řádkům nebo ne. U druhého z nich očekáváte opakované spouštění, abyste neustále měnili, které řádky budou přiřazeny kterým číslům řádků. Tento článek zkoumá různé techniky výpočtu čísel řádků v nedeterministickém pořadí. Doufáme, že najdeme techniku, která bude spolehlivá i optimální.

Zvláštní poděkování patří Paulu Whiteovi za tip týkající se konstantního skládání, za techniku konstanty za běhu a za to, že byl vždy skvělým zdrojem informací!

Když záleží na objednávce

Začnu případy, kde na pořadí čísel záleží.

V mých příkladech použiji tabulku s názvem T1. K vytvoření této tabulky použijte následující kód a naplňte ji ukázkovými daty:

Zvažte následující dotaz (nazveme jej Dotaz 1):

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY datacol) AS n FROM dbo.T1;

Zde chcete přiřadit čísla řádků v rámci každé skupiny identifikované pomocí grp sloupce, seřazené podle datacol sloupce. Když jsem na svém systému spustil tento dotaz, dostal jsem následující výstup:

id grp datacol n--- ---- -------- ---5 A 40 12 A 50 211 A 50 37 B 10 13 B 20 2

Čísla řádků jsou zde přiřazována v částečně deterministickém a částečně nedeterministickém pořadí. Tím mám na mysli to, že máte jistotu, že v rámci stejného oddílu získá řádek s větší hodnotou datacol větší hodnotu čísla řádku. Vzhledem k tomu, že datacol není v rámci oddílu grp jedinečný, není pořadí přiřazení čísel řádků mezi řádky se stejnými hodnotami grp a datacol nedeterministické. Tak je tomu v případě řádků s hodnotami id 2 a 11. Oba mají hodnotu grp A a hodnotu datacol 50. Když jsem tento dotaz provedl na svém systému poprvé, řádek s id 2 dostal řádek číslo 2 a řádek s id 11 dostal řádek číslo 3. Nezáleží na tom, jak je pravděpodobné, že k tomu dojde v praxi na serveru SQL Server; pokud znovu spustím dotaz, teoreticky by řádek s ID 2 mohl být přiřazen řádku číslo 3 a řádek s ID 11 by mohl být přiřazen řádku číslo 2.

Pokud potřebujete přiřadit čísla řádků na základě na zcela deterministickém pořadí, zaručujícím opakovatelné výsledky napříč provedením dotazu, pokud se podkladová data nezmění, potřebujete, aby kombinace prvků v klauzulích rozdělení a okna byla jedinečná. Toho lze v našem případě dosáhnout přidáním id sloupce do klauzule objednávky okna jako tiebreaker. Klauzule OVER by pak byla:

OVER (PARTITION BY grp ORDER BY datacol, id)

Každopádně, při výpočtu čísel řádků na základě nějaké smysluplné specifikace objednávky, jako je tomu v Dotazu 1, musí SQL Server zpracovat řádky seřazené kombinací prvků dělení oken a řazení. Toho lze dosáhnout buď vytažením předobjednaných dat z indexu, nebo seřazením dat. V tuto chvíli není v T1 žádný index, který by podporoval výpočet ROW_NUMBER v dotazu 1, takže SQL Server se musí rozhodnout pro třídění dat. To lze vidět v plánu pro dotaz 1 zobrazeném na obrázku 1.

Obrázek 1: Plán pro dotaz 1 bez podpůrného indexu

Všimněte si, že plán skenuje data z seskupeného indexu pomocí vlastnosti Ordered: False. To znamená, že skenování nemusí vracet řádky seřazené podle indexového klíče. Je tomu tak, protože seskupený index se zde používá jen proto, že se stane, že pokrývá dotaz, a ne kvůli jeho klíčovému pořadí. Plán poté použije třídění, což má za následek další náklady, změnu měřítka N Log N a zpožděnou dobu odezvy. Operátor segmentu vytvoří příznak označující, zda je řádek první v oddílu nebo ne. Nakonec operátor Sequence Project přiřadí čísla řádků počínaje 1 v každém oddílu.

Pokud se chcete vyhnout potřebě třídění, můžete připravit krycí index se seznamem klíčů, který je založen na prvcích rozdělení a řazení, a seznam zahrnutí, který je založen na krycích prvcích.Líbí se mi myslet na tento index jako na index POC (pro rozdělení, řazení a zakrytí). Zde je definice POC, která podporuje náš dotaz:

CREATE INDEX idx_grp_data_i_id ON dbo.T1(grp, datacol) INCLUDE(id);

Znovu spustit dotaz 1:

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY datacol) AS n FROM dbo.T1;

Plán tohoto provedení je uveden na obrázku 2.

Obrázek 2: Plán pro dotaz 1 s indexem POC

Všimněte si, že tentokrát plán prohledá index POC pomocí vlastnosti Ordered: True. To znamená, že skenování zaručuje, že řádky budou vráceny v pořadí indexových klíčů. Vzhledem k tomu, že data jsou z indexu vytažena předobjednána tak, jak to vyžaduje funkce okna, není nutné explicitní třídění. Škálování tohoto plánu je lineární a doba odezvy je dobrá.

Když na pořadí nezáleží

Věci jsou trochu komplikované, když potřebujete přiřadit čísla řádků zcela nedeterministicky Přirozenou věcí, kterou v takovém případě chcete udělat, je použít funkci ROW_NUMBER bez zadání klauzule pořadí oken. Nejprve zkontrolujeme, zda to umožňuje standard SQL. Zde je relevantní část standardu definující pravidla syntaxe okna funkce:

Všimněte si, že položka 6 uvádí funkce < ntile function >, < funkce olova nebo zpoždění >, < typ funkce funkce > nebo ROW_NUMBER a pak položka 6a říká, že pro funkce < ntile function >, < funkce lead or lag >, RANK nebo DENSE_RANK klauzule o pořadí okna musí b e přítomný. Neexistuje žádný výslovný jazyk, který by uváděl, zda ROW_NUMBER vyžaduje klauzuli pořadí oken, či nikoli, ale zmínka o funkci v položce 6 a její vynechání v 6a by mohlo znamenat, že klauzule je pro tuto funkci volitelná. Je celkem zřejmé, proč by funkce jako RANK a DENSE_RANK vyžadovaly klauzuli pořadí oken, protože tyto funkce se specializují na manipulaci s vazbami a vazby existují pouze tehdy, když existuje specifikace objednávání. Určitě byste však mohli vidět, jak by mohla funkce ROW_NUMBER těžit z volitelné klauzule objednávky okna.

Zkusme to tedy a pokusme se vypočítat čísla řádků bez objednání okna na serveru SQL Server:

SELECT id, grp, datacol, ROW_NUMBER() OVER() AS n FROM dbo.T1;

Výsledkem tohoto pokusu je následující chyba:

Ve skutečnosti, pokud zkontrolujete dokumentaci funkce serveru ROW_NUMBER k serveru SQL, najdete následující text:

Takže zřejmě je klauzule pořadí oken pro funkci ROW_NUMBER na serveru SQL povinná . Mimochodem, to je také případ Oracle.

Musím říci, že si nejsem jistý, zda rozumím důvodům ng za tímto požadavkem. Nezapomeňte, že dovolujete definovat čísla řádků na základě částečně nedeterministického pořadí, jako v Dotazu 1. Proč tedy nedovolit nedeterminismus celou cestu? Možná existuje nějaký důvod, o kterém nepřemýšlím. Pokud vás napadne takový důvod, sdílejte prosím.

Každopádně můžete namítnout, že pokud vám na objednávce nezáleží, vzhledem k tomu, že klauzule o objednávce okna je povinná, můžete zadat jakoukoli objednat. Problém s tímto přístupem spočívá v tom, že pokud si objednáte nějaký sloupec z dotazovaných tabulek, může to znamenat zbytečný výkonnostní trest. Pokud není k dispozici žádný podpůrný index, zaplatíte za explicitní řazení. Pokud je k dispozici podpůrný index, omezujete modul úložiště na strategii skenování pořadí indexů (podle seznamu propojeného s indexem). Nepovolíte mu větší flexibilitu, jakou obvykle má, když na pořadí nezáleží při výběru mezi skenováním pořadí indexu a skenováním pořadí přidělení (na základě stránek IAM).

Jeden nápad, který stojí za vyzkoušení je určit konstantu, například 1, v klauzuli pořadí oken. Pokud je podporováno, doufáte, že je optimalizátor dostatečně chytrý na to, aby si uvědomil, že všechny řádky mají stejnou hodnotu, takže neexistuje žádná skutečná relevance řazení, a proto není nutné vynutit skenování pořadí nebo indexu. Zde je dotaz, který se pokouší o tento přístup:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1) AS n FROM dbo.T1;

Bohužel SQL Server toto řešení nepodporuje. Generuje následující chybu:

SQL Server podle všeho předpokládá, že pokud používáte celočíselnou konstantu v klauzuli pořadí oken, představuje to pořadovou pozici prvku v seznamu VÝBĚR, jako když zadáte celé číslo v prezentaci OBJEDNAT Klauzule BY. Pokud tomu tak je, další možností, kterou se vyplatí vyzkoušet, je určit neintegrovanou konstantu, například:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY "No Order") AS n FROM dbo.T1;

Ukázalo se, že i toto řešení není podporováno. SQL Server generuje následující chybu:

Klauzule pořadí oken zřejmě nepodporuje žádný druh konstanty.

Zatím jsme se dozvěděli následující informace o relevanci objednávání oken funkce ROW_NUMBER na serveru SQL Server:

1. ORDER BY je vyžadováno.
2. Nelze objednat podle celočíselné konstanty, protože SQL Server si myslí, že se snažíte určit pořadovou pozici v příkazu SELECT.
3. Nelze objednat podle jakýkoli druh konstanty.

Závěrem je, že byste měli objednávat pomocí výrazů, které nejsou konstantami. Je zřejmé, že můžete objednávat podle seznamu sloupců z dotazovaných tabulek. Ale snažíme se najít efektivní řešení, kde si optimalizátor může uvědomit, že neexistuje relevance objednávek.

Konstantní skládání

Závěr zatím je, že nemůžete použít konstanty v the Klauzule pořadí oken ROW_NUMBER, ale co výrazy založené na konstantách, jako například v následujícím dotazu:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+0) AS n FROM dbo.T1;

Tento pokus se však stane obětí procesu známého jako konstanta skládání, které má obvykle pozitivní dopad na výkon dotazů. Myšlenkou této techniky je zlepšit výkon dotazu složením určitého výrazu založeného na konstantách na jejich výsledné konstanty v rané fázi zpracování dotazu. Zde najdete podrobnosti o tom, jaké druhy výrazů lze neustále skládat. Náš výraz 1 + 0 je složen na 1, což má za následek stejnou chybu, jakou jste dostali při přímém zadání konstanty 1:

Podobná situace by byla i při pokusu o zřetězení dvouznakových řetězcových literálů, například:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY "No" + " Order") AS n FROM dbo.T1;

Získáte stejnou chybu, jakou jste dostali, když zadáváte přímo doslovný „No Order“:

Bizarro world – chyby, které chybám brání

Život je plný překvapení …

Jedna věc, která brání neustálému skládání, je situace, kdy by výraz obvykle vedl k chybě. Například výraz 2147483646 + 1 lze konstantně skládat, protože má za následek platnou hodnotu typu INT. Pokus o spuštění následujícího dotazu tedy selže:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 2147483646+1) AS n FROM dbo.T1;

Výraz 2147483647 + 1 však nelze konstantně složit, protože takový pokus by vyústil v chybu přetečení INT. Dopad na objednávání je docela zajímavý. Zkuste následující dotaz (budeme tomu říkat Dotaz 2):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 2147483647+1) AS n FROM dbo.T1;

Kupodivu, tento dotaz běží úspěšně! Co se stane, je to, že na jedné straně SQL Server nedokáže použít konstantní skládání, a proto je objednávání založeno na výrazu, který není jedinou konstantou. Na druhou stranu optimalizátor zjistí, že hodnota řazení je pro všechny řádky stejná, takže ignoruje výraz řazení úplně. To se potvrzuje při zkoumání plánu pro tento dotaz, jak je znázorněno na obrázku 3.

Obrázek 3: Plán pro dotaz 2

Dodržujte že plán skenuje nějaký krycí index pomocí vlastnosti Ordered: False. To byl přesně náš výkonnostní cíl.

Podobným způsobem zahrnuje následující dotaz úspěšný pokus o konstantní skládání, a proto selže:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1/1) AS n FROM dbo.T1;

Následující dotaz zahrnuje neúspěšný pokus o konstantní skládání, a proto uspěje a vygeneruje plán zobrazený dříve na obrázku 3:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1/0) AS n FROM dbo.T1;

Následující dotaz zahrnuje úspěšný pokus o konstantní skládání (literál „1“ VARCHAR se implicitně převede na INT 1 a poté se 1 + 1 složí na 2), a proto selže:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+"1") AS n FROM dbo.T1;

Následující dotaz zahrnuje a neúspěšný pokus o konstantní skládání (nelze převést „A“ na INT), a proto uspěl a vygeneroval plán zobrazený dříve na obrázku 3:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+"A") AS n FROM dbo.T1;

Abych byl upřímný, i když tato bizarní technika dosahuje našeho původního výkonnostního cíle, nemohu říci, že ji považuji za bezpečnou, a proto se na ni příliš necítím.

Konstanty běhu založené na funkcích

Při hledání dobrého řešení pro výpočet čísel řádků v nedeterministickém pořadí existuje několik technik, které se zdají bezpečnější než poslední nepředvídatelné řešení: použití běhových konstant založených na funkcích, použití poddotazu na základě konstanty, použití aliasovaný sloupec založený na konstantě a použití proměnné.

Jak vysvětluji v T-SQL chybách, úskalích a osvědčených postupech – determinismu, většina funkcí v T-SQL je hodnocena pouze jednou na odkaz v dotazu – ne jednou na řádek. To platí i pro většinu nedeterministických funkcí, jako je GETDATE a RAND. Existuje velmi málo výjimek z tohoto pravidla, jako jsou funkce NEWID a CRYPT_GEN_RANDOM, které se vyhodnocují jednou za řádek. Většina funkcí, jako je GETDATE, @@ SPID a mnoho dalších, se vyhodnotí jednou na začátku dotazu a jejich hodnoty se poté považují za běhové konstanty. Odkaz na takové funkce se nedostane konstantní. Díky těmto vlastnostem je runtime konstanta, která je založena na funkci, dobrou volbou jako prvek pro objednávání oken, a skutečně se zdá, že ji T-SQL podporuje. Optimalizátor si zároveň uvědomuje, že v praxi neexistuje relevance objednávek, aby se zabránilo zbytečným pokutám za výkon.

Zde je příklad použití funkce GETDATE:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY GETDATE()) AS n FROM dbo.T1;

Tento dotaz získá stejný plán, jak je uvedeno výše na obrázku 3.

Zde je další příklad použití funkce @@ SPID (vrácení aktuálního ID relace):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY @@SPID) AS n FROM dbo.T1;

A co funkce PI? Zkuste následující dotaz:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY PI()) AS n FROM dbo.T1;

Tento selže s následující chybou:

Funkce jako GETDATE a @@ SPID jsou přehodnoceny jednou za provedení plánu, takže je nelze získat neustále skládané. PI představuje vždy stejnou konstantu, a proto se konstantně složí.

Jak již bylo zmíněno dříve, existuje jen velmi málo funkcí, které se vyhodnotí jednou za řádek, například NEWID a CRYPT_GEN_RANDOM. To z nich dělá špatnou volbu jako prvek pro objednávání oken, pokud potřebujete nedeterministické pořadí – nezaměňovat s náhodným uspořádáním. Proč platit zbytečné pokuty za třídění?

Zde je příklad použití funkce NEWID:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY NEWID()) AS n FROM dbo.T1;

Plán pro tento dotaz je zobrazen na obrázku 4, což potvrzuje, že SQL Server přidal explicitní třídění na základě výsledku funkce.

Obrázek 4: Plán pro dotaz 3

Pokud chcete přiřadit čísla řádků v náhodném pořadí je to v každém případě technika, kterou chcete použít. Musíte si jen uvědomit, že to přináší náklady na třídění.

Použití poddotazu

Poddotaz na základě konstanty můžete také použít jako výraz pro objednávání oken (např. ORDER BY (VYBERTE „Žádná objednávka“)). Také s tímto řešením optimalizátor serveru SQL Server uznává, že neexistuje žádná relevance objednávek, a proto neukládá zbytečné řazení ani neomezuje možnosti úložného modulu na ty, které musí zaručovat objednávku. Zkuste spustit následující dotaz jako příklad:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT "No Order")) AS n FROM dbo.T1;

Získáte stejný plán, který je uveden výše na obrázku 3.

Jedna z velkých výhod této techniky je, že můžete přidat svůj vlastní osobní kontakt.Možná se vám NULL opravdu líbí:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM dbo.T1;

Možná se vám určité číslo opravdu líbí:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT 42)) AS n FROM dbo.T1;

Možná chcete někomu poslat zprávu:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT "Lilach, will you marry me?")) AS n FROM dbo.T1;

Dostanete smysl.

Realizovatelné, ale trapné

Existuje několik technik, které fungují, ale jsou trochu trapné. Jedním z nich je definovat alias sloupce pro výraz založený na konstantě a poté použít tento alias sloupce jako prvek pro objednávání oken. Můžete to udělat buď pomocí tabulkového výrazu, nebo pomocí operátoru CROSS APPLY a konstruktoru hodnoty tabulky. Zde je příklad:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY ) AS n FROM dbo.T1 CROSS APPLY ( VALUES("No Order") ) AS A();

Získáte stejný plán, který je uveden výše na obrázku 3.

Další možností je použití proměnné jako prvek pro objednávání oken:

DECLARE @ImABitUglyToo AS INT = NULL; SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY @ImABitUglyToo) AS n FROM dbo.T1;

Tento dotaz také získá plán zobrazený dříve na obrázku 3.

Co když použiji svůj vlastní UDF ?

Možná si myslíte, že použití vlastního UDF, který vrací konstantu, může být dobrou volbou jako prvek pro objednávání oken, pokud chcete nedeterministické pořadí, ale není. Jako příklad zvažte následující definici UDF:

DROP FUNCTION IF EXISTS dbo.YouWillRegretThis;GO CREATE FUNCTION dbo.YouWillRegretThis() RETURNS INTASBEGIN RETURN NULLEND;GO

Zkuste použít UDF jako klauzuli pro objednávání oken, podobně (budeme tomu říkat Query 4):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY dbo.YouWillRegretThis()) AS n FROM dbo.T1;

Před SQL Serverem 2019 (nebo úrovní paralelní kompatibility < 150) budou uživatelem definované funkce vyhodnoceny na řádek . I když vrátí konstantu, nedostanou se do řady. V důsledku toho můžete na jedné straně použít takový UDF jako prvek pro objednávání oken, ale na druhé straně to bude mít za následek trest řazení. To potvrzuje prozkoumání plánu pro tento dotaz, jak ukazuje obrázek 5.

Obrázek 5: Plán pro dotaz 4

Počínaje serverem SQL Server 2019 se na úrovni kompatibility > = 150 tyto uživatelem definované funkce začlení, což je většinou skvělá věc, ale v našem případě vede k chybě:

Takže použití UDF založené na konstanta jako prvek řazení okna buď vynutí řazení nebo chybu v závislosti na verzi serveru SQL, kterou používáte, a vaší úrovni kompatibility databáze. Stručně řečeno, nedělejte to.

Rozdělená čísla řádků v nedeterministickém pořadí

Běžným případem použití čísel dělených řádků na základě nedeterministického pořadí je vrácení libovolného řádku ze skupiny. Vzhledem k tomu, že podle definice v tomto scénáři existuje rozdělovací prvek, mysleli byste si, že bezpečnou technikou by v takovém případě bylo použít prvek rozdělování oken také jako prvek řazení oken. Jako první krok spočítejte čísla řádků takto:

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY grp) AS n FROM dbo.T1;

Plán tohoto dotazu je uveden na obrázku 6.

Obrázek 6: Plán pro dotaz 5

Důvod, proč je náš podpůrný index skenován pomocí vlastnosti Ordered: True, je ten, že SQL Server potřebuje zpracovat řádky každého oddílu jako jednotka. To je případ před filtrováním. Pokud filtrujete pouze jeden řádek na oddíl, máte jako možnosti oba algoritmy založené na pořadí a hash.

Druhým krokem je umístit dotaz s výpočtem čísla řádku do výrazu tabulky a do vnější dotaz filtruje řádek s řádkem číslo 1 v každém oddílu, například:

Teoreticky má být tato technika bezpečná, ale Paul white našel chybu, která ukazuje, že pomocí této metody můžete získat atributy z různé zdrojové řádky ve vráceném řádku výsledků na oddíl. Používání runtime konstanty založené na funkci nebo poddotazu založeném na konstantě, protože objednávací prvek se zdá být bezpečný i v tomto scénáři, takže se ujistěte, že místo toho použijete řešení, jako je následující: projde tímto způsobem bez mého svolení

Pokus o výpočet počtu řádků na základě nedeterministického pořadí je běžnou potřebou. Bylo by hezké, kdyby T-SQL jednoduše učinil klauzuli pořadí oken volitelnou pro funkci ROW_NUMBER, ale není. Pokud ne, bylo by hezké, kdyby alespoň umožňoval použití konstanty jako prvku pro objednávání, ale ani to není podporovaná možnost.Ale pokud se zeptáte pěkně, ve formě poddotazu na základě konstanty nebo běhové konstanty založené na funkci to SQL Server povolí. To jsou dvě možnosti, které mi nejvíce vyhovují. Opravdu se necítím dobře s podivnými chybnými výrazy, které, jak se zdá, fungují, takže nemohu doporučit tuto možnost.