Over Clause:
Determines the partitioning and ordering of a rowset before the associated
window function is applied.
That is, the OVER clause defines a window or user-specified set of rows
within a query result set. A window function then computes a value for each row
in the window.
You can use the OVER clause with functions to compute aggregated values
such as moving averages, cumulative
aggregates, running totals, or a top N per group results.
OVER (
[ <PARTITION
BY clause> ]
[ <ORDER BY
clause> ]
[
<ROW or RANGE clause> ]
)<PARTITION BY clause>
::=
PARTITION BY
value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[
COLLATE collation_name ]
[ ASC
| DESC ]
[
,...n ]
<ROW or RANGE clause>
::=
{ ROWS | RANGE } <window
frame extent>
<window frame extent>
::=
{ <window frame preceding>
| <window frame between>
}
<window frame between>
::=
BETWEEN <window frame bound> AND
<window frame bound>
<window frame bound>
::=
{ <window frame preceding>
| <window frame following>
}
<window frame
preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification>
PRECEDING
| CURRENT ROW
}
<window frame
following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification>
FOLLOWING
| CURRENT ROW
}
<unsigned value
specification> ::=
{ <unsigned integer literal> }
select * from test order by id
id cust_number
sal
----------- ------------- ---------------------
10 150000 15000.00
10 10 15000.00
30 150000 35000.00
30 30 35000.00
60 150000 10000.00
(5 row(s) affected)
PARTITION BY:
Divides the query result set into
partitions. The window function is applied to each partition separately and
computation restarts for each partition.
select ID,cust_number,ROW_NUMBER() over(partition by id order by cust_number)rtn from test
ID cust_number rtn
----------- -------------
--------------------
10 10 1
10 150000
2
30 30 1
30 150000 2
60 150000 1
(5 row(s) affected)
value_expression
Specifies the column by which the rowset
is partitioned.
value_expression can only refer to
columns made available by the FROM clause.
value_expression cannot refer to expressions or aliases in the select list.
value_expression can be a column
expression, scalar subquery, scalar function, or user-defined variable.
select ID ,cust_number,ROW_NUMBER() over(partition by 10 order by cust_number)rtn from test
ID cust_number rtn
----------- -------------
--------------------
10 10 1
30 30 2
10 150000 3
30 150000 4
60 150000 5
(5 row(s) affected)
declare
@var int = 10
select ID,cust_number,ROW_NUMBER() over(partition by @var order by cust_number)rtn from test
ID cust_number rtn
----------- -------------
--------------------
10 10 1
30 30 2
10 150000 3
30 150000 4
60 150000 5
(5 row(s) affected)
select ID as alias,cust_number,ROW_NUMBER() over(partition by alias order by cust_number)rtn from test
Msg
207, Level 16, State 1, Line 1
Invalid
column name 'alias'.
<ORDER BY clause>
Defines the logical order of the rows within each partition of the result
set. That is, it specifies the logical order in which the window function calculation
is performed.
Order_by_expression
Specifies a column or expression on which to sort. order_by_expression can
only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.
select ID ,cust_number,ROW_NUMBER() over(partition by cust_number
order by 1)rtn from test
Msg 5308, Level 16, State
1, Line 1
Windowed functions do not
support integer indices as ORDER BY clause expressions.
COLLATE collation_name
Specifies that the ORDER BY operation should be performed according to the
collation specified in collation_name. collation_name can be either a Windows
collation name or a SQL collation name. COLLATE is applicable only for columns
of type char, varchar, nchar,
and nvarchar.
ASC | DESC
Specifies that the values in the specified column should be sorted in
ascending or descending order.
ASC is the default sort order.
Null values are treated as
the lowest possible values.
ROWS | RANGE
Applies to: SQL Server 2012 through SQL Server 2014.
Further limits the rows
within the partition by specifying start and end points within the partition.
This is done by specifying a range of rows with respect to the current row
either by logical association or physical association. Physical association is achieved by using the ROWS clause.
The ROWS clause limits the
rows within a partition by specifying a fixed number of rows preceding or
following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by
specifying a range of values with respect to the value in the current row.
Preceding and following rows
are defined based on the ordering in the ORDER BY clause.
The window frame “RANGE …
CURRENT ROW …” includes all rows that have the same values in the ORDER BY
expression as the current row.
For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
means that the window of rows that the function operates on is three rows in
size, starting with 2 rows preceding until and including the current row.
Note:
ROWS or RANGE requires that the ORDER BY
clause be specified. If ORDER BY contains multiple order expressions, CURRENT
ROW FOR RANGE considers all columns in the ORDER BY list when determining the
current row.
UNBOUNDED PRECEDING
Applies to: SQL Server 2012 through SQL Server 2016.
Specifies that the window
starts at the first row of the partition. UNBOUNDED PRECEDING can only be
specified as window starting point.
<unsigned value
specification> PRECEDING
Specified with <unsigned
value specification>to indicate the number of rows or values to precede the
current row. This specification is not
allowed for RANGE.
CURRENT ROW
Applies to: SQL Server 2012
through SQL Server 2016.
Specifies that the window
starts or ends at the current row when used with ROWS or the current value when
used with RANGE. CURRENT ROW can be specified as both a starting and ending
point.
BETWEEN <window frame
bound > AND <window frame bound >
Used with either ROWS or
RANGE to specify the lower (starting) and upper (ending) boundary points of the
window. <window frame bound> defines the boundary starting point and
<window frame bound> defines the boundary end point. The upper bound
cannot be smaller than the lower bound.
UNBOUNDED FOLLOWING
Applies to: SQL Server 2012
through SQL Server 2016.
Specifies that the window
ends at the last row of the partition. UNBOUNDED FOLLOWING can only be
specified as a window end point.
For example RANGE BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current
row and ends with the last row of the partition.
<unsigned value specification> FOLLOWING
Specified with <unsigned
value specification> to indicate the number of rows or values to follow the
current row. When <unsigned value specification> FOLLOWING is specified
as the window starting point, the ending point must be <unsigned value
specification>FOLLOWING.
For example, ROWS BETWEEN 2
FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row
that follows the current row and ends with the tenth row that follows the
current row. This specification is not
allowed for RANGE.
unsigned integer literal
Applies to: SQL Server 2012
through SQL Server 2016.
Is a positive integer literal
(including 0) that specifies the number of rows or values to precede or follow
the current row or value. This specification is valid only for ROWS.
More than one window function can be
used in a single query with a single FROM clause. The OVER clause for each
function can differ in partitioning and ordering.
If PARTITION BY is not specified, the
function treats all rows of the query result set as a single group.
Important!
If ROWS/RANGE is specified and
<window frame preceding> is used for <window frame extent> (short
syntax) then this specification is used for the window frame boundary starting
point and CURRENT ROW is used for the boundary ending point.
For example “ROWS 5 PRECEDING” is equal
to “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.
Note:
If ORDER BY is not specified entire partition is used for a window frame.
This applies only to functions that do not require ORDER BY clause. If
ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED
PRECEDING AND CURRENT ROW is used as default for window frame. This applies only
to functions that have can accept optional ROWS/RANGE specification. For
example, ranking functions cannot accept ROWS/RANGE, therefore this window
frame is not applied even though ORDER BY is present and ROWS/RANGE is not.
Limitations and
Restrictions
The OVER clause cannot be
used with the CHECKSUM aggregate function.
RANGE cannot be used with
<unsigned value specification> PRECEDING or <unsigned value
specification> FOLLOWING.
Depending on the ranking,
aggregate, or analytic function used with the OVER clause, <ORDER BY
clause> and/or the <ROWS and RANGE clause> may not be supported.
No comments:
Post a Comment