FUNCIONES ANALITICAS

Hay veces en las que encuentro consultas SQL a las que llamo "query ataque de pánico", porque puedo imaginarme el sufrimiento del que la programó. Con mucha frecuencia, este tipo de consultas aparecen en casos en las que una función analítica hubiera resuelto toda la complejidad del problema.

Al detalle. Una consulta del tipo "Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de..." suele consultarse con una subconsulta para cada "fecha de...".

Éste ejemplo, o el típico "Los tres contratos más recientes, las cinco últimas cancelaciones, etc." siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones... y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.

...evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.

Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.

Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).

FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)

Las funciones analíticas están descritas en la documentación de Oracle y entre éstas, por ejemplo, se hallan MAX(), MIN(), RANK(), etc.

Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:

SELECT     a.ID_FACTURA,                                 
           a.FALINEA_AUX - b.minCount + 1 ID_FALINEA,    
           a.ID_CLIENT,                                  
           a.ID_COMPTEFACT,                              
           a.PRODUCT_ID,                                 
           a.ID_PRCATPRODUCTE,                           
           a.DS_PRNUMSERVEI,                             
           a.ID_FACONCEPTE,                              
           a.DT_FAFACTURACIO,                            
           a.NUM_FAIMPORTCONCEPTE,                       
           a.PRODUCT_LABEL,                              
           a.DT_MOVIMENT,                                
           a.FG_TIPUSOPERACIO,                           
           a.asset_id,                                   
           a.PRODUCT_ATTR_VALUE                          
FROM vw_ci_linia_factura_tmp a,                      
     (select t.id_factura,                               
             t.dt_fafacturacio,                          
             min(t.falinea_aux) minCount                 
      from vw_ci_linia_factura_tmp t                 
      group by t.id_factura,                             
               t.dt_fafacturacio                         
     ) b                                                 
WHERE a.id_factura = b.id_factura                        
ORDER BY a.id_factura, a.FALINEA_AUX - b.minCount + 1 ASC;

En este caso, la necesidad de obtener la primera línea de factura para cada linea de factura, parece que obliga a cargar dos veces la vista (y su conjunto de tablas) en memoria y hacer una JOIN entre ambas.

No es necesario. Los costes de ejecución se reducen a la mitad.

SELECT     a.ID_FACTURA,                    
           a.FALINEA_AUX - min(falinea_aux) over
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,     
           a.ID_CLIENT,                  
           a.ID_COMPTEFACT,            
           a.PRODUCT_ID,                
           a.ID_PRCATPRODUCTE,          
           a.DS_PRNUMSERVEI,            
           a.ID_FACONCEPTE,             
           a.DT_FAFACTURACIO,           
           a.NUM_FAIMPORTCONCEPTE,      
           a.PRODUCT_LABEL,             
           a.DT_MOVIMENT,               
           a.FG_TIPUSOPERACIO,          
           a.asset_id,                  
        a.PRODUCT_ATTR_VALUE            
FROM sta_vw_ci_linia_factura_tmp a      
ORDER BY 1,2 ASC;

Vamos a ver con una serie de ejemplos lo fácil que puede ser hacer consultas SQL para obtener rankings, listas "Top n", acumulados, etc. Usaremos la tabla SCOTT.EMP para los ejemplos.

Imaginemos que nos piden un listado de empleados con su salario, y el salario máximo del departamento al que pertenecen:

select a.deptno dp, a.ename, a.sal, b.dept_max_sal
from scott.emp a, (select deptno, max(sal) dept_max_sal from scott.emp
group by deptno) b
where a.deptno=b.deptno;

DP ENAME SAL DEPT_MAX_SAL
---------- ---------- ---------- ------------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 SMITH 800 3000
20 ADAMS 1100 3000
20 FORD 3000 3000
20 SCOTT 3000 3000
20 JONES 2975 3000
30 ALLEN 1760 2850
30 BLAKE 2850 2850
30 MARTIN 1375 2850
30 JAMES 950 2850
30 TURNER 1500 2850
30 WARD 1375 2850
14 rows selected.

Esto mismo que hemos resuelto con una join sobre la misma tabla, lo podemos obtener eliminando la join si hacemos uso de una función analítica:

select a.deptno dp, a.ename, a.sal,max(sal) over (partition by deptno) dept_max_sal
from scott.emp a;

DP ENAME SAL DEPT_MAX_SAL
---------- ---------- ---------- ------------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 SMITH 800 3000
20 ADAMS 1100 3000
20 FORD 3000 3000
20 SCOTT 3000 3000
20 JONES 2975 3000
30 ALLEN 1760 2850
30 BLAKE 2850 2850
30 MARTIN 1375 2850
30 JAMES 950 2850
30 TURNER 1500 2850
30 WARD 1375 2850
14 rows selected.

La función que hemos usado se compone de dos partes:

• En la primera le decimos qué queremos calcular: max(sal). De igual forma podríamos haber usado otras funciones como: SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, etc.
• En la segunda le indicamos el ámbito o "ventana" de la operación (en este caso el departamento). Le hemos dicho que calcule el máximo salario para el departamento del empleado en cuestíón.

Siguiendo con el ejemplo anterior, lo podemos complicar más añadiendo el salario máximo para el tipo de trabajo de cada empleado:

select deptno dp,ename, job, sal,
max(sal) over (partition by deptno) dept_max_sal,max(sal) over (partition by job) job_max_sal
from scott.emp;

DP ENAME JOB SAL DEPT_MAX_SAL JOB_MAX_SAL
---------- ---------- --------- ---------- ------------ -----------
10 MILLER CLERK 1300 5000 1300
10 CLARK MANAGER 2450 5000 2975
10 KING PRESIDENT 5000 5000 5000
20 SCOTT ANALYST 3000 3000 3000
20 FORD ANALYST 3000 3000 3000
20 SMITH CLERK 800 3000 1300
20 JONES MANAGER 2975 3000 2975
20 ADAMS CLERK 1100 3000 1300
30 JAMES CLERK 950 2850 1300
30 MARTIN SALESMAN 1375 2850 1760
30 TURNER SALESMAN 1500 2850 1760
30 WARD SALESMAN 1375 2850 1760
30 ALLEN SALESMAN 1760 2850 1760
30 BLAKE MANAGER 2850 2850 2975
14 rows selected.

Hemos visto que podemos usar tantas funciones analíticas como queramos en la misma consulta, y además "particionando" por diferentes columnas en cada expresión.

Otra aplicación de las funciones analíticas es la generación de informes que incluyan rankings (como pueden ser los "Top n") y porcentajes. Siguiendo con el ejemplo sobre SCOTT.EMP, supongamos que tenemos que hacer un informe del salario de cada empleado, donde sólo aparezcan los tres de cada departamento con mayor sueldo:

select deptno,ename,sal,top3
from (select deptno, ename, sal,dense_rank() over (partition by deptno order by sal desc) top3
from scott.emp)
where top3<=3

DEPTNO ENAME SAL TOP3
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 ADAMS 1100 3
30 BLAKE 2850 1
30 ALLEN 1760 2
30 TURNER 1500 3
10 rows selected.

Analicemos la consulta anterior:

• La función analítica que nos sirve de base para obtener el "Top 3" es DENSE_RANK(). Hay otra función que nos permite obtener rankings: RANK(). La diferencia está en que RANK() genera huecos cuando hay valores iguales y DENSE_RANK() no. Para poder establecer el ranking, la función lleva una cláusula "order by" descendente; y por supuesto la cláusula "partition" que permite definir el ámbito en cuestión, en este caso el departamento.
• En la consulta interior (la del from de la principal), estamos generando el ranking; y en la consulta principal seleccionamos aquellos cuyo ranking sea igual o menor que 3.

Igual que hemos sacado un ranking podríamos obtener porcentajes (en tantos por 1), usando CUME_DIST(). Por ejemplo: listado del salario de los empleados que incluya el % que supone el salario de cada uno respecto al más alto de su departamento:

select deptno,ename,sal,
(cume_dist() over (partition by deptno order by sal))*100 pct
from scott.emp;

DEPTNO ENAME SAL PCT
---------- ---------- ---------- ----------
10 MILLER 1300 33.3333333
10 CLARK 2450 66.6666667
10 KING 5000 100
20 SMITH 800 20
20 ADAMS 1100 40
20 JONES 2975 60
20 SCOTT 3000 100
20 FORD 3000 100
30 JAMES 950 16.6666667
30 WARD 1375 50
30 MARTIN 1375 50
30 TURNER 1500 66.6666667
30 ALLEN 1760 83.3333333
30 BLAKE 2850 100
14 rows selected.

Para finalizar vamos a ver un ejemplo de informe con acumulados: listado de salarios ordenados por fecha, mostrando una columna con el salario acumulado:

select deptno dp, hiredate, sal,sum(sal) over (order by hiredate ROWS UNBOUNDED PRECEDING) acum
from scott.emp;

DP HIREDATE SAL ACUM
---------- --------- ---------- ----------
20 17-DEC-80 800 800
30 20-FEB-81 1760 2560
30 22-FEB-81 1375 3935
20 02-APR-81 2975 6910
30 01-MAY-81 2850 9760
10 09-JUN-81 2450 12210
30 08-SEP-81 1500 13710
30 28-SEP-81 1375 15085
10 17-NOV-81 5000 20085
30 03-DEC-81 950 21035
20 03-DEC-81 3000 24035
10 23-JAN-82 1300 25335
20 09-DEC-82 3000 28335
20 12-JAN-83 1100 29435
14 rows selected.



VIDEO DE CONEXION DE MSQL CON JAVA E HIBERNATE

PRODUCTOS QUE MANEJAN MODELOS MULTIDIMENSIONALES

El data warehouse se mantiene aparte de las bases de datos operacionales
y se puede implementar utilizado servidores OLAP multidimensionales
(MOLAP) o servidores OLAP relacionales (ROLAP).
MOLAP (Multidimensional OLAP)
Los datos se almacenan en estructuras de datos multidimensionales (matrices
multidimensionales sobre las que se realizan directamente las operaciones OLAP).
ROLAP (Relational OLAP)
El DW se implementa como una base de datos relacional (las operaciones
multidimensionales OLAP se traducen en operaciones relacionales estándar).

http://www.fortunecity.es/imaginapoder/apunte/434/modelamul.htm

PRODUCTOS DE BASE DE DATOS QUE MANEJAN MODELOS MULTIDIMENSIONALES

PRODUCTOS DE BD  QUE  MANEJAN MODELOS MULTIDIMENSIONALES

Producto	Hardware	Software
MaxVision Toolkit	MaxPCI:Pipeline Vision ProcessorCuando está inspeccionando un rápido movimiento de web, una hoblea de silicon de 300mm, una pelota de 500 partes BGA, imaginando un panel de silicon amorfo de rayos X, o trabajando en una aplicación similar, MaxPCI tiene el poderm la exactitud, y flexibilidad para tener el trabajo hecho. Este es ideal para resoluciones que están en miles de pixeles, no cientos, o precisiones que están en 12, 16 o 24 bits, pero no 8 bits. Datacube 40 MHz crosspoint ASIC 65x75x8-bit @ 40 MHz 55x57x1-bit @ 40 MHz 3 GB/seg. de banda ancha interna Buses de encendido virtualmente ilimitados mvPower: mvPower combina cubos de datos probados de tecnología de procesamiento con la función excepcional de un Motorola a 100 MHz PowerPC® 603e Pentium II. a 400 MHz mínimo	Windows NT 4.0 con la actualización para el año 2000 VxWorks Leguajes basados en C LynxOS 2.4
Informix MetaCube 4.2	Servidores Cliente/Servidor de dos o tres capasConecciones potenciales ilimitadas, dependiendo de la memoria del CPU Computadores IBM o 100% compatibles con un procesador 586 a 200Mhz o más Al menos 3 o 4 Megabytes de memoria por usuario corriendo el Explorador de Web Datacube Multiples computadoras con 128MB de memoria para soporte del Sistema operativo Windows NT Un servidor de Transacciones y un servidor de Web 25 MB de espacio en disco CDROM 16MB RAM minimum para el Cliente	Opciones: UNIX, Microsoft Windows 95, Windows 98 and Windows NT, Version 4.2 Software de comunicación TCP/IP
Delphi Edición Empresarial	Intel Pentium 90 o más(P166 recomendado) Microsoft Windows 95, 98 or NT4.0 con Servicio Pack 3 o posterior 32MB RAM (64MB recomendado) Espacio de Disco Duro 80 MB para inatalación compacta 230 MB para instalación completa CDROM VGA o mayores resoluciones de monitor Mouse	drivers InterBase, Oracle, Sybase, Informix, MS SQL Server, and DB2

---

Ventajas /Desventajas y Costos

Productos	Ventajas	Desventajas	Costos
MaxVision Toolkit	Los cubos de datos proveen de hardware de procesamiento de imagenes, software, y sistemas para maquinas de alta calidad de vision, para medicina, y aplicaciones de inspección de superficies 100% industrial. También provee de las mejores funciones de procesamiento de imagenes, subsistemas de video de tiempo real en disco duro, y herramientas de software para maquinas especializada. Todos estos aditamientos le dan una gran ventaja con respecto a sus competidores en procesamiento de imagenes pues es de mejor calidad la imagen obtenida.	Puede llegar a tener altos costosEl procesamiento puede llegar a ser lento por las grandes cantidades de información	No disponible
Informix MetaCube 4.2	Provee una vista rápida de datos dentro de su datawarehouse o datamarts via reportes y traza un mapa que este conectado con su pensamiento intuitivo acerca de sus negocios. Soporta una abierta arquitectura de multiplataforma que permite la integración de la tercera parte de las herramientas y de aplicaciones analiticas, y los desarrollos de aplicaciones especiales. Los ambientes de soporte a las decisiones pueden ser completamente adecuadas para alcanzar sus especificas necesidades. Integra un extendido alcance entre el Web y la escalabilidad de ROLAP que juntos le permite funcionar con las más avanzadas funciones OLAP -instrucciones donde quiera, arrastre y exclusión de constructores de consultas, gráficado, encubado, o más dentro de su ambiente basado en un buscador de Web estandar. Envía altas funciones de acceso de datos en tiempo cero. A través de pequeñas manipulaciones de datos para mejorar la rápidez y calidad de analisis, las respuestas son enviadas rápidamente para facilitar la creación de nuevas oportunidades de negocios. Reduce los costos de administración de bases de datos haciendo datawarehouses y manteniendo facilmente los datamarts- pasando un un completo juego de herramientas avanzadas diseñadas para simplificar y automatizar la optimización de pequeños y grandes datawarehouses y data martscomo tales.	La conección multiplataforma puede hacer más eficiente la conección de aplicaciones pero esta puede llevar tiempo que puede costar muchoLa información manipulada puede llegar a perderse en el proceso o puedeser erronea	No disponible,solo se pueden obtener Demos, Literatura, etc. pero enviar $500 dls. Para registrarse sepagaría de 450 a 650 dls
Delphi Edición Empresarial	Ingeniería de bases de datos para fácil acceso a culaquier sistema de base de datosAvanzado control de transacciones Información comprensiva de Base de datos Ilimitado desarrollo en Interbase Express	Puede ser un sistema muy complejo en cuanto a informaciónPuede haber demasiadas Bases de Datos para hacer su acceso más fácil	Solo se menciona una opción de suscripción por 12 meses para obtener las mejoras que vayan saliendo y un telefono para llamar : 831-431-1064.

MAPA CONCEPTUAL