SQL ablakfunkciók

Itt kezdődik? Ez a lecke egy teljes hosszúságú oktatóanyag része az SQL használatának az adatelemzéshez. Nézze meg az elejét.

Ebben a leckében a következőket fogjuk ismertetni:

• Bevezetés az ablakfunkciókba
• Alapvető ablakos szintaxis
• A szokásos gyanúsítottak: SUM, COUNT és AVG
• ROW_NUMBER ()
• RANK () és DENSE_RANK ()
• NTILE
• HACS és VEZET
• Ablak álnév meghatározása
• Fejlett ablakolási technikák

Ez a lecke Washington DC Capital Bikeshare Programjának adatait használja fel, amely közzéteszi részletes utazási szintű történelmi adatok a weboldalukon. Az adatokat 2014 februárjában töltötték le, de csak a 2012 első negyedévében gyűjtött adatokra korlátozódnak. Minden sor egy menetet jelent. A legtöbb mező magától értetődő, kivéve a rider_type -t: A “Regisztrált” havi tagságot jelent a túramegosztó programban, a “Alkalmi” azt jelenti, hogy a versenyző 3 napos bérletet vásárolt. A start_time és a end_time mezőket eredeti dátumformájukból kitisztítottuk, hogy megfeleljenek az SQL dátum formázásának – ebben a táblázatban időbélyegként tárolódnak.

Bevezetés az ablakfunkciókba

A PostgreSQL dokumentációja kiváló munkát végez az Ablakfunkciók fogalmának bevezetésében:

Egy ablakfüggvény számításokat hajt végre az aktuális sorhoz valamilyen módon kapcsolódó táblázatsorok között. Ez összehasonlítható azzal a típusú számítással, amelyet egy összesítő függvénnyel lehet elvégezni. De a szokásos összesítő függvényekkel ellentétben egy ablak használata A függvény nem okozza, hogy a sorok egyetlen kimeneti sorba csoportosuljanak – a sorok megtartják külön identitásukat. A kulisszák mögött az ablak funkció nemcsak a lekérdezés eredményének aktuális sorához képes hozzáférni.

Ennek a legpraktikusabb példája a futó összesítés:

Láthatja, hogy a fenti lekérdezés összesítést hoz létre (running_total) a GROUP BY használata nélkül. Bontjuk le a szintaxist és nézzük meg, hogyan működik.

Alapvető szintaxis

A fenti összesítés első része, SUM(duration_seconds) , úgy néz ki, mint bármely más összesítés. A OVER hozzáadásával ablakfüggvényként jelöljük meg. A fenti összesítést úgy olvashatja el, hogy “vegye fel a duration_seconds a teljes eredményhalmazon, start_time sorrendben. “

Ha szűkíteni szeretné az ablakot a teljes adatkészletről az egyes csoportokon belül, az adatkészlethez, ehhez használhatja a PARTITION BY t:

A fenti lekérdezés a start_terminal szerint rendezi a lekérdezést. . A start_terminal minden egyes értékén belül rendezi a következő: start_time, és az aktuális soron futó összes összeg és a duration_seconds. Görgessen lefelé, amíg a start_terminal érték megváltozik, és észreveszi, hogy a running_total elölről indul. Ez történik, ha a (z) PARTITION BY használatával csoportosít. Ha még mindig elüt a ORDER BY, egyszerűen megrendeli a kijelölt oszlop. (s) ugyanúgy, mint a ORDER BY záradék, azzal a különbséggel, hogy minden partíciót különként kezel. Ez létrehozza a futó összeget is – ORDER BY nélkül minden érték egyszerűen összeadódik a duration_seconds értékekkel a megfelelő start_terminal. Próbálja meg futtatni a fenti lekérdezést ORDER BY nélkül, hogy ötletet kapjon:

A ORDER és PARTITION meghatározza, hogy mi az úgynevezett” ablak “- az adatok rendezett részhalmaza, amelyre a számításokat végezzük.

Megjegyzés: Az ablakfüggvények és a standard összesítések nem használhatók ugyanazokban lekérdezés. Pontosabban: “nem vehet fel ablakfunkciókat egy GROUP BY záradékba.

A szokásos gyanúsítottak: SUM, COUNT és AVG

Ablakfunkciók használatakor ugyanazokat az összesítéseket alkalmazhatja, mint normál körülmények között – SUM, COUNT és AVG. Ezek megértésének legegyszerűbb módja az előző példa újrafuttatása néhány további funkcióval. Létrehozás

Alternatív megoldásként ugyanazok a függvények az ORDER BY paranccsal:

Győződjön meg róla, hogy az előző két lekérdezést bekapcsolta a Mode-ba és futtatta őket. Ez a következő gyakorlati probléma nagyon hasonlít a példákhoz, ezért próbálkozzon a fenti kód módosításával, ne pedig a semmiből induljon.

ROW_NUMBER ()

ROW_NUMBER() pontosan azt csinálja, aminek hangzik – megjeleníti az adott sor számát. Első értéke 1, a sorokat pedig az ablak utasítás ORDER BY részének megfelelően számozza. A ROW_NUMBER() nem igényel változó megadását a zárójelben:

A PARTITION BY záradék használata lehetővé teszi, hogy kezdje meg az 1-es számolást minden partícióban. A következő lekérdezés újrakezdi az egyes terminálok számlálását:

RANK () és DENSE_RANK ()

RANK() kissé eltér a ROW_NUMBER() -től. Ha például a start_time alapján rendel, akkor előfordulhat, hogy egyes terminálok két azonos kezdési idõvel rendelkeznek. Ebben az esetben azonos rangot kapnak, míg a ROW_NUMBER() különböző számokat ad nekik. A következő lekérdezésben észreveszi a start_terminal 31000 4. és 5. megfigyelését – mindkettő 4-es, és a következő eredmény 6-os rangot kap:

Alkalmazásától függően használhatja a DENSE_RANK() szót is a RANK() helyett. Képzeljünk el egy olyan helyzetet, amikor három bejegyzésnek ugyanaz az értéke. Bármelyik parancs használatával mindannyian azonos rangot kapnak. E példa kedvéért mondjuk “s” azt “s” 2. ” A két parancs így értékelné a következő eredményeket másképp:

• RANK() az azonos soroknak 2-es rangot adna, majd a 3. helyet kihagyja és 4, tehát a következő eredmény 5 lenne.
• DENSE_RANK() akkor is minden azonos sornak 2-es rangot adna, de a következő sor 3-as lesz. a sorok kihagyásra kerülnek.

NTILE

Az ablakfüggvényekkel meghatározhatja, hogy egy adott sor melyik percentilisbe (vagy kvartilisbe, vagy bármely más felosztásba) esik. A szintaxis: NTILE(*# of buckets*). Ebben az esetben az ORDER BY határozza meg, hogy melyik oszlopot használja a kvartilisek (vagy bármilyen tetszőleges számú “csempe” meghatározásához) adja meg). Például:

A fenti lekérdezés eredményeit megnézve láthatja, hogy a percentile oszlop nem pontosan úgy számol, ahogyan azt elvárhatja. Ha csak két rekordja volt, és te a percentiliseket mérted, akkor elvárhatod, hogy az egyik rekord meghatározza az 1., a másik rekord pedig a 100. százalékot. A NTILE függvény használatával valójában egy 1. és egy 2. percentilisben lévő rekord látható. Ezt láthatja a 31000 – a percentile oszlop csak numerikus rangsornak tűnik. Ha lefelé görget a start_terminal 31007 pontig, akkor láthatja, hogy megfelelően kiszámítja a percentiliseket, mert 100-nál több rekord van a start_terminal számára. Ha nagyon kicsi ablakokkal dolgozik, ne feledje ezt, és fontolja meg kvartilis vagy hasonlóan kicsi használatát sávok.

HACS és VEZET

Gyakran hasznos lehet összehasonlítani a sorokat az előző vagy a következő sorokkal, különösen ha értelmes sorrendben kapta meg az adatokat. Használhatja a LAG vagy a LEAD elemeket olyan oszlopok létrehozásához, amelyek értékeket húznak más sorok – csak annyit kell tennie, hogy megadja, melyik oszlopból húzza ki, és hány sorral távolabb szeretné végrehajtani a húzást. LAG húz az előző sorokból, és LEAD a következő sorokból húz:

Ez különösen akkor hasznos, ha kiszámolni szeretné különbségek a sorok között:

A difference oszlop első sora null, mert nincs előző sor, ahonnan ki lehetne húzni. Ehhez hasonlóan a LEAD használatával nullákat hoz létre az adatkészlet végén. Ha egy kicsit tisztábbá szeretné tenni az eredményeket, akkor a nullák eltávolításához egy külső lekérdezésbe csomagolhatja:

Ablak-álnév meghatározása

Ha írást tervez több ablakfunkció ugyanabba a lekérdezésbe, ugyanazon ablak használatával álnevet hozhat létre.Vegyük a fenti NTILE példát:

Ez így írható át:

A WINDOW a záradéknak, ha szerepel, mindig a WHERE záradék után kell lennie.

Fejlett ablakkezelési technikák

Megtekintheti az ablakok teljes listáját. funkciók a Postgres-ben (a szintaxis mód használja) a Postgres dokumentációjában. Ha ablakfunkciókat használ egy csatlakoztatott adatbázisban, akkor nézze meg a rendszerének megfelelő szintaxis útmutatót.