sql - 为什么 LAST_VALUE() 在 SQL Server 中不起作用？

Question

这是我拥有的数据(注意，这只是针对一个实体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 返回一行。

我做错了什么？

Answer 1

尝试添加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