sql 自定义函数 示例_SQL滞后函数概述和示例

sql 自定义函数 示例

In the article SQL Server Lead function overview and examples, we explored Lead function for performing computational operations on data. This article gives an overview of the SQL Lag function and its comparison with the SQL Lead function.

在“ SQL Server Lead函数概述和示例”一文中 ，我们探讨了Lead函数用于对数据执行计算操作。 本文概述了SQL滞后函数及其与SQL Lead函数的比较。

SQL延迟功能概述 (Overview of SQL Lag function)

We use a Lag() function to access previous rows data as per defined offset value. It is a window function available from SQL Server 2012 onwards. It works similar to a Lead function. In the lead function, we access subsequent rows, but in lag function, we access previous rows. It is a useful function in comparing the current row value from the previous row value.

我们使用Lag（）函数根据定义的偏移值访问先前的行数据。 从SQL Server 2012开始，它是一个窗口函数。 它的工作方式类似于Lead功能。 在Lead函数中，我们访问后续行，而在lag函数中，我们访问先前行。 在比较当前行值和前一行值时，此功能很有用。

滞后函数的语法 (Syntax of Lag function)

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

It uses following arguments.

它使用以下参数。

• Scalar_expression: We define a column name or expression in this argument. The lag function does calculations on this column. It is a mandatory argument, and we cannot execute the lag function without this
  Scalar_expression：我们在此参数中定义列名称或表达式。 滞后函数在此列上进行计算。 这是一个强制性参数，没有这个我们就无法执行lag函数
• Offset: We define an integer number in this argument. The lag function uses this argument forgo behind the number of rows (offset). The default value for this argument is one. It is an optional argument
  偏移：我们在此参数中定义一个整数。 滞后函数在行数（偏移量）后面使用此参数。 此参数的默认值为1。 这是一个可选参数
• Default: Suppose we define an offset, value that does not lie in the boundary of the data. For example, we specified offset value 3 for the first row. A lag function cannot go three rows behind. It displays the default value if specified. If we do not specify any value for this, the lag function displays NULL in the output for out of range values
  默认值：假设我们定义了一个偏移量，该值不位于数据边界内。 例如，我们为第一行指定了偏移值3。 滞后功能不能落后三行。 如果指定，它将显示默认值。 如果我们没有为此指定任何值，则滞后函数在输出中将NULL显示为超出范围的值
• PARTITION BY: It creates a logical boundary of data. Suppose we have an extensive data set and we require calculations on a smaller set of data, we can define partitions for it. For example, sales data for an organization might contain data for several years. We can create a partition quarterly and do the computation. It is as well an optional argument
  PARTITION BY：它创建数据的逻辑边界。 假设我们有一个广泛的数据集，并且需要对较小的数据集进行计算，则可以为其定义分区。 例如，组织的销售数据可能包含几年的数据。 我们可以每季度创建一个分区并进行计算。 它也是一个可选参数
• ORDER BY: We can sort data in ascending or descending order using ORDER by clause. By default, it uses ascending order to sort data
  ORDER BY：我们可以使用ORDER by子句对数据进行升序或降序排序。 默认情况下，它使用升序对数据进行排序

We will use data from the previous article for demonstration of SQL Server Lag function as well:

我们还将使用上一篇文章中的数据来演示SQL Server Lag函数：

DECLARE   @Employee TABLE
  (
       EmpCode VARCHAR(10),
       EmpName   VARCHAR(10),
       JoiningDate  DATE
    )
INSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018')
INSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018')
INSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018')
INSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018')
INSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018')
INSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018')
GO
SELECT * FROM   @Employee;

We have the following data in the Employee table:

我们在Employee表中有以下数据：

示例1：不带默认值SQL Lag函数 (Example 1: SQL Lag function without a default value)

Execute the following query to use the Lag function on the JoiningDate column with offset one. We did not specify any default value in this query.

执行以下查询以使用偏移量为1的JoiningDate列上的滞后函数。 我们没有在该查询中指定任何默认值。

Execute the following query (we require to run the complete query along with defining a variable, its value):

执行以下查询（我们需要运行完整的查询以及定义变量及其值）：

SELECT *, 
       Lag(JoiningDate, 1) OVER(.
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

In the output, we can note the following:

在输出中，我们可以注意到以下内容：

• The first row shows NULL value for the EndDate column because it does not have any previous rows
  第一行显示EndDate列的NULL值，因为它没有任何先前的行
• The second row contains previous row value in the EndDate column. It takes value from the previous row due to offset value 1
  第二行包含EndDate列中的上一行值。 由于偏移值1，它从上一行获取值

示例2：具有默认值SQL Lag函数 (Example 2: SQL Lag function with a default value)

In the previous example, we get NULL value as a default value. Let’s use a default end date in the lag function. This example also uses offset value 1 in the lag function:

在前面的示例中，我们获得NULL值作为默认值。 让我们在lag函数中使用默认的结束日期。 此示例还在lag函数中使用偏移值1：

SELECT *, 
       Lag(JoiningDate, 1,'1999-09-01') OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

In the output, we can see a default value instead of NULL in the first row:

在输出中，我们可以在第一行看到默认值而不是NULL：

We can use compatible data types in the default value column. If we use incompatible data types, we get the following error message:

我们可以在默认值列中使用兼容的数据类型。 如果我们使用不兼容的数据类型，则会收到以下错误消息：

示例3：偏移值为2SQL滞后函数 (Example 3: SQL Lag function with OFFSET value 2)

Previously, we used default offset 1 in Lag function, and it takes value from the previous row. In the example, we use offset value 2. In the output, you can see we have a default value for row 1 and 2. In row 3, it takes value from row 1:

以前，我们在Lag函数中使用默认偏移量1，它从上一行获取值。 在示例中，我们使用偏移值2。在输出中，您可以看到我们为第1行和第2行提供了默认值。在第3行中，它从第1行获取值：

SELECT *, 
       Lag(JoiningDate, 2,'1999-09-01') OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

示例4：带有PARTITION BY子句SQL滞后函数 (Example 4: SQL Lag function with PARTITION BY clause)

As discussed earlier, we use the PARTITION BY clause to create a logical subset of data. Let’s use this PARTITION function on the ProductSales table. You can refer to the SQL Server Lead function to create this table:

如前所述，我们使用PARTITION BY子句创建数据的逻辑子集。 让我们在ProductSales表上使用此PARTITION函数。 您可以参考SQL Server Lead函数来创建此表：

In the following query, we use SQL Server Lag function and view the output:

在以下查询中，我们使用SQL Server滞后函数并查看输出：

SELECT [Year], 
       [Quarter], 
       Sales, 
       LAG(Sales, 1, 0) OVER(
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

In the output, the lag function considers all rows as a single data set and applies Lag function:

在输出中，lag函数将所有行视为一个数据集，并应用Lag函数：

In the ProductSales table, we have data for the years of 2017, 2018 and 2019. We want to use a lag function on a yearly basis. We use the PARTITION BY clause on the Year column and define the logical subset of data on a yearly basis. We use the Order by clause on year and quarter columns to sort data first on a yearly basis and then monthly:

在ProductSales表中，我们具有2017年，2018年和2019年的数据。我们希望每年使用滞后函数。 我们在Year列上使用PARTITION BY子句，并每年定义数据的逻辑子集。 我们使用“年”和“季度”列上的“排序依据”子句对数据进行逐年排序，然后按月排序：

SELECT [Year], 
       [Quarter], 
       Sales, 
       LAG(Sales, 1, 0) OVER(PARTITION BY [Year]
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

In the following screenshot, we can see three partitions of data for 2017,2018 and 2019 year. The Lag function individually works on each partition and calculates the required data:

在以下屏幕截图中，我们可以看到2017、2018和2019年的三个数据分区。 滞后函数分别在每个分区上工作并计算所需的数据：

结论 (Conclusion)

In this article, we learned the SQL Lag function and its usage to retrieve a value from previous rows. Here is the quick summary of the lag function:

在本文中，我们学习了SQL Lag函数及其在前几行中检索值的用法。 这是滞后函数的快速摘要：

• Lag function fetches the value from the previous rows based on the offset defined
  滞后函数根据定义的偏移量从前几行获取值
• Offset one is the default offset value, and in this Lag, the function retrieves a value from the previous row
  偏移量一是默认偏移量值，在此延迟中，函数从上一行检索一个值
• PARTITION BY clause defines a logical boundary of data based on the specified condition
  PARTITION BY子句根据指定条件定义数据的逻辑边界
• The lag function uses default value NULL for out-of-range data
  滞后函数对超出范围的数据使用默认值NULL
• We can use the Lag function with common table expression, stored procedures, and functions for computation purposes
  我们可以将Lag函数与公用表表达式，存储过程和函数一起使用，以进行计算

翻译自: https://www.sqlshack.com/sql-lag-function-overview-and-examples/

sql 自定义函数 示例