Tämä artikkeli kattaa SQL PARTITION BY -lausekkeen ja erityisesti ero GROUP BY: n kanssa valitussa lauseessa. Tutkimme myös useita SQL PARTITION BY -tyyppisiä käyttötapoja.
Käytämme SQL PARTITION BY -tulosjoukkoa jakamaan tulosjoukko osioihin ja suorittamaan laskelmat jokaiselle ositetun datan osajoukolle.
Näytteen valmistelu Tiedot
Luodaan Tilaukset-taulukko SQLShackDemo-esimerkkitietokantaani ja lisätään tietueita uusien kyselyjen kirjoittamiseen.
Käytän ApexSQL Generate -sovellusta lisätäksesi näytetietoja tähän artikkeliin. Napsauta hiiren kakkospainikkeella Tilaukset-taulukkoa ja Luo testitiedot.
Se käynnistää ApexSQL Generate -ominaisuuden. Loin komentosarjan tietojen lisäämiseksi Tilaukset-taulukkoon. Suorita tämä komentosarja lisätäksesi 100 tietuetta Tilaukset-taulukkoon.
Kun suoritamme lisäyslausekkeet, voimme nähdä Tilaukset-taulukon tiedot seuraavassa kuvassa.
Käytämme SQL GROUP BY -lauseketta ryhmittelemään tulokset määritetyn sarakkeen mukaan ja laskemaan vaaditut arvot yhdistämistoiminnoilla, kuten Avg (), Min (), Max ().
Ryhmittele funktion syntaksin mukaan
|
1
2
3
4
|
SELECT-lauseke, koontitoiminto ()
FROM-taulukoista
WHERE ehdot
RYHMÄ BY-lausekkeella
|
Oletetaan, että haluamme löytää seuraavat arvot Tilaukset-taulukosta.
- Minimitilausarvo kaupungissa
- Suurin tilausarvo kaupungissa
- Keskimääräinen tilauksen arvo kaupungissa
Suorita seuraava kysely GROUP BY -lausekkeella näiden arvojen laskemiseksi.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Tilausmäärä) TotalOrderAmount
FROM.
RYHMÄ asiakaskunnan mukaan;
|
Seuraavassa kuvakaappauksessa näemme Keskiarvo, Minimi ja enimmäisarvot ryhmiteltynä CustomerCityn mukaan.
Haluamme nyt lisätä tulokseen myös CustomerName- ja OrderAmount -sarakkeen. Lisätään nämä sarakkeet select-lauseeseen ja suoritetaan seuraava koodi.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
alkaen.
RYHMÄ asiakaskunnan mukaan;
|
Kun olemme suorittaneet tämän kyselyn, saat virheilmoituksen . SQL GROUP BY -lausekkeessa voimme käyttää select-lauseen saraketta, jos sitä käytetään myös Group by lausekkeessa. Se ei salli valintalausekkeessa yhtään saraketta, joka ei ole osa GROUP BY -lauseketta.
Voimme käyttää SQL: ää PARTITION BY -lauseke tämän ongelman ratkaisemiseksi. Tutkitaan sitä tarkemmin seuraavassa osassa.
SQL PARTITION BY
Voimme käyttää SQL PARTITION BY -lauseketta OVER-lausekkeen kanssa määritelläksesi sarakkeen, johon meidän on suoritettava aggregaatio. . Edellisessä esimerkissä käytimme Group By with CustomerCity -saraketta ja laskimme keskiarvon, minimi- ja maksimiarvot.
Suoritetaan tämä skenaario uudelleen SQL PARTITION BY -lausekkeella seuraavaa kyselyä käyttäen.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (tilausmäärä) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM;
|
Lähdössä saadaan kootut arvot, jotka ovat samanlaisia kuin RYHMÄ lausekkeella. Saatat huomata eron SQL PARTITION BY- ja GROUP BY -lausekkeiden lähdöissä.
|
Ryhmittele |
SQL PARTITION BY |
|
Saamme rajoitetun määrän tietueita käyttämällä Group By -lauseketta |
Saamme kaikki taulukon tietueet käyttämällä PARTITION BY -lauseketta. |
|
Se antaa yhden rivi ryhmää kohti tulosjoukossa. Esimerkiksi saamme tuloksen kullekin CustomerCity-ryhmälle GROUP BY -lausekkeessa. |
Se antaa kootut sarakkeet jokaisen tietueen kanssa määritetyssä taulukossa. Tilaukset-taulukossa on 15 tietuetta. SQL PARTITION BY-kyselylähdössä saadaan myös 15 riviä sekä Min-, Max- ja keskiarvot. |
Edellisessä esimerkissä saamme virheilmoituksen, jos yritämme lisätä sarakkeen, joka ei ole osa GROUP BY -lauseketta.
Voimme lisätä vaaditut sarakkeet select-käskyyn SQL PARTITION BY -lausekkeella. . Lisätään Asiakasnimi ja Tilausmäärä -sarakkeet ja suoritetaan seuraava kysely.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (Tilausmäärä) YLITTY (OSAKKEET Customercityn mukaan) AS MinOrderAmount,
SUM (Tilausmäärä) OVER (PARTITION BY asiakaskohtaisesti) TotalOrderAmount
ALK.
|
Saamme CustomerName- ja OrderAmount-sarakkeet yhdessä yhdistetty funktio. Saamme myös kaikki rivit Tilaukset-taulukossa.
Seuraavassa kuvakaappauksessa voit: CustomerCity Chicago, se suorittaa aggregaatit (Avg, Min ja Max) ja antaa arvot vastaavissa sarakkeissa.
Vastaavasti voimme käyttää muita aggregaattitoimintoja, kuten kuten laskea selvittämään tilausten kokonaismäärä tietyssä kaupungissa SQL PARTITION BY -lausekkeen avulla.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
AsiakkaanNimi,
Tilausmäärä
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO) N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Voimme nähdä tietyn kaupungin tilausmäärät. Esimerkiksi meillä on kaksi tilausta Austinin kaupungista; se näyttää arvon 2 CountofOrders -sarakkeessa.
PARTITION BY -lauseke rivillä ROW_NUMBER ()
Voimme käytä SQL PARTITION BY -lauseketta ROW_NUMBER () -toiminnon kanssa, jotta jokaiselle riville annetaan rivinumero. Määritämme seuraavat parametrit, jotta ROW_NUMBER voidaan käyttää SQL PARTITION BY -lausekkeen kanssa.
- PARTITION BY -sarake – Tässä esimerkissä haluamme jakaa tiedot CustomerCity-sarakkeeseen
- Järjestä : Määritämme ORDER BY -sarakkeessa sarakkeen tai ehdon, joka määrittelee rivinumeron. Tässä esimerkissä haluamme lajitella tiedot OrderAmount-sarakkeesta
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
asiakkaan nimi,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS ”Rivinumero”,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Seuraavassa kuvakaappauksessa näemme CustomerCity Chicagon , meillä on rivin numero 1 tilaukselle, jolla on suurin määrä 7577.90. se tarjoaa rivinumeron laskevalla OrderAmount-arvolla.
PARTITION BY -lauseke kumulatiivisella kokonaisarvolla
Oletetaan, että haluat saada kumulatiivisen kokonaismäärän tilauksista tilauksessa.Kumulatiivisen kokonaismäärän tulee olla nykyisen rivin ja osion seuraavan rivin.
Esimerkiksi Chicagon kaupungissa meillä on neljä tilausta.
|
CustomerCity |
CustomerName |
Sijoitus |
Tilausmäärä |
Kumulatiiviset rivit yhteensä |
Kumulatiivinen summa |
|
Chicago |
Marvin |
Sijoitus 1 +2 |
|||
|
Chicago |
Lawrence |
Sijoitus 2 + 3 |
|||
|
Chicago |
Alex |
Sijoitus 3 + 4 |
|||
|
Chicago |
Jerome |
Sijoitus 4 |
Seuraavassa kyselyssä määritimme ROWS-lausekkeen lue nykyinen rivi (nykyisen rivin avulla) ja seuraava rivi (käyttäen 1 SEURAAVA). Lisäksi se laskee näiden rivien summan käyttämällä summaa (Orderamount) ja osiota CustomerCityssä (käyttäen OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
AsiakkaanNimi,
Tilausmäärä,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS ”Rivinumero”,
MUUTA (VARCHAR (20), SUM (tilausmäärä) OVER (OSITTELU asiakaskohtaisesti
Tilauksen mukaan OrderAmount DESC -RIVIT JOKAINEN RIVI JA 1 SEURAAVA), 1) AS CumulativeTotal,
|
Vastaavasti voimme laskea kumulatiivisen keskiarvon käyttämällä seuraava kysely SQL PARTITION BY -lausekkeen kanssa.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
Tilauksen mukaan OrderAmount DESC) AS ”Rivinumero”,
MUUTA (VARCHAR (20), AVG (tilausmäärä) YLITTÄVÄ (OSITTAMINEN asiakaskohtaisesti
TILAA TILAUKSEN JÄLKEEN JÄLKEISEN RIVIN JA 1 JÄLKEEN) , 1) AS-kumulatiivinenAVG
|
RIVITTÄMÄT RAJOITTAMATTOMAT ENNEN PARTITION BY -lauseketta
Voimme käyttää SQL PARTITION BY -lausekkeen kanssa RIVITTÄMÄTÖN RIVITÄ, jos valitset rivin SQL Partition by BY -lausekkeella. ennen nykyistä riviä ja suurinta arvoa r ow nykyisen rivin jälkeen.
Seuraavassa taulukossa voimme nähdä rivin 1; sillä ei ole mitään riviä, jolla olisi suuri arvo tässä osiossa. Siksi kumulatiivinen keskiarvo on sama kuin rivillä 1 OrderAmount.
Rivillä 2 se etsii nykyisen rivin arvon (7199,61) ja korkeimman arvon rivin 1 (7577,9). Se laskee näiden kahden summan keskiarvon.
Rivillä 3 se etsii nykyistä arvoa (6847,66) ja suurempaa summan arvoa kuin tämä arvo, joka on 7199,61 ja 7577,90. Se laskee näiden keskiarvon ja palauttaa.
|
CustomerCity |
Asiakasnimi |
Sijoitus |
Tilausmäärä |
Kumulatiiviset keskimääräiset rivit |
Kumulatiivinen keskiarvo |
|
Chicago |
Marvin |
Sijoitus 1 |
|||
|
Chicago |
Lawrence |
Sijoitus 1 + 2 |
|||
|
Chicago |
Alex |
Sijoitus 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Sijoitus 1 + 2 + 3 + 4 |
Suorita seuraava kysely saadaksesi tämän tuloksen näytetiedoillamme.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
TILAA OrderAmount DESC) AS ”Rivinumero”,
MUUNNA (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
TILAA TILAUKSESSA OrderAmount DESC -RIVIT RAJOITTAMATON), 1) AS CumulativeAvg
FROM.;
|
Johtopäätös
Tässä artikkelissa tutkimme SQL PARTIION BY -lauseketta ja sen vertailua GROUP BY -lausekkeeseen. Opimme myös sen käytön muutamalla esimerkillä. Toivon, että pidät tästä artikkelista hyödyllistä ja voit kysyä kysymyksiä alla olevista kommenteista.
- Kirjoittaja
- Viimeisimmät artikkelit
Hän on luonut yhden suurimmista ilmaisista online-artikkelikokoelmista yhdestä aiheesta, 50-osaisella sarjallaan SQL Server Always On Availability Groups -ryhmissä. Hänen osuutensa perusteella SQL Server -yhteisöön hänet on tunnustettu useilla palkinnoilla, kuten arvostettu ”Vuoden paras kirjailija” jatkuvasti vuosina 2020 ja 2021 SQLShackissa.
Raj on aina kiinnostunut uusista haasteista, joten jos tarvitset konsultointia apua mistä tahansa hänen kirjoituksissaan käsitellystä aiheesta, hän on tavoitettavissa osoitteessa [email protected]
Näytä kaikki käyttäjän Rajendra Gupta viestit
- Istunnon aikakatkaisut SQL Serverissä aina käytettävissä Ryhmät – 8. helmikuuta 2021
- Pienien ja suurten versioiden päivitykset AWS RDS SQL Serverille – 29. tammikuuta 2021
- AWS RDS PostgreSQL -esiintymien käyttöönotto – 27. tammikuuta 2021