SELECT – OVER-lauseke (Transact-SQL)

• 11.11.2017
• 17 minuuttia aikaa lukea
• • V
  • L
  • c
  • j
  • M
  • +11

Koskee: SQL Server -palvelinta (kaikki tuetut versiot) Azure SQL -tietokanta Azure SQL Hallittu ilmentymä Azure Synapse Analytics div id = ”19fbb4c644”> Rinnakkaistietovarasto

Määrittää rivisarjan osituksen ja järjestyksen ennen siihen liittyvän ikkunatoiminnon käyttöä. Toisin sanoen OVER-lauseke määrittää ikkunan tai käyttäjän määrittelemän riviryhmän kyselyn tulosjoukossa. Ikkunatoiminto laskee sitten arvon jokaiselle ikkunan riville. Voit käyttää OVER-lauseketta funktioiden kanssa laskeaksesi yhteenlasketut arvot, kuten liikkuvat keskiarvot, kumulatiiviset aggregaatit, juoksevat kokonaissummat tai ylin N ryhmää kohti.

• Ranking-toiminnot

• aggregaattifunktiot

• analyyttiset funktiot

• funktion seuraava arvo

Transact-SQL-syntaksin yleissopimukset

Syntaksi

Argumentit

Ikkunafunktioiden OVER -lausekkeessa voi olla seuraavat argumentit:

• PARTITION BY, joka jakaa kyselyn tulosjoukon osioihin.
• ORDER BY, joka määrittää rivien loogisen järjestyksen tulosjoukon jokaisessa osiossa.
• RIVIT / ALUE, joka rajoittaa osion rivejä määrittämällä aloitus- ja loppupisteet osiossa. Se vaatii argumentin ORDER BY ja oletusarvo on osion alusta nykyiseen elementtiin, jos argumentti ORDER BY on määritetty.

Jos et määritä argumenttia, ikkunafunktioita käytetään koko tulosjoukkoon.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Jakaa kyselyn tulosjoukon osioihin. Ikkunafunktio lisätään kuhunkin osioon erikseen ja laskenta käynnistyy uudelleen jokaiselle osalle ion.

Jos PARTITION BY -asetusta ei ole määritetty, funktio käsittelee kyselyn tulospaketin kaikkia rivejä yhtenä osiona. Toimintoa käytetään kaikissa riveissä. osiossa, jos et määritä lauseketta ORDER BY.

PARTITION BY value_expression

Määrittää sarakkeen, jolla rivi jaetaan. arvo_lauseke voi viitata vain sarakkeisiin, jotka FROM-lauseke tarjoaa käyttöön. arvo_lauseke ei voi viitata lausekkeisiin tai aliaksiin valintaluettelossa. arvo_lauseke voi olla sarake-lauseke, skalaarinen alakysely, skalaarifunktio tai käyttäjän määrittelemä muuttuja.

TILAA MITÄ

Määrittää rivien loogisen järjestyksen tulosjoukon jokaisessa osiossa. Toisin sanoen se määrittää loogisen järjestyksen, jossa ikkunatoiminnon laskenta suoritetaan.

• Jos sitä ei ole määritetty, oletusjärjestys on ASC ja ikkuna-toiminto käyttää kaikkia osion rivejä.
• Jos se on määritetty ja ROWS / RANGE-arvoa ei määritetä, oletusarvoa RANGE UNBOUNDED PRECEDING AND CURRENT ROW käytetään ikkunalle kehys toiminnoilla, jotka voivat hyväksyä valinnaiset ROWS / RANGE-määritykset (esimerkiksi min tai max).

order_by_expression
Määrittää sarakkeen tai lausekkeen, jonka mukaan lajitellaan. order_by_expression voi viitata vain sarakkeisiin, jotka FROM-lause tarjoaa käyttöön. Kokonaislukua ei voida määrittää edustamaan sarakkeen nimeä tai aliasta.

COLLATE collation_name
Määrittää, että ORDER BY -operaatio on suoritettava collation_name-määritetyn lajittelun mukaisesti. collation_name voi olla joko Windowsin lajittelunimi tai SQL-lajittelunimi. Lisätietoja on ohjeaiheessa Lajittelu ja Unicode-tuki. COLLATE on käytettävissä vain sarakkeille, joiden tyyppi on char, varchar, nchar ja nvarchar.

ASC | DESC
Määrittää, että määritetyn sarakkeen arvot on lajiteltava nousevassa tai laskevassa järjestyksessä. ASC on oletuslajittelujärjestys.Nolla-arvoja käsitellään pienimpinä mahdollisina arvoina.

RIVIT tai ALUE

Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

Muut rajoitukset rivit osiossa määrittämällä alku- ja loppupisteet osiossa. Tämä tehdään määrittämällä riviryhmä nykyiseen riviin joko loogisen assosiaation tai fyysisen assosiaation avulla. Fyysinen assosiaatio saavutetaan käyttämällä ROWS-lauseketta.

ROWS-lauseke rajoittaa osion rivejä määrittämällä kiinteän määrän rivejä nykyistä riviä edeltävälle tai seuraavalle riville. Vaihtoehtoisesti RANGE-lauseke rajoittaa loogisesti osion rivejä määrittelemällä arvoalue nykyisen rivin arvoon nähden. Edelliset ja seuraavat rivit määritetään ORDER BY -lausekkeen järjestyksen perusteella. Ikkunakehys ”RANGE … CURRENT ROW …” sisältää kaikki rivit, joilla on samat arvot ORDER BY -lausekkeessa kuin nykyisellä rivillä. Esimerkiksi RIVIT 2 ENNEN ENNEN KÄYTETTÄVÄT JA NYKYINEN RIVI tarkoittaa, että toimintorivien ikkuna on kooltaan kolme riviä, alkaen kahdesta rivistä, jotka edeltävät nykyistä riviä.

RAJOITTAMATON ENNAKOINTI

Koskee seuraavia: SQL Server 2012 (11.x) ja uudemmat.

Määrittää, että ikkuna alkaa osion ensimmäiseltä riviltä. RAJOITTAMATON ENNAKOINTI voidaan määrittää vain ikkunan aloituspisteeksi.

< allekirjoittamaton arvomääritys > ESITTELY
Määritetty < allekirjoittamattomalla arvomäärityksellä > osoittamaan nykyistä riviä edeltävien rivien tai arvojen lukumäärä. Tätä määritystä ei sallita ALUEELLA.

NYKYINEN RIVI

Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

Määrittää, että ikkuna alkaa tai päättyy nykyiselle riville käytettäessä ROWS tai nykyiseen arvoon käytettäessä RANGE. NYKYINEN RIVI voidaan määrittää sekä lähtö- että loppupisteeksi.

JA

Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

Käytetään joko RIVIT tai ALUE kanssa ikkunan alemman (alku) ja ylemmän (loppu) rajapisteen määrittämiseen. < ikkunakehys sidottu > määrittelee rajan aloituskohdan ja < ikkunakehys sidottu > määrittelee rajan loppupisteen. Yläraja ei voi olla pienempi kuin alaraja.

RAJOITTAMATON JÄLKEEN

Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

Määrittää, että ikkuna päättyy osion viimeiseen riviin. RAJOITTAMATON SEURANTA voidaan määrittää vain ikkunan päätepisteeksi. Esimerkiksi ALUE VIRTARIVIN JA RAJOITTAMATON SEURANTA määrittää ikkunan, joka alkaa nykyisellä rivillä ja päättyy osion viimeisellä rivillä.

< allekirjoittamaton arvon määritys > SEURAAVA
Määritetty < allekirjoittamattomalla arvomäärityksellä > osoittamaan rivejä tai arvoja nykyisen rivin seuraamiseksi. Kun < allekirjoittamaton arvomääritys > SEURAAVA määritetään ikkunan aloituspisteeksi, loppupisteen on oltava < allekirjoittamaton arvomääritys > SEURAAVA. Esimerkiksi RIVIT 2 SEURAAVAN JA 10 SEURAAVAN välillä määrittelee ikkunan, joka alkaa toisella rivillä, joka seuraa nykyistä riviä, ja päättyy kymmenennellä rivillä, joka seuraa nykyistä riviä. Tätä määritystä ei sallita RANGE: lle.

allekirjoittamaton kokonaisluku literaali
Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

On positiivinen kokonaislukutunnus (sisältää 0 ), joka määrittää rivien tai arvojen määrän edeltävän tai seuraavan nykyisen rivin tai arvon jälkeen. Tämä määritys on voimassa vain ROWS-sarjoille.

Yleisiä huomautuksia

Yhdessä kyselyssä voi käyttää useampaa kuin yhtä ikkunatoimintoa yhdellä FROM-lauseella. Kunkin funktion OVER-lause voi erota osioinnissa ja järjestyksessä.

Jos PARTITION BY -asetusta ei ole määritelty, funktio käsittelee kyselytulosjoukon kaikkia rivejä yhtenä ryhmänä.

Tärkeää !

Jos RIVIT / ALUE on määritetty ja < -ikkunan kehystä, joka edeltää >, käytetään < ikkunakehyksen laajuus > (lyhyt syntakse), tätä määrittelyä käytetään ikkunakehyksen raja-aloituspisteeseen ja CURRENT ROW raja-loppupisteeseen. Esimerkiksi ”RIVIT 5 ENNEN” on yhtä suuri kuin ”RIVIT 5 ENNEN KÄYTETTÄVÄN JA NYKYISEN RIVIN”.

Rajoitukset ja rajoitukset

OVER-lausetta ei voi käyttää CHECKSUM-aggregaattitoiminnon kanssa.

RANGE -asetusta ei voida käyttää < -allekirjoitetun arvon määrityksen kanssa > ENNEN tai < allekirjoittamaton arvomääritys > SEURAAVA.

Sijoituksesta, aggregaatista tai analyyttisestä riippuen OVER-lauseen kanssa käytetty funktio, < ORDER BY -lauseke > ja / tai < RIVIT- ja ALUE-lauseketta > ei ehkä tueta.

Esimerkkejä

A. OVER-lauseen käyttäminen ROW_NUMBER-funktion kanssa

Seuraava esimerkki osoittaa OVER-lauseen käyttämisen ROW_NUMBER-funktion kanssa rivinumeron näyttämiseksi kullekin osion riville. OVER-lauseessa määritetty ORDER BY -lauseke järjestää kunkin osion rivit sarakkeella SalesYTD. SELECT-käskyn ORDER BY -lauseke määrittää järjestyksen, jossa koko kyselyn tulosjoukko palautetaan.

Tässä on tulosjoukko.

B. OVER-lausekkeen käyttäminen koostefunktioiden kanssa

Seuraava esimerkki käyttää lauseketta OVER, jossa on kootut funktiot kaikilla kyselyn palauttamilla riveillä. Tässä esimerkissä OVER -lausekkeen käyttö on tehokkaampaa kuin käyttämällä alakyselyjä yhdistettyjen arvojen johtamiseen.

Tässä on tulosjoukko.

Seuraava esimerkki näyttää OVER -lausekkeen käyttämisen koostefunktiolla lasketussa arvossa.

Tässä on tulosjoukko. Huomaa, että aggregaatit lasketaan SalesOrderID avulla ja Percent by ProductID lasketaan kunkin SalesOrderID.

C. Liikkuvan keskiarvon ja kumulatiivisen summan tuottaminen

Seuraava esimerkki käyttää AVG- ja SUM-funktioita OVER-lausekkeen avulla tuottamaan liikkuvan keskiarvon ja kumulatiivisen vuotuisen myynnin kokonaismäärän kullekin alueelle Sales.SalesPerson taulukko. Tiedot on osioitu TerritoryID ja loogisesti järjestetty SalesYTD. Tämä tarkoittaa, että AVG-toiminto lasketaan jokaiselle alueelle myyntivuoden perusteella. Huomaa, että sarakkeessa TerritoryID 1 myyntivuonna 2005 on kaksi riviä, jotka edustavat kahta myyntihenkilöä, joilla on myyntiä kyseisenä vuonna. Näiden kahden rivin keskimääräinen myynti lasketaan ja sitten laskelmaan sisältyy kolmas vuoden 2006 myyntiä edustava rivi.

Tässä on tulosjoukko.

Tässä esimerkissä OVER-lauseke ei sisällä PARTITION BY. Tämä tarkoittaa, että funktiota sovelletaan kaikkiin kyselyn palauttamiin riveihin. OVER-lauseessa määritetty ORDER BY -lauseke määrittää loogisen järjestyksen, johon AVG-toimintoa käytetään. Kysely palauttaa myynnin liukuvan keskiarvon vuosittain kaikilla WHERE-lausekkeessa määritellyillä myyntialueilla. SELECT-käskyssä määritetty ORDER BY -lauseke määrittää järjestyksen, jossa kyselyn rivit näytetään.

Tässä on tulosjoukko.

D. ROWS-lausekkeen määrittäminen

Koskee seuraavia: SQL Server 2012 (11.x) ja uudempia.

Seuraava esimerkki käyttää ROWS-lauseketta määrittääkseen ikkunan, jonka yli rivit lasketaan nykyinen rivi ja N seuraavien rivien määrä (1 rivi tässä esimerkissä).

Tässä on tulosjoukko.

Seuraavassa esimerkissä ROWS-lauseke määritetään RAJOITTAMATTOMAN TARKASTUKSEN kanssa. Tuloksena on, että ikkuna alkaa osion ensimmäiseltä riviltä.

Tässä on tulosjoukko.

Esimerkkejä: Rinnakkaistietovarasto

E. OVER-lausekkeen käyttö ROW_NUMBER-funktion kanssa

Seuraava esimerkki palauttaa ROW_NUMBER-arvon myyntiedustajille heidän määritetyn myyntikiintiönsä perusteella.

Tässä on osittainen tulosjoukko.

F. OVER-lausekkeen käyttäminen koostefunktioiden kanssa

Seuraavissa esimerkeissä on esitetty OVER-lauseen käyttö koostefunktioiden kanssa. Tässä esimerkissä OVER-lausekkeen käyttö on tehokkaampaa kuin alakyselyjen käyttö.

Tässä on tulosjoukko.

Seuraava esimerkki näyttää OVER-lauseen käyttämisen koostefunktiolla lasketussa arvossa. Huomaa, että aggregaatit lasketaan SalesOrderNumber -toiminnolla ja prosenttiosuus koko myyntitilauksesta lasketaan kunkin SalesOrderNumber -rivin kullekin riville.

Tämän tulosjoukon ensimmäinen alku on:

Katso myös

Kokoomatoiminnot (Transact-SQL)
Analyyttiset toiminnot (Transact-SQL)
Erinomainen blogiteksti ikkunatoiminnoista ja OVER-sivustosta, sqlmag.com, kirjoittanut Itzik Ben-Gan