Aggregaattifunktiot

Nämä ovat tavallisia aggregaattifunktioita (AVG, SUM, MAX, MIN, COUNT), joita käytetään ikkunakontekstissa. Olemme jo käyttäneet tämän tyyppistä ikkunafunktiota edellisessä luvussa.

Järjestysfunktiot

Järjestysfunktiot SQL:ssä ovat ikkunafunktioita, joiden avulla voidaan antaa järjestys jokaiselle riville tulosjoukon osiossa. Nämä funktiot ovat erittäin hyödyllisiä järjestettyjen laskentojen ja analyysien suorittamiseen.

RANK(): antaa yksilöllisen järjestysnumeron jokaiselle erilaiselle riville osiossa ORDER BY -ehdon perusteella. Samat arvot saavat saman järjestysnumeron, ja järjestyksessä jää aukkoja;
DENSE_RANK(): samanlainen kuin RANK(), mutta ilman aukkoja järjestysnumeroinnissa;
NTILE(n): jakaa järjestetyn osion rivit n ryhmään ja antaa jokaiselle riville ryhmänumeron.

Esimerkki

Järjestämme myynnit Amount-sarakkeen perusteella jokaiselle ProductID:lle nousevassa järjestyksessä käyttämällä DENSE_RANK() -funktiota:


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

Tulostaulu sisältää kaikki päätaulun tiedot sekä lisäsarakkeen, joka näyttää kunkin myynnin järjestysnumeron kyseiselle tuotteelle.

Arvovertailufunktiot

Arvovertailuikkunafunktiot SQL:ssä mahdollistavat nykyisen rivin arvojen vertailun muiden rivien arvoihin samassa osiossa.
Nämä funktiot ovat erityisen hyödyllisiä trendejä analysoitaessa, vierekkäisiin riveihin perustuvissa laskelmissa tai tiettyjen riviarvojen hakemisessa määritellystä ikkunasta. SQL:ssä on useita arvovertailufunktioita:

LAG(): hakee arvon edellisestä rivistä ilman tarvetta itse-liitokselle;
LEAD(): hakee arvon seuraavasta rivistä ilman tarvetta itse-liitokselle;
FIRST_VALUE(): palauttaa ikkunakehyksen ensimmäisen rivin arvon;
LAST_VALUE(): palauttaa ikkunakehyksen viimeisen rivin arvon.

Esimerkki

Käytetään LAG()-arvovertailuikkunafunktiota myyntimäärän muutoksen laskemiseen edelliseen myyntiin verrattuna jokaiselle tuotteelle:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Kyselyn kuvaus

Kyselyn pääosat:

SELECT-lause: Määrittää sarakkeet, jotka haetaan taulusta.
- Sarakkeiden nimet: Sisältää sarakkeet kuten SalesID, ProductID, SalesDate ja Amount, jotka näyttävät olennaiset tiedot jokaisesta myynnistä.
- Ikkunafunktio: Käyttää LAG()-funktiota edellisen rivin arvon hakemiseen tietystä sarakkeesta osiossa. Lisäksi laskettu sarake näyttää nykyisen ja edellisen arvon erotuksen.

Ikkunafunktion syntaksi:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: Sarake, josta edellinen arvo haetaan.
- offset: Kuinka monta riviä taaksepäin nykyisestä rivistä arvo haetaan (oletus 1).
- default_value: Arvo, joka palautetaan, jos offset ylittää osion rajat (valinnainen).
- PARTITION BY partition_column: Jakaa tulosjoukon osioihin, joihin ikkunafunktio sovelletaan, varmistaen että funktio toimii jokaisessa osiossa erikseen.
- ORDER BY order_column: Määrittää rivien järjestyksen kussakin osiossa, jotta ikkunafunktio käsittelee rivit loogisessa järjestyksessä.

Tämän ansiosta voimme helposti hakea tietoa myyntierojen kehityksestä kullekin tuotteelle ilman ali- tai tallennettujen kyselyjen käyttöä.
Voimme myös laskea erotukset kaikille myynneille ilman ositusta seuraavalla kyselyllä:


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Voit huomata, että emme sisällyttäneet PARTITION BY -lausetta OVER-lohkoon. Tämä tarkoittaa, että emme halua hakea edellisiä arvoja vain tietylle tuotteelle, vaan kaikille myynneille taulussa.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 3. Luku 3

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Ikkunafunktioiden Tyypit

Katsotaan lyhyesti tärkeimmät SQL:ssä käytettävät ikkunafunktiotyypit.

Aggregaattifunktiot

Järjestysfunktiot

RANK(): antaa yksilöllisen järjestysnumeron jokaiselle erilaiselle riville osiossa ORDER BY -ehdon perusteella. Samat arvot saavat saman järjestysnumeron, ja järjestyksessä jää aukkoja;
DENSE_RANK(): samanlainen kuin RANK(), mutta ilman aukkoja järjestysnumeroinnissa;
NTILE(n): jakaa järjestetyn osion rivit n ryhmään ja antaa jokaiselle riville ryhmänumeron.

Esimerkki

Järjestämme myynnit Amount-sarakkeen perusteella jokaiselle ProductID:lle nousevassa järjestyksessä käyttämällä DENSE_RANK() -funktiota:


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

Tulostaulu sisältää kaikki päätaulun tiedot sekä lisäsarakkeen, joka näyttää kunkin myynnin järjestysnumeron kyseiselle tuotteelle.

Arvovertailufunktiot

LAG(): hakee arvon edellisestä rivistä ilman tarvetta itse-liitokselle;
LEAD(): hakee arvon seuraavasta rivistä ilman tarvetta itse-liitokselle;
FIRST_VALUE(): palauttaa ikkunakehyksen ensimmäisen rivin arvon;
LAST_VALUE(): palauttaa ikkunakehyksen viimeisen rivin arvon.

Esimerkki

Käytetään LAG()-arvovertailuikkunafunktiota myyntimäärän muutoksen laskemiseen edelliseen myyntiin verrattuna jokaiselle tuotteelle:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Kyselyn kuvaus

Kyselyn pääosat:

SELECT-lause: Määrittää sarakkeet, jotka haetaan taulusta.
- Sarakkeiden nimet: Sisältää sarakkeet kuten SalesID, ProductID, SalesDate ja Amount, jotka näyttävät olennaiset tiedot jokaisesta myynnistä.
- Ikkunafunktio: Käyttää LAG()-funktiota edellisen rivin arvon hakemiseen tietystä sarakkeesta osiossa. Lisäksi laskettu sarake näyttää nykyisen ja edellisen arvon erotuksen.

Ikkunafunktion syntaksi:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: Sarake, josta edellinen arvo haetaan.
- offset: Kuinka monta riviä taaksepäin nykyisestä rivistä arvo haetaan (oletus 1).
- default_value: Arvo, joka palautetaan, jos offset ylittää osion rajat (valinnainen).
- PARTITION BY partition_column: Jakaa tulosjoukon osioihin, joihin ikkunafunktio sovelletaan, varmistaen että funktio toimii jokaisessa osiossa erikseen.
- ORDER BY order_column: Määrittää rivien järjestyksen kussakin osiossa, jotta ikkunafunktio käsittelee rivit loogisessa järjestyksessä.


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 3. Luku 3