SQL Server window frame--rows, RANGE

When it comes to window frames, you have to mention the open window function.

The open window function supports partitioning, sorting, and framing three elements in the following syntax format:

1  Over (   2        []  3         [   ]   4        []  5       )  

Window Partitioning:

is to partition the rows that have the same value for the specified column of the window, and the partition is similar to the grouping, but the group specifies that the entire SELECT statement can only follow this grouping, but the partition can specify a different partition in one statement.

1 <  by clause> ::=  2by[]  

Window Sort:

After partitioning can be specified, the logical order of the rows for each window is determined before the window is evaluated.

1 < ORDER  by clause> ::=  2ORDER by order_by_expression   3     []   4     []   5      []  

Window frame:

The framework is a further partitioning of the window, and the framework has two scoping methods: one is to use the rows clause to limit the number of rows in the partition by specifying a fixed number of rows before or after the current row, and the range clause to determine the number of rows in the partition according to the current value of the sorted column, based on the same value.

1 <ROWorRANGE clause>::=  2{ROWS|RANGE}<Window frame Extent>  3 4 <Window frame Extent>::=   5{<Window frame preceding>  6   | <Window framebetween>  7 }  8 <Window framebetween>::=   9   between <Window frame bound>  and <Window frame bound>  Ten  One <Window frame bound>::=    A{<Window frame preceding>   -   | <Window frame following>   - }   the  - <Window frame preceding>::=    - {   - unbounded preceding +   | <Unsigned_value_specification>preceding -   |  CurrentROW + }   A  at <Window frame following>::=    - {   - unbounded following -   | <Unsigned_value_specification>following -   |  CurrentROW - }   in  - <Unsigned value specification>::=    to{<UnsignedintegerLiteral>}

When you use a frame, you must have an ORDER BY clause, and if you specify only the ORDER BY clause without a frame, the default framework takes the RANGE unbounded preceding and current ROW.

If the window function does not specify an ORDER BY clause, there is no calculation for the Rows/range window.

If the Rows/range clause uses <window frame preceding>, then current row is the default end line for the frame, for example: "Rows 5 preceding" equivalent to "rows between 5 Precedi NG and current ROW ".

Ps:range only supports the use of the unbounded and current ROW window frame separators.

Examples of partitions that are not specified by using partition by:

1 Select *,2 sum(u_id) Over(Order  byu_id) Column 1,3 sum(u_id) Over(Order  byu_id RANGEbetweenunbounded preceding and  Currentrow) column 2,4 sum(u_id) Over(Order  byu_id rowsbetweenunbounded preceding and  Currentrow) Column 3,5 sum(u_id) Over(Order  byu_id rowsbetween 1Preceding and 2following) column 4,6 sum(u_id) Over(Order  byu_id ROWSbetween 1Preceding and  Currentrow) Column 57  fromUserInfo

Results Analysis:

Range is a logical window that specifies the range of values for the current row and the number of columns is not fixed, as long as the row values are within range and the corresponding columns are included.

Rows is a physical window, that is, the data for the first n rows and the last n rows are computed after ordering by the ORDER BY clause. (independent of the value of the current row, only related to the sorted line number)

Column 1 does not specify a window, so the default is RANGE unbounded preceding and current row, so it is the same as the column 2 value.

The "Column 2" range between unbounded preceding and the current row represent values that specify all rows that have a range that precedes the current row.

That is, the value of the first row is: 1 The value of the second row is: 3+1 The value of the third row is: 4+3+1

"Column 3" rows between unbounded preceding and current row represent values that specify values for all the rows that precede the current row.

That is, the value of the first row is: 1 The value of the second row is: 3+1 The value of the third row is: 4+3+1

"Column 4" rows between 1 preceding and 2 following indicate a value that specifies the range of values for the current row and the previous row and the last two rows.

That is, the value of the first row is: 1+3+4 The value of the second row is: 1+3+4+5 The value of the third row is: 3+4+5+6

"Column 5" rows between 1 preceding and the current row represent a value that specifies the range of values to the previous row.

That is, the value of the first row is: 1 The value of the second row is: 1+3 The value of the third row is: 3+4

Use partition by to specify the partitioning example:

1 Select *,2 sum(u_id) Over(Partition byU_pwdOrder  byu_id) Column 1,3 sum(u_id) Over(Partition byU_pwdOrder  byu_id RANGEbetweenunbounded preceding and  Currentrow) column 2,4 sum(u_id) Over(Partition byU_pwdOrder  byu_id rowsbetweenunbounded preceding and  Currentrow) Column 3,5 sum(u_id) Over(Partition byU_pwdOrder  byu_id rowsbetween 1Preceding and 2following) column 4,6 sum(u_id) Over(Partition byU_pwdOrder  byu_id ROWSbetween 1Preceding and  Currentrow) Column 57  fromUserInfo

The above columns (U_PWD) are divided into 3 zones, and using partition by specifies that the partition is first partitioned and then calculated based on the specified window and the specified window range.

Reference:

Https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

SQL Server window frame--rows, RANGE