Valmistautuminen ohjelmointihaastatteluihin · Oppitunti

Ikkunatuloksen suodattaminen

Q: Onko oppitunti ”Ikkunatuloksen suodattaminen” ilmainen?

Kyllä – oppitunnin ”Ikkunatuloksen suodattaminen” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Q: Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.

Q: Kuinka kauan ”Ikkunatuloksen suodattaminen”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Q: Voinko kirjoittaa ja suorittaa koodia tällä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?

Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Miksi ikkunafunktio on käärittävä alikyselyyn tai CTE:hen, jotta sitä voi suodattaa

Oppitunti 4/413 vaihetta

Ikkunatuloksen suodattaminen on ilmainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti CoddyKitissä. Tämä on oppitunti 4/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Miksi ikkunafunktiota ei voi suodattaa WHERE-lausekkeessa

Yleinen haastattelun kompakysymys: kirjoittamalla WHERE ROW_NUMBER() OVER (...) = 1 saatte virheilmoituksen. Ikkunafunktioita ei saa käyttää lausekkeissa WHERE, GROUP BY tai HAVING.

Syynä on looginen suoritusjärjestys. WHERE suoritetaan rivien valitsemiseksi ennen ikkunafunktioiden laskemista. Ikkunaa ei ole vielä edes laskettu, joten siihen ei voi viitata suodatuksessa.

Suoritusjärjestykseen perustuva selitys

Ikkunafunktiot lasketaan omassa vaiheessaan FROM-, WHERE-, GROUP BY- ja HAVING-lausekkeiden jälkeen, mutta ennen lopullisia ORDER BY- ja LIMIT-lausekkeita.

Kun WHERE suoritetaan, sijoitusta tai rivinumeroa ei siis vielä ole olemassa. Suodattaaksenne sen perusteella teidän on annettava ikkunafunktion valmistua ja suodatettava tuotettu sarake sen jälkeen ulommassa kyselytasossa.

Alikyselyyn perustuva vakiomalli

Vakiokorjaus on laskea ikkunafunktio sisäisessä kyselyssä (johdetussa taulussa), antaa tulokselle alias ja suodattaa tätä aliasta ulomman WHERE-lausekkeen avulla.

Johdetulla taululla on oltava alias (t tässä esimerkissä) — haastattelijat kiinnittävät huomiota ehdokkaisiin, jotka unohtavat sen. Nyt rn on tavallinen sarake, johon ulompi kysely voi verrata arvoa.

SELECT *
FROM (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

CTE-malli (usein selkeämpi)

Common Table Expression eli CTE tekee saman asian selkeämmin jäsenneltynä. Määritelkää sijoitus WITH-vaiheessa ja suodattakaa se sitten pääkyselyssä.

Toiminnallisesti tämä on sama kuin alikysely, mutta haastattelijat suosivat yleensä CTE:tä livekoodauksessa, koska tarkoitus hahmottuu ylhäältä alas luettaessa.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

Toimiva esimerkki: ryhmän N parasta

Yksi yleisimmistä ikkunafunktioihin liittyvistä tehtävistä on ”kolme parhaiten palkattua työntekijää kustakin osastosta”. Laskekaa sijoitus CTE:ssä ja säilyttäkää sen jälkeen ulommassa kyselyssä vain rivit, joilla rn <= 3.

Valitkaa sijoitusfunktio tasatulosten käsittelyn perusteella: ROW_NUMBER rajoittaa tuloksen täsmälleen kolmeen riviin osastoa kohden; vaihtakaa funktioksi RANK/DENSE_RANK, jos rajan kohdalla olevat tasatulokset on otettava mukaan.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Toimiva esimerkki: juoksevan summan suodattaminen

Alikyselyyn perustuvaa mallia ei käytetä vain sijoituksiin. Kaikki ikkunatulokset — juoksevat summat, liukuvat keskiarvot ja LAG-erot — on suodatettava samalla tavalla.

Tässä laskemme juoksevan saldon ja säilytämme sitten vain rivit, joilla saldo ylitti ensimmäisen kerran arvon 1000. Suodatus tehdään ikkunafunktiokerroksen ulkopuolella.

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY: Joidenkin tietokantojen tarjoama oikotie

Snowflake, BigQuery, Teradata ja DuckDB tarjoavat QUALIFY-lausekkeen, jolla ikkunatuloksia voi suodattaa suoraan ilman kääreeksi tarvittavaa alikyselyä. Se suoritetaan ikkunafunktioiden jälkeen, juuri siinä vaiheessa kuin tarvitsette.

Mainitkaa QUALIFY osoittaaksenne tuntevanne eri vaihtoehdot, mutta huomioikaa, että se ei kuulu SQL-standardiin eikä sitä ole PostgreSQL:ssä, MySQL:ssä tai SQL Serverissä. Näissä tarvitsette edelleen alikyselyyn tai CTE:hen perustuvan ratkaisun.

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

Älkää sekoittako HAVING-lauseketta ikkunasuodatukseen

Ehdokkaat yrittävät joskus käyttää HAVING-lauseketta sijoituksen suodattamiseen. HAVING suodattaa ryhmiä GROUP BY-koostamisen jälkeen, mutta se suoritetaan edelleen ennen ikkunafunktioita, joten se ei voi viitata ikkunasarakkeseen.

WHERE → suodattaa rivit ennen ryhmittelyä ja ennen ikkunafunktioita.
HAVING → suodattaa koostetut ryhmät, edelleen ennen ikkunafunktioita.
Ikkunatuloksen suodattaminen → edellyttää ulompaa kyselyä (tai QUALIFY-lauseketta).

Esisuodatuksen ja ikkunasuodatuksen yhdistäminen

Usein suodatatte sekä ennen ikkunafunktiota että sen jälkeen. Tehkää tavalliset rivisuodatukset sisäisessä WHERE-lausekkeessa, jotta ikkunafunktio näkee vain olennaiset rivit, ja suodattakaa ikkunatulos sen jälkeen ulommassa kyselyssä.

Tässä esimerkissä rajaamme ensin mukaan vain aktiiviset työntekijät ja valitsemme sitten heidän joukostaan kunkin osaston eniten ansaitsevan. Kun WHERE active sijoitetaan sisäiseen kyselyyn, se muuttaa sitä, mitkä rivit otetaan mukaan sijoitusten laskentaan.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

Suorituskykyä koskeva huomio

Haastattelijat saattavat kysyä, heikentääkö kääreen käyttäminen suorituskykyä. Yleensä ei: optimoija käsittelee alikyselyn tai CTE:n osana yhtä suoritusmallia ja laskee ikkunafunktion kerran. Ylimääräistä taulun läpikäyntiä ei synny vain siksi, että kysely on kääritty.

Yksi poikkeus on se, että joissakin tietokantamoottoreissa CTE voi estää optimointia, koska se materialisoidaan. Suorituskykykriittisissä kohdissa johdettu taulu tai QUALIFY voi siis tuottaa paremman suoritusmallin. Tutkikaa tarvittaessa suorituskykyä komennolla EXPLAIN.

Yleiset virheet

Lopullinen tarkistuslista:

Älkää koskaan sijoittako ikkunafunktiota lausekkeeseen WHERE/HAVING — se aiheuttaa virheen.
Antakaa johdetulle taululle aina alias; nimeämätön FROM-lausekkeen alikysely hylätään.
Valitkaa sijoitusfunktio kysymyksessä tarvittavan tasatulosten käsittelyn perusteella.
Käyttäkää QUALIFY-lauseketta vain sitä tukevissa tietokannoissa; muussa tapauksessa käyttäkää CTE- tai alikyselykäärettä.

Pikatarkistus

Miksi ikkunafunktion suodattaminen edellyttää käärettä?

Vastaa interaktiivisessa oppitunnissa

Kertaus: ikkunatulosten suodattaminen

Nyt hallitsette sijoittamiseen käytettävien ikkunafunktioiden kokonaisuuden:

Ikkunafunktiot suoritetaan lausekkeiden WHERE/GROUP BY/HAVING jälkeen, joten niitä ei voi suodattaa näissä lausekkeissa.
Sijoittakaa ikkunafunktio alikyselyyn tai CTE:hen (antakaa sille aina nimi) ja suodattakaa tulos ulommassa kyselyssä.
Tämä mahdollistaa ryhmän N parhaan, avaimen uusimman rivin ja juoksevan summan raja-arvojen hakemisen.
QUALIFY on kätevä SQL-standardiin kuulumaton oikotie vain Snowflakessa ja BigQueryssä.

Hallussanne on nyt koko se sijoitustyökalupakki, jota haastatteluissa yleensä testataan.

Aloita maksutta

Opi Valmistautuminen ohjelmointihaastatteluihin tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 90
Oppitunnit: 360

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Ikkunatuloksen suodattaminen” ilmainen?

Kyllä – oppitunnin ”Ikkunatuloksen suodattaminen” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Ikkunatuloksen suodattaminen”?

Miksi ikkunafunktio on käärittävä alikyselyyn tai CTE:hen, jotta sitä voi suodattaa Harjoittelet Valmistautuminen ohjelmointihaastatteluihin-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.

Kuinka kauan ”Ikkunatuloksen suodattaminen”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?

Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

OVER, PARTITION BY ja ORDER BY
ROW_NUMBER yksikäsitteiseen numerointiin
RANK ja DENSE_RANK tasatilanteissa
Ikkunatuloksen suodattaminen

← Takaisin: Valmistautuminen ohjelmointihaastatteluihin