SQL windowing for SQL Server Window function form functions

The SQL Server form function is primarily used to handle the rowset defined by the over clause, primarily for parsing and processing

Running totals

Moving averages

Gaps and Islands

Let's look at a simple application-rank the order form information by order from highest to lowest

Use TSQL2012
go
    
SELECT OrderID,
       OrderDate,
       Val,
       RANK () over (order by Val DESC) as Rnk
Sales.ordervalues ORDER BY
RNK

Query Results-

OrderID OrderDate Val RNK

10865 2008-02-02 00:00:00.000 16387.50 1

10981 2008-03-27 00:00:00.000 15810.00 2

11030 2008-04-17 00:00:00.000 12615.05 3

10889 2008-02-16 00:00:00.000 11380.00 4

10417 2007-01-16 00:00:00.000 11188.40 5

10817 2008-01-06 00:00:00.000 10952.85 6

10897 2008-02-19 00:00:00.000 10835.24 7

10479 2007-03-19 00:00:00.000 10495.60 8

Over the role of

The effect of over is to define the rowset form, which provides a context for the current row. The Rank function calculates the rank of the current row based on the specified set and the sort order of the rowset, taking RNK = 5 as an example, with 4 data in front of the sorted data, so its ranking is 4 + 1 = 5

To sum it up, over defines a set of rows that is a function that returns a unique value each time it is returned to the current row. In this example, the Rank function is used to return a ranking of the current row.

Other functions used in the over-over collocation

Aggregate functions-SUM, COUNT, MIN, MAX

Rank function-Rank, Dense_rank, Row_number, Ntile

Distribution Function-Percent_rank, cume_dist, Percentile_cont, Percentile_disc

Offset function-LAG, lead, First_value, Last_value

Application of SQL Server Window Function

The application of form functions is very extensive-like paging, going heavy, grouping on the basis of returning top N rows, computing Running totals,gaps and Islands, percentages, hierarchy sort, pivoting, etc.

The reason why you use Windows forms functions is that the optimizer for SQL Server is not perfect, and, while strong enough, it does not cover all the tuning rules.

Second, SQL Server does not actually execute all possible plans to get an optimal choice on the execution plan, and for SQL itself, the parsing and optimization optimizer for a prescriptive language can only say that it is as good as possible in the shortest amount of time and chooses a good execution plan. The window form function itself has undergone a good tuning process, so the performance will be much better.