SQLShack

alle databasebrukere vet om vanlige aggregatfunksjoner som opererer på en hel tabell og brukes MED EN GRUPPE etter-klausul. Men svært få mennesker bruker Vindusfunksjoner i SQL. Disse opererer på et sett med rader og returnerer en enkelt aggregert verdi for hver rad.

hovedfordelen ved Å bruke Vindusfunksjoner over vanlige aggregatfunksjoner er: Vindusfunksjoner fører ikke til at rader grupperes i en enkelt utdatarad, radene beholder sine separate identiteter og en aggregert verdi legges til hver rad.

La oss ta en titt på Hvordan Window-funksjoner fungerer, og se deretter noen eksempler på å bruke det i praksis for å være sikker på at ting er klare og også hvordan SQL og output sammenligner MED DET FOR SUM () – funksjoner.

som alltid være sikker på at du er fullt sikkerhetskopiert, spesielt hvis du prøver ut nye ting med databasen.

Introduksjon Til Vindusfunksjoner

Vindusfunksjoner opererer på et sett med rader og returnerer en enkelt aggregert verdi for hver rad. Termvinduet beskriver settet med rader i databasen som funksjonen skal fungere på.

Vi definerer Vinduet (sett med rader som funksjoner opererer på) ved HJELP AV EN over () – klausul. Vi vil diskutere mer om OVER () klausulen i artikkelen nedenfor.

Types of Window functions

Syntax

Arguments

window_function
Specify the name of the window function

ALL
ALL is an optional keyword. Når du vil inkludere ALT, vil det telle alle verdier, inkludert dupliserte. DISTINKT støttes ikke i window functions

uttrykk
målkolonnen eller uttrykket som funksjonene opererer på. Med andre ord, navnet på kolonnen som vi trenger en aggregert verdi for. For eksempel, en kolonne som inneholder ordrebeløp slik at vi kan se totale bestillinger mottatt.

Over
Angir vindusklausulene for aggregatfunksjoner.

PARTITION BY partition_list
Definerer vinduet (sett med rader som vindusfunksjon opererer på) for vindusfunksjoner. Vi må gi et felt eller en liste over felt for partisjonen etter PARTITION BY-klausulen. Flere felt må skilles med komma som vanlig. HVIS PARTITION BY ikke er angitt, gruppering vil bli gjort på hele tabellen og verdier vil bli aggregert tilsvarende.

ORDER BY order_list
Sorterer radene i hver partisjon. HVIS ORDER BY ikke er spesifisert, BRUKER ORDER BY hele tabellen.

Eksempler

La oss lage tabell Og sette inn dummy poster for å skrive ytterligere spørsmål. Kjør under kode.

Samlet Vindu Funksjoner

SUM ()

VI vet ALLE SUM () samlet funksjon. Det gjør summen av spesifisert felt for spesifisert gruppe(som by, stat, land etc.) eller for hele tabellen hvis gruppen ikke er angitt. Vi vil se hva som vil være utgangen av vanlig SUM () aggregat funksjon og vindu SUM () aggregat funksjon.

følgende er et eksempel på en vanlig SUM() aggregatfunksjon. Det summerer bestillingsbeløpet for hver by.

du kan se fra resultatsettet at en vanlig aggregatfunksjon grupperer flere rader i en enkelt utdatarad, noe som fører til at individuelle rader mister sin identitet.

dette skjer ikke med vindusaggregatfunksjoner. Rader beholder sin identitet og viser også en samlet verdi for hver rad. I eksemplet nedenfor gjør spørringen det samme, nemlig det samler dataene for hver by og viser summen av total ordrebeløp for hver av dem. Spørringen setter imidlertid inn en annen kolonne for det totale bestillingsbeløpet, slik at hver rad beholder sin identitet. Kolonnen merket grand_total er den nye kolonnen i eksemplet nedenfor.

AVG ()

AVG eller Gjennomsnitt fungerer på nøyaktig samme måte Med En Vindusfunksjon.

følgende spørring vil gi deg gjennomsnittlig bestillingsbeløp for hver by og for hver måned (selv om vi for enkelhets skyld bare har brukt data i en måned).

vi angir mer enn ett gjennomsnitt ved å angi flere felt i partisjonslisten.

det er også verdt å merke seg at du kan bruke uttrykk I listene SOM MÅNED (order_date)som vist under spørring. Som alltid kan du gjøre disse uttrykkene så komplekse som du vil så lenge syntaksen er riktig!

Fra bildet ovenfor kan vi tydelig se at vi i gjennomsnitt har mottatt bestillinger på 12 333 for Arlington city i April 2017.

Gjennomsnittlig Ordrebeløp = Total Ordrebeløp / Total Ordre
= (20,000 + 15,000 + 2,000) / 3
= 12,333

du kan også bruke KOMBINASJONEN AV SUM() & COUNT () – funksjonen for å beregne et gjennomsnitt.

MIN ()

min() aggregat-funksjonen finner minimumsverdien for en angitt gruppe eller for hele tabellen hvis gruppen ikke er angitt.

For eksempel ser vi etter den minste ordren (minimumsordre) for hver by vi vil bruke følgende spørring.

MAX ()

AKKURAT SOM min () – funksjonene gir deg minimumsverdien, VIL MAX () – funksjonen identifisere den største verdien av et spesifisert felt for en spesifisert gruppe av rader eller for hele tabellen hvis en gruppe ikke er spesifisert.

la oss finne den største ordren (maksimum bestillingsbeløp) for hver by.

COUNT ()

COUNT() FUNKSJONEN teller poster / rader.

MERK AT DISTINCT ikke støttes med window COUNT () – funksjonen, mens den støttes for den vanlige COUNT () – funksjonen. DISTINKT hjelper deg med å finne distinkte verdier for et angitt felt.

for eksempel, hvis vi ønsker å se hvor mange kunder som har lagt inn en bestilling i April 2017, kan vi ikke direkte telle alle kunder. Det er mulig at samme kunde har lagt inn flere bestillinger i samme måned.

COUNT (customer_name) vil gi deg en feil resultat som det vil telle duplikater. MENS COUNT (DISTINCT customer_name) vil gi deg riktig resultat som det teller hver unik kunde bare en gang.

Gyldig for vanlig TELLE () funksjon:

Ugyldig for vindu TELLER() funksjon:

ovennevnte spørring Med Vindu funksjon vil gi deg under feil.

Nå, la oss finne den totale rekkefølgen mottatt for hver by ved hjelp av window COUNT () funksjon.

ranking Window Functions

AKKURAT Som vindusaggregatfunksjoner samler verdien av et spesifisert felt, rangerer RANGERINGSFUNKSJONER verdiene til et spesifisert felt og kategoriserer dem i henhold til deres rangering.

DEN vanligste BRUKEN AV RANGERINGSFUNKSJONER er å finne de øverste (N) postene basert på en viss verdi. For Eksempel, Topp 10 høyest betalte ansatte, Topp 10 rangerte studenter, Topp 50 største ordrer etc.

følgende STØTTES RANGERING funksjoner:

RANK (), DENSE_RANK(), ROW_NUMBER(), NTILE()

la oss diskutere dem en etter en.

RANK ()

RANK () – funksjonen brukes til å gi en unik rangering til hver post basert på en spesifisert verdi, for eksempel lønn, ordrebeløp etc.

HVIS to poster har samme verdi, vil RANK () – funksjonen tildele samme rangering til begge postene ved å hoppe over neste rangering. Dette betyr – hvis det er to identiske verdier i rang 2, vil den tildele samme rang 2 til begge postene og deretter hoppe over rang 3 og tildele rang 4 til neste post.

la oss rangere hver ordre etter deres ordrebeløp.

fra bildet ovenfor kan du se at samme rang (3) er tildelt to identiske poster (hver med et ordrebeløp på 15 000) og deretter hopper den neste rang (4) og tildele rang 5 til neste rekord.

DENSE_RANK ()

DENSE_RANK () – funksjonen er identisk MED RANG () – funksjonen, bortsett fra at den ikke hopper over noen rangering. DETTE betyr at HVIS to identiske poster blir funnet, vil DENSE_RANK() tildele samme rang til begge postene, men ikke hopp over og hopp over neste rang.

La oss se hvordan dette fungerer i praksis.

som du tydelig kan se ovenfor, blir samme rang gitt til to identiske poster (hver med samme bestillingsbeløp) og deretter blir neste rangnummer gitt til neste post uten å hoppe over en rangverdi.

ROW_NUMBER ()

navnet er selvforklarende. Disse funksjonene tilordner et unikt radnummer til hver post.

radnummeret tilbakestilles for hver partisjon hvis PARTITION BY er angitt. La oss se hvordan ROW_NUMBER () fungerer uten PARTISJON AV og deretter MED PARTISJON AV.

ROW_NUMBER() UTEN PARTISJON av

ROW_NUMBER() MED PARTISJON av

Merk at vi har gjort partisjonen på byen. Dette betyr at radnummeret tilbakestilles for hver by og starter på nytt på 1 igjen. Rekkefølgen på radene bestemmes imidlertid av ordrebeløp, slik at for en gitt by vil det største ordrebeløpet være den første raden og så tildelt rad nummer 1.

NTILE ()

NTILE() er en svært nyttig vindu funksjon. Det hjelper deg å identifisere hva persentil (eller kvartil, eller noen annen underavdeling) en gitt rad faller inn.

Dette betyr at hvis du har 100 rader og du vil opprette 4 kvartiler basert på et angitt verdifelt, kan du gjøre det enkelt og se hvor mange rader som faller inn i hver kvartil.

La oss se et eksempel. I spørringen nedenfor har vi angitt at vi vil opprette fire kvartiler basert på ordrebeløp. Vi vil da se hvor mange ordrer som faller inn i hver kvartil.

NTILE lager fliser basert på følgende formel:Her er vårt eksempel, vi har totalt 10 rader og 4 fliser er spesifisert i spørringen, så antall rader i hver flis vil være 2,5 (10/4). Som antall rader skal være hele tall, ikke et desimal. SQL engine vil tildele 3 rader for de to første gruppene og 2 rader for de resterende to gruppene.

Verdi Vindu Funksjoner

LAG () OG LEAD()

LEAD() og LAG() funksjoner er svært kraftig, men kan være komplisert å forklare. Siden dette er en innledende artikkel nedenfor, ser vi på et veldig enkelt eksempel for å illustrere hvordan du bruker dem.

LAG-funksjonen gjør det mulig å få tilgang til data fra forrige rad i samme resultatsett uten BRUK AV SQL-koblinger. Du kan se i eksempelet nedenfor, ved HJELP AV LAG-funksjonen fant vi forrige bestillingsdato.

Script for å finne forrige ordredato ved HJELP AV LAG() funksjon:

LEAD-funksjonen gir tilgang til data fra neste rad i samme resultatsett uten BRUK AV SQL-koblinger. Du kan se i eksempelet nedenfor, ved HJELP AV BLYFUNKSJONEN fant vi neste bestillingsdato.

Skript for å finne neste ordredato ved HJELP AV BLY () – funksjonen:

FIRST_VALUE() Og LAST_VALUE ()

disse funksjonene hjelper deg med å identifisere første og siste post i en partisjon eller hele tabellen hvis PARTITION BY ikke er spesifisert.

La oss finne den første og siste rekkefølgen av hver by fra vårt eksisterende datasett. MERK ORDER BY clause er obligatorisk for FIRST_VALUE() og LAST_VALUE() funksjoner

Fra bildet ovenfor kan vi tydelig se at første ordre mottatt på 2017-04-02 og siste ordre mottatt på 2017-04-11 For Arlington city, og det fungerer det samme for andre byer.

Nyttige Lenker

• Backup Typer& Strategier for SQL Databaser
• TechNet Artikkel PÅ Over Klausulen
• Msdn Artikkel PÅ DENSE_RANK

Andre gode artikler Fra Ben

HVORDAN SQL Server velger en vranglås offer

Hvordan for å bruke vindu funksjoner