Redgate Hub (Dansk)

jeg har tilbragt de sidste seks år på at rejse rundt i USA, fortæller database fagfolk om, T-SQL-Vinduet Funktioner i SQL lørdage og andre arrangementer. Jeg er forbløffet over, hvor få mennesker har hørt om disse funktioner og endnu færre, der bruger dem. I slutningen af hver præsentation kommer en eller flere mennesker op for at sige, at de ønskede, at de lærte om disse funktioner år tidligere, fordi de kunne have været gavnlige for så mange forespørgsler.,

disse funktioner er blevet fremmet for at forbedre ydeevnen i forhold til andre, mere traditionelle metoder. Jeg er delvis enig. De gør mange forespørgsler lettere at skrive, og nogle gange forbedrer de ydeevnen.

Intet at gøre med Windows OS

Disse funktioner er en del af ANSI SQL-2003-Standarderne, og i tilfælde af SQL Server, er T-SQL-funktioner, der bruges til at skrive forespørgsler. De har intet at gøre med operatingindo .s-operativsystemet eller API-opkald. Andre databasesystemer, såsom Oracle, har også inkluderet disse som en del af deres eget s .l-sprog.,

vindue (også vindues-eller vinduesfunktioner) udfører en beregning over et sæt rækker. Jeg kan godt lide at tænke på at “kigge gennem vinduet” på de rækker, der returneres og har en sidste chance for at udføre en beregning. Vinduet er defineret af over-klausulen, der bestemmer, om rækkerne er opdelt i mindre sæt, og om de er bestilt. Faktisk, hvis du bruger en vinduesfunktion, vil du altid bruge en OVER-klausul. Over-klausulen er også en del af den næste værdi for syntaks, der kræves til sekvensobjektet, men ellers bruges den med vinduesfunktioner.,

over-klausulen kan indeholde en PARTITION efter valg. Dette bryder rækkerne i mindre sæt. Du tror måske, at dette er det samme som gruppe efter, men det er det ikke. Ved gruppering returneres en række pr. Når du bruger PARTITION BY, returneres alle detaljer rækker sammen med beregningerne. Hvis du har et vindue i dit hjem, der er opdelt i ruder, er hver rude et vindue. Når man tænker på vinduesfunktioner, er hele sættet af resultater en partition, men når man bruger PARTITION BY, kan hver partition også betragtes som et vindue., PARTITION BY understøttes – og valgfrit – til alle vinduesfunktioner.

over-klausulen kan også indeholde en ordre efter valg. Dette er uafhængigt af rækkefølgen efter klausul i forespørgslen. Nogle af funktionerne kræver ordre fra, og det understøttes ikke af de andre. Når rækkefølgen af rækkerne er vigtig, når beregningen anvendes, er ordren ved påkrævet.

vinduesfunktioner må kun bruges i SELECT and ORDER efter klausuler i en forespørgsel. De anvendes efter enhver sammenføjning, filtrering eller gruppering.,

Rangeringsfunktioner

de mest almindeligt anvendte vinduesfunktioner, rangeringsfunktioner, har været tilgængelige siden 2005. Det var da Microsoft introducerede ro ._number, RANK, DENSE_RANK og NTILE. Ro ._number bruges meget ofte til at tilføje unikke rækkenumre til en partition eller til hele resultatsættet. Tilføjelse af et rækkenummer, eller en af de andre rangeringsfunktioner, er normalt ikke målet, men det er et skridt på vejen til løsningen.

rækkefølge efter er påkrævet i over-klausulen, når du bruger ro ._number og de andre funktioner i denne gruppe., Dette fortæller databasemotoren i hvilken rækkefølge tallene skal anvendes. Hvis værdierne for de kolonner eller udtryk, der bruges i rækkefølge efter, ikke er unikke, vil RANK og DENSE_RANK håndtere båndene, mens ro ._number ikke er interesseret i bånd. NTIL bruges til at opdele rækkerne i spande baseret på ordren af.

en fordel ved ro ._number er evnen til at omdanne ikke-unikke rækker til unikke rækker. Dette kunne bruges til at fjerne dublerede rækker, for eksempel.

for at vise, hvordan dette fungerer, skal du starte med en temp-tabel, der indeholder duplikatrækker., Det første skridt er at oprette bordet og udfylde det.

tilføjelse af ro ._number og partitionering ved hver kolonne genstarter rækkenumrene for hvert unikt sæt rækker. Du kan identificere de unikke rækker ved at finde dem med et rækkenummer svarende til en.,

Nu, alt hvad du skal gøre, er at slette alle rækker, der har en række tal, der er større end én., Problemet er, at du ikke kan tilføje vinduesfunktioner til clausehere-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 derefter slette rækkerne lige fra CTE.

Succes! De ekstra rækker blev slettet, og et unikt sæt rækker forbliver.

for At se forskellen mellem ROW_NUMBER, RANG, og DENSE_RANK, kører denne forespørgsel:

Den RÆKKEFØLGE, SOM for hver OVER klausul er OrderDate der er ikke enestående. Denne kunde placerede to ordrer på 2013-10-24. Ro ._number fortsatte bare med at tildele tal og gjorde ikke noget andet, selvom der er en duplikatdato., Rang tildelt 6 til begge rækker og derefter fanget op til ro ._number med en 8 på næste række. DENSE_RANK tildelte også 6 til de to rækker, men tildelte 7 til den følgende række.

to forklare forskellen, tænk på ro ._number som positional. Rang er både positionel og logisk. Disse to rækker rangeres logisk ens, men den næste række rangeres efter positionen i sættet. DENSE_RANK rangerer dem logisk. Bestilling 2013-11-04 er den 7. unikke dato.

den endelige funktion i denne gruppe kaldes NTILE. Det tildeler spand numre til rækkerne i stedet for rækkenumre eller rækker., Her er et eksempel:

NTIL har en parameter, i dette tilfælde 4, hvilket er antallet af spande, du vil se i resultaterne. Ordren ved anvendes til summen af salget. Rækkerne med de laveste 25% er tildelt 1, rækkerne med de højeste 25% er tildelt 4. Endelig er resultaterne af NTILE ganget med 1000 for at komme op med bonusbeløbet. Da 14 ikke kan fordeles jævnt med 4, går en ekstra række ind i hver af de to første spande.

Vindueaggregater

Vindueaggregater blev også introduceret med s .l Server 2005., Disse gør det nemt at skrive nogle vanskelige forespørgsler, men vil ofte udføre værre end ældre teknikker. De giver dig mulighed for at tilføje din favorit aggregat funktion til en ikke-aggregeret forespørgsel. Sig, for eksempel vil du gerne vise alle kundeordrer sammen med subtotal for hver kunde., Ved at tilføje et BELØB ved hjælp af OVER-klausul, kan du gøre dette meget nemt:

Ved at tilføje den PARTITION MED en tilsammen er beregnet for hver enkelt kunde. Enhver samlet funktion kan bruges, og orden efter i over-klausulen understøttes ikke.

Enhindo.Aggregate Enhancements i 2012

begyndende med 2012 kan du tilføje en ordre ved at OVER-klausulen til windowindo. aggregates for at producere løbende totaler og glidende gennemsnit, for eksempel. Samtidig introducerede Microsoft begrebet indramning. Tilføjelse af en PARTITION ved er som at dele et vindue i ruder., Tilføjelse af indramning er som at skabe et farvet glasvindue. Hver række har et individuelt vindue, hvor udtrykket vil blive anvendt.

Med denne forbedring kan du oprette løbende totaler, selv uden at tilføje indramningssynta .en., Here is an example that returns a running total by customer:

standard ramme, som bruges, hvis et billede ikke er angivet, er INTERVALLET MELLEM UNBOUNDED FOREGÅENDE OG AKTUELLE RÆKKE. Desværre fungerer dette ikke så godt, som hvis du angiver denne ramme i stedet: rækker mellem ubegrænset foregående og nuværende række. Forskellen er ordet rækker. RANGE er kun delvist implementeret på dette tidspunkt, og det er beregnet til at arbejde med perioder, mens rækker er positionelle., Rammen, rækker mellem ubegrænset foregående og nuværende række betyder, at vinduet består af den første række af partitionen og alle rækker op til den aktuelle række. Hver beregning sker over et andet sæt rækker. For eksempel, når du udfører beregningen for række 4, bruges rækkerne 1 til 4.

Når du udfører beregningen for række 5, er rækkerne 1 til 5. Vinduet bliver større, når du bevæger dig fra en række til den næste.,

Du kan også bruge den syntaks, der RÆKKER MELLEM N FOREGÅENDE OG AKTUELLE kolonne eller RÆKKER MELLEM AKTUELLE RÆKKE OG N FØLGENDE. Dette kan være nyttigt til beregning af et glidende gennemsnit på tre måneder, for eksempel. Følgende figur repræsenterer rækker mellem 2 foregående og nuværende række.

Når 5 er den aktuelle række, bevæger vinduet sig; det ændrer ikke størrelse.,

Her er en liste af termer, som du har brug for at vide, når du skriver udformningen indstilling:

jeg må indrømme, at denne syntaks er en smule forvirrende, men ved hjælp af SQL-Prompt hjælper gør det at skrive udformningen mulighed nemmere!

Offsetfunktioner

også inkluderet i udgivelsen af s .l Server 2012 er fire funktioner, der giver dig mulighed for at inkludere værdier fra andre rækker – uden at gøre en selvforbindelse. Microsoft kalder disse ‘analytiske funktioner’, men jeg henviser altid til dem som ‘offsetfunktioner’, når jeg præsenterer om dette emne., To af funktionerne giver dig mulighed for at trække kolonner eller udtryk fra en række før (forsinkelse) eller efter (føre) den aktuelle række. De to andre funktioner giver dig mulighed for at returnere værdier fra den første række af partitionen (FIRST_VALUE) eller sidste række af partitionen (LAST_VALUE). FIRST_VALUE og LAST_VALUE kræver også indramning, så sørg for at inkludere rammen, når du bruger disse funktioner. Alle fire funktioner kræver ordren efter valg af OVER-klausulen. Det giver mening, fordi databasemotoren skal kende rækkefølgen af rækkerne for at finde ud af, hvilken række der indeholder værdien, der skal returneres.,

Nogle mennesker har et yndlingsband; nogle mennesker har en yndlingsfilm. Jeg har en favorit funktion-forsinkelse. Det er nemt at bruge (Ingen ramme!) og udfører stor., Here is an example:

LAG og FØRE kræver et argument – den kolonne eller udtryk, som du ønsker at vende tilbage. Som standard returnerer LAG værdien fra den forrige række, og LEAD returnerer værdien fra den følgende række. Du kan ændre det ved at angive en værdi for OFFSET-parameteren, som er 1 som standard. Bemærk, at den første række af partitionen returnerer NULL. Hvis du ønsker at tilsidesætte NULLs, kan du angive en standardværdi., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE og LAST_VALUE kan bruges til at finde en værdi fra den første række eller meget sidste række af partition. Sørg for at angive rammen, ikke kun af ydeevneårsager, men fordi standardrammen ikke fungerer som du ville forvente med LAST_VALUE. Standardrammen, interval mellem ubegrænset foregående og nuværende række, går kun op til den aktuelle række. Den sidste række af partitionen er ikke inkluderet., For at få de forventede resultater, skal du sørge for at angive rækker mellem aktuelle række og ubegrænset følgende, når du bruger LAST_VALUE., Her er et eksempel på brug af FIRST_VALUE:

Statistiske Funktioner

Microsoft grupper disse fire funktioner – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – sammen med offset funktioner kalder alle otte analytiske funktioner. Da jeg kan lide at skelne disse fra offsetfunktionerne, kalder jeg disse statistiske.

PERCENT_RANK og CUME_DIST giver en placering for hver række over en partition. De adskiller sig lidt. PERCENT_RANK returnerer procentdelen af rækker, der rangerer lavere end den aktuelle række., “Min score er højere end 90% af scoringerne.”CUME_DIST, eller kumulativ fordeling, returnerer den nøjagtige rang. “Min score er på 90% af scoringerne.”Her er et eksempel ved hjælp af den gennemsnitlige høje temperatur i St. Louis for hver måned. Bemærk, at rækkerne blev bestemt af Fahrenheit-temperaturen.

rækkerne bestemmes ikke af de relative værdier, men af rækkernes positioner. Bemærk, at Marts og November har den samme gennemsnitlige høje temp, så de blev rangeret den samme.

du spekulerer måske på, hvordan du beregner PROCENT_RANK og CUME_DIST., Her er de formler:

PERCENTILE_DISC og PERCENTILE_CONT arbejde i den modsatte måde. I betragtning af en procent rang, find værdien på den rang., De adskiller sig i, at PERCENTILE_DISC returnerer en værdi, der findes i sættet, mens PERCENTILE_CONT beregner en nøjagtig værdi, hvis ingen af værdierne i sættet falder nøjagtigt til den rang. Du kan bruge PERCENTILE_CONT til at beregne en median ved at levere 0,5 som procent rang. For eksempel, hvilken temperatur ligger på 50% i St. Louis?

PERCENTILE_CONT-funktionen tager gennemsnittet af de to værdier tættest på midten, 67 og 69, og gennemsnit dem. PERCENTILE_DISC returnerer en nøjagtig værdi, 67., Bemærk også, at disse to funktioner har en ekstra klausul, der ikke ses i de andre funktioner, inden for gruppen, der indeholder ordren i stedet for inden for over-klausulen.

resum This

Denne artikel er en meget hurtig oversigt over T-s .l vindue funktioner. To typer af funktioner blev frigivet med s .l Server 2005, ranking funktioner og vindue aggregater. Med 2012, har du forbedret vindue aggregat med indramning og de analytiske funktioner. Jeg kan godt lide at adskille de analytiske funktioner i to grupper, offset-og statistiske funktioner., Vinduesfunktioner gør mange forespørgsler lettere at skrive, og jeg tror, det er den største fordel. I nogle tilfælde vil forespørgslerne også fungere bedre, men det er en diskussion til en anden dag.

Jeg håber, at denne artikel har inspireret dig til at lære mere om disse fantastiske funktioner!