last - Resultados inesperados al usar FIRST_VALUE() en SQL Server 2012

sql server 2012 lag over

Question

Cuando uso FIRST_VALUE en un conjunto de datos que construyo a mano obtengo un resultado, y cuando lo uso en un conjunto de datos que resulta de una combinación a la izquierda, obtengo un resultado diferente, aunque los conjuntos de datos me parecen contener los mismos valores de datos He reproducido el problema con un conjunto de datos simple a continuación.

¿Puede alguien decirme si he entendido mal algo?

Este SQL produce el resultado esperado, que FIRST_VALUE es NULL y LAST_VALUE es 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

Este SQL usa un LEFT JOIN que da como resultado el mismo conjunto de datos que el anterior, pero FIRST_VALUE parece ignorar el 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

También puedo mostrar que el comportamiento de unión a la izquierda es diferente cuando se usa una variable de tabla vs. un CTE. Cuando se utiliza un CTE para generar los datos, FIRST_VALUE ignora el NULL. Usar exactamente el mismo SQL pero poner los resultados en una variable de tabla o una tabla temporal da como resultado que se tenga en cuenta el NULO.

Con un CTE, los resultados de SQL Server no incluyen NULL en la determinación FIRST_VALUE:

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

Pero con una variable de tabla, lo hace:

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

Una respuesta tardía a este post, pero que se comparte sin embargo.

Puede usar el orden por bandera para "degradar" los valores nulos.

Entonces en tu caso ... puedes usar

... FIRST_VALUE (aval) OVER (PARTICIÓN POR agrupo ORDER BY (iif (aval es nulo, 1,0)), aval FILAS ENTRE DESACTIVADAS ANTERIORES Y DESORDENADAS A CONTINUACIÓN) fv ...

(Tenga en cuenta que utilizo el valor 1 para valores nulos, ya que debe ordenar el campo ascendente, por lo que los valores no nulos tendrán prioridad)

Saludos - LA.

Answer 2

Los ejemplos proporcionados muestran muy claramente que hay una inconsistencia en la implementación de la función analítica FIRST_VALUE() .

Dependiendo de si la tabla subyacente en la cláusula FROM es una tabla base (o temporal o una variable de tabla o incluso una tabla derivada creada sobre la marcha) en un caso y una tabla derivada (o cte) creada por LEFT JOIN de dos creados sobre la marcha tablas en el segundo caso, los resultados son diferentes. Parece que los valores NULL se ignoran en el segundo caso o se tratan como valores altos.

Y no deberían ser diferentes, porque el resultado de una consulta SQL no debería depender de cómo la cláusula FROM obtiene los valores de la tabla que proporciona a la cláusula SELECT y también porque la documentación de la cláusula OVER establece claramente cómo deben ser valores NULL tratado:

order_by_expression

Especifica una columna o expresión para ordenar. order_by_expression solo puede hacer referencia a las columnas disponibles mediante la cláusula FROM. No se puede especificar un número entero para representar un nombre de columna o alias.

...

ASC | DESC

Especifica que los valores en la columna especificada se deben ordenar en orden ascendente o descendente. ASC es el orden de clasificación predeterminado. Los valores nulos se tratan como los valores más bajos posibles .

Entonces, los resultados correctos, según la documentación del Servidor SQL, son los que no ignoran los valores NULL. Cualquier otro resultado no debería suceder y dado que sucede, es un error .

Le sugerí que pruebe en la versión más reciente (y no solo en el RTM) ya que puede haber sido identificada y corregida en algún service pack o actualización y si todavía está allí (o si no tiene una versión más nueva disponible) para enviar esto como un error en el sitio de Connect.

Actualizar

Para referencia futura, el error fue enviado por el OP. El enlace es: Connect item y (nuestro) @Aaron Bertrand ha comentado allí que también aparece en la mayoría de las compilaciones actuales de SQL 2014.