Oppiskele Ikkunafunktioiden Tyypit | Joitakin Lisäaiheita

Käydään lyhyesti läpi tärkeimmät SQL:ssä käytettävät ikkunafunktiotyypit.

Aggregaattifunktiot

Nämä ovat tavanomaisia 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 rivit järjestetyssä osiossa n ryhmään ja antaa ryhmänumeron jokaiselle riville.

Esimerkki

Järjestetään myynnit Amount-kentän perusteella kullekin 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 rivien arvojen hakemisessa määritellyssä ikkunassa. SQL:ssä on useita arvovertailufunktioita:

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

Esimerkki

Käytetään LAG()-arvovertailuikkunafunktiota myyntimäärän muutoksen laskemiseen edelliseen myyntiin verrattuna kullekin 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;

Tämän ansiosta voimme helposti hakea tietoa myyntierojen muutoksista kullekin tuotteelle ilman alikyselyitä tai tallennettuja proseduuria.
Voimme myös laskea erotukset kaikille myynneille ilman osiointia 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

Pyyhkäise näyttääksesi valikon

Käydään lyhyesti läpi 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 rivit järjestetyssä osiossa n ryhmään ja antaa ryhmänumeron jokaiselle riville.

Esimerkki

Järjestetään myynnit Amount-kentän perusteella kullekin 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ä tulosjoukossa ilman tarvetta itse-liitokselle;
LEAD(): hakee arvon seuraavasta rivistä tulosjoukossa ilman tarvetta itse-liitokselle;
FIRST_VALUE(): palauttaa ensimmäisen rivin arvon ikkunakehyksessä;
LAST_VALUE(): palauttaa viimeisen rivin arvon ikkunakehyksessä.

Esimerkki

Käytetään LAG()-arvovertailuikkunafunktiota myyntimäärän muutoksen laskemiseen edelliseen myyntiin verrattuna kullekin 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;


              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