SQL:Last_Value() 返回错误的结果(但 First_Value() 工作正常) [英] SQL: Last_Value() returns wrong result (but First_Value() works fine)

问题描述

如快照所示，我在 SQL Server 2012 中有一个表:

I have a table in SQL Server 2012 as the snapshot shows:

然后我使用 Last_Value() 和 First Value 来获取不同 YearMonth 的每个 EmpID 的 AverageAmount.脚本如下:

Then I'm using Last_Value() and First Value to get AverageAmount of each EmpID for different YearMonth. The script is as follows:

SELECT A.EmpID,  
       First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS  '200901AvgAmount', 
       Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '201112AvgAmount'

FROM  Emp_Amt  AS A

然而，这个查询的结果是:

However, the result for this query is:

在201112AvgAmount"列中，每个EmpID显示不同的值，而200901AvgAmount"有正确的值.

In column of "201112AvgAmount", it shows different values for each EmpID while "200901AvgAmount" has correct values.

我的 SQL 脚本有什么问题吗?我在网上做了很多研究，但仍然找不到答案......

Is there anything wrong with my SQL script? I did a lot research online but still cannot find the answer....

推荐答案

您的脚本没有任何问题，这是 SQL Server 中分区的一种工作方式:/.如果您将 LAST_VALUE 更改为 MAX，结果将相同.解决方案是:

There is nothing wrong with your script, this is a way how partitioning works in SQL server :/. If you change LAST_VALUE to MAX result will be the same. Solution would be:

SELECT A.EmpID,  
       First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS  '200901AvgAmount', 
       Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '201112AvgAmount'  
FROM  Emp_Amt  AS A

有一篇关于它的好文章，链接.GL！

There is a great post about it, link. GL!

这篇关于SQL:Last_Value() 返回错误的结果(但 First_Value() 工作正常)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！