SQLShack (Norsk)

Denne artikkelen vil dekke SQL PARTITION BY-setningen og, spesielt forskjellen med GROUP BY i en valgt uttalelse. Vi vil også utforske ulike bruksområder for SQL PARTITION BY.

Vi bruker SQL PARTITION BY for å dele resultatsettet inn i partisjoner og utføre beregning på hver delmengde av partisjonerte data.

Forberedelse av prøve Data

La oss lage en ordretabell i eksempeldatabasen SQLShackDemo og sette inn poster for å skrive ytterligere spørsmål.

Jeg bruker ApexSQL Generate for å sette inn eksempeldata i denne artikkelen. Høyreklikk på ordretabellen og generer testdata.

Den starter ApexSQL Generate. Jeg genererte et skript for å sette inn data i ordretabellen. Utfør dette skriptet for å sette inn 100 poster i ordretabellen.

Når vi utfører innsettingsuttalelser, kan vi se dataene i ordretabellen i følgende bilde.

Vi bruker SQL GROUP BY-ledd til å gruppere resultater etter spesifisert kolonne og bruker samlede funksjoner som Gj.sn. (), Min (), Maks () for å beregne nødvendige verdier.

Gruppere etter funksjonssyntaks

Anta at vi vil finne følgende verdier i ordretabellen

• Minimumsverdi i en by
• Maksimum ordreverdi i en by
• Gjennomsnittlig ordreverdi i en by

Utfør følgende spørring med GROUP BY-ledd for å beregne disse verdiene.

I skjermbildet nedenfor kan vi se Gjennomsnitt, Minimum og maksimale verdier gruppert etter CustomerCity.

Nå vil vi også legge til kolonnen CustomerName og OrderAmount i utdataene. La oss legge til disse kolonnene i seleksjonsuttalelsen og utføre følgende kode.

Når vi utfører dette spørsmålet, får vi en feilmelding . I SQL GROUP BY-setningen kan vi bruke en kolonne i select-setningen hvis den også brukes i Group by clause. Det tillater ikke noen kolonne i select-setningen som ikke er en del av GROUP BY-setningen.

Vi kan bruke SQL DELING BY-klausul for å løse dette problemet. La oss utforske det nærmere i neste avsnitt.

SQL PARTITION BY

Vi kan bruke SQL PARTITION BY-setningen med OVER-setningen for å spesifisere kolonnen som vi trenger for å utføre aggregering. . I forrige eksempel brukte vi Group By med CustomerCity-kolonnen og beregnet gjennomsnitts-, minimums- og maksimumsverdier.

La oss kjøre dette scenariet på nytt med SQL PARTITION BY-setningen ved hjelp av følgende spørsmål.

I utgangen får vi aggregerte verdier som ligner på en GROUP Etter klausul. Du kan merke en forskjell i utdata for SQL PARTITION BY- og GROUP BY-leddutdata.

I forrige eksempel får vi en feilmelding hvis vi prøver å legge til en kolonne som ikke er en del av GROUP BY-setningen.

Vi kan legge til nødvendige kolonner i et utvalgssetning med SQL PARTITION BY-leddet. . La oss legge til CustomerName og OrderAmount-kolonner og utføre følgende spørsmål.

Vi får CustomerName og OrderAmount-kolonnen sammen med utdataene fra den samlede funksjonen. Vi får også alle radene som er tilgjengelige i ordretabellen.

I skjermbildet nedenfor kan du for CustomerCity Chicago utføre aggregeringer (Gj.sn., Min og Maks) og gir verdier i respektive kolonner.

På samme måte kan vi bruke andre samlede funksjoner som som telling for å finne ut totalt antall bestillinger i en bestemt by med SQL PARTITION BY-setningen.

Vi kan se ordretellinger for en bestemt by. For eksempel har vi to bestillinger fra Austin city derfor; den viser verdi 2 i CountofOrders-kolonnen.

PARTITION BY klausul med ROW_NUMBER ()

Vi kan bruk SQL PARTITION BY-setningen med ROW_NUMBER () -funksjonen for å ha et radnummer på hver rad. Vi definerer følgende parametere for å bruke ROW_NUMBER med SQL PARTITION BY-setningen.

• PARTITION BY-kolonne – I dette eksemplet vil vi dele data i CustomerCity-kolonnen
• Bestill etter : I ORDER BY-kolonnen definerer vi en kolonne eller tilstand som definerer radnummer. I dette eksemplet ønsker vi å sortere data i kolonnen OrderAmount

I skjermbildet nedenfor får vi se for CustomerCity Chicago , har vi rad nummer 1 for bestilling med høyest beløp 7577,90. det gir radnummer med synkende OrderAmount.

DELING BY-ledd med kumulativ totalverdi

Anta at vi ønsker å få en kumulativ sum for ordrene i en partisjon.Kumulativ total skal være av den gjeldende raden og den følgende raden i partisjonen.

For eksempel i Chicago by, vi har fire bestillinger.

I det følgende spørsmålet har vi den angitte ROWS-setningen å se foreles gjeldende rad (ved hjelp av AKTUELL RAD) og neste rad (ved hjelp av 1 FØLGENDE). Den beregner videre summen på disse radene ved hjelp av sum (Orderamount) med en partisjon på CustomerCity (ved hjelp av OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).

På samme måte kan vi beregne det kumulative gjennomsnittet ved hjelp av følgende spørsmål med SQL PARTITION BY-setningen.

RADER UBEGRENSET FREMGÅENDE med PARTITION BY-setningen

Vi kan bruke RADER UBEGRENSET FREMGANG med SQL PARTITION BY-leddet for å velge en rad i en partisjon før gjeldende rad og høyeste verdi r ow etter gjeldende rad.

I den følgende tabellen kan vi se for rad 1; den har ikke noen rad med høy verdi i denne partisjonen. Derfor er kumulativ gjennomsnittsverdi den samme som i rad 1 OrderAmount.

For rad2 ser det etter nåværende radverdi (7199.61) og høyeste verdi rad 1 (7577.9). Den beregner gjennomsnittet for disse to beløpene.

For rad 3 ser det etter nåverdi (6847,66) og høyere beløpsverdi enn denne verdien som er 7199,61 og 7577,90. Den beregner gjennomsnittet av disse og returnerer.

Utfør følgende spørsmål for å få dette resultatet med eksempeldataene våre.

Konklusjon

I denne artikkelen utforsket vi SQL PARTIION BY-setningen og sammenligningen med GROUP BY-setningen. Vi lærte også bruken med noen få eksempler. Jeg håper du finner denne artikkelen nyttig og gjerne stille spørsmål i kommentarene nedenfor

• Forfatter
• Nylige innlegg

Som MCSA-sertifisert og Microsoft Certified Trainer i Gurgaon, India, med 13 års erfaring, jobber Rajendra for en rekke store selskaper med fokus på ytelsesoptimalisering, overvåking, høy tilgjengelighet , og katastrofegjenopprettingsstrategier og implementering. Han er forfatter av hundrevis av autoritative artikler om SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS / Amazon RDS, Git og relaterte teknologier som har blitt sett av over 10m lesere til dags dato.
Han er skaperen av en av de største gratis online samlingene av artikler om et enkelt emne, med sin 50-delte serie om SQL Server Always On Availability Groups. Basert på sitt bidrag til SQL Server-fellesskapet, har han blitt anerkjent med forskjellige priser, inkludert den prestisjetunge «Årets beste forfatter» kontinuerlig i 2020 og 2021 på SQLShack.
Raj er alltid interessert i nye utfordringer, så hvis du trenger rådgivning hjelp til ethvert emne som dekkes i hans skrifter, kan du nås på [email protected]
Se alle innlegg av Rajendra Gupta

Siste innlegg av Rajendra Gupta (se alle)

• Tidsavbrudd for økt i SQL Server Alltid på tilgjengelighet Grupper – 8. februar 2021
• Utføre mindre og større versjonsoppgraderinger for AWS RDS SQL Server – 29. januar 2021
• Distribuere AWS RDS PostgreSQL-forekomster – 27. januar 2021