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;
CANTIDAD NOMBRE FIRST_VALUE_ASC FIRST_VALUE_DESC FIRST_VALUE_RANGO_COMPLETO LAST_VALUE_RANGO_COMPLETO
1
Amelia Amelia Pepe Amelia Pepe
1
Carlos Amelia Pepe Amelia Pepe
1
Carmen Amelia Pepe Amelia Pepe
1
Luis Amelia Pepe Amelia Pepe
1
Pepe Amelia Pepe Amelia Pepe
2
Elena Elena Mercedes Elena Mercedes
2
Mercedes Elena Mercedes Elena Mercedes
3
Jose Jose Jose Jose Jose
  Jacobo Jacobo Luisa Jacobo Luisa
  Luisa Jacobo Luisa Jacobo Luisa

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;
CANTIDAD NOMBRE FIRST_VALUE
1
Amelia Amelia
1
Carlos Amelia
1
Carmen Amelia
1
Luis Amelia
1
Pepe Amelia
2
Elena Elena
2
Mercedes Elena
3
Jose Jose
  Jacobo Jacobo
  Luisa Jacobo

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;
CANTIDAD NOMBRE FIRST_VALUE_ASC FIRST_VALUE_DESC LAST_VALUE_ASC LAST_VALUE_DESC FIRST_VALUE_RANGO_COMPLETO LAST_VALUE_RANGO_COMPLETO
1
Amelia Amelia Pepe Amelia Amelia Amelia Pepe
1
Carlos Amelia Pepe Carlos Carlos Amelia Pepe
1
Carmen Amelia Pepe Carmen Carmen Amelia Pepe
1
Luis Amelia Pepe Luis Luis Amelia Pepe
1
Pepe Amelia Pepe Pepe Pepe Amelia Pepe
2
Elena Elena Mercedes Elena Elena Elena Mercedes
2
Mercedes Elena Mercedes Mercedes Mercedes Elena Mercedes
3
Jose Jose Jose Jose Jose Jose Jose
  Jacobo Jacobo Luisa Jacobo Jacobo Jacobo Luisa
  Luisa Jacobo Luisa Luisa Luisa Jacobo Luisa

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.

Comments are closed.