SQL-ikkunan toiminnot

Aloitetaanko tästä? Tämä oppitunti on osa SQL: n tietojen analysointia koskevaa täyspitkää opetusohjelmaa. Katso alku.

Tässä oppitunnissa käsitellään seuraavaa:

• Johdatus ikkunatoimintoihin
• Perustoimintojen syntaksi
• Tavalliset epäillyt: SUMMA, LASKE ja AVG
• ROW_NUMBER ()
• RANK () ja DENSE_RANK ()
• NTILE
• Paikallinen toimintaryhmä ja johtaja
• Ikkunan aliaksen määrittely
• Edistyneet ikkunointitekniikat

Tässä oppitunnissa käytetään Washington DC: n Capital Bikeshare -ohjelman tietoja, yksityiskohtaiset matkatason historialliset tiedot verkkosivustollaan. Tiedot ladattiin helmikuussa 2014, mutta ne rajoittuvat vuoden 2012 ensimmäisen vuosineljänneksen aikana kerättyihin tietoihin. Jokainen rivi edustaa yhtä matkaa. Useimmat kentät ovat itsestään selviä, paitsi rider_type: ”Rekisteröity” tarkoittaa kuukausittaista jäsenyyttä ratsastusohjelmaan, ”Casual” kertoo, että ratsastaja osti 3 päivän lipun. start_time – ja end_time -kentät puhdistettiin alkuperäisistä muodoistaan vastaamaan SQL-päivämäärän muotoilua – ne tallennetaan tähän taulukkoon aikaleimoina.

Ikkunatoimintojen esittely

PostgreSQL: n dokumentaatio tekee erinomaisen työn esittelemällä ikkunatoimintojen käsitteen:

Ikkunatoiminto suorittaa laskutoimituksen joukolle taulukkorivejä, jotka liittyvät jotenkin nykyiseen riviin. Tämä on verrattavissa laskutyyppiin, joka voidaan suorittaa aggregaattitoiminnolla. Mutta toisin kuin tavalliset aggregaattitoiminnot, ikkunan käyttö funktio ei saa rivejä ryhmittymään yhdeksi tulosriviksi – rivit säilyttävät erillisen identiteettinsä. Kulissien takana ikkunatoiminto pystyy käyttämään muutakin kuin vain kyselytuloksen nykyistä riviä.

Käytännön esimerkki tästä on juokseva summa:

Voit nähdä, että yllä oleva kysely luo aggregaatin (running_total) käyttämättä GROUP BY. Hajotetaan syntaksit ja katsotaan miten se toimii.

Perustoimintojen syntaksit

Yllä olevan aggregaation ensimmäinen osa, SUM(duration_seconds) , näyttää paljon kuin mikä tahansa muu aggregaatio. Lisäämällä OVER tämä merkitään ikkunafunktioksi. Voit lukea yllä olevan aggregaatin muodossa ”ota summa duration_seconds koko tulosjoukossa järjestyksessä start_time. ”

Jos haluat kaventaa ikkunan koko tietojoukosta yksittäisiin ryhmiin sisällä tietoaineistoa, voit käyttää PARTITION BY tehdäksesi niin:

Yllä olevat kyselyryhmät järjestävät kyselyn start_terminal . Jokaisessa start_terminal -arvossa se järjestetään start_time -arvolla, ja nykyisen rivin ja kaikkien edellisten duration_seconds. Vieritä alaspäin, kunnes start_terminal -arvo muuttuu ja huomaat, että running_total alkaa uudestaan. Sitä tapahtuu, kun ryhmittelet käyttämällä ryhmää PARTITION BY. Jos ORDER BY on edelleen törmännyt sinuun, se vain tilaa nimetty sarake (s) samalla tavoin kuin lauseke ORDER BY, paitsi että se käsittelee jokaista osiota erillisenä. Se luo myös juoksevan kokonaissumman – ilman ORDER BY, jokainen arvo on yksinkertaisesti summa kaikilla duration_seconds -arvoilla start_terminal. Yritä suorittaa yllä oleva kysely ilman ORDER BY -tietoa saadaksesi idean:

ORDER ja PARTITION määrittele mitä kutsutaan” ikkunaksi ”- järjestettyjen tietojen joukko, jolle laskelmat tehdään.

Huomaa: Et voi käyttää ikkunatoimintoja ja vakioaggregaatteja samassa Kysely. Tarkemmin sanottuna et voi ”sisällyttää ikkunatoimintoja GROUP BY -lausekkeeseen.

Tavalliset epäillyt: SUMMA, LASKE ja AVG

Kun käytät ikkunatoimintoja, voit käyttää samoja aggregaatteja kuin normaalissa tilanteessa – SUM, COUNT ja AVG. Helpoin tapa ymmärtää nämä on ajaa edellinen esimerkki uudelleen lisätoiminnoilla. Tee

Vaihtoehtoisesti samat toiminnot mallilla ORDER BY:

Varmista, että olet liittänyt nämä kaksi edellistä kyselyä Modeen ja suorittanut ne. Tämä seuraava käytäntöongelma on hyvin samanlainen kuin esimerkit, joten yritä muokata yllä olevaa koodia sen sijaan, että aloitat alusta.

ROW_NUMBER ()

ROW_NUMBER() tekee juuri miltä se kuulostaa – näyttää tietyn rivin numeron. Alku on 1 ja numeroi rivit ikkuna-lauseen ORDER BY -osan mukaan. ROW_NUMBER() ei vaadi sinua määrittämään muuttujaa sulkeissa:

PARTITION BY -lausekkeen avulla voit aloita laskea 1 uudelleen kussakin osiossa. Seuraava kysely aloittaa laskennan uudestaan jokaiselle päätelaitteelle:

RANK () ja DENSE_RANK ()

RANK() on hieman erilainen kuin ROW_NUMBER(). Jos tilaat esimerkiksi start_time, voi olla, että joillakin päätelaitteilla on kaksi samanlaista aloitusaikaa. Tällöin heille annetaan sama sijoitus, kun taas ROW_NUMBER() antaa heille eri numerot. Seuraavassa kyselyssä huomaat start_terminal 31000: n neljännen ja viidennen havainnon – molemmille annetaan 4 ja seuraava tulos saa 6:

Voit myös käyttää DENSE_RANK() sovelluksen RANK() sijaan. Kuvittele tilanne, jossa kolmella merkinnällä on sama arvo. Kummallakin komennolla he kaikki saavat saman arvon. Tämän esimerkin vuoksi sanotaan ”s sanoa” s ”2.” Tällöin nämä kaksi komentoa arvioivat seuraavat tulokset eri tavalla:

• RANK() antaisi samanlaisille riveille arvon 2, sitten ohittaisiin rivit 3 ja 4, joten seuraava tulos olisi 5
• DENSE_RANK() antaisi edelleen kaikille identtisille riveille arvon 2, mutta seuraava rivi olisi 3 – ei rivejä ohitetaan.

NTILE

Ikkunatoimintojen avulla voit tunnistaa, mihin prosenttipisteeseen (tai kvartiili, tai mikä tahansa muu osa-alue) tietty rivi kuuluu. Syntaksi on NTILE(*# of buckets*). Tällöin ORDER BY määrittää, mitä saraketta käytetään kvartiilien (tai minkä tahansa määrän ”ruutujen” määrittämiseen) täsmentää). Esimerkki:

Tarkastellessasi yllä olevan kyselyn tuloksia näet, että percentile -sarakkeessa ei lasketa tarkalleen kuin voit odottaa. Jos vain sinulla oli kaksi tietuetta ja mitattiin prosenttipisteitä, odotat yhden tietueen määrittävän ensimmäisen prosenttipisteen ja toisen tietueen määrittävän 100. prosenttipisteen. Käyttämällä NTILE -toimintoa, mitä näet, on yksi tietue 1. prosenttipisteessä ja toinen 2. prosenttipisteessä. Voit nähdä tämän tuloksista haulla start_terminal 31000 – percentile -sarake näyttää vain numeeriselta järjestykseltä. Jos vierität alas kohtaan start_terminal 31007, voi nähdä, että se laskee prosenttipisteet oikein, koska tälle start_terminal tietueelle on yli 100 tietuetta. Jos työskentelet hyvin pienten ikkunoiden kanssa, pidä tämä mielessä ja harkitse kvartiilien tai vastaavien pienten käyttöä bändit.

LAG ja LEAD

Voi olla usein hyödyllistä verrata rivejä edellisiin tai seuraaviin riveihin, etenkin jos olet saanut tiedot järkevässä järjestyksessä. Voit käyttää LAG tai LEAD sarakkeita luodaksesi sarakkeita, jotka vetävät arvoja muista rivit – sinun tarvitsee vain kirjoittaa, mistä sarakkeesta vedät ja kuinka monta riviä poispäin haluat tehdä vedon. LAG vetää edellisiltä riveiltä ja LEAD vetää seuraavilta riveiltä:

Tämä on erityisen hyödyllistä, jos haluat laskea eroja rivien välillä:

difference -sarakkeen ensimmäinen rivi on tyhjä, koska edellistä riviä ei ole vedettävä. Vastaavasti LEAD -toiminnon käyttäminen luo nollia tietojoukon loppuun. Jos haluat tehdä tuloksista hieman puhtaampia, voit kääriä ne ulkoiseen kyselyyn nollien poistamiseksi:

Ikkunan aliaksen määrittäminen

Jos aiot kirjoittaa useita ikkunatoimintoja samaan kyselyyn, käyttämällä samaa ikkunaa, voit luoda aliaksen.Ota yllä oleva esimerkki NTILE:

Tämä voidaan kirjoittaa uudestaan seuraavasti:

WINDOW Jos lauseke sisältyy, sen tulee aina tulla WHERE -lausekkeen perään.

Edistyneet käämitystekniikat

Voit tarkistaa täydellisen luettelon ikkunoista toiminnot Postgresissä (syntaksitila käyttää) Postgres-dokumentaatiossa. Jos käytät ikkunatoimintoja yhdistetyssä tietokannassa, sinun on tarkasteltava järjestelmääsi sopivaa syntaksia.