Viisi parasta SQL-ikkunatoimintoa, jotka sinun pitäisi tietää datatieteen haastatteluista

SQL on datamaailman universaali kieli, ja se on tärkein taito data-ammattilaisena.

Syy, miksi SQL on niin tärkeä, on se, että se on tärkein taito, jota vaaditaan tietojen kiistelyvaiheessa. Suuri osa tietojen tutkimisesta, tietojen käsittelystä, putkien kehittämisestä ja kojelaudan luomisesta tapahtuu SQL:n kautta.

Se, mikä erottaa suuret datatieteilijät hyvistä datatieteilijöistä, on se, että suuret datatieteilijät voivat kiistellä dataa niin paljon kuin SQL:n ominaisuudet sallivat. Suuri osa SQL:n tarjonnan täysimääräisestä hyödyntämisestä on ikkunatoimintojen käytön osaaminen.

Sanottuamme, sukelkaamme siihen!

1. Delta, jossa LEAD() ja LAG()

 
LEAD() ja LAG() käytetään useimmiten verrattaessa yhtä ajanjaksoa tietyn mittarin edelliseen ajanjaksoon. Antaakseni muutaman esimerkin…

• Voit saada delta kunkin vuoden myynnin ja edellisen vuoden myynnin välillä
• Saat kirjautumisten/tulosten/verkkosivustokäyntien delta-arvon kuukausittain
• Voit vertailla käyttäjien vaihtuvuutta kuukausittain

Esimerkiksi:

Seuraava kysely näyttää, kuinka voit kysyä kulujen kuukausittaista prosentuaalista muutosta

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Kumulatiiviset summat SUM() tai COUNT()

 
Juoksevien kokonaismäärien laskeminen voidaan tehdä yksinkertaisesti Windowsin funktiolla, joka alkaa SUM()- tai COUNT()-merkillä. Tämä on tehokas työkalu, kun haluat näyttää tietyn mittarin kasvun ajan myötä. Tarkemmin sanottuna se on hyödyllinen seuraavissa tilanteissa:

• Hanki juoksevat kokonaistulot ja kulut ajan mittaan
• Hanki sovellukseen käytetyn ajan kokonaismäärä käyttäjää kohti
• Hanki tulosten kokonaismäärä ajan mittaan

Esimerkiksi:

Seuraava esimerkki näyttää, kuinka voit sisällyttää kuukausittaisten kustannusten kumulatiivisen summan:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Liikkuvat keskiarvot AVG()

 
AVG() on todella tehokas Windows-toiminnoissa, koska sen avulla voit laskea liukuvat keskiarvot ajan myötä.

Liukuvat keskiarvot ovat yksinkertainen, mutta tehokas tapa ennustaa arvoja lyhyellä aikavälillä. Ne ovat myös erittäin hyödyllisiä tasoittamaan kaavion haihtuvia käyriä. Yleensä liikkuvia keskiarvoja käytetään mittaamaan yleistä suuntaa, missä asiat liikkuvat.

Tarkemmin…

• Niitä voidaan käyttää viikoittaisen myynnin yleisen trendin saamiseksi (nouseeko keskiarvo ajan myötä?). Tämä merkitsisi kasvua yrityksenä.
• Niitä voidaan myös käyttää viikoittaisten tulosten tai verkkosivustokäyntien yleisen trendin selvittämiseen.

Esimerkiksi:

Seuraava kysely on esimerkki 10 päivän liukuvan keskiarvon saamisesta konversioille.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() on erityisen hyödyllinen, kun haluat saada ensimmäisen tai viimeisen tietueen. Jos sinulla on esimerkiksi taulukko siitä, milloin salin jäsenet tulivat salille ja haluat saada päivämäärän, jolloin he tulivat salille ensimmäisenä päivänä, voit OSITTAA asiakkaan (nimi/tunnus) ja TILAA ostopäivämäärän mukaan. Sitten saadaksesi ensimmäisen rivin voit yksinkertaisesti suodattaa rivit, joiden rivinumero on yksi.

Esimerkiksi:

Tämä esimerkki näyttää, kuinka voit käyttää ROW_NUMBER():ta saadaksesi ensimmäisen päivämäärän, jolloin jokainen jäsen (käyttäjä) vieraili.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Yhteenvetona totean, että jos sinun on koskaan hankittava ensimmäinen tai viimeinen tietue, ROW_NUMBER() on loistava tapa saavuttaa se.

5. Tietueen sijoitus DENSE_RANK()

 
DENSE_RANK() on samanlainen kuin ROW_NUMBER(), paitsi että se palauttaa saman arvon samanarvoisille arvoille. Tiheä luokitus on varsin hyödyllinen, kun on kyse huipputietueiden hakemisesta, esimerkiksi:

• Jos haluat kerätä tämän viikon 10 katsotuinta Netflix-ohjelmaa
• Jos haluat saada 100 parasta käyttäjää käytettyjen dollarien perusteella
• Jos haluat nähdä 1000 vähiten aktiivista käyttäjää

Esimerkiksi:

Jos haluat sijoittaa parhaat asiakkaasi kokonaismyynnin perusteella, DENSE_RANK() olisi sopiva funktio käytettäväksi.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Kiitos lukemisesta!

 
Ja siinä kaikki! Toivon, että tämä auttaa sinua haastattelun valmistelussa – olen varma, että jos tiedät nämä 5 käsitettä läpikotaisin, pärjäät hyvin useimpien SQL-ikkunatoimintojen ongelmissa.

Kuten aina, toivon sinulle kaikkea hyvää oppimispyrkimyksissäsi!

 
 
Terence Shin on datan harrastaja, jolla on yli 3 vuoden kokemus SQL:stä ja 2+ vuoden kokemus Pythonista, ja bloggaaja Towards Data Science- ja KDnuggets-sivustoissa.

Alkuperäinen. Postitettu luvalla.

Lähde: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html