admin

Yleiskatsaus

Opetus: 10 min
Harjoitukset: 10 min
Kysymykset

  • Miten voin laskea summia, keskiarvoja ja muita yhteenvetoarvoja?

Tavoitteet

  • Määrittele yhteenlasku ja anna esimerkkejä sen käytöstä.

  • Kirjoita kyselyitä, jotka laskevat aggregoituja arvoja.

  • Seuraa aggregointia suorittavan kyselyn suoritusta.

  • Erittele, miten puuttuvia tietoja käsitellään aggregoinnin aikana.

Haluamme nyt laskea vaihteluvälit ja keskiarvot tiedoillemme.Osaamme valita kaikki päivämäärät Visited-taulukosta:

SELECT dated FROM Visited;

dated

-null-

mutta yhdistääksemme ne,meidän on käytettävä aggregaatiofunktiota, kuten min tai max.Kukin näistä funktioista ottaa syötteenä joukon tietueita,ja tuottaa tulosteena yhden tietueen:

SELECT min(dated) FROM Visited;

min(päivätty) 

SELECT max(dated) FROM Visited;

max(päivätty)

min ja max ovat vain kaksi SQL:iin sisäänrakennettua aggregointitoimintoa.Kolme muuta ovat avg,count ja sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

avg(luku) 
SELECT count(reading) FROM Survey WHERE quant = 'sal';

count(luku) 
SELECT sum(reading) FROM Survey WHERE quant = 'sal';

sum(lukeminen)

Tässä käytimme count(reading),mutta olisimme yhtä hyvin voineet laskea quanttai minkä tahansa muun taulukon kentän,tai jopa käyttää count(*),koska funktio ei välitä itse arvoista,vain siitä kuinka monta arvoa on.

SQL antaa meidän tehdä useita aggregointeja kerralla.Voimme esimerkiksi löytää järkevien suolapitoisuusmittausten vaihteluvälin:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;
min(lukema) max(lukema)
0.05 0.21

Voidaan myös yhdistää aggregoidut tulokset ja raakatulokset,tosin tulos saattaa yllättää:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;
person count(*)
järvi 7

Miksi järven nimi esiintyy Roerichin tai Dyerin sijaan?Vastaus on, että kun sen on aggregoitava kenttä,mutta sille ei ole kerrottu, miten se tehdään, tietokantahallinta valitsee todellisen arvon syöttöjoukosta.Se voi käyttää ensimmäistä käsiteltyä arvoa, viimeistä arvoa tai jotain aivan muuta.

Toinen tärkeä seikka on se, että kun aggregoitavia arvoja ei ole – esimerkiksi kun ei ole rivejä, jotka täyttävät WHERE-lausekkeen – aggregaation tulos on ”en tiedä ”eikä nolla tai jokin muu mielivaltainen arvo:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';
henkilö max(lukema) summa(lukema)
-null- -null- -null-

Yksi viimeinen tärkeä ominaisuus aggregaatiofunktioissa on se, että ne ovat epäjohdonmukaisia muun SQL:n kanssa erittäin hyödyllisellä tavalla.Jos laskemme yhteen kaksi arvoa ja toinen niistä on nolla, tulos on nolla.Jos käytämme sum:tä laskemaan yhteen kaikki joukon arvot ja jokin näistä arvoista on nolla, tuloksen pitäisi myös olla nolla.On kuitenkin paljon hyödyllisempää, että aggregaatiofunktiot jättävät nolla-arvot huomiotta ja yhdistävät vain ne arvot, jotka eivät ole nolla.Tämän käyttäytymisen ansiosta voimme kirjoittaa kyselyt seuraavasti:

SELECT min(dated) FROM Visited;

min(päivätty)

sen sijaan, että joudumme aina suodattamaan nimenomaisesti:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

min(dated)

Kaikkien tietueiden yhdistäminen kerralla ei aina ole järkevää.Oletetaan esimerkiksi, että epäilemme, että tiedoissamme on systemaattinen harha ja että joidenkin tutkijoiden säteilylukemat ovat korkeampia kuin toisten.Tiedämme, että tämä ei toimi:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';
henkilö count(lukema) round(avg(lukema), 2)
roe 8 6.56

koska tietokannan ylläpitäjä valitsee yhden mielivaltaisen tutkijan nimen sen sijaan, että laskisi yhteen erikseen jokaisen tutkijan osalta.Koska tiedemiehiä on vain viisi,voisimme kirjoittaa viisi kyselyä muodossa:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';
henkilö count(lukeminen) round(avg(lukeminen), 2)
dyer 2 8.81

Mutta tämä olisi työlästä,ja jos meillä olisi joskus aineisto, jossa olisi viisikymmentä tai viisisataa tiedemiestä,mahdollisuudet saada kaikki kyselyt oikein ovat pienet.

Mitä meidän on tehtävä, on käskeä tietokannan hallintaa aggregoimaan kunkin tiedemiehen tunnit erikseen GROUP BY-lausekkeen avulla:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;
henkilö count(lukeminen) pyöristää(avg(lukeminen), 2)
värisempi 2 8.81
järvi 2 1.82
pb 3 6.66
roe 1 11.25

GROUP BY tekee juuri sen, mitä sen nimi antaa ymmärtää: ryhmittelee kaikki tietueet, joilla on sama arvo määritellyssä kentässä, yhteen, jotta aggregointi voi käsitellä jokaisen erän erikseen.Koska kussakin erässä kaikilla tietueilla on sama arvo person-kentässä, ei ole enää väliä sillä, että tietokannan hoitaja valitsee mielivaltaisen arvon näytettäväksi aggregoitujen reading-arvojen rinnalla.

Niin kuin voimme lajitella useilla kriteereillä kerralla, voimme myös ryhmitellä useilla kriteereillä.Saadaksemme keskimääräisen lukeman esimerkiksi tutkijan ja mitatun määrän mukaan, lisäämme vain toisen kentän GROUP BY-lausekkeeseen:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;
henkilö määrä luku(lukema) pyöristää(avg(lukema), 2)
-null- sal 1 0.06
-null- temp 1 -26.0
dyer rad 2 8.81
dyer sal 2 0.11
lake rad 2 1.82
järvi sal 4 0.11
järvi lamp lämpö 1 -16.0
pb rad 3 6.66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Huomaa, että olemme lisänneet quant näytettävien kenttien luetteloon,koska muuten tuloksissa ei olisi paljon järkeä.

Mennään vielä askeleen pidemmälle ja poistetaan kaikki merkinnät, joissa emme tiedä, kuka on tehnyt mittauksen:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;
henkilö kvantti count(lukema) pyöristys(avg(lukema), 2)
dyer rad 2 8.81
dyer sal 2 0.11
järvi rad 2 1.82
järvi sal 4 0.11
järvi temp 1 -16.0
pb rad 3 6.66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Katsotaan tarkemmin,tämä kysely:

  1. valitsi Survey-taulusta tietueet, joissa person-kenttä ei ollut nolla;

  2. ryhmitteli nämä tietueet osajoukkoihin siten, että person– ja quant-arvot kussakin osajoukossa olivat samat;

  3. järjesteli nämä alaryhmät ensin person:n mukaan ja sitten kunkin alaryhmän sisällä quant:n mukaan;ja

  4. laski tietueiden lukumäärän jokaisessa alaryhmässä,laski kunkin alaryhmän keskiarvon reading ja valitsi jokaisesta person– ja quant-arvon (ei ole väliä, mitkä, koska ne ovat kaikki samat).

Lämpötilalukemien laskeminen

Miten monta lämpötilalukemaa Frank Pabodie kirjasi,ja mikä oli niiden keskiarvo?

Ratkaisu

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';
lasku(lukema) keskiarvo(lukema)
2 -20.0

Keskiarvo NULL:lla

Arvojoukon keskiarvo on arvojen summa jaettuna arvojen lukumäärällä.Tarkoittaako tämä, että avg-funktio palauttaa 2.0 tai 3.0, kun sille annetaan arvot 1.0, null ja 5.0?

Ratkaisu

Vastaus on 3.0.NULL ei ole arvo; se on arvon puuttuminen.Sellaisenaan sitä ei oteta mukaan laskentaan.

Voit varmistaa tämän suorittamalla tämän koodin:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Mitä tämä kysely tekee?

Haluamme laskea erotuksen jokaisen yksittäisen säteilylukeman ja kaikkien säteilylukemien keskiarvon välillä.Kirjoitamme kyselyn:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Mitä tämä oikeastaan tuottaa, ja keksitkö miksi?

Ratkaisu

Kysely tuottaa vain yhden rivin tuloksia, vaikka oikeasti haluaisimme tuloksen kullekin lukemalle.Funktio avg() tuottaa vain yhden arvon, ja koska se ajetaan ensimmäisenä, taulukkoa supistetaan yhteen riviin.reading-arvo on yksinkertaisesti mielivaltainen.

Saadaksemme haluamamme tuloksen meidän olisi suoritettava kaksi kyselyä:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Tämä tuottaa keskiarvon (6.5625), jonka voimme sitten lisätä toiseen kyselyyn:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Tämä tuottaa haluamamme tuloksen, mutta voimme yhdistää sen yhdeksi kyselyksi käyttämällä alakyselyjä.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

Tällöin meidän ei tarvitse suorittaa kahta kyselyä.

Yhteenvetona se, mitä olemme tehneet, on se, että olemme korvanneet avg(reading) alkuperäisessä kyselyssä (SELECT avg(reading) FROM Survey WHERE quant='rad'):llä.

järjestys ketjutettaessa

Funktio group_concat(field, separator)ketjuttaa kaikki kentän arvot käyttäen määritettyä erotinmerkkiä(tai ’,’ jos erotinta ei ole määritetty).Käytä tätä tuottaaksesi yksirivisen listan tiedemiesten nimistä,esimerkiksi:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Keksitkö keinon järjestää lista sukunimen mukaan?

Kärkikohdat

  • Käytä aggregointifunktioita useiden arvojen yhdistämiseen.

  • Aggregointifunktiot eivät huomioi null-arvoja.

  • Aggregointi tapahtuu suodatuksen jälkeen.

  • Käytä GROUP BY -toimintoa osajoukkojen yhdistämiseen erikseen.

  • Jos kentälle ei ole määritetty aggregointitoimintoa, kysely voi palauttaa mielivaltaisen arvon kyseiselle kentälle.

admin

Vastaa

Sähköpostiosoitettasi ei julkaista.

lg