Redgate Hub (Svenska)

Jag har tillbringat de senaste sex åren reser runt i USA berättar databas proffs om T-SQL fönsterfunktioner på SQL lördagar och andra evenemang. Jag är förvånad över hur få människor har hört talas om dessa funktioner och ännu färre som använder dem. I slutet av varje presentation kommer en eller flera personer att säga att de önskade att de lärde sig om dessa funktioner år tidigare eftersom de kunde ha varit fördelaktiga för så många frågor.,

dessa funktioner har främjats för att förbättra prestanda jämfört med andra, mer traditionella metoder. Jag håller delvis. De gör många frågor lättare att skriva, och ibland förbättrar de prestanda.

ingenting att göra med Windows OS

dessa funktioner är en del av ANSI SQL 2003 standarder och, när det gäller SQL Server, är T-SQL-funktioner som används för att skriva frågor. De har inget att göra med Windows operativsystem eller några API-samtal. Andra databassystem, som Oracle, har också inkluderat dessa som en del av sitt eget SQL-språk.,

fönster (även fönster-eller fönsterfönster) funktioner utför en beräkning över en uppsättning rader. Jag gillar att tänka på ”Titta genom fönstret” på raderna som returneras och har en sista chans att utföra en beräkning. Fönstret definieras av över-klausulen som bestämmer om raderna är partitionerade i mindre uppsättningar och om de beställs. Faktum är att om du använder en fönsterfunktion använder du alltid en ÖVERKLAUSUL. OVER-klausulen är också en del av nästa värde för syntax som krävs för sekvensobjektet, men annars används det med fönsterfunktioner.,

OVER-klausulen kan innehålla en PARTITION efter alternativ. Detta bryter raderna i mindre uppsättningar. Du kanske tror att detta är samma som grupp av, men det är det inte. Vid gruppering returneras en rad per unik grupp. När du använder PARTITION BY returneras alla detaljrader tillsammans med beräkningarna. Om du har ett fönster i ditt hem som är uppdelat i rutor, varje ruta är ett fönster. När man tänker på fönsterfunktioner är hela uppsättningen resultat en partition, men när man använder PARTITION BY kan varje partition också betraktas som ett fönster., PARTITION BY stöds-och valfritt-för alla fönsterfunktioner.

OVER-klausulen kan också innehålla en ORDER efter alternativ. Detta är oberoende av ordern efter klausul i frågan. Några av funktionerna kräver ORDER BY, och det stöds inte av de andra. När ordningen på raderna är viktig vid tillämpningen av beräkningen krävs ordern efter.

fönsterfunktioner får endast användas i Välj och ordning efter klausuler i en fråga. De appliceras efter någon sammanfogning, filtrering eller gruppering.,

Rankningsfunktioner

de vanligaste fönsterfunktionerna, rankningsfunktionerna, har varit tillgängliga sedan 2005. Det var då Microsoft introducerade ROW_NUMBER, RANK, DENSE_RANK och NTILE. ROW_NUMBER används mycket ofta, för att lägga till unika radnummer till en partition eller hela resultatuppsättningen. Att lägga till ett radnummer, eller en av de andra rankningsfunktionerna, är vanligtvis inte målet, men det är ett steg längs vägen till lösningen.

ORDER BY krävs i over-klausulen när du använder ROW_NUMBER och de andra funktionerna i den här gruppen., Detta talar om databasmotorn i vilken ordning siffrorna ska tillämpas. Om värdena för kolumnerna eller uttrycken som används i ordern efter inte är unika, kommer RANK och DENSE_RANK att hantera banden, medan ROW_NUMBER inte bryr sig om band. NTILE används för att dela raderna i hinkar baserat på ORDER av.

en fördel med ROW_NUMBER är möjligheten att vända icke-unika rader till unika rader. Detta kan till exempel användas för att eliminera dubbla rader.

för att visa hur detta fungerar, börja med en temp-tabell som innehåller dubbla rader., Det första steget är att skapa tabellen och fylla den.

Om du lägger till ROW_NUMBER och partitionerar med varje kolumn startas radnumren om för varje unik uppsättning rader. Du kan identifiera de unika raderna genom att hitta dem med ett radnummer som är lika med en.,

nu, allt du behöver göra är att ta bort alla rader som har ett radnummer större än en., Problemet är att du inte kan lägga till fönsterfunktioner i WHERE-klausulen.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Du kan sedan ta bort raderna direkt från CTE.

framgång! De extra raderna togs bort, och en unik uppsättning rader kvarstår.

för att se skillnaden mellan ROW_NUMBER, RANK och DENSE_RANK, kör den här frågan:

ordern för varje över-klausul är OrderDate som inte är unik. Denna kund lade två beställningar på 2013-10-24. ROW_NUMBER fortsatte bara att tilldela nummer och gjorde inget annat trots att det finns ett dubblettdatum., RANK tilldelas 6 till båda raderna och sedan fångas upp till ROW_NUMBER med en 8 på nästa rad. DENSE_RANK tilldelades också 6 till de två raderna men tilldelades 7 till följande rad.

två förklarar skillnaden, tänk på ROW_NUMBER som position. RANK är både positionell och logisk. Dessa två rader rankas logiskt samma, men nästa rad rankas av positionen i uppsättningen. DENSE_RANK rankar dem logiskt. Order 2013-11-04 är den 7: e unika datum.

den slutliga funktionen i denna grupp kallas NTILE. Det tilldelar hinknummer till raderna istället för radnummer eller LED., Här är ett exempel:

NTILE har en parameter, i det här fallet 4, vilket är antalet hinkar du vill se i resultaten. Ordern från tillämpas på summan av försäljningen. Raderna med de lägsta 25% tilldelas 1, raderna med de högsta 25% tilldelas 4. Slutligen multipliceras resultaten av NTILE med 1000 för att komma med bonusbeloppet. Eftersom 14 inte kan delas jämnt med 4, går en extra rad in i var och en av de två första hinkarna.

fönster aggregat

fönster aggregat infördes också med SQL Server 2005., Dessa gör att skriva några knepiga frågor lätt men kommer ofta att utföra sämre än äldre tekniker. De tillåter dig att lägga till din favorit aggregat funktion till en icke-aggregerad fråga. Säg till exempel att du vill visa alla kundorder tillsammans med delsumman för varje kund., Genom att lägga till en summa med over-klausulen kan du åstadkomma detta mycket enkelt:

genom att lägga till partitionen med beräknas en delsumma för varje kund. Alla aggregerade funktioner kan användas, och ORDER BY i over-klausulen stöds inte.

Window Aggregate Enhancements in 2012

Från och med 2012 kan du lägga till en ORDER genom att över-klausulen till window aggregates för att producera löpande summor och glidande medelvärden, till exempel. Samtidigt introducerade Microsoft begreppet inramning. Lägga till en PARTITION genom är som att dela ett fönster i rutor., Lägga inramning är som att skapa ett målat glas fönster. Varje rad har ett enskilt fönster där uttrycket kommer att tillämpas.

med denna förbättring kan du skapa löpande summor även utan att lägga till inramningssyntaxen., Here is an example that returns a running total by customer:

standardramen, som används om en ram inte är angiven, är intervallet mellan obegränsad föregående och aktuell rad. Tyvärr, detta kommer inte att fungera lika bra som om du anger denna ram i stället: rader mellan obundet föregående och aktuell rad. Skillnaden är ordet rader. Intervallet är endast delvis implementerat vid denna tidpunkt, och det är avsett för att arbeta med tidsperioder, medan raderna är positionella., Ramen, rader mellan obundet föregående och nuvarande rad, innebär att fönstret består av den första raden av partitionen och alla rader upp till den aktuella raden. Varje beräkning görs över en annan uppsättning rader. När du till exempel utför beräkningen för rad 4 används raderna 1 till 4.

när beräkningen utförs för rad 5 är raderna 1-5. Fönstret blir större när du flyttar från en rad till nästa.,

Du kan också använda syntaxraderna mellan N PRECEEDING och aktuell rad eller rader mellan aktuell rad och N följande. Detta kan vara användbart för att beräkna ett tremånaders glidande medelvärde, till exempel. Följande figur representerar RADER MELLAN 2 FÖREGÅENDE OCH NUVARANDE RAD.

När 5 är den aktuella raden flyttas fönstret; det ändrar inte Storlek.,

här är listan över termer du behöver veta när du skriver inramning alternativet:

Jag erkänner att denna syntax är lite förvirrande men använder SQL Prompt hjälper gör att skriva inramning alternativet enklare!

Offsetfunktioner

som också ingår i utgåvan av SQL Server 2012 är fyra funktioner som gör att du kan inkludera värden från andra rader – utan att göra en självkoppling. Microsoft kallar dessa ”analytiska funktioner” , men jag hänvisar alltid till dem som ”offsetfunktioner” när jag presenterar om detta ämne., Två av funktionerna gör att du kan dra kolumner eller uttryck från en rad före (fördröjning) eller efter (leda) den aktuella raden. De andra två funktionerna gör att du kan returnera värden från den första raden i partitionen (FIRST_VALUE) eller sista raden i partitionen (LAST_VALUE). FIRST_VALUE och LAST_VALUE kräver också inramning, så var noga med att inkludera ramen när du använder dessa funktioner. Alla fyra av funktionerna kräver ORDER efter option av OVER-klausulen. Det är vettigt, eftersom databasmotorn måste veta ordningen på raderna för att ta reda på vilken rad som innehåller värdet att återvända.,

vissa har ett favoritband; vissa har en favoritfilm. Jag har en favoritfunktion-LAG. Det är lätt att använda (Ingen ram!) och utför bra., Here is an example:

LAG och LEAD kräver ett argument – kolumnen eller uttrycket du vill returnera. Som standard returnerar LAG värdet från föregående rad, och LEAD returnerar värdet från följande rad. Du kan ändra det genom att ange ett värde för OFFSETPARAMETERN, som är 1 som standard. Observera att den första raden i partitionen returnerar NULL. Om du vill åsidosätta null kan du ange ett standardvärde., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE och LAST_VALUE kan användas för att hitta ett värde från den allra första raden eller allra sista raden i partitionen. Var noga med att ange ramen, inte bara av prestandaskäl, men eftersom standardramen inte fungerar som du kan förvänta dig med LAST_VALUE. Standardramen, intervallet mellan obundet föregående och aktuell rad, går bara upp till den aktuella raden. Den sista raden i partitionen ingår inte., För att få de förväntade resultaten, var noga med att ange rader mellan aktuell rad och obundet följande när du använder LAST_VALUE., Här är ett exempel med FIRST_VALUE:

Statistiska funktioner

Microsoft grupperar dessa fyra funktioner – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – tillsammans med offsetfunktionerna som anropar alla åtta de analytiska funktionerna. Eftersom jag gillar att skilja dessa från offsetfunktionerna kallar jag dessa statistiska.

PERCENT_RANK och CUME_DIST ger en rangordning för varje rad över en partition. De skiljer sig något. PERCENT_RANK returnerar andelen rader som rankas lägre än den aktuella raden., ”Min poäng är högre än 90% av poängen.”CUME_DIST, eller kumulativ fördelning, returnerar den exakta rankningen. ”Min poäng är på 90% av poängen.”Här är ett exempel med den genomsnittliga höga temperaturen i St. Louis för varje månad. Observera att ledningarna bestämdes av Fahrenheit-temperaturen.

leden bestäms inte av de relativa värdena, utan av radernas positioner. Observera att Mars och November har samma genomsnittliga höga temp, så de rankades på samma sätt.

Du kanske undrar hur man beräknar PERCENT_RANK och CUME_DIST., Här är formlerna:

percentile_disc och percentile_cont fungerar på motsatt sätt. Med tanke på en procentuell rang, hitta värdet på den rangen., De skiljer sig åt i att PERCENTILE_DISC returnerar ett värde som finns i uppsättningen medan PERCENTILE_CONT beräknar ett exakt värde om inget av värdena i uppsättningen faller exakt vid den rangen. Du kan använda PERCENTILE_CONT för att beräkna en median genom att leverera 0.5 som procentrankningen. Till exempel, vilken temperatur rankas vid 50% i St. Louis?

funktionen PERCENTILE_CONT tar medelvärdet av de två värdena närmast mitten, 67 och 69, och medelvärden dem. PERCENTILE_DISC returnerar ett exakt värde, 67., Observera också att dessa två funktioner har en extra klausul som inte ses i de andra funktionerna, inom gruppen, som innehåller ordern genom i stället för inom over-klausulen.

sammanfattning

den här artikeln är en mycket snabb översikt över T-SQL-fönsterfunktioner. Två typer av funktioner släpptes med SQL Server 2005, ranking funktioner och fönster aggregat. Med 2012 har du förbättrat fönster aggregat med inramning och analytiska funktioner. Jag gillar att separera analysfunktionerna i två grupper, offset och statistiska funktioner., Fönsterfunktioner gör många frågor lättare att skriva, och jag tror att det är den största fördelen. I vissa fall kommer frågorna också att fungera bättre, men det är en diskussion för en annan dag.

Jag hoppas att den här artikeln har inspirerat dig att lära dig mer om dessa fantastiska funktioner!