Nová historie

Funkce okna SQL: Rychlý přehled s příklady

podle Luca Liu5m2025/01/05

FA-AF

Příliš dlouho; Číst

Funkce okna jsou výkonnou funkcí v SQL, která se používá k provádění výpočtů přes sadu řádků souvisejících s aktuálním řádkem. Na rozdíl od agregačních funkcí funkce okna neseskupují řádky do jednoho výstupu. Vracejí výsledek pro každý řádek při zachování kontextu datové sady.

featured image - Funkce okna SQL: Rychlý přehled s příklady

Zavedení

Funkce okna jsou výkonnou funkcí v SQL, která se používá k provádění výpočtů v sadě řádků souvisejících s aktuálním řádkem. Na rozdíl od agregačních funkcí funkce okna neseskupují řádky do jednoho výstupu; vracejí výsledek pro každý řádek při zachování kontextu datové sady.

V tomto článku prozkoumáme některé běžně používané funkce okna SQL ( ROW_NUMBER() , RANK() , DENSE_RANK() , NTILE() , LEAD() a LAG() ) s příklady.

Vzorová tabulka: Údaje o prodeji

K demonstraci funkcí okna použijeme následující tabulku prodeje:

SalesID	Číslo zákazníka	Produkt	Kraj	Množství	Datum prodeje
1	101	Přenosný počítač	Severní	1200	2023-01-05
2	102	Tableta	Severní	800	2023-02-15
3	103	Telefon	Severní	800	2023-03-10
4	104	Tableta	Severní	500	2023-04-01
5	105	Přenosný počítač	Jižní	1300	2023-05-05
6	106	Tableta	Jižní	700	2023-06-20
7	107	Telefon	Západ	900	2023-07-15
8	108	Přenosný počítač	Východní	1300	2023-08-10

1. ROW_NUMBER()

Funkce ROW_NUMBER() přiřadí každému řádku v oddílu jedinečné číslo, seřazené podle zadaného sloupce.

Úkol : Přiřaďte každému prodeji v rámci regionu jedinečné číslo řádku na základě částky prodeje (od nejvyšší po nejnižší).

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

výsledek :

SalesID	Kraj	Množství	RowNum
1	Severní	1200	1
2	Severní	800	2
3	Severní	800	3
4	Severní	500	4
5	Jižní	1300	1
6	Jižní	700	2
7	Západ	900	1
8	Východní	1300	1

2. RANK()

Funkce RANK() přiřadí pořadí každému řádku v rámci oddílu. Řádky se stejnými hodnotami získají stejné pořadí a další pořadí je přeskočeno.

Úkol : Seřaďte prodeje v rámci každého regionu podle částky (od nejvyšší po nejnižší).

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

výsledek :

SalesID	Kraj	Množství	Pořadí
1	Severní	1200	1
2	Severní	800	2
3	Severní	800	2
4	Severní	500	4
5	Jižní	1300	1
6	Jižní	700	2
7	Západ	900	1
8	Východní	1300	1

Klíčová vlastnost :

Pro region Sever obě Částka = 800 řádků sdílejí pořadí 2.

Další pozice je přeskočena (tj. pozice 3 chybí) a skočí na 4.

3. DENSE_RANK()

Funkce DENSE_RANK() přiděluje pořadí jako RANK(), ale nepřeskakuje pořadí po nerozhodném výsledku.

Úkol : Přiřaďte prodejům v rámci jednotlivých regionů husté pořadí podle množství (od nejvyšší po nejnižší).

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

výsledek :

SalesID	Kraj	Množství	DenseRank
1	Severní	1200	1
2	Severní	800	2
3	Severní	800	2
4	Severní	500	3
5	Jižní	1300	1
6	Jižní	700	2
7	Západ	900	1
8	Východní	1300	1

Klíčová vlastnost :

Pro region Sever obě Částka = 800 řádků sdílejí pořadí 2.

Další hodnost je 3, bez přeskakování hodností.

4. NTILE()

NTILE() rozdělí řádky do zadaného počtu přibližně stejných skupin.

Úkol : Rozdělte všechny prodeje do 4 skupin na základě Částky v sestupném pořadí.

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

výsledek :

SalesID	Množství	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. VÉST()

LEAD() načte hodnotu z dalšího řádku ve stejném oddílu.

Úkol : Porovnejte každou prodejní částku s další prodejní částkou seřazenou podle SaleDate.

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

výsledek :

SalesID	Množství	DalšíČástka
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čte hodnotu z předchozího řádku v rámci stejného oddílu.

Úkol : Porovnejte každou prodejní částku s předchozí prodejní částkou seřazenou podle SaleDate.

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

výsledek :

SalesID	Množství	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ávěr

Funkce okna SQL jako ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD() a LAG() poskytují výkonné způsoby analýzy dat v oddílech.

Klíčové poznatky:

ROW_NUMBER() přiřadí každému řádku jedinečný identifikátor.
RANK() a DENSE_RANK() se liší v tom, jak zacházejí s remízou (přeskakování vs. žádné přeskakování).
NTILE() je užitečné pro rozdělení řádků do statistických skupin.
LEAD() a LAG() umožňují srovnání se sousedními řádky.

Zvládnutím těchto funkcí můžete efektivně zvládat komplexní analýzy a klasifikační úkoly!

Děkuji, že jste si udělal čas a prozkoumal se mnou statistiky související s daty. Vážím si vašeho angažmá. Pokud považujete tyto informace za užitečné, zvu vás, abyste mě sledovali nebo se se mnou spojili na LinkedIn . Šťastné objevování! 👋