Новая гісторыя

Функцыі вокнаў SQL: кароткі агляд з прыкладамі

па Luca Liu5m2025/01/05

FA-AF

Занадта доўга; Чытаць

Аконныя функцыі - гэта магутная функцыя SQL, якая выкарыстоўваецца для выканання вылічэнняў у наборы радкоў, звязаных з бягучым радком. У адрозненне ад агрэгаваных функцый, аконныя функцыі не групуюць радкі ў адзін вывад. Яны вяртаюць вынік для кожнага радка, захоўваючы кантэкст набору даных.

featured image - Функцыі вокнаў SQL: кароткі агляд з прыкладамі

Уводзіны

Аконныя функцыі - гэта магутная функцыя SQL, якая выкарыстоўваецца для выканання вылічэнняў у наборы радкоў, звязаных з бягучым радком. У адрозненне ад агрэгаваных функцый, аконныя функцыі не групуюць радкі ў адзін вывад; яны вяртаюць вынік для кожнага радка, захоўваючы кантэкст набору дадзеных.

У гэтым артыкуле мы вывучым некаторыя часта выкарыстоўваюцца аконныя функцыі SQL ( ROW_NUMBER() , RANK() , DENSE_RANK() , NTILE() , LEAD() і LAG() ) з прыкладамі.

Прыклад табліцы: даныя аб продажах

Мы будзем выкарыстоўваць наступную табліцу продажаў, каб прадэманстраваць функцыі вокнаў:

SalesID	Ідэнтыфікатар кліента	Прадукт	Рэгіён	Сума	Дата продажу
1	101	Ноўтбук	Поўнач	1200	2023-01-05
2	102	Планшэт	Поўнач	800	2023-02-15
3	103	Тэлефон	Поўнач	800	2023-03-10
4	104	Планшэт	Поўнач	500	2023-04-01
5	105	Ноўтбук	поўдзень	1300	2023-05-05
6	106	Планшэт	поўдзень	700	2023-06-20
7	107	Тэлефон	Захад	900	2023-07-15
8	108	Ноўтбук	Усход	1300	2023-08-10

1. ROW_NUMBER()

Функцыя ROW_NUMBER() прысвойвае ўнікальны нумар кожнаму радку ў раздзеле, упарадкаваны па вызначаным слупку.

Задача : прысвоіць унікальны нумар радка кожнай продажы ў рэгіёне на аснове сумы продажу (ад вышэйшай да найменшай).

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

вынік :

SalesID	Рэгіён	Сума	Нумар радка
1	Поўнач	1200	1
2	Поўнач	800	2
3	Поўнач	800	3
4	Поўнач	500	4
5	поўдзень	1300	1
6	поўдзень	700	2
7	Захад	900	1
8	Усход	1300	1

2. РАНГ()

Функцыя RANK() прысвойвае ранг кожнаму радку ў раздзеле. Радкі з аднолькавымі значэннямі атрымліваюць аднолькавы ранг, а наступны ранг прапускаецца.

Задача : ранжыраваць продажы ў кожным рэгіёне па суме (ад самай высокай да найменшай).

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

вынік :

SalesID	Рэгіён	Сума	Ранг
1	Поўнач	1200	1
2	Поўнач	800	2
3	Поўнач	800	2
4	Поўнач	500	4
5	поўдзень	1300	1
6	поўдзень	700	2
7	Захад	900	1
8	Усход	1300	1

Ключавая асаблівасць :

Для паўночнага рэгіёну абодва радкі Amount = 800 маюць ранг 2.

Наступны ранг прапускаецца (гэта значыць, ранг 3 адсутнічае) і пераходзіць на 4.

3. DENSE_RANK()

Функцыя DENSE_RANK() прызначае такія рангі, як RANK(), але яна не прапускае рангі пасля роўнасці.

Задача : прызначыць шчыльныя рангі для продажаў у кожным рэгіёне па колькасці (ад высокага да самага нізкага).

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

вынік :

SalesID	Рэгіён	Сума	DenseRank
1	Поўнач	1200	1
2	Поўнач	800	2
3	Поўнач	800	2
4	Поўнач	500	3
5	поўдзень	1300	1
6	поўдзень	700	2
7	Захад	900	1
8	Усход	1300	1

Ключавая асаблівасць :

Для паўночнага рэгіёну абодва радкі Amount = 800 маюць ранг 2.

Наступны ранг - 3, без пропуску шэрагаў.

4. NTILE()

NTILE() дзеліць радкі на зададзеную колькасць прыкладна роўных груп.

Задача : падзяліць усе продажу на 4 групы па колькасці ў парадку змяншэння.

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

вынік :

SalesID	Сума	Квартыль
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() атрымлівае значэнне з наступнага радка ў тым жа раздзеле.

Задача : параўнаць суму кожнай продажу з наступнай сумай продажу, упарадкаванай па даце продажу.

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

вынік :

SalesID	Сума	Наступная сума
1	1200	800
2	800	800
3	800	500
4	500	1300
5	1300	700
6	700	900
7	900	1300
8	1300	НУЛЬ

6. LAG()

LAG() атрымлівае значэнне з папярэдняга радка ў тым жа раздзеле.

Задача : параўнаць кожную суму продажу з папярэдняй сумай продажу, упарадкаванай па даце продажу.

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

вынік :

SalesID	Сума	PrevAmount
1	1200	НУЛЬ
2	800	1200
3	800	800
4	500	800
5	1300	500
6	700	1300
7	900	700
8	1300	900

Заключэнне

Такія аконныя функцыі SQL, як ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD() і LAG(), забяспечваюць магутныя спосабы аналізу даных у раздзелах.

Асноўныя вывады:

ROW_NUMBER() прызначае ўнікальны ідэнтыфікатар для кожнага радка.
RANK() і DENSE_RANK() адрозніваюцца спосабам апрацоўкі сувязяў (прапусканне або адсутнасць пропуску).
NTILE() карысны для падзелу радкоў на статыстычныя групы.
LEAD() і LAG() дазваляюць параўноўваць з суседнімі радкамі.

Асвоіўшы гэтыя функцыі, вы зможаце эфектыўна вырашаць складаныя задачы аналітыкі і ранжыравання!

Дзякуй, што знайшлі час, каб разам са мной вывучыць інфармацыю, звязаную з дадзенымі. Я цаню ваш удзел. Калі вы лічыце гэтую інфармацыю карыснай, я запрашаю вас сачыць за мной або звязацца са мной на LinkedIn . Прыемнага вывучэння!👋