Oracle Live

01/07/2016

SQL – Analytic Functions

Filed under: Queries — mogukiller @ 12:47 pm
-- COUNT | SUM | MIN | MAX
-- LEAD | LAG 
-- RANK | DENSE_RANK
-- ROW_NUMBER()
-- LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY )

======================================
COUNT | SUM | MIN | MAX OVER ()
======================================
'
COUNT(*) OVER([sentencias])
	[Sentencias]:
		partition by column: Te agrupa por esa columna y te saca el total
		partition by column1, column2: Conteo de elementos iguales que resulta de agrupar por esas dos columnas
MIN(column) | MAX(column) OVER([sentencias])
SUN(valor) OVER([sentencias])	
	[Sentencias]:	
		order by column: Va sumando progresivamente los valores de la columna que aparece en el sum pero ordenados por la columna que metas en el over.
		range_between
			unbounded preceding and current row: 		Esto tiene en cuenta todas las anteriores filas y la actual
			n preceding and current row:				Esto tiene en cuenta la fila anterior y la actual
			current row and n following:				Esto tiene en cuenta la actual y la fila siguiente.
'
EJEMPLOS

select deptno,ename,sal,
	sum(sal)over(order by hiredate
		range between unbounded preceding
		and current row) as run_total1,
	
	sum(sal)over(order by hiredate
		rows between 1 preceding
		and current row) as run_total2,
	
	sum(sal)over(order by hiredate
		range between current row
		and unbounded following) as run_total3,
	
	sum(sal)over(order by hiredate
		rows between current row
		and 1 following) as run_total4
from emp
where deptno=10

/*
DEPTNO ENAME 	SAL 		RUN_TOTAL1 RUN_TOTAL2 	RUN_TOTAL3 RUN_TOTAL4
------ ------ ----- ---------- ---------- ---------- ----------
10 CLARK		2450 		2450 		2450 		8750 		7450
10 KING 		5000 		7450 		7450 		6300 		6300
10 MILLER 		1300 		8750 		6300 		1300 		1300
*/

SELECT 
	ID_DEPARTAMENTO, NAME, COUNT(*) OVER(PARTITION BY ID_DEPARTAMENTO) AS NUM_EMPLEADOS,
	SALARIO, SUM(SALARIO) OVER (ORDER BY FECHA_CONTRATO) INCREMENTAL_DEPARTAMENTO
FROM EMPLEADOS;

SELECT ID_DEPARTAMENTO, NAME, SALARIO,
	SUM(SALARIO) OVER (ORDER BY FECHA_CONTRATO ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS SUM_ANTERIOR,
	SUM(SALARIO) OVER (ORDER BY FECHA_CONTRATO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS SUM_ANTERIOR,
	MIN(SALARIO) OVER (ORDER BY SALARIO) AS MIN_SALARIO,
	MAX(SALARIO) OVER (ORDER BY SALARIO) AS MAX_SALARIO,
	MIN(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_1, -- Esto es como si pusiesemos los parentesis vacios
	MAX(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_1, -- Esto es como si pusiesemos los parentesis vacios
	MIN(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS MIN_2,
	MAX(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS MAX_2,
	MIN(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) AS MIN_3,
	MAX(SALARIO) OVER (ORDER BY SALARIO RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) AS MAX_3
FROM EMPLEADOS;
/*
ID_DEPARTAMENTO NAME          SALARIO SUM_ANTERIOR SUM_ANTERIOR MIN_SALARIO MAX_SALARIO      MIN_1      MAX_1      MIN_2      MAX_2      MIN_3      MAX_3
--------------- ---------- ---------- ------------ ------------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
              2 JON                50         2050         5050          50          50         50      12001         50         50         50         50
              2 JAVI              120         1670        12121          50         120         50      12001        120        120        120        120
              2 LUCAS             200        12201         2200          50         200         50      12001        200        200        200        200
              2 MARCELA          1400         2950         2950          50        1400         50      12001       1400       1400       1400       1400
              2 LUISMI           1450         3950         1450          50        1450         50      12001       1450       1450       1450       1450
              1 LUIS             1550         1550         2950          50        1550         50      12001       1550       1550       1550       1550
              2 JUAN             1550         2950         1670          50        1550         50      12001       1550       1550       1550       1550
              2 PATRI            2000         4000         2050          50        2000         50      12001       2000       2000       2000       2000
              2 ALICIA           2000         2200         4000          50        2000         50      12001       2000       2000       2000       2000
              2 JULIO            2000         7000         4100          50        2000         50      12001       2000       2000       2000       2000
              2 PEDRO            2100         4100         4600          50        2100         50      12001       2100       2100       2100       2100
              2 PILI             2500         4600         3950          50        2500         50      12001       2500       2500       2500       2500
              2 RAFA             5000         5050         7000          50        5000         50      12001       5000       5000       5000       5000
              2 TONI            12001        12121        12201          50       12001         50      12001      12001      12001      12001      12001

*/
SELECT ID_DEPARTAMENTO, PUESTO,
		COUNT(*) OVER (PARTITION BY ID_DEPARTAMENTO) AS TOTAL_DEPARTAMENTO,
		COUNT(*) OVER (PARTITION BY ID_DEPARTAMENTO,PUESTO) AS TOTAL_DEPARTAMENTO_PUESTO
FROM EMPLEADOS;
/*
ID_DEPARTAMENTO PUESTO               TOTAL_DEPARTAMENTO TOTAL_DEPARTAMENTO_PUESTO
--------------- -------------------- ------------------ -------------------------
              1 MANAGER                               1                         1
              2 MANAGER                               4                         1
              2 TECNICO                               4                         2
              2 TECNICO                               4                         2
              2 VENDEDOR                              4                         1
              3 RRHH                                  4                         1
              3 TECNICO                               4                         3
              3 TECNICO                               4                         3
              3 TECNICO                               4                         3
              4 MANAGER                               4                         1
              4 RRHH                                  4                         1
              4 TECNICO                               4                         1
              4 VENDEDOR                              4                         1
              5 TECNICO                               1                         1
			  
*/
COMPUTE SUM LABEL Total OF I1 ON REPORT
COMPUTE SUM LABEL Total OF I2 ON REPORT
COMPUTE SUM LABEL Total OF I3 ON REPORT
COMPUTE SUM LABEL Total OF I4 ON REPORT
BREAK ON REPORT
SELECT 
	USERNAME,
	MAX( CASE WHEN INST_ID=1 THEN TOTALES_INST ELSE 0 END) AS I1,
	MAX( CASE WHEN INST_ID=2 THEN TOTALES_INST ELSE 0 END) AS I2,
	MAX( CASE WHEN INST_ID=3 THEN TOTALES_INST ELSE 0 END) AS I3,
	MAX( CASE WHEN INST_ID=4 THEN TOTALES_INST ELSE 0 END) AS I4
FROM( SELECT INST_ID, USERNAME, 
		COUNT(*) OVER(PARTITION BY INST_ID,USERNAME) AS TOTALES_INST
FROM gv$session
WHERE USERNAME IS NOT NULL)  
GROUP BY USERNAME;

USERNAME                               I1         I2         I3
------------------------------ ---------- ---------- ----------
                                       44         44         41
EPPGA                                1134       1105         52
SYS                                     6          4          4


=========================================================================
LEAD | LAG OVER ()	--> Nos devuelve le siguiente registro atendiendo a una columna	
=========================================================================

LEAD OVER ()	--> Nos devuelve le siguiente registro atendiendo a una columna	
LAG OVER ()		--> Nos devuelve el anterior registro atendiendo a una columna	
SELECT ID_EMPLEADO, FECHA_CONTRATO, 
	LEAD(FECHA_CONTRATO,1,NULL) OVER(ORDER BY ID_EMPLEADO) FECHA_SIGUIENTE,
	LAG (FECHA_CONTRATO,1,NULL) OVER(ORDER BY ID_EMPLEADO) FECHA_ANTERIOR FROM EMPLEADOS;
/*
ID_EMPLEADO FECHA_CO FECHA_SI FECHA_AN
----------- -------- -------- --------
          1 25/10/80 25/12/85
          2 25/12/85 02/11/00 25/10/80
          3 02/11/00 25/11/01 25/12/85
          4 25/11/01 25/10/01 02/11/00
          5 25/10/01 10/11/01 25/11/01
          6 10/11/01 13/11/01 25/10/01
          7 13/11/01 24/11/01 10/11/01
          8 24/11/01 12/01/01 13/11/01
          9 12/01/01 04/02/01 24/11/01
         10 04/02/01 14/11/01 12/01/01
         11 14/11/01 25/11/01 04/02/01
         12 25/11/01 25/11/10 14/11/01
         13 25/11/10 25/11/01 25/11/01
         14 25/11/01 28/04/13 25/11/10
         16 28/04/13          25/11/01*/

-- Parametros:
	FECHA_CONTRATO: Es la columna que quiero mostrar.
	1: El offset entre registros.
	NULL: Es es parametro que devuelve en el caso que no haya registros.
	ID_EMPLEADO: Indica el criterio por el que se orden.

=========================================================================
RANK | DENSE_RANK OVER()
=========================================================================
/*
Se obtiene el ranking dentro de un determinado grupo 'ID_DEPARTAMENTO' ORDENADO DENTRO DE UNA COLUMNA 'SUELDO'
NOTA: En el ejemplo se ve como la diferencia en RANK y DENSE_RANK es que cuando dos elementos comparten el mismo ranking el siguiento puesto no es consecutivo.
*/

COL NAME FOR A10	
SELECT ID_DEPARTAMENTO, NAME, SALARIO, 
	MAX(SALARIO) OVER(PARTITION BY ID_DEPARTAMENTO) as Max_departamento,
	RANK() OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC) as ranking_sueldo,
	DENSE_RANK() OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC) as dense_rank_sueldo
	FROM EMPLEADOS;
/*	
ID_DEPARTAMENTO NAME          SALARIO MAX_DEPARTAMENTO RANKING_SUELDO DENSE_RANK_SUELDO
--------------- ---------- ---------- ---------------- -------------- -----------------
              1 LUIS             1550             1550              1                 1
              2 PEDRO            2100             2100              1                 1
              2 JUAN             1550             2100              2                 2
              2 MARCELA          1400             2100              3                 3
              2 LUCAS             200             2100              4                 4
              3 RAFA             5000             5000              1                 1
              3 PATRI            2000             5000              2                 2
              3 ALICIA           2000             5000              2                 2
              3 JAVI              120             5000            	4                 3 <----
              4 PILI             2500             2500              1                 1
              4 TONI             1800             2500              2                 2
              4 LUISMI           1450             2500              3                 3
              4 JUAN             1350             2500              4                 4
              4 JON               500             2500              5                 5
              5 JULIO            2000             2000              1                 1
*/			  
=========================================================================
ROW_NUMBER() OVER()
=========================================================================

/*
Saca la posicion de un determinado elemento, similar a DENSE_RANK
*/

SELECT ID_DEPARTAMENTO, NAME, SALARIO, POSICION
FROM (
SELECT ID_DEPARTAMENTO, NAME, SALARIO,
	ROW_NUMBER() OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC) AS POSICION
FROM EMPLEADOS)
WHERE POSICION <=3 ORDER BY ID_DEPARTAMENTO;
/*
ID_DEPARTAMENTO NAME          SALARIO   POSICION
--------------- ---------- ---------- ----------
              1 LUIS             1550          1
              2 PEDRO            2100          1
              2 JUAN             1550          2
              2 MARCELA          1400          3
              3 RAFA             5000          1
              3 ALICIA           2000          2
              3 PATRI            2000          3
              4 PILI             2500          1
              4 TONI             1800          2
              4 LUISMI           1450          3
              5 JULIO            2000          1
*/			  
=========================================================================
 LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY ) OVER ()
=========================================================================
  
BREAK ON INDEX_OWNER ON TABLE_NAME SKIP 1
COLUMN index_owner       format a15 wrap           heading "Index|Owner"
COLUMN table_name        format a30 wrap           heading "Table|Name"
COLUMN index_name        format a30 wrap           heading "Index|Name"
COLUMN columnas			 format a70 wrap           heading "Columns|List"
SELECT DISTINCT
 INDEX_OWNER, TABLE_NAME, INDEX_NAME,
 LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION) OVER (PARTITION BY INDEX_OWNER,TABLE_NAME,INDEX_NAME) AS COLUMNAS
FROM 
 DBA_IND_COLUMNS
WHERE
 INDEX_OWNER LIKE UPPER ('%&ENTER_OWNER_NAME%') 
 AND TABLE_NAME LIKE UPPER ('%&ENTER_TABLE_NAME%');
 
 /*
Index           Table                          Index                          Columns
Owner           Name                           Name                           List
--------------- ------------------------------ ------------------------------ ----------------------------------------------------------------------
TPCC            CUSTOMER                       CUSTOMER_I1                    C_W_ID,C_D_ID,C_ID
                                               CUSTOMER_I2                    C_LAST,C_W_ID,C_D_ID,C_FIRST,C_ID

                ITEM                           ITEM_I1                        I_ID

                DISTRICT                       DISTRICT_I1                    D_W_ID,D_ID

                ORDER_LINE                     IORDL                          OL_W_ID,OL_D_ID,OL_O_ID,OL_NUMBER

                STOCK                          STOCK_I1                       S_I_ID,S_W_ID

                WAREHOUSE                      WAREHOUSE_I1                   W_ID

                NEW_ORDER                      INORD                          NO_W_ID,NO_D_ID,NO_O_ID

                ORDERS                         ORDERS_I1                      O_W_ID,O_D_ID,O_ID
                                               ORDERS_I2                      O_W_ID,O_D_ID,O_C_ID,O_ID
*/	

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: