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
