为什么在SQL Server中LAST_VALUE()函数无法正常工作？

Question

为什么在SQL Server中LAST_VALUE()函数无法正常工作？

9

以下是我拥有的数据（请注意，这仅适用于一个实体ID/员工ID，可能会有多个。一个实体ID可以拥有多个员工ID）：

SELECT  EntityId,
        EmployeeId,
        PayPeriodStart,
        IsFullTime
FROM    dbo.Payroll
WHERE   EmployeeId = 316691
        AND PayPeriodStart <= '12/31/2014'
        AND PayPeriodEnd >= '1/1/2014';

我想获取每个EntityID和EmployeeID组合中的最后一个“IsFullTime”值。

我尝试了以下方法：

SELECT  EntityId,
        EmployeeId,
        LAST_VALUE(IsFullTime) OVER (PARTITION BY EntityId, EmployeeId ORDER BY EntityId, EmployeeId, PayPeriodStart) AS LastIsFullTimeValue
FROM    dbo.Payroll
WHERE   EmployeeId = 316691
        AND PayPeriodStart <= '12/31/2014'
        AND PayPeriodEnd >= '1/1/2014';

但是我得到了这个：

查询应该只返回每个EntityID / EmployeeID的一行。

我做错了什么？

- JJ.

这个运行的是哪个版本的SQL Server？执行 SELECT @@VERSION 的输出是什么？LAST_VALUE 是 SQL Server 2012 中的新功能，不支持早期版本。 - marc_s

如果您想要“每个EntityID / EmployeeID仅有一行”，则需要使用GROUP BY。 - mustaccio

在一个子查询中，您需要使用distinct，group by或row_number()。 - Hogan

4个回答

18

这里有一个技巧。我使用 FIRST_VALUE + DESC 排序（而不是 LAST_VALUE + ASC 排序）。有效果。这是最简短的方法，它能避免你恐惧的 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

FIRST_VALUE(IsFullTime) OVER (
    PARTITION BY EntityId, EmployeeId
    ORDER BY EntityId DESC, EmployeeId DESC, PayPeriodStart DESC
) AS LastIsFullTimeValue

同意，不明白为什么LAST_VALUE不够直观，需要比其姊妹函数FIRST_VALUE更多的注意力。

如果您希望关注性能，请阅读：https://sqlperformance.com/2019/08/sql-performance/t-sql-bugs-pitfalls-and-best-practices-window-functions

- Przemyslaw Remin

2

谢谢，对我来说完美无缺！但是我不明白为什么last_value没有起作用。 - Dmitry Gusarov

避免使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING有什么好处？这只是语法问题还是会影响查询性能？ - jakubiszon

我过去几天一直在练习FIRST_VALUE和LAST_VALUE，后者让我感到非常困惑。就像你所指出的，FIRST_VALUE相当直观和简单，但是LAST_VALUE却非常不直观，再加上无界前置和无界后置的陌生语言，让我更加困惑。现在我认为我明白了LAST_VALUE的工作原理。谢谢！ - Stack0verflow

5

要了解发生了什么，您必须理解框架的概念。帧允许您指定一个比分区更小的窗口行集。默认框架包含从第一行开始到当前行的行。对于第1行，窗口只是第1行。对于第3行，窗口包含1到3行。使用FIRST_VALUE时，默认情况下包括第一行，因此您不必担心它以获得预期结果。

当使用LAST_VALUE和默认框架时，窗口仅到达当前行。当前行是窗口的最后一行。为了解决这个问题，您必须指定框架，在这种情况下为“ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”。这意味着窗口从当前行开始，并在分区的最后一行结束。运行下一个示例以查看如何正确使用LAST_VALUE。

查看http://www.sqlservercentral.com/articles/T-SQL/124112/

- Luis Fulco

谢谢你对 LAST_VALUE 的令人困惑的行为进行了澄清，我已经为此苦苦挣扎了好几天。 - Stack0verflow

4

我相信您想使用ROW_NUMBER()，并根据payperiodstart日期获取最后一个值：

SELECT t.EntityId
    ,t.EmployeeId
    ,t.LastIsFullTimeValue
FROM (
    SELECT EntityId
        ,EmployeeId
        ,ROW_NUMBER() OVER (
            PARTITION BY EntityId
            ,EmployeeId ORDER BY PayPeriodStart DESC
            ) AS rn
        ,LastIsFullTimeValue
    FROM dbo.Payroll
    WHERE EmployeeId = 316691   -- you could probably put this in your outer query instead
        AND PayPeriodStart <= '12/31/2014'
        AND PayPeriodEnd >= '1/1/2014'
    ) t
WHERE t.rn = 1;

- FutbolFan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- shawnt00 · Accepted Answer

尝试添加 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

我认为包括 ORDER BY 的分析函数的默认窗口在当前行结束。

LAST_VALUE(IsFullTime) OVER (
    PARTITION BY EntityId, EmployeeId
    ORDER BY EntityId, EmployeeId, PayPeriodStart
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LastIsFullTimeValue