SQLShack (Suomi)
Tässä artikkelissa keskustellaan SQL ROW_NUMBER -funktio. Tämä on jatkoa SQL-olennaisille sarjoille. Tässä oppaassa selitän, mistä ikkunafunktiossa on kyse, ja näet esimerkkiesimerkkejä SQL ROW_NUMBER -funktion taustalla olevien käsitteiden ymmärtämiseksi.
Johdanto
Eniten SQL Serverin yleisesti käytetty toiminto on SQL ROW_NUMBER -funktio. SQL ROW_NUMBER -toiminto on saatavana SQL Server 2005: sta ja uudemmista versioista.
ROW_NUMBER lisää yksilöllisen kasvavan numeron tulosruutuun. Järjestys, jossa rivinumeroita käytetään, määritetään ORDER BY -lausekkeella. Useimmiten yksi tai useampi sarake määritetään ORDER BY -lausekkeessa, mutta on mahdollista käyttää monimutkaisempia lausekkeita tai jopa alakyselyä. Joten se luo jatkuvasti kasvavan integraaliarvon ja se alkaa aina yhdestä ja seuraavat rivit saavat seuraavan korkeamman arvon.
Voit käyttää sitä myös PARTITION BY -lausekkeen kanssa. Mutta kun se ylittää osion rajan tai rajan, se nollaa laskurin ja alkaa arvosta 1. Joten osiossa voi olla arvoja 1, 2, 3 ja niin edelleen, ja toiset osiot käynnistävät laskurin uudelleen luvuista 1, 2, 3… ja niin edelleen.
Perustiedot:
- SQL ROW_NUMBER -funktio on väliaikaisten arvojen sarjan pysyvä generointi, ja se lasketaan dynaamisesti, kun kysely suoritetaan.
- Ei ole takeita siitä, että SQL-kyselyn palauttamat rivit SQL ROW_NUMBER -toiminnon avulla järjestetään täsmälleen samalla tavalla jokaisen suorituksen yhteydessä.
- ROW_NUMBER- ja RANK-toiminnot ovat samanlaisia. ROW_NUMBER: n lähtö on arvosarja, joka alkaa 1: stä 1: n lisäyksellä, mutta vaikka RANK-funktio, arvoja lisätään myös 1: llä, mutta arvot toistuvat linkeille.
- Jos olet kokemus Oraclesta, ROWNUM on sinulle tutumpi. Se on näennäispylväs. Se alkaa 1: llä ja menee alaspäin kasvamalla yhdellä pöydän loppuun.
- SQL ROW_NUMBER -funktio on luonteeltaan dynaaminen ja voimme nollata arvot käyttämällä PARTITION BY -lauseketta.
- Kyselyn ORDER BY -lauseke ja OVER ORDER BY -lauseke. lauseella ei ole mitään tekemistä toistensa kanssa.
Syntaksi
|
1
2
|
RIVINUMBER ()
OVER (] order_by_clause col1, col2 ..)
|
ROW_NUMBER
ROW_NUMBER ja OVER-funktio ja käytä sitten sulkeissa ORDER BY -lauseketta. Sen on käytettävä ORDER BY -lauseketta eräänlaisen järjestyksen asettamiseksi tulosjoukolle.
OVER
OVER-lauseke määrittää ikkunan tai riviryhmän, jonka ikkuna toiminto toimii, joten sinun on todella tärkeää ymmärtää. Mahdolliset OVER-lausekkeen osat ovat ORDER BY ja PARTITION BY.
OVER-lauseen ORDER BY -lauseketta tuetaan, kun rivit on järjestettävä tietyllä tavalla, jotta toiminto toimisi.
|
1
|
OSIO OSALLA arvo_lauseke1
|
PARTITION BY
Partition By -lauseke on valinnainen. Määritettäessä arvoa se jakaa FROM-lauseen tuottaman tulosjoukon osioihin, joihin käytetään SQL ROW_NUMBER -funktiota. PARTITION-lauseessa määritetyt arvot määrittelevät tulosjoukon rajat. Jos PARTITION BY -lauseketta ei ole määritetty, OVER-lauseke toimii tulosjoukon kaikilla riveillä yhtenä tietojoukkona. Tämä lauseke voi koostua yhdestä tai useammasta sarakkeesta, monimutkaisemmasta lausekkeesta tai jopa alakyselystä.
order_by_clause
Order by lause on pakollinen lauseke. Se määrittää väliaikaisen arvon järjestyksen ja assosiaation määritetyn osion riveille. ORDER BY -lauseke on OVER-lausekkeen lauseke, ja se määrittää, miten rivit on järjestettävä tietyllä tavalla funktiota varten.
Demo
Tässä osiossa Katson SQL ROW_NUMBER -funktiota. Olen käyttänyt koko esittelyn ajan AdventureWorks2016-tietokantaa.
ROW_NUMBER: n käyttö SQL-kyselyssä
Seuraavissa esimerkeissä näemme OVER-lausekkeen käytön.
Hanki luettelo kaikista asiakkaista projisoimalla sarakkeet, kuten SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue ja RowNum. Rivi_numero-toimintoa käytetään CustomerID-sarakkeen järjestyksessä.Väliaikainen arvo alkaa 1: stä, joka on määritetty CustomerID-tilauksen perusteella, ja arvoja jatketaan taulukon viimeisiin riveihin saakka. CustomerID-tilausta ei taata, koska emme määritä kyselyssä ORDER BY -lauseketta.
|
1
2
3
4
5
6
7
8
9
10
11
|
KÄYTÄ AdventureWorks2016;
GO
VALITSE RIVINUMMA () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;
|
Kuinka järjestää lauseen mukaan
Seuraava esimerkki käyttää kyselyssä ORDER BY -lauseketta. SalesOrderID-sarakkeessa käytetty kyselyn ORDER BY -lauseke. Voimme nähdä, että tulosteet ovat edelleen järjestettyjä ja palautettuja. Rivi_numeroa käytetään edelleen asiakastunnukseen. Lähtö osoittaa, että kyselyn ORDER BY ja OVER-lauseen ORDER BY ovat riippumattomia lähdöstä.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
KÄYTÄ AdventureWorks2016;
GO
VALITSE RIVINUMMA () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
TILAA MUKAAN SalesOrderID;
|
Kuinka käyttää useita sarakkeita OVER-lausekkeen kanssa
Seuraavassa esimerkissä näet, että olemme listanneet asiakastunnuksen ja OrderDate-lausekkeen ORDER BY. Tämä antaa asiakkaalle tiedot viimeisimmistä tilaustiedoista sekä koko tulosjoukolle määritetyn numerosarjan.
|
1
2
3
4
5
6
7
8
9
10
|
KÄYTÄ AdventureWorks2016;
GO
VALITSE RIVINUMMA () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
|
SQL ROW_NUMBER -funktion käyttäminen PARTITIONin kanssa
Seuraava esimerkki käyttää PARTITION BY -lauseketta CustomerID- ja OrderDate-kentissä. Tuloksesta näet, että asiakkaalla 11019 on kolme tilausta kuukaudelta 2014-kesäkuu. Tässä tapauksessa osio tehdään useammalle kuin yhdelle sarakkeelle.
Osio on OrderDate- ja CustomerID-yhdistelmä. Rivi_numero alkaa alusta jokaiselle ainutlaatuiselle OrderDate- ja CustomerID-yhdistelmälle. Tällä tavalla on helppo löytää asiakas, joka on tehnyt useita tilauksia samana päivänä.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
KÄYTÄ AdventureWorks2016;
GO
VALITSE RIVINUMMA () YLITTÄVÄ (OSIOITUMINEN asiakastunnuksella,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;
|
Kuinka palauttaa rivien osajoukko käyttämällä CTE: tä ja ROW_NUMBER
Seuraavassa esimerkissä analysoidaan SalesOrderHeader, jotta voimme näyttää kunkin asiakkaan viiden suurimman tilauksen kuukausi. Kuukausi-funktiota käyttämällä orderDate-sarakkeita manipuloidaan kuukausiosan noutamiseksi.Tällä tavalla tiettyä kuukautta (OrderDate) vastaava myynti ja asiakas (CustomerID) jaetaan.
CTE: tä käytetään kunkin kuukauden viiden suurimman tilauksen luetteloon. Osiotiedoille luodaan ikkuna, joka määritetään arvojen kanssa ja sitten CTE kutsutaan hakemaan suurimmat tilaukset.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CTE: llä
AS (VALITSE RIVINUMBER YLI (OSIO asiakastunnuksen mukaan, KUUKAUSI (Tilauspäivä) TILAUS SUMMAN KUVAUS, TotalDue KUVAUS) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) kuukausi,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5
|
Yhteenveto
Olemme toistaiseksi tarkistaneet SQL ROW_NUMBER -funktion yksityiskohtaisesti. Olemme keskustelleet useista esimerkeistä yksinkertaisista monimutkaisiin. Keskustelimme myös SQL ROW_NUMBER -funktion käytöstä CTE: n (Common Table Expressions) kanssa. Useimmissa tapauksissa näet aina ylilausekkeen jokaisessa ikkunatoiminnossa.
Ylälauseke määrittää ikkunan, jonka kukin rivi näkee. Ylälausekkeen sisällä on osio, jota taas tukevat kaikki ikkunafunktiot, jota seuraa järjestys lauseella. Siinä kaikki nyt … Toivottavasti nautit artikkelin lukemisesta.
- Kirjoittaja
- Viimeisimmät viestit
Erikoisuuteni on suunnitella & korkean käytettävyyden ratkaisujen ja alustojen välisen DB-siirron toteuttamista. Tällä hetkellä työskentelevät tekniikat ovat SQL Server, PowerShell, Oracle ja MongoDB.
Näytä kaikki viestit, jotka on kirjoittanut Prashanth Jayaram
- Nopea yleiskatsaus SQL-tietokantatarkastukseen – 28. tammikuuta 2021
- Azure Data Sync -sovelluksen asettaminen Azure SQL -tietokantojen ja paikallisen SQL Serverin välille – 20. tammikuuta 2021
- Azure SQL -tietokannan tuonti- ja vientitoimintojen suorittaminen PowerShellin avulla – 14. tammikuuta , 2021