Introduction to SQL Server window functions

Brief introduction

After SQL Server 2012, the window functions have been greatly enhanced, but for many developers, the window function is not well understood, resulting in such a powerful function is wasted, so this article mainly discusses the concept of Windows function in SQL Server.

What is a window function

window functions, which can also be called OLAP functions or analytic functions. Understanding the window function begins with an understanding of aggregate functions, and we know the concept of aggregate functions by merging values from a column of multiple rows into one row, such as SUM, AVG, and so on, with a simple concept as shown in Figure 1.

Figure 1. Aggregate functions

Therefore, generally, the number of rows after aggregation is less than the number of rows before the aggregation. For a window function, the input result is equal to the output result, to give a simple example, if you calculate product Type A and product type B,a products are divided into 5 small categories, B products are divided into 2 small classes, the results of the application of the window function can or 7 rows, after the window function has been applied count, attached on each line, for example, " A product, a small class 1, 5 ", while the B small class becomes" B products, B small Class 1, 2 "The last column is the result of the application of the window function.

Now that we have a preliminary overview of the window function, I will provide some concrete examples to make the concept of window function more profound, the window function in addition to the above mentioned input line equals output line, there are the following features and benefits:

Aggregations similar to GROUP by

Non-sequential Access data

You can use analytic functions, aggregate functions, and rank functions for window functions

Simplifies SQL code (eliminates join)

Eliminate intermediate tables

The window function is the last part of the entire SQL statement, which means that the window function is performed on the result set of the SQL query and therefore is not affected by the group BY, having,where clause.

A typical example of a window function is the sort function we used after SQL Server 2005, as shown in Listing 1.

Row_number () (partition by XX xxx desc) rownumber

Code Listing 1. Sort functions available for paging

So, we can abstract the syntax of the window function, as shown in Listing 2.

function () over (PARTITION by column 1, column 2,orderby column 3, window clause) as column alias

Code Listing 2. Syntax for window functions

A simple example

Let's take a look at a simple example, if we want to aggregate the employee table in the AdventureWorks sample database according to sex, for example, I would like to get the result: "Login name, sex, the total number of all employees of this gender", if we use the traditional wording, it must A subquery is involved, as shown in code Listing 3.

Select [Loginid],gender,
    
(select COUNT (*) from [adventureworks2012].[ HumanResources]. [Employee] a WHERE a.gender=b.gender) as 

gendertotal from
    
  [adventureworks2012].[ HumanResources]. [Employee] B