SQL/Funktiot

Koostefunktiot

• Viisi standardifunktiota:
• AVG --keskiarvo
• MIN -- pienin arvo
• MAX -- suurin arvo
• SUM -- summa
• COUNT -- lukumäärä
• Esim. montako henkilöä on henkilo-taulussa:
  

SELECT COUNT(*) AS lkm
FROM henkilo;

Koostefunktiot

• Standardifunktiot vrt. Excelin vastaavat.

SELECT COUNT(tutkinto) AS lkm
FROM henkilo;

=> NULL-arvoja ei lasketa!

Koostefunktioiden käyttö

• Koostefunktioita käytetään SELECT-lauseen sarakeluettelossa ja HAVING-lauseessa
  

Haetaan suurin ja pienin hinta:

SELECT MAX(hinta), MIN(hinta)
FROM tuote;

• WHERE-lauseessa koostefunktioita ei voi käyttää (vaan ne on laitettava HAVING-lauseeseen)

Haetaan myyjät, joiden myymien tuotteiden hinta yhteensä on yli 5000:

SELECT myyja, SUM(hinta) -- tuotteiden hintojen summa
FROM tuote
GROUP BY myyja           -- ryhmittely myyjän mukaan
HAVING SUM(hinta) > 5000;

• DISTINCT-rajaus (toimii muissa tuotteissa paitsi Accessissa):
  

SELECT COUNT(DISTINCT city) -- monestako kaupungista
FROM authors;               -- on kirjailijoita

Koostefunktioiden käyttö

Haetaan projektien yhteenlasketut tunnit:

SELECT ptun, SUM(tunnit)
FROM proj_henk GROUP BY ptun;

Monessako kaupungissa projekteja?:

SELECT COUNT(DISTINCT sijainti) FROM projekti;

• HAVING: "sama ryhmille kuin where riveille"

Ryhmittely - Group By

• GROUP BY -ehdolla tiedot ryhmitellään jonkin sarakkeen tai sarakkeiden mukaan ryhmiin
• GROUP BY -ehdon ryhmiin kohdistetaan yleensä jokin summafunktio
• kaikki SELECT-listan lauseet, joissa ei ole funktiota, on lueteltava GROUP BY –lauseessa!
  

Haetaan kunkin myyjän tuotteiden hintojen keskiarvo:

SELECT myyja, AVG(hinta)
FROM tuote
GROUP BY myyja;

Kunkin myyjän myymien tuotteiden määrä:

SELECT myyja, COUNT(myyja)
FROM tuote
GROUP BY myyja;

Ryhmittely - Group By

Haetaan projektien keskimääräiset tunnit:

SELECT ptun, AVG(tunnit) FROM proj_henk
GROUP BY ptun;

Akateeminen esimerkki:

SELECT ptun, AVG(tunnit) FROM proj_henk
GROUP BY htun;

=> Ei toimi järkevästi, ptun pitää olla GROUP BY -osassa!

Testaa vaikka seuraavien lisäysten jälkeen:

INSERT INTO henkilo VALUES (6666, 'Rantala', 'Ari',
'TAMPERE', 'FK', 6666.66, 66,now(),4);

INSERT INTO proj_henk
VALUES ('P4', 6666, 666,666);

Ryhmittely - Having

• Sekä HAVING- että WHERE-lauseella voidaan ryhmittelyn tulosta rajata:
  

SELECT myyja, COUNT(*) -- myös NULL kelpaa
FROM tuote
WHERE tuoteHinta > 100
GROUP BY myyja         -- hae myyjät joilla
HAVING COUNT(*) >2;    -- vähintään 3 tuotetta

• Tulosjoukkoa voidaan siis rajata eri tasoilla :
• rajataan ensiksi ryhmiteltävä joukko WHERE-lausekkeella
• tästä syntynyt tulos ryhmitellään GROUP BY -määrittelyllä
• lopuksi ryhmiteltyä joukkoa voidaan vielä rajata HAVING-lauseella
  

Merkkijonofunktioita

Merkkitiedon käsittely

Standardissa merkkijono saadaan funktiolla

SUBSTRING(mjono FROM mista FOR pituus);

-- Esim:
SELECT sukunimi, ',', SUBSTRING(etunimi FROM 1 FOR 1), '.'
FROM henkilo

Monissa tuotteissa on kuitenkin SUBSTR-funktio:

SUBSTR(mjono,mista,mihin);

-- Esim:

SELECT sukunimi, ', ', SUBSTR(etunimi,1,1), '.'
FROM henkilo;

===> Tuloksena lista:

Aaltonen, M.
Bergman, S.
jne.

Muita merkkijonofunktioita:

CHAR_LENGTH    -- merkkien määrä (Oraclessa LENGTH)
UPPER(nimi)    -- merkit isoiksi (Accessissa UCase)
LOWER(nimi)    -- merkit pieniksi (Accessissa LCase)
LEFT(nimi, n)  -- merkkijono vasemmalta (n merkkiä)
RIGHT(nimi, n) -- merkkijono oikealta (n merkkiä)

Merkkijonofunktioita

• Mysql:ssä toimii esimerkkitietokantaan:

SELECT snimi,',', SUBSTRING( enimi FROM 1 FOR 3),'.' FROM henkilo;
SELECT snimi,',', SUBSTR( enimi,1,3),'.' FROM henkilo;

• Muita kokeiluja esimerkkitietokantaan:

SELECT snimi, char_length(snimi) AS 'Sukunimen pituus' FROM henkilo;


-- Varman päälle:
SELECT * FROM projekti WHERE lower(sijainti) = 'turku';
SELECT LEFT(enimi,3) FROM henkilo;

Vielä merkkijonojen käsittelystä

Merkkijonojen yhdistäminen/liittäminen (konkatenointi)

• Standardin mukaan kahdella putkimerkillä/pystyviivalla. (Noudattaa Oracle ja DB2)
  

SELECT sukunimi || ',' || etunimi AS Nimi
FROM henkilo

• MS:n tuotteissa käytetään + -merkkiä ja MySQL:ssä CONCAT-funktiota
• Tietotyypin muuntaminen:
• eksplisiittisesti numeerisen ja merkkimuotoisen välillä, esim. ANSI-standardin CAST-funktiolla tai Oraclessa TO_CHAR (n,[format [,lang]]); muutoin implisiittisesti
  

Merkkijonoesimerkkejä

MySQL:

SELECT concat(enimi, ' ', snimi) AS Kokonimi FROM henkilo;


SELECT concat(LPAD(enimi,10,'-'), ' lemppari ', snimi) AS Kokonimi
FROM henkilo;

SELECT CAST(pvm as Date) AS PAIVAMAARA FROM henkilo;


SELECT snimi, enimi, CAST(veropros AS DECIMAL(5,2)) as vpros
FROM henkilo;

SELECT snimi, enimi, DATE_FORMAT(pvm, '%d.%m.%Y %H:%i:%s')
FROM henkilo ORDER BY pvm DESC; -- pvm muotoilu, uusin ensin.)

• Merkijonofunktioita mm. http://www.tutorialspoint.com/mysql/mysql-string-functions.htm
  

Muita funktioita

• Numerofunktioita ovat mm. desimaalien pyöristysfunktiot ROUND ja FLOOR; ROUND-funktiolla saadaan luku pyöristettyä kokonaisluvuksi:
  

SELECT ROUND(palkka*1.1, 0) AS palkankorotus
FROM henkilo;

• NULL-arvon muuttamiseen voidaan käyttää esim. COALESCE-funktiota, jolla NULL muutetaan esim. nollaksi:
  

COALESCE(sarake, 0);

• Päivämääräfunktiot, ks. Hovi s. 56 – 65
• Tuotekohtaiset maa-asetukset huomioitava
• ANSI-standardissa CURRENT_DATE: tämä päivä
  

SQL:2003-standardin uusia funktioita

• Nk. skalaarifunktiot: LN (), EXP (), POWER (), SQRT(), FLOOR (), CEIL[ING] (), WIDTH_BUCKET()
• Koostefunktiot: STDDEV_POP (), STDDEV_SAMP (),VAR_POP (), VAR_SAMP () ym.
• Nk. taulukkofunktiot: RANK () OVER ...,DENSE_RANK () OVER ..., PERCENT_RANK (), OVER ..., CUME_DIST () OVER ..., ROW_NUMBER (), OVER ...
• Muitakin on ...
• Tuotekohtaisista eroista on hyvä yhteenveto:
• http://troels.arvin.dk/db/rdbms/#functions
  

CASE-lause

• Tarvitaan kun sarakkeen otsikko riippuu sarakkeen arvosta
• Kaksi syntaksia (sarakenimen kanssa tai ilman)
• Esimerkiksi haetaan tuotteita ja luokituksia:
  

SELECT tuote_nimi,
CASE
WHEN hinta < 100 THEN 'edullinen'
ELSE 'laatutavara'
END AS luokitus
FROM tuote

Päivämääräjuttuja

Kokeile:

SELECT CURRENT_DATE; SELECT CURDATE(); SELECT now();


SELECT YEAR(pvm) as vuosi,MONTH(pvm) as kk, DAY(pvm) AS paiva
FROM henkilo;

Päivien ero päivinä:

DATEDIFF(CURRENT_DATE, pvm) AS eropaivia

• https://dev.mysql.com/doc/refman/5.7/en/datetime.html
  
• http://www.java2s.com/Tutorial/MySQL/0280__Date-Time-Functions/Catalog0280__Date-Time-Functions.htm
• http://www.w3schools.com/SQL/sql_functions.asp
  

---

Luku on kooste Ari Hovin kirjasta, Jouni Huotarin materiaalista ja Ari Rantalan täydennyksistä.