Funciones Oracle: FIRST_VALUE y LAST_VALUE

Las funciones FIRST_VALUE() y LAST_VALUE() proporcionan el valor inicial y final en una lista ordenada respectivamente.

FIRST_VALUE: Obtendría el primer valor
LAST_VALUE: Obtendría el último valor

SINTAXIS:

función (<expresion_valor_1>  [,<offset>  [, <valor_por_defecto> ]]) OVER
    ([PARTITION BY <expresion_valor_3> [,<expresion_valor_4>, ...]])
    ORDER BY <expresion_valor_2> [ASC|DESC] 
    [NULLS FIRST | NULLS LAST] [,…]
    [ventana])

siendo ventana:

[ROWS|RANGE] inicio AND fin

función – FIRST_VALUE o LAST_VALUE
Expresion_valor_1 – Puede ser una columna u otra función no analítica a obtener
Expresion_valor_3, Expresion_valor_4, … – Puede ser una columna u otra función no analítica a obtener
Ventana – En las funciones analíticas cuando se ordena se puede especificar un rango de filas a considerar ignorando el resto. Para ello se utiliza la palabra reservada «ROWS» o «RANGE» en función de si es un número de filas o un rango lógico, siendo inicio y fin:
UNBOUNDED PRECEDING: El rango empieza en la primera fila de la partición
UNBOUNDED FOLLOWING: El rango finaliza en la última fila de la partición
CURRENT ROW: El rango empieza en la fila que se está tratando o finaliza en la fila que se está tratando
n PRECEDING or n FOLLOWING: El rango empieza o finaliza n filas antes o después de la que se está tratando

Ejemplo 1:

CREATE TABLE tabla
(cantidad NUMBER,
 nombre VARCHAR2(100));

INSERT INTO tabla (cantidad, nombre) VALUES (1,'Luis');
INSERT INTO tabla (cantidad, nombre) VALUES (1,'Carlos');
INSERT INTO tabla (cantidad, nombre) VALUES (2,'Elena');
INSERT INTO tabla (cantidad, nombre) VALUES (NULL, 'Luisa');
INSERT INTO tabla (cantidad, nombre) VALUES (NULL,'Jacobo');
INSERT INTO tabla (cantidad, nombre) VALUES (1,'Carmen');
INSERT INTO tabla (cantidad, nombre) VALUES (2,'Mercedes');
INSERT INTO tabla (cantidad, nombre) VALUES (1,'Pepe');
INSERT INTO tabla (cantidad, nombre) VALUES (1,'Amelia');
INSERT INTO tabla (cantidad, nombre) VALUES (3,'Jose');

commit;

SELECT cantidad,
       nombre,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc) as first_value_asc,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre desc) as first_value_desc,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_value_rango_completo,
       LAST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_rango_completo
  FROM tabla
 ORDER by cantidad, nombre;

POSIBLES PROBLEMAS, QUE SE PUEDEN PRESENTAR

APARECE RESULTADOS ALEATORIOS:

Puede ser debido a ordenar de una forma no determinística, por lo que Oracle podría tomar de manera aleatoria un registro u otro.

Ejemplo 2:

SELECT cantidad,
       nombre,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY cantidad) as first_value
  FROM tabla
 ORDER by cantidad, nombre;

En este caso hemos obtenido para la cantidad 1 a Amelia, pero podría haber sido Carlos, ya que también tiene cantidad 1.

NO SE OBTIENEN LOS VALORES ESPERADOS:

Ejemplo 3:

SELECT cantidad,
       nombre,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc) as first_value_asc,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre desc) as first_value_desc,
       LAST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc) AS last_value_asc,
       LAST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre desc) AS last_value_desc,
       FIRST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_value_rango_completo,
       LAST_VALUE(nombre) IGNORE NULLS
         OVER (PARTITION BY cantidad ORDER BY nombre asc  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_rango_completo
  FROM tabla
 ORDER by cantidad, nombre;

Quizas podríamos esperar, que todas las columnas que utilizan la función last_value fueran iguales. Oracle a la hora de evaluar los valores funciones analíticas FIRST_VALUE y LAST_VALUE, primero particiona en subconjuntos, ordena los resultados en una ventana y aplica la función analítica.

¿Qué está ocurriendo? La ventana por defecto como deciamos, esta cogiendo desde el primer registro al actual. A la hora de evaluar la función LAST_VALUE en una ordenación, el máximo valor es el actual con la ventana por defecto. Habría que cambiar la ventana añadiendo «RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING» y ampliarla hasta el final o al menos desde el registro actual «RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING», para evitar los problemas.