Новая история

Оконные функции 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() присваивает уникальный номер каждой строке в разделе, упорядоченной по указанному столбцу.

Задача : Присвоить уникальный номер строки каждой продаже в регионе на основе суммы продажи (от наибольшей к наименьшей).

 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

Ключевая особенность :

Для северного региона обе строки с количеством = 800 имеют ранг 2.

Следующий ранг пропускается (т.е. ранг 3 отсутствует) и выполняется переход к 4.

3. ПЛОТНЫЙ_РАНГ()

Функция DENSE_RANK() назначает ранги так же, как RANK(), но не пропускает ранги после равенств.

Задача : Присвоить объемным показателям продаж в каждом регионе ранжирование по объему (от наибольшего к наименьшему).

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

Результат :

SalesID	Область	Количество	ПлотныйРанг
1	Север	1200	1
2	Север	800	2
3	Север	800	2
4	Север	500	3
5	Юг	1300	1
6	Юг	700	2
7	Запад	900	1
8	Восток	1300	1

Ключевая особенность :

Для северного региона обе строки с количеством = 800 имеют ранг 2.

Следующий ранг — 3, без пропуска рангов.

4. НТИЛЬ()

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() извлекает значение из предыдущей строки в том же разделе.

Задача : Сравнить сумму каждой продажи с суммой предыдущей продажи, упорядоченной по дате продажи.

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

Результат :

SalesID	Количество	ПредыдущаяСумма
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 . Приятного изучения!👋