SQL LAG 函数

SQL Server 中的 LAG 函数是分析函数之一，它的作用与 LEAD 函数正好相反，用于执行高级数据分析。它允许您在不使用任何自连接 (SELF JOIN) 的情况下访问前一行的数据或值。LAG 函数在将当前行值与其前一行进行比较时非常有用，因此您可以计算两个时期之间的差异或跟踪数据集中的销售趋势等。

LAG 函数返回的值取决于 ORDER BY 子句。ORDER BY 子句决定了数值的排序方式，LAG 函数在相同的顺序下检索前一行。本文将探讨 SQL Server LAG 函数在有或没有 PARTITION BY 子句、默认值和偏移量值的情况下如何使用。

SQL LAG 函数语法

访问前一行的 LAG 函数的基本语法如下所示：

SELECT LAG([Scalar Expression], [Offset_Value], [Default_Value]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

让我们来探讨 SQL Server LAG 函数的参数。

  • 标量表达式 (Scalar Expression)：可以是您要从中检索先前值的列名、表达式或子查询。它返回单个值。
  • 偏移量值 (Offset_Value)（可选）：请定义要从当前行向后移动或查看的行数。默认值为 1，因此 SQL LAG 函数检索前一行值。例如，如果偏移量为 3，它将查看回溯三行，并选择第三个前一行（-3）的值作为结果集。可以使用列、子查询或任何返回单个值的表达式。
  • 默认值 (Default_Value)（可选）：它允许您定义默认值，并接受常量值或表达式。如果先前行值为空 (NULL) 或偏移量值不在范围内，则将返回此默认值。如果忽略它，则返回 NULL。
  • ORDER BY 子句 (Order_By_Clause)：请指定列名以升序或降序对分区数据进行排序。请参阅 SQL Server 中的 ORDER BY 子句。
  • PARTITION BY 子句 (Partition_By_Clause)：它根据给定列将选定的记录结果集分隔成多个分区。例如，按部门名称对员工进行分区。
    • 如果指定了 PARTITION BY 子句，SQL Server LAG 函数将在每个分区内独立工作。它开始选取每个分区中的前一行，并对其他分区执行相同的操作。
    • 如果未指定 PARTITION BY 子句，LAG 函数会将所有表行视为一个分区。

注意：如果标量表达式为 NULL 或其默认值设置为 NULL，则 LAG 函数将输出 NULL。

SQL Server LAG 函数示例

LAG 函数在将当前值与先前值进行比较时非常有用。例如，并排比较客户当前的支出与上次的支出。另一个例子是查找产品今年的销售额与前一时期或前一个月、前一天等的销售额进行比较。

以下示例列表将探讨 SQL LAG 函数检索先前行的功能。它首先从没有默认值开始，然后说明忽略 PARTITION BY 子句会发生什么。接下来是具有默认值和偏移量值的示例。

无默认值的 SQL LAG 函数

下面的查询是 LAG 函数的一个简单基本示例。我们没有使用偏移量、默认值和 PARTITION BY 子句来检索先前的销售额。由于我们没有使用 PARTITION BY，LAG 函数将整个表视为一个分区。

ORDER BY [YearlyIncome] 语句根据员工的年收入升序对员工记录进行排序。接下来，LAG([Sales]) 将返回前一行的先前销售值（即前一行）作为输出。如果没有可返回的行，SQL Server LAG 函数将返回 NULL 值，因为我们没有设置任何默认值。

SELECT [EmpID],[Name],[Education],[Occupation]
      ,[YearlyIncome],[Year],[Sales]
      ,LAG([Sales]) OVER (ORDER BY [YearlyIncome]) AS Out1
  FROM [emp]

可以看到，LAG 函数为第一条记录返回了 NULL 作为输出。这是因为该记录没有前一行（实际上是第一条记录）。Out1 列的第二条记录是 Sales 列的第一行值。

带默认值的 SQL Server LAG 函数

LAG 函数在指定默认值时提供了灵活性，当先前行中没有数据时，将返回该默认值。如果您观察下面的图片，Out1 列的第一行是 NULL。所以，我将偏移量值设置为 1，默认值设置为 0。

SELECT [EmpID],[Name],[Education],[Occupation]
      ,[YearlyIncome],[Year],[Sales]
      ,LAG([Sales], 1, 0) OVER (ORDER BY [YearlyIncome]) AS Out2
  FROM [emp]

您可以看到 LAG 函数已将 Out2 列中的 NULL 值替换为 0。请参阅“LEAD”和“SELF JOIN”文章。

处理 NULL 值

SQL Server LAG 函数后来引入了两个关键字 IGNORE NULLS 和 RESPECT NULLS 来处理给定列中的 null 值。

RESPECT NULLS 是默认的，因此使用此关键字是可选的。无论是否使用此关键字，LAG 函数都将 NULL 视为未定义值并返回 NULL 作为输出。为了演示这一点，请编辑上面的表，并在 sales 列中添加一两个 NULL 值，然后尝试以下代码。

LAG([Sales]) IGNORE NULLS OVER (ORDER BY [Year]) AS SaleCompare

而 IGNORE NULLS 会忽略先前行中的 NULL，并返回最近的非 NULL 值。

LAG([Sales]) RESPECT NULLS OVER (ORDER BY [Year]) AS SaleCompare

带 PARTITION BY 子句的 SQL LAG 函数

如何在分区记录中写出前值是在面试中可能遇到的一个常见问题。PARTITION BY 子句根据指定的列将表分成多个分区。它基本上是对相似的项进行分组。

以下查询按职业 (Occupation) 对 Employee 表数据进行分区，这意味着每种独特的职业类型都是一个单独的分区。ORDER BY [Year] 行使用员工的入职年份对每个分区进行排序。然后，LAG 函数返回每个分区（员工职业）中的先前销售值。当服务器转到后续的职业时，它将重置 LAG 值。

SELECT [Name],[Education],[Occupation]
      ,[YearlyIncome],Year,[Sales]
      ,LAG([Sales], 1, 0) OVER (
        PARTITION BY [Occupation] ORDER BY [Year]
        ) AS Out1
FROM [emp]

您可以看到，对于第 1、5、9 和 13 条记录，LAG 函数被赋值为 0，因为在该分区内没有先前的记录。如果您删除默认值，这些 0 将被替换为 NULL。

注意：您也可以在 PARTITION BY 子句和 ORDER BY 子句中使用多个列。例如，LAG([Sales], 1, 0) OVER ( PARTITION BY Education, Occupation ORDER BY [Year], INCOME ) AS Out1。这有助于通过进一步划分分区来比较大量数据。

带偏移量值的 SQL LAG 函数

在上面的语句中，我们使用了带 PARTITION BY 子句的 LAG 函数，它在每个分区内选择前几行。这是因为默认情况下，LAG 函数返回前一行的值。但是，您也可以指定偏移量值。

让我们看看当我们将 LAG 函数的偏移量值从 1 更改为 2，并将默认值更改为 100 时会发生什么。

SELECT [Name],[Education],[Occupation]
      ,[YearlyIncome],Year,[Sales]
      ,LAG([Sales], 2, 100) OVER (
        PARTITION BY [Occupation] ORDER BY [Year]
        ) AS Out2
FROM [emp]

您可以看到，我们使用了 2 的偏移量，并且该 LAG 函数选择第 2 行（向前 2 行）而不是前一行。将偏移量值修改为 3 将向前回溯三行，并跳过最后两行选择倒数第三行（-3 行）等。

如何查找销售额差异？

除了简单地显示当前销售额与先前销售额的对比之外，您还可以使用 SQL Server LAG 函数查找两个时期（此处为今年和去年）之间的销售额差异。为了实现这一点，您必须将 LAG(Sales) 的输出减去实际销售额。因此，请将以下代码添加到第一个示例中以查看结果。

Sales - LAG([Sales]) OVER (
        PARTITION BY [Occupation] ORDER BY [Year]) AS sales_difference

如何计算年增长率？

我们也可以使用子查询来实现相同的结果。在这里，子查询将使用 LAG 函数查找按职业分组的先前销售额。主查询计算差异。

SELECT e.*, (e.Sales - PreSale) AS Growth
FROM (
    SELECT [Name],[Education],[Occupation]
    ,[YearlyIncome],Year,[Sales], 
    LAG([Sales]) OVER (
        PARTITION BY [Occupation] ORDER BY [Year]) AS PreSale FROM emp
    ) e 
    ORDER BY Occupation, Year

要获得差异百分比，请在 FROM 子句之前添加以下代码。我的意思是，作为第二行。

CONCAT(ROUND(( e.Sales - PreSale) * 100 /PreSale,0),'%')  'Sales%'
SQL Server LAG Function with/without Default Value

将 SQL LAG 函数与 CASE 语句一起使用

除了上面的子查询和下面提到的 CTE 之外，您还可以将 CASE 语句与 LAG 函数一起使用。您可以直接在 CASE WHEN 中使用 LAG 函数，但为了可读性和可重用性，我们从上面的示例中继承了子查询的概念。这里，当当前销售额大于先前销售额时，显示“增加”。否则，显示“减少”。

SELECT e.*, (e.Sales - PreSale) AS Growth,
CASE
WHEN e.Sales > PreSale THEN 'Sales Increase'
WHEN e.Sales < PreSale THEN 'Sales Drop'
ELSE 'No Change'
END AS SalesChange

FROM (
    SELECT [Name],[Education],[Occupation]
    ,[YearlyIncome],Year,[Sales], 
    LAG([Sales]) OVER (
        PARTITION BY [Occupation] ORDER BY [Year]) AS PreSale FROM emp
    ) e 
    ORDER BY Occupation, Year

将 LAG 函数与其他窗口函数结合使用

SQL Server 允许您将 LAG 函数与其他窗口函数和聚合函数结合使用以执行复杂计算。为了演示这一点，我们使用了公共表表达式 (CTE)。

WITH SaleData AS
(
	SELECT Year,
	SUM(Sales) AS Sales,
	LAG(SUM(Sales)) OVER(ORDER BY Year) AS Previous_Sales
	FROM emp
	GROUP BY Year
)
SELECT Year, Sales, Previous_Sales, Sales - Previous_Sales AS SalesDiff,
CONCAT(ROUND(100 * (Sales - Previous_Sales) /  Previous_Sales, 0), '%') AS Sales_Percent
FROM SaleData
ORDER BY Year
SQL LAG Function with PARTITION BY clause and Offset Value

评论已关闭。