Ejemplo de funciones analíticas para explotar información de Libra I

Este es el primero de una serie de artículos en los que mostraremos casos útiles de uso de funciones analíticas y de funciones avanzadas que nos proporciona Oracle para explotar la información almacenada en Libra.

En este artículo nos vamos a centrar en el uso de Ventanas que permiten analizar el registro que se obtiene con los n registros anteriores y los n registros posteriores.

La mejor forma de verlo es con un ejemplo. Tenemos los siguientes registros de movimientos de almacén de un artículo:

Fecha    Movimiento   Cantidad
-------- ---------- ----------
24/01/04 INV_I           10000
19/07/04 VENT0             -10
15/07/05 VENT0             -12
15/07/05 VENT0              12
15/07/05 VENT0             -12
15/07/05 VENT0              12
15/07/05 VENT0              -1
15/07/05 VENT0               1
24/02/09 SAL_D           -9990
24/02/09 ENT_I              10

Estos registros han sido obtenidos con la siguiente consulta:

SELECT fecha_movim "Fecha", codigo_movimiento "Movimiento", cantidad_unidad1 "Cantidad"
  FROM historico_movim_almacen
 WHERE codigo_articulo = '00000089'
   AND codigo_empresa = '013'
   AND tipo_movimiento != '09'
 ORDER by fecha_movim;

Usando funciones analíticas se podría añadir una columna que muestre el stock resultante del artículo después de haberse realizado el movimiento, para ello es necesario sumar el campo "Cantidad" desde el principio hasta la fila que es obtenida, para ello añadimos la siguiente columna: SUM(cantidad_unidad1) OVER(ORDER BY fecha_movim ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Vamos a analizar esta función:

• SUM(cantidad_unidad1): Se indica que se desea sumar el campo "cantidad_unidad1".
• OVER(): Se indica que es una función analítica, por lo que SUM(cantidad_unidad1) lo realizará sobre los registros especificados dentro de OVER y no sobre los obtenidos directamente por la SELECT, de manera que no hace falta agrupar por ningún campo.
• ORDER BY fecha_movim: La suma se realizará de forma ordenada usando el campo "fecha_movim".
• ROWS BETWEEN: Se indica la ventana de registros sobre los que se desea hacer la suma.
• UNBOUNDED PRECEDING: Indicamos que se deben sumar todos los registros anteriores, si se cambia UNBOUNDED por un número, por ejemplo 10 PRECEDING  se sumarían sólo los 10 registros anteriores.
• AND CURRENT ROW: Se indica que sólo debe de sumar hasta la fila actual, es decir, las siguientes filas se ignoran.

La SELECT completa quedaría de la siguiente forma:

SELECT fecha_movim "Fecha", codigo_movimiento "Movimiento", cantidad_unidad1 "Cantidad",
       SUM(cantidad_unidad1) OVER(ORDER BY fecha_movim ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "Stock"
  FROM historico_movim_almacen
 WHERE codigo_articulo = '300300'
   AND codigo_empresa = '013'
   AND tipo_movimiento != '09'
 ORDER by fecha_movim;

El resultado que se obtiene sobre los registros mostrados originalmente es el siguiente:

Fecha    Movimiento   Cantidad      Stock
-------- ---------- ---------- ----------
24/01/04 INV_I           10000      10000
19/07/04 VENT0             -10       9990
15/07/05 VENT0             -12       9978
15/07/05 VENT0              12       9990
15/07/05 VENT0             -12       9978
15/07/05 VENT0              12       9990
15/07/05 VENT0              -1       9989
15/07/05 VENT0               1       9990
24/02/09 SAL_D           -9990          0
24/02/09 ENT_I              10         10

De esta forma también se puede por ejemplo obtener de forma muy rápida una consulta para saber en qué momentos un determinado artículo de Libra se ha quedado sin stock, con la siguiente consulta:

SELECT fecha_movim "Fecha", codigo_movimiento "Movimiento", cantidad_unidad1 "Cantidad"
FROM (
      SELECT fecha_movim, codigo_movimiento, cantidad_unidad1,
             SUM(cantidad_unidad1) OVER(ORDER BY fecha_movim ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) stock
        FROM historico_movim_almacen
       WHERE codigo_articulo = '300300'
         AND codigo_empresa = '013'
         AND tipo_movimiento != '09'
       ORDER by fecha_movim) h
WHERE h.stock = 0;

El resultado de aplicar esta consulta sobre los registros de ejemplo únicamente nos devolverá el siguiente registro:

Fecha    Movimiento   Cantidad      Stock
-------- ---------- ---------- ----------
24/02/09 SAL_D           -9990          0

by Tamara Bravo