Shulou(Shulou.com)05/31 Report--

This article shows you the usage of SQL Server high-level functions, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Starting from the 2012 version, SQL Server introduces LEAD and LAG functions, which can change the previous method of associating the query to directly obtain the number of adjacent rows of the current data, and can easily add, subtract, multiply and divide the data of the upper and lower adjacent rows. Today we will introduce to you the usage of these two functions.

LAG function

The role of LAG

LAG provides access to rows at a given physical offset before the current row. Use this parsing function in a SELECT statement to compare the values in the current row with those in the previous row.

Syntax of LAG

LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)

Parameter interpretation

Scalar_expression

The value to be returned based on the specified offset. This is an expression of any type that returns a single (scalar) value. Scalar_expression cannot be an analysis function.

Offset

The number of rows after the current row (from which a value is obtained). If not specified, the default value is 1.

OVER

To be a windowing function, the LAG function must be used with the windowing function.

LAG example

WITH T AS (SELECT 1 ID,10 NUM UNION ALL SELECT 1, 20 UNION ALL SELECT 1, 30 UNION ALL SELECT 2, 40 UNION ALL SELECT 2, 50 UNION ALL SELECT 2, 60) SELECT ID,NUM, LAG (NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs, LAG (NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs, LAG (NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs FROM T

(hint: you can move the code left and right)

The results are as follows:

From the example above, we can see

1. For column OneArgs, the value of the NUM column in the group is offset one row by default, and the first row of each group is replaced by the default NULL.

2. For TowArgs, the offset row displayed by two parameters is used, and the value of NUM is also offset one line backward.

3. For ThreeArgs, not only the displayed offset of 2 lines is used, but also the third parameter changes the default value NULL after offset to 0

Practical example: how to solve the sum of the upper and lower lines in a group?

SELECT ID,NUM, NUM+LAG (NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM) AS Result FROM T

The results are as follows:

Note: since the first line defaults to 0, the result of the first row of each group is NUM+0=NUM

LEAD function

The LEAD function is just the opposite of the LAG function, which offsets the specified number of lines forward, and the default is 1 line.

The syntax ha parameter is similar to LAG, so I won't repeat it here. Let's look directly at the example:

SELECT ID,NUM, LEAD (NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs, LEAD (NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs, LEAD (NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs FROM T

Results:

The usage is similar to the LAG function, except that the data in the group is offset by a specified number of rows respectively.

Practical example: solve the difference between the upper and lower lines in the same group?

SELECT ID,NUM, LEAD (NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM)-NUM AS Result FROM T

Results:

The last row of each group defaults to 0, so 0-NUM=-NUM

What are the above contents of the use of SQL Server advanced functions? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.