Nová história

Funkcie okna SQL: Rýchly prehľad s príkladmi

podľa Luca Liu5m2025/01/05

FA-AF

Príliš dlho; Čítať

Funkcie okien sú výkonnou funkciou v SQL, ktorá sa používa na vykonávanie výpočtov v skupine riadkov súvisiacich s aktuálnym riadkom. Na rozdiel od agregovaných funkcií, funkcie okna nezoskupujú riadky do jedného výstupu. Vrátia výsledok pre každý riadok pri zachovaní kontextu množiny údajov.

featured image - Funkcie okna SQL: Rýchly prehľad s príkladmi

Úvod

Funkcie okien sú výkonnou funkciou v SQL, ktorá sa používa na vykonávanie výpočtov v skupine riadkov súvisiacich s aktuálnym riadkom. Na rozdiel od agregovaných funkcií, funkcie okna nezoskupujú riadky do jedného výstupu; vrátia výsledok pre každý riadok pri zachovaní kontextu množiny údajov.

V tomto článku preskúmame niektoré bežne používané funkcie okna SQL ( ROW_NUMBER() , RANK() , DENSE_RANK() , NTILE() , LEAD() a LAG() ) s príkladmi.

Vzorová tabuľka: Údaje o predaji

Na ukážku funkcií okna použijeme nasledujúcu tabuľku predaja:

SalesID	CustomerID	Produkt	región	Suma	Dátum predaja
1	101	laptop	Severná	1200	2023-01-05
2	102	Tablet	Severná	800	2023-02-15
3	103	Telefón	Severná	800	2023-03-10
4	104	Tablet	Severná	500	2023-04-01
5	105	laptop	Juh	1300	5. 5. 2023
6	106	Tablet	Juh	700	2023-06-20
7	107	Telefón	West	900	2023-07-15
8	108	laptop	východ	1300	2023-08-10

1. ROW_NUMBER()

Funkcia ROW_NUMBER() priraďuje každému riadku v rámci oddielu jedinečné číslo zoradené podľa určeného stĺpca.

Úloha : Priraďte jedinečné číslo riadku každému predaju v rámci regiónu na základe sumy predaja (od najvyššej po najnižšiu).

 SELECT SalesID, Region, Amount, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum FROM Sales;

výsledok :

SalesID	región	Suma	RowNum
1	Severná	1200	1
2	Severná	800	2
3	Severná	800	3
4	Severná	500	4
5	Juh	1300	1
6	Juh	700	2
7	West	900	1
8	východ	1300	1

2. RANK()

Funkcia RANK() priraďuje poradie každému riadku v rámci oddielu. Riadky s rovnakými hodnotami získajú rovnaké poradie a ďalšie poradie sa preskočí.

Úloha : Zoraďte predaj v rámci každého regiónu podľa sumy (od najvyššej po najnižšiu).

 SELECT SalesID, Region, Amount, RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank FROM Sales;

výsledok :

SalesID	región	Suma	Poradie
1	Severná	1200	1
2	Severná	800	2
3	Severná	800	2
4	Severná	500	4
5	Juh	1300	1
6	Juh	700	2
7	West	900	1
8	východ	1300	1

Kľúčová vlastnosť :

Pre región Sever sa obe Čiastka = 800 riadkov delí na poradie 2.

Ďalšia pozícia sa preskočí (tj 3. pozícia chýba) a preskočí na 4.

3. DENSE_RANK()

Funkcia DENSE_RANK() priraďuje hodnosti ako RANK(), ale nepreskakuje poradie po nerozhodnom výsledku.

Úloha : Priraďte predajom v rámci každého regiónu husté poradie podľa množstva (od najvyššej po najnižšiu).

 SELECT SalesID, Region, Amount, DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank FROM Sales;

výsledok :

SalesID	región	Suma	DenseRank
1	Severná	1200	1
2	Severná	800	2
3	Severná	800	2
4	Severná	500	3
5	Juh	1300	1
6	Juh	700	2
7	West	900	1
8	východ	1300	1

Kľúčová vlastnosť :

Pre región Sever sa obe Čiastka = 800 riadkov delí na poradie 2.

Ďalšia hodnosť je 3, bez preskakovania hodností.

4. NTILE()

NTILE() rozdeľuje riadky na určený počet približne rovnakých skupín.

Úloha : Rozdeľte všetky predaje do 4 skupín podľa čiastky v zostupnom poradí.

 SELECT SalesID, Amount, NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile FROM Sales;

výsledok :

SalesID	Suma	Kvartil
5	1300	1
8	1300	1
1	1200	2
7	900	2
2	800	3
3	800	3
4	500	4
6	700	4

5. LEAD()

LEAD() načíta hodnotu z nasledujúceho riadku v rámci toho istého oddielu.

Úloha : Porovnajte každú predajnú sumu s ďalšou predajnou sumou zoradenou podľa dátumu predaja.

 SELECT SalesID, Amount, LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount FROM Sales;

výsledok :

SalesID	Suma	NextAmount
1	1200	800
2	800	800
3	800	500
4	500	1300
5	1300	700
6	700	900
7	900	1300
8	1300	NULL

6. MAS()

LAG() načíta hodnotu z predchádzajúceho riadku v rámci toho istého oddielu.

Úloha : Porovnajte každú predajnú sumu s predchádzajúcou predajnou sumou zoradenou podľa SaleDate.

 SELECT SalesID, Amount, LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount FROM Sales;

výsledok :

SalesID	Suma	PrevAmount
1	1200	NULL
2	800	1200
3	800	800
4	500	800
5	1300	500
6	700	1300
7	900	700
8	1300	900

Záver

Funkcie okna SQL ako ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD() a LAG() poskytujú výkonné spôsoby analýzy údajov v rámci oddielov.

Kľúčové poznatky:

ROW_NUMBER() priraďuje každému riadku jedinečný identifikátor.
RANK() a DENSE_RANK() sa líšia v tom, ako zaobchádzajú s remízou (preskakovanie vs. žiadne preskakovanie).
NTILE() je užitočné na rozdelenie riadkov do štatistických skupín.
LEAD() a LAG() umožňujú porovnanie so susednými riadkami.

Osvojením si týchto funkcií dokážete efektívne zvládnuť zložité analytické a hodnotiace úlohy!

Ďakujem, že ste si našli čas a preskúmali so mnou štatistiky súvisiace s údajmi. Vážim si vašu angažovanosť. Ak považujete tieto informácie za užitočné, pozývam vás, aby ste ma sledovali alebo sa so mnou spojili na LinkedIn . Príjemné objavovanie!👋