在Server 2012中使用FIRST_VALUE()时的意外结果

Question

当我在手工构造的数据集上使用FIRST_VALUE时，我得到了一个结果，而当我将它用于一个由左联接产生的数据集时，我得到了一个不同的结果--尽管在我看来，这些数据集包含了完全相同的数据值。下面我用一个简单的数据来重现这个问题。

有人能告诉我我是否误解了什么吗？

此SQL生成预期的结果，即FIRST_VALUE为NULL，LAST_VALUE为30。

SELECT
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 1 agroup, 10 aval
  UNION ALL SELECT 1, NULL
  UNION ALL SELECT 1, 30
) T

此SQL使用左联接，其结果与上面的数据集相同，但FIRST_VALUE似乎忽略了NULL。

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
) T

我还可以显示，当使用表变量和CTE时，左联接行为是不同的。当使用CTE生成数据时，FIRST_VALUE会忽略NULL。使用完全相同的SQL，但将结果放在表变量或临时表中，则会考虑NULL。

对于CTE，Server结果在FIRST_VALUE确定中不包括NULL：

WITH T AS
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
)

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
 T

但是对于一个表变量，它是这样做的：

DECLARE @T TABLE (agroup INT,akey INT,aval INT)

INSERT INTO
  @T
SELECT 
  T1.agroup,
  T1.akey,
  T2.aval 
FROM 
(
  SELECT 1 agroup, 1 akey
  UNION ALL SELECT 1, 2
  UNION ALL SELECT 1, 3
) T1
LEFT JOIN
(
  SELECT 1 akey, 10 aval
  UNION ALL SELECT 3,30
) T2 ON T1.akey = T2.akey


SELECT 
agroup,
aval,
FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
@T

Answer 1

所提供的示例非常清楚地表明，FIRST_VALUE()解析函数的实现存在不一致性。

根据FROM子句中的基础表在一种情况下是基表(或临时表变量，甚至派生表)，以及在第二种情况下由动态表创建的LEFT JOIN创建的派生表(或cte)，结果是不同的。似乎NULL值在第二种情况下会被忽略，或者被视为高值。

而且它们不应该不同，因为SQL查询的结果不应该取决于FROM子句如何获取它提供给SELECT子句的表的值，还因为OVER子句的文档清楚地说明了如何对待NULL值：

order_by_expression 指定要对其排序的列或表达式。order_by_expression只能引用FROM子句提供的列。不能指定整数来表示列名或别名。 ..。 ASC | DESC 指定指定列中的值应按升序或降序排序。ASC是默认的排序顺序。空值被视为最低可能值。

因此，根据Server文档，正确的结果是不忽略空值的结果。任何其他结果都不应该发生，而且由于它确实发生了，所以是一个bug。

我建议您在最近的版本(而不仅仅是RTM中)进行测试，因为它可能已经在某些服务包或更新中被识别和更正，如果它仍然存在(或者如果您没有可用的新版本)将其作为bug提交到Connect站点中。

更新

作为将来的参考，错误是由OP提交的。链接是：连接项和(我们的) @Aaron Bertrand评论说，它也出现在大多数当前的SQL2014构建中。

Answer 2

对这篇文章的回答有点晚，但还是有一个要分享的。

您可以使用order标志“降级”空值。

所以在你的情况下。您可以使用

..。FIRST_VALUE(aval) fv ( (iif(aval为null，1,0))按分组顺序划分)，aval行在无界前与无界跟随之间)

(请注意，我将值1用于空值，因为它应该对字段进行升序排序，因此非空值优先)

干杯-洛杉矶。