Nabhan Hub

– olen viettänyt viimeiset kuusi vuotta matkustaa ympäri yhdysvaltoja kertomassa tietokantaan ammattilaisille T-SQL-Ikkunan Toiminnot SQL-lauantaisin ja muita tapahtumia. Ihmettelen, kuinka harva on kuullut näistä toiminnoista ja vielä harvempi niitä käyttävistä. Jokaisen esityksen lopussa yksi tai useampi ihminen tulee sanomaan, että he toivoivat oppineensa näistä toiminnoista vuosia aiemmin, koska ne olisivat voineet olla hyödyllisiä niin monille kyselyille.,

näitä toimintoja on edistetty suorituskyvyn parantamiseksi muihin, perinteisempiin menetelmiin verrattuna. Olen osittain samaa mieltä. Ne helpottavat monien kyselyjen kirjoittamista, ja joskus ne parantavat suorituskykyä.

ole Mitään tekemistä Windows-KÄYTTÖJÄRJESTELMÄN

Nämä toiminnot ovat osa ANSI-SQL: 2003-Standardien ja, siinä tapauksessa, SQL Server, T-SQL toimintoja käytetään kirjoittaa kyselyitä. Niillä ei ole mitään tekemistä Windows-käyttöjärjestelmän tai API-puhelujen kanssa. Myös muut tietokantajärjestelmät, kuten Oracle, ovat sisällyttäneet ne osaksi omaa SQL-kieltään.,

Ikkuna (myös windowing tai windowed) – funktiot suorittavat laskennan rivisarjan yli. Haluan ajatella ”ikkunan läpi katsomista” riveillä, jotka palautetaan ja joilla on viimeinen mahdollisuus tehdä laskelma. Ikkuna määritellään YLILAUSEKKEELLA, joka määrittää, onko rivit jaettu pienempiin sarjoihin ja onko ne tilattu. Itse asiassa, jos käytät ikkunatoimintoa, käytät aina OVER-lauseketta. YLI lauseke on myös osa ENSI-ARVO syntaksin tarvitaan järjestyksessä objekti, mutta muuten se on käyttää ikkunan toimintoja.,

OVER-lauseke voi sisältää VÄLIJAON option mukaan. Tämä rikkoo rivit pienemmiksi sarjoiksi. Voisi ajatella, että tämä on sama kuin ryhmä kerrallaan, mutta se ei ole. Ryhmittelyssä palautetaan yksi rivi kutakin ainutlaatuista ryhmää kohti. Kun käytät OSIO, kaikki yksityiskohdat rivit palautetaan yhdessä laskelmat. Jos kodissasi on ikkuna, joka on jaettu paneeleihin, jokainen paneeli on ikkuna. Kun ajattelet ikkuna toimintoja, koko joukko tuloksia on osio, mutta kun käytät OSIO, jokainen osio voidaan pitää myös ikkuna., PARTITION BY on tuettu-ja valinnainen-kaikille windowing toimintoja.

OVER-lauseke voi sisältää myös tilauksen optioittain. Tämä on riippumaton järjestyksestä kyselyn lausekkeella. Osa toiminnoista vaatii järjestystä, ja muut eivät tue sitä. Kun rivien järjestys on tärkeää laskentaa sovellettaessa, tarvitaan järjestysnumero.

Ikkunatoimintoja voidaan käyttää vain valitussa ja järjestyksessä kyselyn lausekkeilla. Niitä käytetään liittymisen, suodattamisen tai ryhmittelyn jälkeen.,

Ranking Toiminnot

yleisimmin käytetty ikkuna toimintoja, ranking toimintoja, on ollut saatavilla vuodesta 2005. Silloin Microsoft esitteli ROW_NUMBER, RANK, DENSE_RANK, ja NATILE. ROW_NUMEROA käytetään hyvin usein, lisäämään uniikkeja rivinumeroita osioon tai koko tulossarjaan. Lisäämällä rivin numero, tai joku muu ranking toiminnot, ei yleensä tavoite, mutta se on askel matkan varrella ratkaisu.

tilausjärjestys vaaditaan ylilausekkeessa käytettäessä RIVI_NUMEROA ja muita tämän ryhmän toimintoja., Tämä kertoo tietokantamoottorille, missä järjestyksessä numeroita tulee käyttää. Jos järjestyksessä käytettyjen sarakkeiden tai lausekkeiden arvot eivät ole ainutlaatuisia, RANK ja DENSE_RANK käsittelevät siteitä, kun taas ROW_NUMBER ei välitä siteistä. NTILEÄ käytetään jakamaan rivit ämpäreihin järjestyksen mukaan.

yksi ROW_NUMERON etu on kyky muuttaa ei-uniikit rivit ainutlaatuisiksi riveiksi. Tällä voitaisiin poistaa esimerkiksi päällekkäisiä rivejä.

Jos haluat näyttää, miten tämä toimii, Aloita temp-taulukolla, joka sisältää päällekkäisiä rivejä., Ensimmäinen askel on luoda pöytä ja kansoittaa se.

Lisäämällä ROW_NUMBER ja osiointi, jonka jokainen sarake uudelleen rivin numerot kunkin ainutlaatuinen joukko rivejä. Yksilölliset rivit voi tunnistaa löytämällä ne, joiden rivinumero on yhtä suuri.,

Nyt, kaikki mitä sinun tarvitsee tehdä, on poistaa kaikki rivit, jotka on rivin numero, joka on suurempi kuin yksi., Ongelma on se, että et voi lisätä ikkunatoimintoja WHERE-lausekkeeseen.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Voit sitten poistaa rivit suoraan CTE: stä.

Menestys! Ylimääräiset rivit poistettiin, ja jäljelle jäi ainutlaatuinen rivisarja.

nähdä eroa ROW_NUMBER, SIJOITUS, ja DENSE_RANK, suorittaa tämän kyselyn:

JÄRJESTYS kunkin YLI lauseke on OrderDate joka ei ole ainutlaatuinen. Asiakas teki kaksi tilausta vuosina 2013-10-24. ROW_NUMBER vain jatkoi numeroiden jakamista eikä tehnyt mitään muuta, vaikka on olemassa päällekkäinen päivämäärä., Sijoitus osoitettu 6 molemmille riveille ja sitten kiinni ROW_NUMBER kanssa 8 seuraavalla rivillä. DENSE_RANK antoi myös 6: n kahdelle riville, mutta antoi 7: n seuraavalle riville.

kaksi selittää eron, ajattele RIVI_NUMEROA positionaalisena. Sijoitus on sekä positionaalinen että looginen. Nämä kaksi riviä sijoittuvat loogisesti samaan, mutta seuraava rivi sijoittuu sijoituksen mukaan. Dense_rank sijoittaa ne loogisesti. Jotta 2013-11-04 on 7 ainutlaatuinen päivämäärä.

tämän ryhmän lopullista funktiota kutsutaan NTILEKSI. Se antaa ämpärinumerot riveille rivinumeroiden tai rivien sijaan., Tässä on esimerkki:

NTILE on parametri, tässä tapauksessa 4, joka on määrä kauhat haluat nähdä tuloksia. Tilausta sovelletaan myynnin summaan. Rivit, joiden alin 25% on annettu 1, rivit, joilla on korkein 25% on annettu 4. Lopuksi tulokset NTILE kerrotaan 1000 keksiä bonus määrä. Koska 14: ää ei voi jakaa tasaisesti 4: llä, jokaiseen kahteen ensimmäiseen ämpäriin menee ylimääräinen rivi.

Ikkuna-aggregaatit

Ikkuna-aggregaatit otettiin käyttöön myös SQL Server 2005: llä., Nämä tekevät kirjoittaminen joitakin hankala kyselyt helppoa, mutta usein suorittaa huonompi kuin vanhemmat tekniikoita. Niiden avulla voit lisätä suosikkisi aggregaattifunktion ei-aggregaattikyselyyn. Sano, esimerkiksi haluat näyttää kaikki asiakkaiden tilaukset sekä välisumma jokaiselle asiakkaalle., Lisäämällä SUMMAN käyttäen YLI mainittiin, voit tehdä tämän hyvin helposti:

lisäämällä OSIO, a välisumma lasketaan kullekin asiakkaalle. Kaikkia aggregaattifunktioita voidaan käyttää, eikä ORDER by in the OVER-lauseke tue.

Ikkuna Yhteenlaskettu Parannuksia vuonna 2012

Alussa 2012, voit lisätä MÄÄRÄYS, JONKA YLI lauseke ikkuna-aggregaattien tuottamiseksi käynnissä yhteensä ja liukuvia keskiarvoja, esimerkiksi. Samalla Microsoft esitteli rajauksen käsitteen. Väliseinän lisääminen on kuin ikkunan jakaminen paneeleihin., Kehystyksen lisääminen on kuin lasimaalauksen luominen. Jokaisella rivillä on yksilöllinen ikkuna, jossa ilmaisua sovelletaan.

tämän lisälaitteen, voit luoda käynnissä yhteensä jopa ilman lisäämällä muotoilu syntaksi., Here is an example that returns a running total by customer:

oletuksena kehys, jota käytetään, jos runko ei ole määritelty, on VÄLILLÄ RAJATON EDELLISEN JA NYKYISEN RIVIN. Valitettavasti tämä ei toimi niin hyvin kuin jos määrität tämän kehyksen sijaan: rivit rajattoman edellisen ja nykyisen rivin välillä. Erona on SANARIVIT. ALUE on vain osittain toteutettu tällä hetkellä, ja se on tarkoitettu työskentely aikoja, kun RIVIT on asentohuimaus., Kehys, rivit rajattoman edeltävän ja nykyisen rivin välillä, tarkoittaa, että ikkuna koostuu osion ensimmäisestä rivistä ja kaikista riveistä aina nykyiseen riviin asti. Jokainen laskenta tehdään eri riveillä. Esimerkiksi rivin 4 laskemisessa käytetään rivejä 1-4.

Kun suoritat laskennan rivi 5, rivit 1-5. Ikkuna kasvaa suuremmaksi siirryttäessä rivistä toiseen.,

Voit myös käyttää syntaksin RIVIEN VÄLISTÄ N EDELTÄVÄN JA NYKYISEN RIVIN tai RIVIEN VÄLISTÄ NYKYISEN RIVIN JA N JÄLKEEN. Tästä voisi olla hyötyä esimerkiksi kolmen kuukauden liukuvan keskiarvon laskemisessa. Seuraava luku edustaa rivejä 2 edeltävän ja nykyisen rivin välillä.

Kun 5 on nykyinen rivi, ikkuna liikkuu; se ei muuta koko.,

Tässä on luettelo ehdot sinun täytyy tietää, kun kirjoitat kehystys vaihtoehto:

myönnän, että tämä syntaksi on hieman sekava, mutta käyttää SQL Prompt auttaa tekee kirjallisesti kehystys vaihtoehto helpompaa!

Offset Toiminnot

mukana on Myös vapauttaa SQL Server 2012 on neljä toimintoja, joiden avulla voit sisällyttää arvot muut rivit – tekemättä itse liittyä. Microsoft kutsuu näitä ’analytic toimintoja, mutta olen aina kutsuvat niitä nimellä ’offset toiminnot’, kun hän esittää tästä aiheesta., Kahdesta funktiosta voit vetää sarakkeita tai lausekkeita rivistä ennen (viivettä) tai sen jälkeen (johtaa) nykyistä riviä. Kahden muun funktion avulla voit palauttaa arvot osion ensimmäisestä rivistä (FIRST_VALUE) tai partition viimeisestä rivistä (LAST_VALUE). FIRST_VALUE ja LAST_VALUE vaativat myös rajausta, joten muista sisällyttää kehys näitä funktioita käytettäessä. Kaikki neljä funktiota vaativat tilauksen option mukaan OVER-lauseke. Tämä on järkevää, koska tietokantamoottorin on tiedettävä rivien järjestys selvittääkseen, mikä rivi sisältää palautettavan arvon.,

joillakin on suosikkibändi; joillakin on suosikkielokuva. Minulla on suosikki toiminto-viive. Se on helppokäyttöinen(ei kehystä!) ja toimii hyvin., Here is an example:

LAG ja JOHTAA vaativat argumentti – sarake tai lauseke, jonka haluat palauttaa. Oletuksena LAG palauttaa arvon edellisestä rivistä, ja LEAD palauttaa arvon seuraavasta rivistä. Voit muuttaa sitä toimittamalla arvon OFFSET-parametrille, joka on oletuksena 1. Huomaa, että ensimmäinen rivi osio palauttaa NULL. Jos haluat ohittaa Nullit, voit toimittaa oletusarvon., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE ja LAST_VALUE voidaan löytää arvo, joka on ensimmäinen rivi tai viimeinen rivi osio. Varmista, että määrität kehystä, ei vain suorituskyvyn vuoksi, mutta koska oletuksena runko ei toimi kuten odottaa LAST_VALUE. Oletuskehys, vaihteluväli rajoittamattoman edellisen ja nykyisen rivin välillä, menee vain nykyiseen riviin asti. Väliseinän viimeinen rivi ei ole mukana., Jos haluat saada odotetut tulokset, muista määrittää rivit nykyisen rivin ja rajoittamattoman seuraavan välillä, kun käytät LAST_VALUETA., Tässä on esimerkki käyttäen FIRST_VALUE:

Tilastolliset Toiminnot

Microsoft ryhmiä nämä neljä toiminnot – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – yhdessä offset toimintoja, jossa kaikki kahdeksan analytic toimintoja. Koska haluan erottaa nämä offset-toiminnot, kutsun näitä tilastollisia.

PERCENT_RANK ja CUME_DIST antavat sijoituksen kullekin riville osion yli. Ne eroavat hieman toisistaan. PROSENTTI_RANK palauttaa rivien prosenttiosuuden, joka on pienempi kuin nykyinen rivi., ”Oma pistemäärä on yli 90% tuloksista.”CUME_DIST eli kumulatiivinen jakauma palauttaa tarkan sijoituksen. ”Minun pisteet on 90% tuloksista.”Tässä on esimerkki, jossa käytetään St. Louisin keskimääräistä korkeaa lämpötilaa joka kuukausi. Huomaa, että rivit määräytyivät Fahrenheitin lämpötilan mukaan.

joukkoon ei määritetty suhteellinen arvoja, mutta kannat riviä. Huomaa, että maalis-ja marraskuussa on sama keskimääräinen korkea lämpötila,joten ne oli rankattu sama.

saatat ihmetellä, miten lasketaan PROSENTTI_RANK ja CUME_DIST., Tässä ovat kaavat:

PERCENTILE_DISC ja PERCENTILE_CONT työn vastakkaiseen suuntaan. Kun otetaan huomioon prosentuaalinen arvo, Etsi arvo kyseisestä arvosta., Ne eroavat siinä, että PERCENTILE_DISC tulee palauttaa arvon, joka on asetettu, kun PERCENTILE_CONT laskee tarkan arvon, jos yksikään arvojen joukko kuuluu juuri tuohon listalla. Voit käyttää PROSENTTILE_CONTIA mediaanin laskemiseen toimittamalla 0,5 prosenttiluokaksi. Esimerkiksi mikä lämpötila on St. Louisissa 50%?

PERCENTILE_CONT toiminto kestää keskimäärin kaksi arvoa lähimpänä keskellä, 67 ja 69, ja keskimäärin niitä. PERSENTILE_DISC palauttaa tarkan arvon, 67., Huomaa myös, että näillä kahdella funktiolla on ylimääräinen lauseke, jota ei ole muissa funktioissa, ryhmän sisällä, joka sisältää järjestyksen OVER-lausekkeen sijaan.

Yhteenveto

Tämä artikkeli on hyvin nopea katsaus T-SQL-ikkunatoimintoihin. SQL Server 2005: llä julkaistiin kahdentyyppisiä toimintoja, ranking-toiminnot ja ikkuna-aggregaatit. Kanssa 2012, sinulla on parannettu ikkuna aggregaatti kehystys ja analyyttiset toiminnot. Haluan erottaa analyyttiset funktiot kahteen ryhmään, offset-ja tilastollisiin funktioihin., Ikkunatoiminnot helpottavat monien kyselyjen kirjoittamista, ja uskon, että se on tärkein etu. Joissakin tapauksissa kyselyt toimivat myös paremmin, mutta se on keskustelu vielä yhden päivän.

Toivottavasti tämä artikkeli on inspiroinut sinua oppimaan lisää näistä fantastisista toiminnoista!