Radnummer med icke-bestämd ordning

Fönstret ROW_NUMBER har många praktiska tillämpningar, långt bortom de uppenbara rankningsbehoven. För det mesta, när du beräknar radnummer, måste du beräkna dem baserat på en viss ordning och du anger önskad beställningsspecifikation i funktionens fönsterordning. Det finns dock fall där du behöver beräkna radnummer i ingen särskild ordning; med andra ord, baserat på icke-bestämd ordning. Detta kan vara över hela frågeresultatet eller inom partitioner. Exempel inkluderar tilldelning av unika värden till resultatrader, deduplicering av data och returnering av valfri rad per grupp.

Observera att behovet av att tilldela radnummer baserat på icke-bestämd ordning skiljer sig från att behöva tilldela dem baserat på slumpmässig ordning. Med den förstnämnda bryr du dig bara inte i vilken ordning de tilldelas, och om upprepade körningar av frågan fortsätter att tilldela samma radnummer till samma rader eller inte. Med det senare förväntar du dig att upprepade körningar fortsätter att ändra vilka rader som tilldelas vilka radnummer. Denna artikel undersöker olika tekniker för beräkning av radnummer i icke-bestämd ordning. Förhoppningen är att hitta en teknik som är både tillförlitlig och optimal.

Särskilt tack till Paul White för tipset om konstant vikning, för runtime konstant teknik och för att alltid vara en bra informationskälla!

När beställning betyder

Jag ska börja med fall där radnumreringen spelar roll.

Jag använder en tabell som heter T1 i mina exempel. Använd följande kod för att skapa denna tabell och fylla den med exempeldata:

Tänk på följande fråga (vi kallar den fråga 1):

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

Här vill du att radnummer ska tilldelas inom varje grupp som identifieras av kolumngrp, ordnad av kolumnens datacol. När jag körde den här frågan på mitt system fick jag följande utdata:

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

Radnummer tilldelas här i en delvis deterministisk och delvis icke-bestämd ordning. Vad jag menar med detta är att du har en försäkran om att inom samma partition kommer en rad med ett större datacol-värde att få ett större radnummervärde. Eftersom datacol inte är unikt i grp-partitionen är dock ordningen för tilldelning av radnummer mellan rader med samma grp- och datacol-värden icke bestämd. Så är fallet med raderna med id-värdena 2 och 11. Båda har grp-värdet A och datacol-värdet 50. När jag körde denna fråga på mitt system för första gången fick raden med id 2 rad nummer 2 och rad med id 11 fick rad nummer 3. Tänk inte på sannolikheten för att detta händer i praktiken i SQL Server; om jag kör frågan igen teoretiskt sett kan raden med id 2 tilldelas rad nummer 3 och raden med id 11 kan tilldelas rad nummer 2.

Om du behöver tilldela radnummer baserat i en helt deterministisk ordning, som garanterar repeterbara resultat över körningar av frågan så länge den underliggande informationen inte ändras, behöver du kombinationen av element i fönsterpartitionering och beställningsklausuler för att vara unik. Detta kan uppnås i vårt fall genom att lägga till kolumn-id i fönstrets ordningsklausul som en tiebreaker. OVER-klausulen skulle då vara:

OVER (PARTITION BY grp ORDER BY datacol, id)

Hur som helst, när du beräknar radnummer baserat på någon meningsfull beställningsspecifikation som i fråga 1, måste SQL Server bearbeta rader ordnade efter kombinationen av fönsterpartitionering och beställningselement. Detta kan uppnås genom att antingen dra i förbeställd data från ett index eller genom att sortera data. För närvarande finns det inget index på T1 för att stödja ROW_NUMBER-beräkningen i fråga 1, så SQL Server måste välja att sortera data. Detta kan ses i planen för fråga 1 som visas i figur 1.

Figur 1: Planera för fråga 1 utan stödjande index

Observera att planen skannar data från det klustrade indexet med en ordnad: falsk egenskap. Det betyder att skanningen inte behöver returnera de rader som ordnats med indexnyckeln. Så är fallet eftersom det klustrade indexet används här bara för att det råkar täcka frågan och inte på grund av dess nyckelordning. Planen tillämpar sedan en sortering, vilket resulterar i extra kostnad, N Log N-skalning och fördröjd svarstid. Segmentoperatören producerar en flagga som indikerar om raden är den första i partitionen eller inte. Slutligen tilldelar operatören Sequence Project radnummer som börjar med 1 i varje partition.

Om du vill undvika behovet av sortering kan du förbereda ett täckningsindex med en nyckellista som är baserad på partitionerings- och beställningselementen och en inkluderingslista som baseras på täckelementen.Jag tycker om att tänka på detta index som ett POC-index (för partitionering, beställning och täckning). Här är definitionen av POC som stöder vår fråga:

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

Kör fråga 1 igen:

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

Planen för detta utförande visas i figur 2.

Figur 2: Planera för fråga 1 med ett POC-index

Observera att den här gången skannar POC-indexet med en Ordered: True-egendom. Detta innebär att skanningen garanterar att raderna returneras i indexnyckelordning. Eftersom data hämtas i förväg från indexet som fönsterfunktionen behöver, finns det inget behov av explicit sortering. Skalningen av denna plan är linjär och svarstiden är bra.

När ordning inte spelar någon roll

Saker blir lite knepiga när du behöver tilldela radnummer med en helt icke-bestämd ordning. Det naturliga att vilja göra i ett sådant fall är att använda ROW_NUMBER-funktionen utan att ange en fönsterordningsklausul. Låt oss först kontrollera om SQL-standarden tillåter detta. Här är den relevanta delen av standarden som definierar syntaxreglerna för fönster funktioner:

Observera att artikel 6 visar funktionerna < ntile-funktion >, < lead- eller lag-funktion >, < rangfunktionstyp > eller ROW_NUMBER och sedan punkt 6a säger att för funktionerna < ntile function >, < bly- eller fördröjningsfunktion >, RANK eller DENSE_RANK ska fönstrets ordningsklausul b nuvarande. Det finns inget uttryckligt språk som anger om ROW_NUMBER kräver en fönsterordningsklausul eller inte, men omnämnandet av funktionen i artikel 6 och dess utelämnande i 6a kan innebära att klausulen är valfri för den här funktionen. Det är ganska uppenbart varför funktioner som RANK och DENSE_RANK kräver en fönsterbeställningsklausul, eftersom dessa funktioner är specialiserade på hantering av band och band finns bara när det finns beställningsspecifikation. Du kan dock säkert se hur ROW_NUMBER-funktionen kan dra nytta av en valfri fönsterordningsklausul.

Så, låt oss prova, och försök att beräkna radnummer utan fönsterordning i SQL Server:

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

Detta försök resulterar i följande fel:

Om du kontrollerar SQL Servers dokumentation av ROW_NUMBER-funktionen hittar du följande text:

Så tydligen är fönstrets ordningsklausul obligatorisk för ROW_NUMBER-funktionen i SQL Server . Så är det förresten också i Oracle.

Jag måste säga att jag inte är säker på att jag förstår anledningen ng bakom detta krav. Kom ihåg att du tillåter att definiera radnummer baserat på en delvis icke-bestämd ordning, som i fråga 1. Så varför inte tillåta icke-bestämning hela vägen? Kanske finns det någon anledning som jag inte tänker på. Om du kan tänka dig en sådan anledning, vänligen dela.

I vilket fall som helst kan du argumentera för att om du inte bryr dig om beställning, med tanke på att fönsterbeställningsklausulen är obligatorisk, kan du ange ordning. Problemet med detta tillvägagångssätt är att om du beställer efter någon kolumn från de förfrågade tabellerna kan detta innebära en onödig prestationsstraff. När det inte finns något stödindex på plats betalar du för explicit sortering. När det finns ett stödjande index på plats begränsar du lagringsmotorn till en strategi för genomsökning av indexordning (enligt listan över indexlänkar). Du tillåter det inte mer flexibilitet som det vanligtvis har när beställning inte spelar någon roll när du väljer mellan en indexordersökning och en allokeringsbeställningssökning (baserat på IAM-sidor).

En idé som är värt att prova är att ange en konstant, som 1, i fönstrets ordningsklausul. Om det stöds, hoppas du att optimeraren är tillräckligt smart för att inse att alla rader har samma värde, så det finns ingen verklig beställningsrelevans och därför inget behov av att tvinga en sortering eller en indexordning. Här är en fråga som försöker denna metod:

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

Tyvärr stöder inte SQL Server den här lösningen. Det genererar följande fel:

Uppenbarligen antar SQL Server att om du använder en heltalskonstant i fönstrets ordningsklausul, representerar den en ordningsposition för ett element i SELECT-listan, som när du anger ett heltal i presentationen BESTÄLLNING BY-klausul. Om så är fallet är ett annat alternativ som är värt att försöka att ange en icke-nummerkonstant, som så:

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

Visas att denna lösning inte stöds också. SQL Server genererar följande fel:

Tydligen stöder inte fönstrets ordningsklausul någon form av konstant.

Hittills har vi lärt oss följande om ROW_NUMBER-funktionens fönsterbeställningsrelevans i SQL Server:

1. ORDER BY krävs.
2. Det går inte att beställa med en heltalskonstant eftersom SQL Server tror att du försöker ange en ordningsposition i SELECT.
3. Det går inte att ordna efter någon form av konstant.

Slutsatsen är att du ska beställa efter uttryck som inte är konstanter. Uppenbarligen kan du beställa med en kolumnlista från de förfrågade tabellerna. Men vi är på jakt efter att hitta en effektiv lösning där optimeraren kan inse att det inte finns någon ordningsrelevans.

Konstant vikning

Slutsatsen hittills är att du inte kan använda konstanter i de ROW_NUMBERs fönsterordningsklausul, men hur är det med uttryck baserade på konstanter, till exempel i följande fråga:

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

Detta försök faller dock offer för en process som kallas konstant vikning, vilket normalt har en positiv resultateffekt på frågor. Tanken bakom denna teknik är att förbättra frågeprestanda genom att lägga ett visst uttryck baserat på konstanter till deras resultatkonstanter i ett tidigt skede av frågebehandling. Här hittar du information om vilka typer av uttryck som kan vikas konstant. Vårt uttryck 1 + 0 är vikat till 1, vilket resulterar i samma fel som du fick när du specificerade konstanten 1 direkt:

Du skulle möta en liknande situation när du försöker sammanfoga två teckensträngbokstäver, som så:

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

Du får samma fel som du fick när du angav bokstavligen ”Ingen beställning” direkt:

Bizarro world – fel som förhindrar fel

Livet är fullt av överraskningar …

En sak som förhindrar konstant vikning är när uttrycket normalt skulle leda till ett fel. Exempelvis kan uttrycket 2147483646 + 1 vikas konstant eftersom det resulterar i ett giltigt INT-typat värde. Följaktligen misslyckas ett försök att köra följande fråga:

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

Uttrycket 2147483647 + 1 kan dock inte vikas konstant eftersom ett sådant försök skulle ha resulterat i ett INT-överflödsfel. Konsekvensen vid beställning är ganska intressant. Försök med följande fråga (vi kallar den här frågan 2):

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

Märkligt nog körs denna fråga framgångsrikt! Vad som händer är att SQL Server å ena sidan inte tillämpar konstant vikning, och därför baseras beställningen på ett uttryck som inte är en enda konstant. Å andra sidan visar optimeraren att beställningsvärdet är detsamma för alla rader, så det ignorerar ordningsuttrycket helt och hållet. Detta bekräftas när planen för denna fråga undersöks som visas i figur 3.

Figur 3: Planera för fråga 2

Observera att planen skannar något täckande index med en Ordered: False property. Detta var precis vårt prestationsmål.

På samma sätt innebär följande fråga ett framgångsrikt konstantvikningsförsök och misslyckas därför:

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

Följande fråga innebär ett misslyckat konstantvikningsförsök och lyckas därför och genererar planen som visas tidigare i figur 3:

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

Följande frågan innebär ett framgångsrikt konstantvikningsförsök (VARCHAR bokstavligt ”1” konverteras implicit till INT 1, och sedan vikas 1 + 1 till 2) och misslyckas därför:

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

Följande fråga involverar en misslyckades med konstant vikningsförsök (kan inte konvertera ”A” till INT) och lyckas därför med att generera planen som visas tidigare i figur 3:

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

För att vara ärlig, även om denna bizarro-teknik uppnår vårt ursprungliga prestationsmål, kan jag inte säga att jag anser att det är säkert och därför är jag inte så bekväm att förlita mig på det.

Runtime konstanter baserat på funktioner

Fortsättning av sökandet efter en bra lösning för att beräkna radnummer med icke-bestämd ordning, det finns några tekniker som verkar säkrare än den senaste knäppa lösningen: att använda runtime-konstanter baserat på funktioner, använda en underfråga baserad på en konstant, använda en aliaserad kolumn baserad på en konstant och använder en variabel.

Som jag förklarar i T-SQL-buggar, fallgropar och bästa praxis – determinism, utvärderas de flesta funktioner i T-SQL bara en gång per referens i frågan – inte en gång per rad. Detta är fallet även med de flesta icke-bestämda funktioner som GETDATE och RAND. Det finns väldigt få undantag från denna regel, som funktionerna NEWID och CRYPT_GEN_RANDOM, som utvärderas en gång per rad. De flesta funktioner, som GETDATE, @@ SPID och många andra, utvärderas en gång i början av frågan, och deras värden betraktas sedan som körtidskonstanter. En hänvisning till sådana funktioner blir inte konstant vikad. Dessa egenskaper gör en runtime-konstant som baseras på en funktion till ett bra val som fönsterbeställningselement, och det verkar verkligen som om T-SQL stöder den. Samtidigt inser optimeraren att det i praktiken inte finns någon beställningsrelevans, vilket undviker onödiga prestationsstraff.

Här är ett exempel med GETDATE-funktionen:

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

Denna fråga får samma plan som visas tidigare i figur 3.

Här är ett annat exempel med funktionen @@ SPID (returnerar det aktuella sessionens ID):

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

Vad sägs om funktionen PI? Försök med följande fråga:

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

Den här misslyckas med följande fel:

Funktioner som GETDATE och @@ SPID utvärderas en gång per utförande av planen, så att de inte kan få konstant vikta. PI representerar alltid samma konstant och blir därför vikat konstant.

Som tidigare nämnts finns det mycket få funktioner som utvärderas en gång per rad, till exempel NEWID och CRYPT_GEN_RANDOM. Detta gör dem till ett dåligt val som fönsterbeställningselement om du behöver icke-bestämd ordning – inte att förväxla med slumpmässig ordning. Varför betala en onödig sorteringsavgift?

Här är ett exempel med NEWID-funktionen:

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

Planen för den här frågan visas i figur 4, vilket bekräftar att SQL Server lade till uttryckligt sortering baserat på funktionens resultat.

Figur 4: Planera för fråga 3

Om du vill att radnumren ska tilldelas i slumpmässig ordning, i alla fall, det är den teknik du vill använda. Du behöver bara vara medveten om att det medför sorteringskostnaden.

Använda en underfråga

Du kan också använda en underfråga baserad på en konstant som fönstrets beställningsuttryck (t.ex. ORDER BY (VÄLJ ”No Order”). Också med den här lösningen erkänner SQL Servers optimerare att det inte finns någon beställningsrelevans och inför därför inte onödig sortering eller begränsar lagringsmotorns val till sådana som måste garantera ordning. Försök att köra följande fråga som ett exempel:

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

Du får samma plan som visas tidigare i figur 3.

En av de stora fördelarna av denna teknik är att du kan lägga till din egen personliga touch.Du kanske gillar NULL:

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

Kanske gillar du verkligen ett visst antal:

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

Kanske vill du skicka ett meddelande till någon:

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

Du fattar poängen.

Gör, men besvärligt

Det finns ett par tekniker som fungerar, men är lite besvärliga. En är att definiera ett kolumnalias för ett uttryck baserat på en konstant, och sedan använda det kolumnalias som fönsterbeställningselement. Du kan göra detta antingen med hjälp av ett tabelluttryck eller med CROSS APPLY-operatören och en tabellvärdekonstruktör. Här är ett exempel för de senare:

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

Du får samma plan som visas tidigare i figur 3.

Ett annat alternativ är att använda en variabel som fönsterbeställningselement:

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

Denna fråga får också den plan som visas tidigare i figur 3.

Vad händer om jag använder min egen UDF ?

Du kanske tror att använda din egen UDF som returnerar en konstant kan vara ett bra val som fönsterbeställningselement när du vill ha icke-bestämd ordning, men det är det inte. Tänk på följande UDF-definition som ett exempel:

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

Försök använda UDF som fönsterbeställningsklausul, så (vi kallar den här frågan 4):

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

Före SQL Server 2019 (eller parallell kompatibilitetsnivå < 150) utvärderas användardefinierade funktioner per rad . Även om de returnerar en konstant blir de inte inline. Följaktligen kan du å ena sidan använda en sådan UDF som fönsterbeställningselementet, men å andra sidan resulterar det i ett slags straff. Detta bekräftas genom att undersöka planen för denna fråga, som visas i figur 5.

Figur 5: Planera för fråga 4

Från och med SQL Server 2019, under kompatibilitetsnivå > = 150, blir sådana användardefinierade funktioner inline, vilket oftast är en bra sak, men i vårt fall resulterar det i ett fel:

Så med en UDF baserad en konstant eftersom fönstrets beställningselement antingen tvingar en sortering eller ett fel beroende på vilken version av SQL Server du använder och din databaskompatibilitetsnivå. Kort sagt, gör inte detta.

Partitionerade radnummer med icke-bestämd ordning

Ett vanligt användningsfall för partitionerade radnummer baserat på icke-bestämd ordning returnerar en rad per grupp. Med tanke på att per definition finns ett partitioneringselement i detta scenario, skulle du tro att en säker teknik i ett sådant fall skulle vara att använda fönsterpartitioneringselementet också som fönsterbeställningselement. Som ett första steg beräknar du radnummer så:

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

Planen för denna fråga visas i figur 6.

Figur 6: Plan för fråga 5

Anledningen till att vårt stödindex skannas med en Ordered: True-egenskap beror på att SQL Server behöver bearbeta varje partitions rader som en en enhet. Så är fallet före filtrering. Om du bara filtrerar en rad per partition har du både orderbaserade och hashbaserade algoritmer som alternativ.

Det andra steget är att placera frågan med radnummerberäkningen i ett tabelluttryck och i den yttre frågan filtrerar raden med rad nummer 1 i varje partition, som så:

Teoretiskt sett är den här tekniken tänkt att vara säker, men Paul white hittade ett fel som visar att med den här metoden kan du få attribut från olika källrader i den returnerade resultatraden per partition. Använda en runtime-konstant baserad på en funktion eller en underfråga baserad på en konstant eftersom beställningselementet verkar vara säkert även i det här scenariot, så se till att du använder en lösning som följande i stället:

Ingen ska passera denna väg utan mitt tillstånd

Att försöka beräkna radnummer baserat på icke-bestämd ordning är ett vanligt behov. Det hade varit trevligt om T-SQL helt enkelt gjorde fönstrets ordningsklausul valfri för ROW_NUMBER-funktionen, men det gör det inte. Om inte, hade det varit trevligt om det åtminstone tillät att använda en konstant som beställningselement, men det är inte heller ett alternativ som stöds.Men om du frågar snyggt, i form av en underfråga baserad på en konstant eller en runtime-konstant baserad på en funktion, tillåter SQL Server det. Det här är de två alternativen som jag är mest bekväm med. Jag känner mig inte riktigt bekväm med de udda felaktiga uttrycken som verkar fungera så jag kan inte rekommendera det här alternativet.