Что такое over в sql

SELECT — предложение OVER (Transact-SQL)

Определяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. То есть предложение OVER определяет окно или определяемый пользователем набор строк внутри результирующего набора запроса. Затем оконная функция вычисляет значение для каждой строки в окне. Вы можете использовать предложение OVER вместе с функциями для вычисления статистических значений, например для вычисления скользящих средних, суммарных статистических выражений, промежуточных итогов или первых N результатов в группе.

• Ранжирующие функции
• Агрегатные функции
• Аналитические функции
• Функция NEXT VALUE FOR

Синтаксис

-- Syntax for SQL Server, Azure SQL Database, and Azure Synapse Analytics OVER ( [ ] [ ] [ ] ) ::= PARTITION BY value_expression , . [ n ] ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ . n ] ::= < ROWS | RANGE >  ::= < | > ::= BETWEEN AND  ::= < | > ::= < UNBOUNDED PRECEDING | PRECEDING | CURRENT ROW > ::= < UNBOUNDED FOLLOWING | FOLLOWING | CURRENT ROW > ::= < > 

-- Syntax for Parallel Data Warehouse OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

Оконные функции могут иметь следующие аргументы в предложении OVER :

• PARTITION BY — разделяет результирующий набор запроса на секции.
• ORDER BY — определяет логический порядок строк в каждой секции результирующего набора.
• ROWS/RANGE — ограничивает строки в пределах секции, указывая начальную и конечную точки. Он требует аргумента ORDER BY , а по умолчанию будет охватывать интервал от начала секции до текущего элемента, если указан аргумент ORDER BY .

Если аргументы не указаны, оконные функции будут применены ко всему результирующему набору.

select object_id , [min] = min(object_id) over() , [max] = max(object_id) over() from sys.objects 

object_id	мин	макс.
3	3	2139154666
5	3	2139154666
.	.	.
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Разделяет результирующий набор запроса на секции. Оконная функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции.

PARTITION BY *value_expression* 

Если аргумент PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну секцию. Функция будет применена ко всем строкам в секции, если не указано предложение ORDER BY .

PARTITION BY value_expression

Определяет столбец, по которому секционируется набор строк. Аргумент value_expression может ссылаться только на столбцы, сделанные доступными с помощью предложения FROM. Аргумент value_expression не может ссылаться на выражения или псевдонимы в списке выбора. Выражение value_expression может быть выражением столбца, скалярным вложенным запросом, скалярной функцией или пользовательской переменной.

select object_id, type , [min] = min(object_id) over(partition by type) , [max] = max(object_id) over(partition by type) from sys.objects 

object_id	type	мин	макс.
68195293	PK	68195293	711673583
631673298	PK	68195293	711673583
711673583	PK	68195293	711673583
.	.	.
3	S	3	98
5	S	3	98
.	.	.
98	S	3	98
.	.	.

ORDER BY

ORDER BY *order_by_expression* [COLLATE *collation_name*] [ASC|DESC] 

Определяет логический порядок строк в каждой секции результирующего набора. То есть он указывает логический порядок, в котором выполняется вычисление оконной функции.

• Если он не указан, то порядок по умолчанию — ASC , а оконная функция будет использовать все строки в секции.
• Если он указан, а аргумент ROWS/RANGE не указан, то по умолчанию используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW для фрейма окна теми функциями, которые могут принимать дополнительную спецификацию ROWS/RANGE (например, min или max ).

select object_id, type , [min] = min(object_id) over(partition by type order by object_id) , [max] = max(object_id) over(partition by type order by object_id) from sys.objects 

object_id	type	мин	макс.
68195293	PK	68195293	68195293
631673298	PK	68195293	631673298
711673583	PK	68195293	711673583
.	.	.
3	S	3	3
5	S	3	5
6	S	3	6
.	.	.
97	S	3	97
98	S	3	98
.	.	.

order_by_expression
Указывает столбец или выражение, по которому производится сортировка. Аргумент order_by_expression может ссылаться только на столбцы, сделанные доступными с помощью предложения FROM. Нельзя указывать целое число для обозначения имени или псевдонима столбца.

COLLATE collation_name
Указывает, что операция ORDER BY должна выполняться в соответствии с параметрами сортировки, указанными в аргументе collation_name. Аргументом collation_name может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Дополнительные сведения см. в статье Collation and Unicode Support. Аргумент COLLATE применяется только к столбцам типа char, varchar, nchar и nvarchar.

ASC | DESC
Указывает порядок сортировки значений в указанном столбце — по возрастанию или по убыванию. Порядок сортировки по умолчанию — ASC. Значения NULL рассматриваются как минимально возможные значения.

ROWS или RANGE

Область применения: SQL Server 2012 (11.x) и более поздних версий.

Еще больше ограничивает строки в пределах секции, указывая начальную и конечную точки. Это достигается путем указания диапазона строк в отношении текущей строки с помощью логических или физических взаимосвязей. Физическая взаимосвязь достигается с помощью предложения ROWS.

Предложение ROWS ограничивает строки внутри секции путем указания фиксированного числа строк, предшествующих или следующих после текущей строки. В качестве альтернативы предложение RANGE логически ограничивает строки внутри секции путем указания диапазона значений в отношении к значению текущей строки. Предшествующие и последующие строки определяются на основании порядка, заданного в предложении ORDER BY. Рамка окна «RANGE . ТЕКУЩАЯ СТРОКА . » включает все строки, имеющие одинаковые значения в выражении ORDER BY, что и текущая строка. Например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает, что окно строк, с которым работает функция, содержит всего три строки, при этом текущей строке предшествуют 2 строки (включая текущую).

select object_id , [preceding] = count(*) over(order by object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) , [central] = count(*) over(order by object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) , [following] = count(*) over(order by object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) from sys.objects order by object_id asc 

object_id	preceding	центральный	following
3	1	3	156
5	2	4	155
6	3	5	154
7	4	5	153
8	5	5	152
.	.	.	.
2112726579	153	5	4
2119678599	154	5	3
2123154609	155	4	2
2139154666	156	3	1

Предложения ROWS и RANGE требуют, чтобы было указано предложение ORDER BY. Если предложение ORDER BY содержит несколько выражений порядка, то CURRENT ROW FOR RANGE при определении текущей строки учитывает все столбцы в списке ORDER BY.

UNBOUNDED PRECEDING

Область применения: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно начинается с первой строки секции. UNBOUNDED PRECEDING может быть указано только как начальная точка окна.

PRECEDING
Указывается со для обозначения числа строк или значений перед текущей строкой. Эта спецификация не допускается в предложении RANGE.

CURRENT ROW

Область применения: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением ROWS или что окно заканчивается на текущем значении при использовании с предложением RANGE. CURRENT ROW может быть задана и как начальная, и как конечная точка.

BETWEEN AND

Область применения: SQL Server 2012 (11.x) и более поздних версий.

BETWEEN AND

Используется совместно с предложением ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна. определяет граничную начальную точку, а определяет граничную конечную точку. Верхняя граница не может быть меньше нижней границы.

UNBOUNDED FOLLOWING

Область применения: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно заканчивается на последней строке секции. UNBOUNDED FOLLOWING может быть указано только как конечная точка окна. Например, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING определяет, что окно начинается на текущей строке и заканчивается на последней строке секции.

FOLLOWING
Указывается со для обозначения числа строк или значений после текущей строки. При указании FOLLOWING как начальной точки окна конечная точка должна быть FOLLOWING. Например, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING определяет, что окно начинается на второй строке после текущей и заканчивается на десятой строке после текущей строки. Эта спецификация не допускается в предложении RANGE.

неподписанный целочисленный литерал
Область применения: SQL Server 2012 (11.x) и более поздних версий.

Положительный целочисленный литерал (включая 0), который указывает число строк или значений перед или после текущей строки или значения. Эта спецификация является допустимой только в предложении ROWS.

Замечания

В одном запросе с одним предложением FROM может использоваться несколько оконных функций. Предложение OVER для каждой функции может отличаться в части секционирования и упорядочения.

Если PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну группу.

Если указано предложение ROWS или RANGE и используется для (короткий синтаксис), то данная спецификация используется в качестве начальной точки границы рамки окна, а CURRENT ROW — в качестве конечной точки границы окна. Например «ROWS 5 PRECEDING» равно «ROWS BETWEEN 5 PRECEDING AND CURRENT ROW».

Если предложение ORDER BY не указано, то для рамки окна используется весь раздел. Это относится только к тем функциям, которым не требуется предложение ORDER BY. Если предложение ROWS или RANGE не указаны, а указано предложение ORDER BY, то в качестве значения по умолчанию для рамки окна используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Это относится только к тем функциям, которые могут принимать дополнительную спецификацию ROWS или RANGE. Например, ранжирующая функция не может принимать предложение ROWS или RANGE, поэтому данная рамка окна не может использоваться, даже несмотря на наличие предложения ORDER BY, а предложение ROWS или RANGE отсутствует.

Ограничения

Предложение OVER не может использоваться с агрегатной функцией CHECKSUM.

В зависимости от используемой функции (ранжирующая, агрегатная или аналитическая) с предложением OVER, и/или могут не поддерживаться.

Примеры

А. Использование предложения OVER с функцией ROW_NUMBER

Следующий пример демонстрирует использование предложения OVER с функцией ROW_NUMBER для отображения номера каждой строки в секции. Предложение ORDER BY, указанное в предложении OVER упорядочивает строки каждой секции по столбцу SalesYTD . Предложение ORDER BY в инструкции SELECT определяет порядок, в котором возвращается весь результирующий набор запроса.

USE AdventureWorks2022; GO SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", p.LastName, s.SalesYTD, a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0 ORDER BY PostalCode; GO 

Row Number LastName SalesYTD PostalCode --------------- ----------------------- --------------------- ---------- 1 Mitchell 4251368.5497 98027 2 Blythe 3763178.1787 98027 3 Carson 3189418.3662 98027 4 Reiter 2315185.611 98027 5 Vargas 1453719.4653 98027 6 Ansman-Wolfe 1352577.1325 98027 1 Pak 4116871.2277 98055 2 Varkey Chudukatil 3121616.3202 98055 3 Saraiva 2604540.7172 98055 4 Ito 2458535.6169 98055 5 Valdez 1827066.7118 98055 6 Mensa-Annan 1576562.1966 98055 7 Campbell 1573012.9383 98055 8 Tsoflias 1421810.9242 98055 

B. Использование предложения OVER с агрегатными функциями

В следующем примере предложение OVER используется с агрегатной функцией для всех возвращаемых запросом строк. В данном примере использование предложения OVER является более эффективным, чем использование вложенных запросов для получения статистических значений.

USE AdventureWorks2022; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg" ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count" ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min" ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max" FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); GO 

SalesOrderID ProductID OrderQty Total Avg Count Min Max ------------ ----------- -------- ----------- ----------- ----------- ------ ------ 43659 776 1 26 2 12 1 6 43659 777 3 26 2 12 1 6 43659 778 1 26 2 12 1 6 43659 771 1 26 2 12 1 6 43659 772 1 26 2 12 1 6 43659 773 2 26 2 12 1 6 43659 774 1 26 2 12 1 6 43659 714 3 26 2 12 1 6 43659 716 1 26 2 12 1 6 43659 709 6 26 2 12 1 6 43659 712 2 26 2 12 1 6 43659 711 4 26 2 12 1 6 43664 772 1 14 1 8 1 4 43664 775 4 14 1 8 1 4 43664 714 1 14 1 8 1 4 43664 716 1 14 1 8 1 4 43664 777 2 14 1 8 1 4 43664 771 3 14 1 8 1 4 43664 773 1 14 1 8 1 4 43664 778 1 14 1 8 1 4 

Следующий пример демонстрирует использование предложения OVER с агрегатной функцией в вычисляемом значении.

USE AdventureWorks2022; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) *100 AS DECIMAL(5,2))AS "Percent by ProductID" FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); GO 

Результирующий набор: Обратите внимание, что агрегаты вычисляются SalesOrderID и Percent by ProductID вычисляются для каждой строки каждой SalesOrderID строки.

SalesOrderID ProductID OrderQty Total Percent by ProductID ------------ ----------- -------- ----------- --------------------------------------- 43659 776 1 26 3.85 43659 777 3 26 11.54 43659 778 1 26 3.85 43659 771 1 26 3.85 43659 772 1 26 3.85 43659 773 2 26 7.69 43659 774 1 26 3.85 43659 714 3 26 11.54 43659 716 1 26 3.85 43659 709 6 26 23.08 43659 712 2 26 7.69 43659 711 4 26 15.38 43664 772 1 14 7.14 43664 775 4 14 28.57 43664 714 1 14 7.14 43664 716 1 14 7.14 43664 777 2 14 14.29 43664 771 3 14 21.4 43664 773 1 14 7.14 43664 778 1 14 7.14 (20 row(s) affected) 

C. Нахождение скользящей средней и кумулятивной суммы

В следующем примере показано использование функций AVG и SUM с предложением OVER для вычисления скользящей средней и кумулятивной суммы годовых продаж по каждой территории, указанной в таблице Sales.SalesPerson . Данные секционируются по TerritoryID и логически сортируются по SalesYTD . Это означает, что функция AVG вычисляется для каждой территории на основании объема продаж за год. Обратите внимание, что в TerritoryID 1 для продаж за 2005 год используются две строки, в которых представлены два менеджера по продажам с показателями за этот год. После расчета среднего значения продаж для двух данных строк в вычисление включается третья строка, представляющая продажи за 2006 год.

USE AdventureWorks2022; GO SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear; 

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal ---------------- ----------- ----------- -------------------- -------------------- -------------------- 274 NULL 2005 559,697.56 559,697.56 559,697.56 287 NULL 2006 519,905.93 539,801.75 1,079,603.50 285 NULL 2007 172,524.45 417,375.98 1,252,127.95 283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07 280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07 284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27 275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18 277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37 276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17 281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17 (10 row(s) affected) 

В этом примере предложение OVER не включает в себя предложение PARTITION BY. Это означает, что функция будет применяться для всех строк, возвращаемых запросом. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции AVG. Запрос возвращает скользящее среднее значение продаж за год для всех территорий, указанных в предложении WHERE. Предложение ORDER BY, указанное в инструкции SELECT, определяет порядок отображения строк запроса.

SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY SalesYear; 

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal ---------------- ----------- ----------- -------------------- -------------------- -------------------- 274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35 275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35 276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35 277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35 280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35 281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35 283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35 284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47 287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47 285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93 (10 row(s) affected) 

D. Указание предложения ROWS

Область применения: SQL Server 2012 (11.x) и более поздних версий.

В следующем примере с помощью предложения ROWS определяется окно, в рамках которого вычисляется текущая строка, а также N последующих строк (1 строка в данном примере).

SELECT BusinessEntityID, TerritoryID ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5; 

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal ---------------- ----------- -------------------- ----------- -------------------- 274 NULL 559,697.56 2005 1,079,603.50 287 NULL 519,905.93 2006 692,430.38 285 NULL 172,524.45 2007 172,524.45 283 1 1,573,012.94 2005 2,925,590.07 280 1 1,352,577.13 2005 2,929,139.33 284 1 1,576,562.20 2006 1,576,562.20 275 2 3,763,178.18 2005 3,763,178.18 277 3 3,189,418.37 2005 3,189,418.37 276 4 4,251,368.55 2005 6,709,904.17 281 4 2,458,535.62 2005 2,458,535.62 

В следующем примере предложение ROWS указывается с UNBOUNDED PRECEDING. В результате окно начинается с первой строки секции.

SELECT BusinessEntityID, TerritoryID ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5; 

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal ---------------- ----------- -------------------- ----------- -------------------- 274 NULL 559,697.56 2005 559,697.56 287 NULL 519,905.93 2006 1,079,603.50 285 NULL 172,524.45 2007 1,252,127.95 283 1 1,573,012.94 2005 1,573,012.94 280 1 1,352,577.13 2005 2,925,590.07 284 1 1,576,562.20 2006 4,502,152.27 275 2 3,763,178.18 2005 3,763,178.18 277 3 3,189,418.37 2005 3,189,418.37 276 4 4,251,368.55 2005 4,251,368.55 281 4 2,458,535.62 2005 6,709,904.17 

Примеры: система платформы аналитики (PDW)

Д. Использование предложения OVER с функцией ROW_NUMBER

В следующем примере возвращается ROW_NUMBER для торговых представителей в зависимости от установленной для них квоты продаж.

-- Uses AdventureWorks SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, FirstName, LastName, CONVERT(VARCHAR(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName; 

Здесь приводится частичный результирующий набор.

RowNumber FirstName LastName SalesQuota --------- --------- ------------------ ------------- 1 Jillian Carson 12,198,000.00 2 Linda Mitchell 11,786,000.00 3 Michael Blythe 11,162,000.00 4 Jae Pak 10,514,000.00 

F. Использование предложения OVER с агрегатными функциями

Следующие примеры демонстрируют использование предложения OVER с агрегатными функциями. В данном примере использование предложения OVER является более эффективным, чем использование вложенных запросов.

-- Uses AdventureWorks SELECT SalesOrderNumber AS OrderNumber, ProductKey, OrderQuantity AS Qty, SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total, AVG(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Avg, COUNT(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Count, MIN(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Min, MAX(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Max FROM dbo.FactResellerSales WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND ProductKey LIKE '2%' ORDER BY SalesOrderNumber,ProductKey; 

OrderNumber Product Qty Total Avg Count Min Max ----------- ------- --- ----- --- ----- --- --- SO43659 218 6 16 3 5 1 6 SO43659 220 4 16 3 5 1 6 SO43659 223 2 16 3 5 1 6 SO43659 229 3 16 3 5 1 6 SO43659 235 1 16 3 5 1 6 SO43664 229 1 2 1 2 1 1 SO43664 235 1 2 1 2 1 1 

Следующий пример демонстрирует использование предложения OVER с агрегатной функцией в вычисляемом значении. Обратите внимание, что статистические выражения вычисляются в столбце SalesOrderNumber , а процент от общего числа заказов на продажу вычисляется для каждой строки каждого SalesOrderNumber .

-- Uses AdventureWorksDW2022 SELECT SalesOrderNumber AS OrderNumber, ProductKey AS Product, OrderQuantity AS Qty, SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total, CAST(1. * OrderQuantity / SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) *100 AS DECIMAL(5,2)) AS PctByProduct FROM dbo.FactResellerSales WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND ProductKey LIKE '2%' ORDER BY SalesOrderNumber,ProductKey; 

Первый запуск этого результирующего набора:

OrderNumber Product Qty Total PctByProduct ----------- ------- --- ----- ------------ SO43659 218 6 16 37.50 SO43659 220 4 16 25.00 SO43659 223 2 16 12.50 SO43659 229 2 16 18.75 

Оконные функции в SQL — что это и зачем они нужны

Краткий гайд, который поможет разобраться в оконных функциях ORDER BY и PARTITION BY.

Многие разработчики, даже давно знакомые с SQL, не понимают оконные функции, считая их какой-то особой магией для избранных. И, хотя реализация оконных функций поддерживается с SQL Server 2005, кто-то до сих пор «копипастит» их со StackOverflow, не вдаваясь в детали. Этой статьёй мы попытаемся развенчать миф о неприступности этой функциональности SQL и покажем несколько примеров работы оконных функций на реальном датасете.

Почему не GROUP BY и не JOIN

Сразу проясним, что оконные функции — это не то же самое, что GROUP BY. Они не уменьшают количество строк, а возвращают столько же значений, сколько получили на вход. Во-вторых, в отличие от GROUP BY, OVER может обращаться к другим строкам. И в-третьих, они могут считать скользящие средние и кумулятивные суммы.

Окей, с GROUP BY разобрались. Но в SQL практически всегда можно пойти несколькими путями. К примеру, может возникнуть желание использовать подзапросы или JOIN. Конечно, JOIN по производительности предпочтительнее подзапросов, а производительность конструкций JOIN и OVER окажется одинаковой. Но OVER даёт больше свободы, чем жёсткий JOIN. Да и объём кода в итоге окажется гораздо меньше.

Для начала

Оконные функции начинаются с оператора OVER и настраиваются с помощью трёх других операторов: PARTITION BY, ORDER BY и ROWS. Про ORDER BY, PARTITION BY и его вспомогательные операторы LAG, LEAD, RANK мы расскажем подробнее.
Все примеры будут основаны на датасете олимпийских медалистов от Datacamp. Таблица называется summer_medals и содержит результаты Олимпиад с 1896 по 2010:

ROW_NUMBER и ORDER BY

Как уже говорилось выше, оператор OVER создаёт оконную функцию. Начнём с простой функции ROW_NUMBER, которая присваивает номер каждой выбранной записи:

SELECT athlete, event, ROW_NUMBER() OVER() AS row_number FROM Summer_Medals ORDER BY row_number ASC; 

Каждая пара «спортсмен — вид спорта» получила номер, причём к этим номерам можно обращаться по имени row_number.
ROW_NUMBER можно объединить с ORDER BY, чтобы определить, в каком порядке строки будут нумероваться. Выберем с помощью DISTINCT все имеющиеся виды спорта и пронумеруем их в алфавитном порядке:

SELECT sport, ROW_NUMBER() OVER(ORDER BY sport ASC) AS Row_N FROM ( SELECT DISTINCT sport FROM Summer_Medals ) AS sports ORDER BY sport ASC; 

PARTITION BY и LAG, LEAD и RANK

PARTITION BY позволяет сгруппировать строки по значению определённого столбца. Это полезно, если данные логически делятся на какие-то категории и нужно что-то сделать с данной строкой с учётом других строк той же группы (скажем, сравнить теннисиста с остальными теннисистами, но не с бегунами или пловцами). Этот оператор работает только с оконными функциями типа LAG, LEAD, RANK и т. д.

LAG

Функция LAG берёт строку и возвращает ту, которая шла перед ней. Например, мы хотим найти всех олимпийских чемпионов по теннису (мужчин и женщин отдельно), начиная с 2004 года, и для каждого из них выяснить, кто был предыдущим чемпионом.
Решение этой задачи требует нескольких шагов. Сначала надо создать табличное выражение, которое сохранит результат запроса «чемпионы по теннису с 2004 года» как временную именованную структуру для дальнейшего анализа. А затем разделить их по полу и выбрать предыдущего чемпиона с помощью LAG:

-- Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы WITH Tennis_Gold AS ( SELECT Athlete, Gender, Year, Country FROM Summer_Medals WHERE Year >= 2004 AND Sport = 'Tennis' AND event = 'Singles' AND Medal = 'Gold') 

-- Оконная функция разделяет по полу и берёт чемпиона из предыдущей строки SELECT Athlete as Champion, Gender, Year, LAG(Athlete) OVER (PARTITION BY gender ORDER BY Year ASC) AS Last_Champion FROM Tennis_Gold ORDER BY Gender ASC, Year ASC; 

Функция PARTITION BY в таблице вернула сначала всех мужчин, потом всех женщин. Для победителей 2008 и 2012 года приведён предыдущий чемпион; так как данные есть только за 3 олимпиады, у чемпионов 2004 года нет предшественников, поэтому в соответствующих полях стоит null.

LEAD

Функция LEAD похожа на LAG, но вместо предыдущей строки возвращает следующую. Можно узнать, кто стал следующим чемпионом после того или иного спортсмена:

-- Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы WITH Tennis_Gold AS ( SELECT Athlete, Gender, Year, Country FROM Summer_Medals WHERE Year >= 2004 AND Sport = 'Tennis' AND event = 'Singles' AND Medal = 'Gold') 

-- Оконная функция разделяет по полу и берёт чемпиона из следующей строки SELECT Athlete as Champion, Gender, Year, LEAD(Athlete) OVER (PARTITION BY gender ORDER BY Year ASC) AS Future_Champion FROM Tennis_Gold ORDER BY Gender ASC, Year ASC; 

RANK

Оператор RANK похож на ROW_NUMBER, но присваивает одинаковые номера строкам с одинаковыми значениями, а «лишние» номера пропускает. Есть также DENSE_RANK, который не пропускает номеров. Звучит запутанно, так что проще показать на примере. Вот ранжирование стран по числу олимпиад, в которых они участвовали, разными операторами:

• Row_number — ничего интересного, строки просто пронумерованы по возрастанию.
• Rank_number — строки ранжированы по возрастанию, но нет номера 3. Вместо этого, 2 строки делят номер 2, а за ними сразу идёт номер 4.
• Dense_rank — то же самое, что и rank_number, но номер 3 не пропущен. Номера идут подряд, но зато никто не оказался пятым из пяти.

-- Табличное выражение выбирает страны и считает годы WITH countries AS ( SELECT Country, COUNT(DISTINCT year) AS participated FROM Summer_Medals WHERE Country in ('GBR', 'DEN', 'FRA', 'ITA','AUT') GROUP BY Country) -- Разные оконные функции ранжируют страны SELECT Country, participated, ROW_NUMBER() OVER(ORDER BY participated DESC) AS Row_Number, RANK() OVER(ORDER BY participated DESC) AS Rank_Number, DENSE_RANK() OVER(ORDER BY participated DESC) AS Dense_Rank FROM countries ORDER BY participated DESC; 

Напоследок

Вот так мы и разложили этот датасет по полочкам при помощи оконных функций. На этом наше введение в оконные функции заканчивается. Надеемся, это было интересно и не так сложно, как могло показаться.

Конечно, это далеко не все возможности оконных функций. Для них есть много других полезных вещей, например ROWS, NTILE и агрегирующие функции (SUM, MAX, MIN и другие), но об этом поговорим в другой раз.

Оконные функции SQL простым языком с примерами

Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений - все написано максимально простым языком для базового понимания.

P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))

Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом

--создание таблицы create table student_grades ( name varchar, subject varchar, grade int); -- наполнение таблицы данными insert into student_grades ( values ('Петя', 'русский', 4), ('Петя', 'физика', 5), ('Петя', 'история', 4), ('Маша', 'математика', 4), ('Маша', 'русский', 3), ('Маша', 'физика', 5), ('Маша', 'история', 3)); --запрос всех данных из таблицы select * from student_grades;

SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.

Оконная функция в SQL - функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.

Партиции (окна из набора строк) - это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.

В чем заключается главное отличие оконных функций от функций агрегации с группировкой?

При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.

При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.

Порядок расчета оконных функций в SQL запросе

Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.

Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING

Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.

После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.

Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.

Синтаксис оконных функций

Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.

Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.

Классы Оконных функций

Множество оконных функций можно разделять на 3 класса:

• Агрегирующие (Aggregate)
• Ранжирующие (Ranking)
• Функции смещения (Value)

Агрегирующие:

Можно применять любую из агрегирующих функций - SUM, AVG, COUNT, MIN, MAX

select name, subject, grade, sum(grade) over (partition by name) as sum_grade, avg(grade) over (partition by name) as avg_grade, count(grade) over (partition by name) as count_grade, min(grade) over (partition by name) as min_grade, max(grade) over (partition by name) as max_grade from student_grades;

Ранжирующие:

В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.

ROW_NUMBER() - функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.

RANK() - функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.

DENSE_RANK() - то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.

select name, subject, grade, row_number() over (partition by name order by grade desc), rank() over (partition by name order by grade desc), dense_rank() over (partition by name order by grade desc) from student_grades;

Про NULL в случае ранжирования:

Для SQL пустые NULL значения будут определяться одинаковым рангом

Функции смещения:

Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.

LAG() - функция, возвращающая предыдущее значение столбца по порядку сортировки.

LEAD() - функция, возвращающая следующее значение столбца по порядку сортировки.

На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.

--создание таблицы create table grades_quartal ( name varchar, quartal varchar, subject varchar, grade int); --наполнение таблицы данными insert into grades_quartal ( values ('Петя', '1 четверть', 'физика', 4), ('Петя', '2 четверть', 'физика', 3), ('Петя', '3 четверть', 'физика', 4), ('Петя', '4 четверть', 'физика', 5) ); --запрос всех данных из таблицы select * from grades_quartal;

select name, quartal, subject, grade, lag(grade) over (order by quartal) as previous_grade, lead(grade) over (order by quartal) as next_grade from grades_quartal;

FIRST_VALUE()/LAST_VALUE() - функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.

В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.

Оконные функции

Фактически мы познакомились с этими функциями, когда рассматривали функции ранжирования. Только сейчас мы будем использовать агрегатные функции вместо функций, которые задают номер/ранг строки. Есть еще одно отличие (в реализации Майкрософт SQL Server 2005/2008) – предложение OVER () не содержит дополнительного предложения ORDER BY , поскольку значение агрегата не зависит от сортировки строк в «окне».

Как и ранее, предложение PARTITION BY определяет «окно», т.е. набор строк, характеризуемых равенством значений списка выражений, указанного в этом предложении. Если предложение PARTITION BY отсутствует, то агрегатные функции применяются ко всему результирующему набору строк запроса. В отличие от классической группировки, где мы получаем на каждую группу одну строку, которая может содержать агрегатные значения, подсчитанные для каждой такой группы, здесь мы можем добавить агрегат к детализированным (несгруппированным) строкам. Рассмотрим несколько примеров использования оконных функций.