Redgate Hub (Norsk)

jeg har brukt de siste seks år som reiser rundt OSS forteller database fagfolk om T-SQL Vinduet Funksjoner i SQL lørdager og andre arrangementer. Jeg er overrasket over hvor få mennesker har hørt om disse funksjonene, og enda færre som bruker dem. På slutten av hver presentasjon, en eller flere personer kommer opp for å si at de ønsket at de lærte om disse funksjonene år tidligere fordi de kunne ha vært til nytte for så mange spørsmål.,

Disse funksjonene har vært fremmet for å forbedre ytelsen i forhold til andre, mer tradisjonelle metoder. Jeg er delvis enig. De gjør mange spørsmål enklere å skrive, og noen ganger de vil forbedre ytelsen.

Ingenting å gjøre med Windows OS

Disse funksjonene er en del av ANSI SQL-2003-Standarder, og i tilfelle av SQL Server, er T-SQL funksjoner som brukes til å skrive spørringer. De har ingenting å gjøre med Windows-operativsystemet eller alle API-kall. Andre database-systemer, slik som Oracle, har også inkludert disse som en del av sin egen SQL-språk.,

Vindu (også windowing eller vindu) funksjoner utføre en beregning over et sett med rader. Jeg liker å tenke på «se gjennom vinduet» på rader som blir returnert, og har en siste sjanse til å utføre en beregning. Vinduet er definert av OVER punkt som bestemmer om radene er oppdelt i mindre sett og hvis de er bestilt. Faktisk, hvis du bruker et vindu funksjon du vil alltid bruke en OVER punkt. OVER punkt er også en del av den NESTE VERDIEN FOR syntaks som kreves for sekvens-objekt, men ellers er det brukt med vindu funksjoner.,

OVER klausul kan inneholde en PARTISJON AV alternativ. Dette bryter rader i mindre sett. Du tenker kanskje at dette er det samme som GRUPPE, men det er det ikke. Ved gruppering av én rad per unik gruppe er returnert. Når du bruker PARTISJONEN MED, alle detalj rader som returneres sammen med beregningene. Hvis du har et vindu i ditt hjem som er delt inn i ruter, hver rute er et vindu. Når du tenker på vinduet funksjoner, hele settet av resultater er en partisjon, men når du bruker PARTISJONEN MED, hver partisjon kan også betraktes som et vindu., PARTISJON som er støttet – og optional – for alle windowing funksjoner.

OVER klausul kan også inneholde en ORDRE VED alternativ. Dette er uavhengig av REKKEFØLGEN AV klausulen i søket. Noen av funksjonene krever ORDRE VED, og det er ikke støttes av den andre. Når rekkefølgen på radene er viktig når du søker beregningen, REKKEFØLGEN er nødvendig.

Vindu funksjoner kan bare brukes i de VELGER og REKKEFØLGEN AV punkter i en spørring. De søkte etter noen å bli med, filtrere eller gruppere.,

Rangering Funksjoner

Den mest brukte vinduet funksjoner, rangering funksjoner, har vært tilgjengelig siden 2005. Det er da Microsoft introduserte ROW_NUMBER, RANG, DENSE_RANK, og NTILE. ROW_NUMBER brukes veldig ofte, for å legge til unike rad tall på en partisjon eller hele resultatsett. Legge til en rad nummer, eller en av de andre rangering funksjoner, er vanligvis ikke målet, men det er et steg på veien til løsningen.

ORDRE som er nødvendig i OVER punkt når du bruker ROW_NUMBER og de andre funksjonene i denne gruppen., Dette forteller database engine rekkefølgen på tallene skal brukes. Hvis verdiene i kolonnene eller uttrykk som brukes i REKKEFØLGEN er ikke unik, da RANG og DENSE_RANK vil tilbyr med bånd, mens ROW_NUMBER ikke bryr seg om bånd. NTILE er brukt til å dele rader i bøtter basert på REKKEFØLGEN AV.

En fordel for ROW_NUMBER er evnen til å slå ikke-unike rader i unike rader. Dette kan brukes til å fjerne dupliserte rader, for eksempel.

for Å vise hvordan dette fungerer, kan du starte med en temp-tabell som inneholder dupliserte rader., Første trinn er å opprette tabellen og fylle den.

ved å Legge ROW_NUMBER og oppdeling av hver kolonne vil starte rad tall for hver av de unike sett med rader. Du kan identifisere unike rader med å finne de med rad nummer lik én.,

Nå, alt du trenger å gjøre er å slette alle rader som har en rad nummer større enn én., Problemet er at du ikke kan legge til vinduet funksjoner til WHERE-setningsdel.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Du kan deretter slette rader rett fra ETC.

Suksess! Den ekstra rader som ble slettet, og et unikt sett av rader gjenstår.

for Å se forskjellen mellom ROW_NUMBER, RANG, og DENSE_RANK, kjøre denne spørringen:

REKKEFØLGEN AV for hvert OVER punkt er OrderDate som ikke er unik. Denne kunden har plassert to bestillinger på 2013-10-24. ROW_NUMBER bare fortsatte å tildele nummer og gjorde ikke noe annerledes selv om det er en kopi dato., RANG tildelt 6 til både rader og deretter tatt opp til ROW_NUMBER med en 8 på neste rad. DENSE_RANK også tildelt 6 til to rader, men tildelt 7 til følgende rad.

To forklare forskjellen, tror på ROW_NUMBER som stedsbestemt. RANK er både stedsbestemt og logisk. De to radene er rangert logisk samme, men neste rad er rangert etter plassering i settet. DENSE_RANK rangerer dem på en logisk måte. For 2013-11-04 er den 7. unike dato.

Den siste funksjonen i denne gruppen er kalt NTILE. Det tilordner bøtte tall til rader i stedet for rad nummer, eller rekker., Her er et eksempel:

NTILE har en parameter, i dette tilfellet 4, som er antall verdiområder du ønsker å se i resultatene. REKKEFØLGEN er anvendt på summen av salg. Radene med lavest 25% er tildelt 1, radene med høyest 25% er tildelt 4. Til slutt, resultatene av NTILE multipliseres med 1000 for å komme opp med en bonus beløpet. Siden 14 kan ikke være jevnt fordelt med 4, en ekstra rad går inn i hvert av de første to bøtter.

– Vinduet Aggregater

Vindu gruppene ble også innført med SQL Server 2005., Disse gjør du skriver noen vanskelige spørsmål enkel, men ofte vil utføre verre enn eldre teknikker. De tillater deg å legge din favoritt samlede funksjonen til en ikke-samlet spørring. For eksempel si at du ønsker å vise alle at kunden bestiller sammen med totalprisen for hver enkelt kunde., Ved å legge til en SUM med OVER punkt, du kan gjøre dette veldig enkelt:

Ved å legge til en PARTISJON AV en delsum er beregnet for hver enkelt kunde. Noen samlede funksjonen kan brukes, og FOR AV i OVER punkt støttes ikke.

– Vinduet Samlet Forbedringer i 2012

Begynner med 2012, kan du legge inn en BESTILLING VED å OVER punkt til vinduet gruppene til å produsere kjører totaler og glidende gjennomsnitt, for eksempel. Samtidig har Microsoft introdusert konseptet for innramming. Legge til en PARTISJON som er som å dele et vindu inn i ruter., Å legge til rammer er som å lage en farget glass vindu. Hver rad har en individuell vinduet der uttrykket blir brukt.

Med dette ekstrautstyret, kan du opprette kjører tilsammen selv uten å legge rammer syntaks., Here is an example that returns a running total by customer:

standard ramme, som brukes hvis en ramme ikke er angitt, er OMRÅDET MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD. Dessverre, vil dette ikke utføre så vel som om du angi denne rammen i stedet: RADER MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD. Forskjellen er ordet RADER. SERIEN er bare delvis implementert på denne tiden, og det er ment for arbeid med perioder av gangen, mens RADENE er stedsbestemt., Rammen RADER MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD, betyr at vinduet består av de første rad på partisjonen, og alle rekker opp til den aktuelle raden. Hver beregning er gjort over et annet sett med rader. For eksempel, når du utfører en beregning for rad 4, rad 1 til 4 er brukt.

Når du utfører beregningen for rad 5, rad er 1 til 5. Vinduet blir større når du flytter fra en linje til neste.,

Du kan også bruke syntaks RADER MELLOM N FORUT OG AKTUELLE RADEN eller RADENE MELLOM GJELDENDE RAD OG N FØLGENDE. Dette kan være nyttig for å beregne en tre-måneders glidende gjennomsnitt, for eksempel. Den følgende figuren representerer RADER MELLOM 2 tidligere OG NÅVÆRENDE RAD.

Når er 5 den gjeldende raden, vinduet beveger seg, det trenger ikke endre størrelse.,

Her er listen over hva du trenger å vite når du skriver rammer alternativ:

jeg innrømme at denne syntaksen er litt forvirrende, men ved hjelp av SQL-Prompten hjelper gjør skriftlig utforming valget enklere!

Offset Funksjoner

Også inkludert med utgivelsen av SQL Server-2012 er fire funksjoner som lar deg ta med verdier fra andre rad – uten å gjøre en selv-bli med. Microsoft kaller disse «analytiske funksjoner’, men jeg har alltid referer til dem som «offset funksjoner» når du presenterer på dette emnet., To av de funksjoner som tillater deg å dra kolonner eller uttrykk fra en rad før (LAG) eller etter (BLY) den aktuelle raden. De to andre funksjonene lar deg for å returnere verdier fra den første raden av partisjonen (FIRST_VALUE) eller siste rad på partisjonen (LAST_VALUE). FIRST_VALUE og LAST_VALUE krever også rammer, så sørg for å inkludere ramme når du bruker disse funksjonene. Alle fire av funksjonene krever ORDREN VED valget av de OVER punkt. Det gir mening, fordi database motoren må vite rekkefølgen på radene for å finne ut hvilken rad som inneholder verdien for å gå tilbake.,

Noen mennesker har en favoritt band; noen mennesker har en favoritt film. Jeg har en favoritt funksjon – LAG. Det er lett å bruke (ingen ramme!) og utfører stor., Here is an example:

LAG og LEDE krever et argument – kolonnen eller uttrykk du ønsker å returnere. Som standard, LAG returnerer verdien fra forrige rad, og FØRE returnerer verdien fra følgende rad. Du kan endre det ved å levere en verdi for OFFSET-parameteren, som er 1 som standard. Legg merke til at den første raden av partisjon returnerer NULL. Hvis du ønsker å overstyre Nuller, kan du angir en STANDARDVERDI., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE og LAST_VALUE kan brukes til å finne en verdi fra første rad, eller aller siste rad på partisjonen. Husk å angi rammen, ikke bare for ytelse grunner, men fordi den standard ramme ikke fungerer som du forventer med LAST_VALUE. Standard ramme, varierer MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD, bare går opp til den aktuelle raden. Den siste raden på partisjonen er ikke inkludert., For å få den forventede resultater, pass på å angi RADER MELLOM GJELDENDE RAD OG UBEGRENSEDE FØLGENDE når du bruker LAST_VALUE., Her er et eksempel bruke FIRST_VALUE:

Statistiske Funksjoner

Microsoft grupper disse fire funksjonene – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – sammen med offset funksjoner kaller alle åtte analytiske funksjoner. Siden jeg liker å skille disse fra offset funksjoner, jeg kaller disse statistiske.

PERCENT_RANK og CUME_DIST gi en rangering for hver rad over en partisjon. De skiller seg litt ut. PERCENT_RANK returnerer prosent av rader som rangeres lavere enn den aktuelle raden., «Min score er høyere enn 90% av score.»CUME_DIST, eller kumulativ fordeling, returnerer nøyaktig verdi. «Min score er på 90% av score.»Her er et eksempel ved hjelp av den gjennomsnittlige høy temperatur i St. Louis for hver måned. Vær oppmerksom på at rekkene ble bestemt av Fahrenheit temperatur.

rekkene er ikke bestemt av den relative verdier, men ved stillinger av rader. Legg merke til at Mars og November har samme gjennomsnittlige høy temp, så de var rangert på samme måte.

lurer Du kanskje på hvordan du skal beregne PERCENT_RANK og CUME_DIST., Her er oppskriftene:

PERCENTILE_DISC og PERCENTILE_CONT arbeid i motsatt måte. Gitt en prosent rang, finne verdien på den graden., De skiller seg i at PERCENTILE_DISC vil returnere en verdi som finnes i det stille mens PERCENTILE_CONT vil beregne en nøyaktig verdi hvis ingen av verdier i settet faller nettopp ved at rang. Du kan bruke PERCENTILE_CONT å beregne en median ved å levere 0.5 som prosent rang. For eksempel, som temperatur rangerer på 50% i St. Louis?

PERCENTILE_CONT funksjonen tar gjennomsnittet av de to verdiene som er nærmest midten, 67 og 69, og gjennomsnitt dem. PERCENTILE_DISC returnerer en nøyaktig verdi, 67., Legg også merke til at disse to funksjonene har en ekstra punkt ikke har sett i andre funksjoner, I GRUPPE, som inneholder REKKEFØLGEN AV i stedet for i OVER punkt.

Oppsummering

Denne artikkelen er en veldig rask oversikt over T-SQL vinduet funksjoner. To typer funksjoner ble utgitt med SQL Server 2005, rangeringen funksjoner og vindu gruppene. Med 2012, har du forbedret vindu, sammen med komponering og analytiske funksjoner. Jeg liker å skille analytiske funksjoner inn i to grupper, offset og statistiske funksjoner., Vinduet funksjoner gjør mange forespørsler lettere å skrive, og jeg tror det er den viktigste fordelen. I noen tilfeller, spørringene vil prestere bedre også, men det er en diskusjon for en annen dag.

jeg håper denne artikkelen har inspirert deg til å lære mer om disse fantastiske funksjoner!