domingo, 10 de julio de 2011

Biomimetica Archivo
Publicado por en No hay comentarios:
Biomimetica
Publicado por en No hay comentarios:

jueves, 7 de abril de 2011

Diagrama de Cono de Nieve

Publicado por en No hay comentarios:

miércoles, 6 de abril de 2011


FUNCIONES ANALITICAS

Una de funciones analíticas

En esta oportunidad veremos una técnica interesante en SQL para relacionar valores de 2 filas consecutivas, para todos los registros de una tabla. El criterio de 'consecutividad' puede estar dado por alguna de las columnas de la tabla, como el id, la fecha de creación del registro, etc.

En el ejemplo a seguir, tenemos un sistema de registro de tickets, con una tabla histórica que registra el momento en que un ticket cambió de estado.
Lo que queremos hacer es un reporte con el tiempo total en cada uno de esos estados, con un único SQL.

Esta no es una consulta trivial, ya que cada registro contiene únicamente una fecha, por tanto tendremos que asociar al menos dos de ellos para obtener un intervalo de tiempo.

Para complicar un poco, lo resolveremos primero con MySQL, que tiene bastante menos potencial que Oracle para este tipo de consultas. De hecho este ejercicio surgió a partir de la necesidad de un colega trabajando con ese motor.

Comenzamos creando la tabla:
CREATE TABLE status_history
(id        INTEGER PRIMARY KEY,
ticket_id INTEGER,
estado    VARCHAR(1),
fecha     DATETIME);
Incluyamos nuestro primer ticket, con algunos cambios de estado entre A, B y C. Los estados tienen un orden cronológico y es posible volver al mismo repetidas veces.
INSERT INTO status_history VALUES (1, 1, 'A', '2010-01-01 14:00:00');
INSERT INTO status_history VALUES (2, 1, 'B', '2010-01-03 18:30:00');
INSERT INTO status_history VALUES (3, 1, 'C', '2010-01-07 10:00:00');
INSERT INTO status_history VALUES (4, 1, 'A', '2010-01-11 12:10:00');
INSERT INTO status_history VALUES (5, 1, 'C', '2010-01-14 15:00:00');
Como se puede ver, el ticket 1 comenzó en el estado A, luego pasó al B, al C, volvió a A y finalizó en C. Todos esos cambios registraron únicamente la fecha de inserción.

Observando los datos de la tabla, deducimos que la diferencia de tiempo entre el segundo registro 'B' y el primero 'A' es tiempo transcurrido en estado 'A' (en el diagrama representado como tA1). Lo mismo para el último intervalo tA2. La suma de tA1 y tA2 representará el tiempo total que el ticket estuvo en estado 'A'.

La estrategia será usar un auto-join y relacionar los registros n y n+1. Para eso necesitamos tener un campo secuencial y sin huecos para que la igualdad funcione.
Veremos primero el caso simple donde cada registro tiene un id consecutivo, y luego nos enfocaremos en resolver el problema de los huecos.

CASO 1 - IDs continuos (1,2,3,4,5....)

En este caso, sabemos que cada id está a una distancia 1 del id siguiente (vale también para otras diferencias), por tanto haremos el join usando la condición id=id+1 para obtener las fechas y la diferencia entre ellas.
SELECT s1.estado,
      TIME_TO_SEC(timediff(s2.fecha,s1.fecha))/60/60 AS total_horas
FROM
  status_history s1, status_history s2
WHERE s1.id+1 = s2.id
 AND s1.ticket_id=1
 AND s2.ticket_id=1

Para manipular la diferencia entre dos fechas, usamos primero TIMEDIFF que me retorna un tipo TIME (HH:MI:SS) y luego TIME_TO_SEC para obtener la cantidad total de segundos.

Obtenemos las siguientes tuplas:
ESTADO   TOTAL_HORAS
======   ============
'A'      52.50000000
'B'      87.50000000
'C'      98.16666667
'A'      74.83333333

Cabe observar que aparecen dos instancias del estado A, que en el diagrama anterior equivalen a tA1 y tA2. Para obtener la suma total, agrupamos por estado y utilizamos la función SUM:
SELECT s1.estado,
      SUM(TIME_TO_SEC(timediff(s2.fecha,s1.fecha)))/60/60 AS total_horas
FROM
  status_history s1, status_history s2
WHERE s1.id+1 = s2.id
 AND s1.ticket_id=1
 AND s2.ticket_id=1
GROUP BY estado

Finalmente el resultado deseado:
ESTADO   TOTAL_HORAS
======   ============
'A'      127.33333333
'B'      87.50000000
'C'      98.16666667
CASO 2 - IDs con huecos (1,2,4,5,9....)

En el caso anterior, podiamos usar la condicion id=id+1, ya que estabamos seguros que siempre iba a haber una correspondencia (con excepción del último registro). En el caso de existir huecos, el id ya no nos sirve; necesitamos otro campo para ese fin. En Oracle, esto sería muy fácil con la columna virtual rownum, pero en MySQL no tenemos ese recurso.
Hay sin embargo, una forma de simular rownum, y es usando una variable declarada en SQL, inicializada en un select interior. La sintáxis no queda muy amigable pero sirve al propósito de este problema, que es tener un campo secuencial sin huecos.

Columna virtual ROWNUM con MySQL:
SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, <tabla o consulta> t
Ahora si, podemos sustituir las tablas por este código y usar rownum como condición.

Para probarlo con nuestro ejemplo, agregaremos un nuevo cambio de estado, con id=8:
INSERT INTO status_history VALUES (8, 1, 'D', '2010-01-16 11:00:00');
La secuencia de ids ahora es 1, 2, 3, 4, 5, 8

La consulta queda así:
SELECT t1.estado,
      SUM(TIME_TO_SEC(timediff(t2.fecha,t1.fecha)))/60/60 AS total_horas
FROM
  (SELECT @rownum1:=@rownum1+1 rownum, s1.*
   FROM (SELECT @rownum1:=0) r, status_history s1
   WHERE s1.ticket_id=1
   ORDER BY s1.id) t1,
  (SELECT @rownum2:=@rownum2+1 rownum, s2.*
   FROM (SELECT @rownum2:=0) r, status_history s2
   WHERE s2.ticket_id=1   
   ORDER BY s2.id) t2
WHERE t1.rownum+1 = t2.rownum
GROUP BY t1.estado
Observar que cada subconsulta utiliza su propia variable @rownum, ya que si usarámos la misma, se incrementaría incorrectamente.

El resultado es:
ESTADO   TOTAL_HORAS
======   ============
'A'      127.33333333
'B'      87.50000000
'C'      142.16666667
Solamente el tiempo de 'C' se vio incrementado con el pasaje de 'C' a 'D'. El estado 'D' no tiene tiempo asociado por tratarse del estado terminal.

La solución Oracle con Funciones Analíticas

Como parte final, voy a mostrar cómo el poder de las funciones analíticas de Oracle nos permiten resolver este tipo de problemas con elegancia y sencillez, sin preocuparnos con auto-joins, rownums ni huecos.
La función ideal en este caso es LEAD. Esta función nos retorna el siguiente registro basado en algun criterio de ordenación, que puede ser una columna.

Todo se resume a usar lo siguiente:
LEAD(fecha, 1, NULL) OVER(ORDER BY id)
  • El primer parámetro es la columna que voy a retornar, en mi caso, fecha.
  • El segundo es el offset que quiero recuperar, es decir cuantas filas 'adelante'.
  • El tercer parámetro es el valor que quiero mostrar si no hay siguiente registro.
  • Por último, la columna que voy a usar como criterio de ordenación.
La consulta principal que me da tiempos por estado (no agrupados) queda así:
SELECT t1.estado,
      (LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 AS total_horas
FROM   status_history t1
ORDER BY id
Estoy restando la fecha del registro siguiente (recuperado por LEAD) al registro actual. Ver que tampoco necesito funciones de fechas, basta con restarlas y multiplicar por 24.

Solo resta agrupar los estados y sumar los totales en una query exterior:
SELECT estado,
      SUM(total_horas) total_horas
FROM   (SELECT t1.estado,
               (LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 total_horas
        FROM   status_history t1
        ORDER  BY id)
WHERE total_horas IS NOT NULL
GROUP  BY estado
ORDER BY estado;
El resultado es el mismo, lo pueden comprobar si se animan a hacer la prueba.
Tengo que agregar la condición total_horas IS NOT NULL, ya que el uso de LEAD no impide que se muestre el estado D con tiempo NULL (recuerdan el tercer parámetro de LEAD?), que en este caso no nos interesa.

Como comentario, la función LAG nos retorna el registro anterior usando la misma sintáxis que LEAD.

Conclusiones

Las funciones analíticas son útiles y necesarias en muchos casos. Además de ser más eficientes, nos permiten resolver rápidamente el problema de agrupar y buscar relaciones entre filas de una misma consulta.

Bases de datos como MySQL no poseen estos recursos, y nos obligan a resolver el problema con mucho más esfuerzo. Esperemos que con la nueva adquisición, Oracle piense en incorporarlas, para beneficio de quienes trabajan con SQL :)

Mientras tanto, en el caso de MySQL lo importante es determinar cuál es la condición que define la consecutividad entre dos registros, teniendo cuidado si hay huecos. En el caso de Oracle, simplemente es encontrar una relación de precedencia entre dos registros.
 
Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar
 bastante los informes analíticos: rankings, acumulados, porcentajes, etc.
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 EMPLEADOS para los
 ejemplos.
Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por
departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY: 
SELECT deptno, MAX(sal)
FROM scott.emp
GROUP BY deptno;
Pero necesitamos tambien una lista de los mejor pagados en cada departamento.
 Quizas necesitariamos una subquery:
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
--     -----  -----  ------------
20     SMITH    800          3000
30     ALLEN   1600          2850
30     WARD    1250          2850
20     JONES   2975          3000
30     MARTIN  1250          2850
30     BLAKE   2850          2850
10     CLARK   2450          5000
20     SCOTT   3000          3000
10     KING    5000          5000
30     TURNER  1500          2850
20     ADAMS   1100          3000
30     JAMES    950          2850
20     FORD    3000          3000
10     MILLER  1300          5000
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     JONES   2975          3000
20     FORD    3000          3000
20     ADAMS   1100          3000
20     SMITH    800          3000
20     SCOTT   3000          3000
30     WARD    1250          2850
30     TURNER  1500          2850
30     ALLEN   1600          2850
30     JAMES    950          2850
30     BLAKE   2850          2850
30     MARTIN  1250          2850
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. Ver punto funciones agregadas.
• 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
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   1600     2
30     TURNER  1500     3
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,3333333333333
10     CLARK    2450 66,6666666666667
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,6666666666667
30     MARTIN   1250 50             
30     WARD     1250 50             
30     TURNER   1500 66,6666666666667
30     ALLEN    1600 83,3333333333333
30     BLAKE    2850 100            
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/12/1980     800     800 
30     20/02/1981    1600    2400
30     22/02/1981    1250    3650
20     02/04/1981    2975    6625
30     01/05/1981    2850    9475
10     09/06/1981    2450   11925
30     08/09/1981    1500   13425
30     28/09/1981    1250   14675
10     17/11/1981    5000   19675
30     03/12/1981     950   20625
20     03/12/1981    3000   23625
10     23/01/1982    1300   24925
20     09/12/1982    3000   27925
20     12/01/1983    1100   29025
Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior
 respectivamente del registro en el que estamos.
El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento, 
inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada 
departamento con mayor sueldo.
 
SELECT
   deptno dept,
   job,
   ename name,
   sal,
   dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END seniority_sal
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT   JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL
-----  ---------  ------  ----  ---------  ----------  ----------------
10     CLERK      MILLER  1300      3      23/01/1982        Yes
10     MANAGER    CLARK   2450      2      09/06/1981        No
10     PRESIDENT  KING    5000      1      17/11/1981        Yes
20     CLERK      SMITH   800       4      17/12/1980        No
20     CLERK      ADAMS   1100      3      12/01/1983        Yes
20     MANAGER    JONES   2975      2      02/04/1981        No
20     ANALYST    FORD    3000      1      03/12/1981        Yes
20     ANALYST    SCOTT   3000      1      09/12/1982        Yes
30     CLERK      JAMES   950       5      03/12/1981        Yes
30     SALESMAN   WARD    1250      4      22/02/1981        No
30     SALESMAN   MARTIN  1250      4      28/09/1981        Yes
30     SALESMAN   TURNER  1500      3      08/09/1981        Yes
30     SALESMAN   ALLEN   1600      2      20/02/1981        Yes
30     MANAGER    BLAKE   2850      1      01/05/1981        Yes

La clave de esta query es la función: 
LEAD(sal) OVER(PARTITION BY deptno ORDER BY hire_date)
Para que esta query funcione es obligatorio al igual que en RANK y DENSE_RANK introducir un orden.

Como vemos en la sintaxis de la función analítica la orden que se elige para una función es totalmente independiente de la orden que se elige para otra dentro de la misma query. Por lo tanto, su llamada para Lead mira la próxima hilera ordenada por hireate, mientras que su llamada para DENSE_RANK todavía clasifica hileras por sueldo.

Una consulta un poco mas complicada podría ser el cálculo de la media arítmetica del salario de los empleados contratados en un periodo comprendido entre los 6 anteriores y porteriores meses con respecto a la fecha de contratación de uno dado. Como vemos el periodo de comparación varía según la fecha de contratación del empleado que estemos tratando.

Para realizar esta query necesitamos el resultado de una función agregada que nos devuelva valores sobre un conjunto de datos variable por periodo de contratación.
ROUND(AVG(sal) OVER
   (
    ORDER BY hiredate
    RANGE BETWEEN
       hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
       AND
       ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
    ) moving_average

Las claves de esta función analítica son las siguientes:
1. La clausula RANGE BETWEEN define una especie de ventana movible que se calcula por cada
registro que nos devuelve la query.
2. La clausula ORDER BY hire_date especifica que hire_date es el campo clave para la ventana movible.
3. Las dos expresiones en la clausula RANGE BETWEEN una de PRECEDING y otro de FOLLOWING, nos establecen un periodo de aproximadamente 180 días cada una sobre el hire_date del registro en el que estemos.
Cuando la query se ejecuta, el  motor de base de datos resta 6 meses al valor de la fecha de contratación (hire_date) para determinar el comienzo de la ventana movible. Igualmente, la base de datos añade 6 meses al valor de hire_date para determinar el final de la ventana movible. Esta resta y suma se realiza por cada registro que nos devuelve la query.
4. No se ha incluido la clausula PARTITION BY por que queremos calcular la media de todos los empleados. Podríamos haver añadido PARTITION BY deptno si hubieramos querido que la media sea considerada por cada empleado dentro de cada departamento.
5. La función ROUND redondea el resultado a entero. 
SELECT
   deptno dept,
   job,
   ename name,
   sal, dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END
   seniority_sal,
   moving_average
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank,
       ROUND(AVG(sal) OVER
                         (ORDER BY hiredate
                          RANGE BETWEEN hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
                          AND ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
            ) moving_average
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT  JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL  MOVING_AVERAGE
----  ---------  ------  ----  ---------  ----------  -------------  --------------
10    CLERK      MILLER  1300      3      23/01/1982       Yes            2167
10    MANAGER    CLARK   2450      2      09/06/1981       No             2148
10    PRESIDENT  KING    5000      1      17/11/1981       Yes            2207
20    CLERK      SMITH   800       4      17/12/1980       No             1988
20    CLERK      ADAMS   1100      3      12/01/1983       Yes            2050
20    MANAGER    JONES   2975      2      02/04/1981       No             1834
20    ANALYST    FORD    3000      1      03/12/1981       Yes            2207
20    ANALYST    SCOTT   3000      1      09/12/1982       Yes            2050
30    CLERK      JAMES   950       5      03/12/1981       Yes            2207
30    SALESMAN   WARD    1250      4      22/02/1981       No             1988
30    SALESMAN   MARTIN  1250      4      28/09/1981       Yes            2364
30    SALESMAN   TURNER  1500      3      08/09/1981       Yes            2364
30    SALESMAN   ALLEN   1600      2      20/02/1981       Yes            1988
30    MANAGER    BLAKE   2850      1      01/05/1981       Yes            1834
Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar bastante los informes analíticos: rankings, acumulados, porcentajes, etc.
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 EMPLEADOS para los ejemplos.
Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY: 
SELECT deptno, MAX(sal)
FROM scott.emp
GROUP BY deptno;
Pero necesitamos tambien una lista de los mejor pagados en cada departamento. Quizas necesitariamos una subquery:
select
   a.deptno dp,
   a.ename,
   a.sal,
   b.dept_max_sal
from
scott.emp a,

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar bastante los informes analíticos: rankings, acumulados, porcentajes, etc.
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 EMPLEADOS para los ejemplos.
Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY: 
SELECT deptno, MAX(sal)
FROM scott.emp
GROUP BY deptno;
Pero necesitamos tambien una lista de los mejor pagados en cada departamento. Quizas necesitariamos una subquery:
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
--     -----  -----  ------------
20     SMITH    800          3000
30     ALLEN   1600          2850
30     WARD    1250          2850
20     JONES   2975          3000
30     MARTIN  1250          2850
30     BLAKE   2850          2850
10     CLARK   2450          5000
20     SCOTT   3000          3000
10     KING    5000          5000
30     TURNER  1500          2850
20     ADAMS   1100          3000
30     JAMES    950          2850
20     FORD    3000          3000
10     MILLER  1300          5000
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     JONES   2975          3000
20     FORD    3000          3000
20     ADAMS   1100          3000
20     SMITH    800          3000
20     SCOTT   3000          3000
30     WARD    1250          2850
30     TURNER  1500          2850
30     ALLEN   1600          2850
30     JAMES    950          2850
30     BLAKE   2850          2850
30     MARTIN  1250          2850
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. Ver punto funciones agregadas.
• 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
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   1600     2
30     TURNER  1500     3
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,3333333333333
10     CLARK    2450 66,6666666666667
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,6666666666667
30     MARTIN   1250 50             
30     WARD     1250 50             
30     TURNER   1500 66,6666666666667
30     ALLEN    1600 83,3333333333333
30     BLAKE    2850 100            
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/12/1980     800     800 
30     20/02/1981    1600    2400
30     22/02/1981    1250    3650
20     02/04/1981    2975    6625
30     01/05/1981    2850    9475
10     09/06/1981    2450   11925
30     08/09/1981    1500   13425
30     28/09/1981    1250   14675
10     17/11/1981    5000   19675
30     03/12/1981     950   20625
20     03/12/1981    3000   23625
10     23/01/1982    1300   24925
20     09/12/1982    3000   27925
20     12/01/1983    1100   29025
Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior
respectivamente del registro en el que estamos.
El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento,
 inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada 
departamento con mayor sueldo.
SELECT
   deptno dept,
   job,
   ename name,
   sal,
   dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END seniority_sal
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT   JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL
-----  ---------  ------  ----  ---------  ----------  ----------------
10     CLERK      MILLER  1300      3      23/01/1982        Yes
10     MANAGER    CLARK   2450      2      09/06/1981        No
10     PRESIDENT  KING    5000      1      17/11/1981        Yes
20     CLERK      SMITH   800       4      17/12/1980        No
20     CLERK      ADAMS   1100      3      12/01/1983        Yes
20     MANAGER    JONES   2975      2      02/04/1981        No
20     ANALYST    FORD    3000      1      03/12/1981        Yes
20     ANALYST    SCOTT   3000      1      09/12/1982        Yes
30     CLERK      JAMES   950       5      03/12/1981        Yes
30     SALESMAN   WARD    1250      4      22/02/1981        No
30     SALESMAN   MARTIN  1250      4      28/09/1981        Yes
30     SALESMAN   TURNER  1500      3      08/09/1981        Yes
30     SALESMAN   ALLEN   1600      2      20/02/1981        Yes
30     MANAGER    BLAKE   2850      1      01/05/1981        Yes

La clave de esta query es la función: 
LEAD(sal) OVER(PARTITION BY deptno ORDER BY hire_date)
Para que esta query funcione es obligatorio al igual que en RANK y DENSE_RANK introducir un orden.

Como vemos en la sintaxis de la función analítica la orden que se elige para una función es totalmente independiente de la orden que se elige para otra dentro de la misma query. Por lo tanto, su llamada para Lead mira la próxima hilera ordenada por hireate, mientras que su llamada para DENSE_RANK todavía clasifica hileras por sueldo.

Una consulta un poco mas complicada podría ser el cálculo de la media arítmetica del salario de los empleados contratados en un periodo comprendido entre los 6 anteriores y porteriores meses con respecto a la fecha de contratación de uno dado. Como vemos el periodo de comparación varía según la fecha de contratación del empleado que estemos tratando.

Para realizar esta query necesitamos el resultado de una función agregada que nos devuelva valores sobre un conjunto de datos variable por periodo de contratación.
ROUND(AVG(sal) OVER
   (
    ORDER BY hiredate
    RANGE BETWEEN
       hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
       AND
       ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
    ) moving_average

Las claves de esta función analítica son las siguientes:
1. La clausula RANGE BETWEEN define una especie de ventana movible que se calcula por cada
registro que nos devuelve la query.
2. La clausula ORDER BY hire_date especifica que hire_date es el campo clave para la ventana movible.
3. Las dos expresiones en la clausula RANGE BETWEEN una de PRECEDING y otro de FOLLOWING, nos establecen un periodo de aproximadamente 180 días cada una sobre el hire_date del registro en el que estemos.
Cuando la query se ejecuta, el  motor de base de datos resta 6 meses al valor de la fecha de contratación (hire_date) para determinar el comienzo de la ventana movible. Igualmente, la base de datos añade 6 meses al valor de hire_date para determinar el final de la ventana movible. Esta resta y suma se realiza por cada registro que nos devuelve la query.
4. No se ha incluido la clausula PARTITION BY por que queremos calcular la media de todos los empleados. Podríamos haver añadido PARTITION BY deptno si hubieramos querido que la media sea considerada por cada empleado dentro de cada departamento.
5. La función ROUND redondea el resultado a entero. 
SELECT
   deptno dept,
   job,
   ename name,
   sal, dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END
   seniority_sal,
   moving_average
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank,
       ROUND(AVG(sal) OVER
                         (ORDER BY hiredate
                          RANGE BETWEEN hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
                          AND ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
            ) moving_average
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT  JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL  MOVING_AVERAGE
----  ---------  ------  ----  ---------  ----------  -------------  --------------
10    CLERK      MILLER  1300      3      23/01/1982       Yes            2167
10    MANAGER    CLARK   2450      2      09/06/1981       No             2148
10    PRESIDENT  KING    5000      1      17/11/1981       Yes            2207
20    CLERK      SMITH   800       4      17/12/1980       No             1988
20    CLERK      ADAMS   1100      3      12/01/1983       Yes            2050
20    MANAGER    JONES   2975      2      02/04/1981       No             1834
20    ANALYST    FORD    3000      1      03/12/1981       Yes            2207
20    ANALYST    SCOTT   3000      1      09/12/1982       Yes            2050
30    CLERK      JAMES   950       5      03/12/1981       Yes            2207
30    SALESMAN   WARD    1250      4      22/02/1981       No             1988
30    SALESMAN   MARTIN  1250      4      28/09/1981       Yes            2364
30    SALESMAN   TURNER  1500      3      08/09/1981       Yes            2364
30    SALESMAN   ALLEN   1600      2      20/02/1981       Yes            1988
30    MANAGER    BLAKE   2850      1      01/05/1981       Yes            1834

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar
bastante los informes analíticos: rankings, acumulados, porcentajes, etc.
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 EMPLEADOS para los
ejemplos.
Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por
departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY: 
SELECT deptno, MAX(sal)
FROM scott.emp
GROUP BY deptno;
Pero necesitamos tambien una lista de los mejor pagados en cada departamento. Quizas
necesitariamos una subquery:
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
--     -----  -----  ------------
20     SMITH    800          3000
30     ALLEN   1600          2850
30     WARD    1250          2850
20     JONES   2975          3000
30     MARTIN  1250          2850
30     BLAKE   2850          2850
10     CLARK   2450          5000
20     SCOTT   3000          3000
10     KING    5000          5000
30     TURNER  1500          2850
20     ADAMS   1100          3000
30     JAMES    950          2850
20     FORD    3000          3000
10     MILLER  1300          5000
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     JONES   2975          3000
20     FORD    3000          3000
20     ADAMS   1100          3000
20     SMITH    800          3000
20     SCOTT   3000          3000
30     WARD    1250          2850
30     TURNER  1500          2850
30     ALLEN   1600          2850
30     JAMES    950          2850
30     BLAKE   2850          2850
30     MARTIN  1250          2850
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. Ver punto funciones agregadas.
• 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
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   1600     2
30     TURNER  1500     3
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,3333333333333
10     CLARK    2450 66,6666666666667
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,6666666666667
30     MARTIN   1250 50             
30     WARD     1250 50             
30     TURNER   1500 66,6666666666667
30     ALLEN    1600 83,3333333333333
30     BLAKE    2850 100            
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/12/1980     800     800 
30     20/02/1981    1600    2400
30     22/02/1981    1250    3650
20     02/04/1981    2975    6625
30     01/05/1981    2850    9475
10     09/06/1981    2450   11925
30     08/09/1981    1500   13425
30     28/09/1981    1250   14675
10     17/11/1981    5000   19675
30     03/12/1981     950   20625
20     03/12/1981    3000   23625
10     23/01/1982    1300   24925
20     09/12/1982    3000   27925
20     12/01/1983    1100   29025
Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior
 respectivamente del registro en el que estamos.
El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento, 
inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada
 departamento con mayor sueldo.
SELECT
   deptno dept,
   job,
   ename name,
   sal,
   dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END seniority_sal
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT   JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL
-----  ---------  ------  ----  ---------  ----------  ----------------
10     CLERK      MILLER  1300      3      23/01/1982        Yes
10     MANAGER    CLARK   2450      2      09/06/1981        No
10     PRESIDENT  KING    5000      1      17/11/1981        Yes
20     CLERK      SMITH   800       4      17/12/1980        No
20     CLERK      ADAMS   1100      3      12/01/1983        Yes
20     MANAGER    JONES   2975      2      02/04/1981        No
20     ANALYST    FORD    3000      1      03/12/1981        Yes
20     ANALYST    SCOTT   3000      1      09/12/1982        Yes
30     CLERK      JAMES   950       5      03/12/1981        Yes
30     SALESMAN   WARD    1250      4      22/02/1981        No
30     SALESMAN   MARTIN  1250      4      28/09/1981        Yes
30     SALESMAN   TURNER  1500      3      08/09/1981        Yes
30     SALESMAN   ALLEN   1600      2      20/02/1981        Yes
30     MANAGER    BLAKE   2850      1      01/05/1981        Yes

La clave de esta query es la función: 
LEAD(sal) OVER(PARTITION BY deptno ORDER BY hire_date)
Para que esta query funcione es obligatorio al igual que en RANK y DENSE_RANK introducir un orden.

Como vemos en la sintaxis de la función analítica la orden que se elige para una función es totalmente independiente de la orden que se elige para otra dentro de la misma query. Por lo tanto, su llamada para Lead mira la próxima hilera ordenada por hireate, mientras que su llamada para DENSE_RANK todavía clasifica hileras por sueldo.

Una consulta un poco mas complicada podría ser el cálculo de la media arítmetica del salario de los empleados contratados en un periodo comprendido entre los 6 anteriores y porteriores meses con respecto a la fecha de contratación de uno dado. Como vemos el periodo de comparación varía según la fecha de contratación del empleado que estemos tratando.

Para realizar esta query necesitamos el resultado de una función agregada que nos devuelva valores sobre un conjunto de datos variable por periodo de contratación.
ROUND(AVG(sal) OVER
   (
    ORDER BY hiredate
    RANGE BETWEEN
       hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
       AND
       ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
    ) moving_average

Las claves de esta función analítica son las siguientes:
1. La clausula RANGE BETWEEN define una especie de ventana movible que se calcula por cada
registro que nos devuelve la query.
2. La clausula ORDER BY hire_date especifica que hire_date es el campo clave para la ventana movible.
3. Las dos expresiones en la clausula RANGE BETWEEN una de PRECEDING y otro de FOLLOWING, nos establecen un periodo de aproximadamente 180 días cada una sobre el hire_date del registro en el que estemos.
Cuando la query se ejecuta, el  motor de base de datos resta 6 meses al valor de la fecha de contratación (hire_date) para determinar el comienzo de la ventana movible. Igualmente, la base de datos añade 6 meses al valor de hire_date para determinar el final de la ventana movible. Esta resta y suma se realiza por cada registro que nos devuelve la query.
4. No se ha incluido la clausula PARTITION BY por que queremos calcular la media de todos los empleados. Podríamos haver añadido PARTITION BY deptno si hubieramos querido que la media sea considerada por cada empleado dentro de cada departamento.
5. La función ROUND redondea el resultado a entero. 
SELECT
   deptno dept,
   job,
   ename name,
   sal, dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END
   seniority_sal,
   moving_average
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank,
       ROUND(AVG(sal) OVER
                         (ORDER BY hiredate
                          RANGE BETWEEN hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
                          AND ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
            ) moving_average
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT  JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL  MOVING_AVERAGE
----  ---------  ------  ----  ---------  ----------  -------------  --------------
10    CLERK      MILLER  1300      3      23/01/1982       Yes            2167
10    MANAGER    CLARK   2450      2      09/06/1981       No             2148
10    PRESIDENT  KING    5000      1      17/11/1981       Yes            2207
20    CLERK      SMITH   800       4      17/12/1980       No             1988
20    CLERK      ADAMS   1100      3      12/01/1983       Yes            2050
20    MANAGER    JONES   2975      2      02/04/1981       No             1834
20    ANALYST    FORD    3000      1      03/12/1981       Yes            2207
20    ANALYST    SCOTT   3000      1      09/12/1982       Yes            2050
30    CLERK      JAMES   950       5      03/12/1981       Yes            2207
30    SALESMAN   WARD    1250      4      22/02/1981       No             1988
30    SALESMAN   MARTIN  1250      4      28/09/1981       Yes            2364
30    SALESMAN   TURNER  1500      3      08/09/1981       Yes            2364
30    SALESMAN   ALLEN   1600      2      20/02/1981       Yes            1988
30    MANAGER    BLAKE   2850      1      01/05/1981       Yes            1834

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar
 bastante los informes analíticos: rankings, acumulados, porcentajes, etc.
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 EMPLEADOS para
 los ejemplos.
Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por
 departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY: 
SELECT deptno, MAX(sal)
FROM scott.emp
GROUP BY deptno;
Pero necesitamos tambien una lista de los mejor pagados en cada departamento.
Quizas necesitariamos una subquery:
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
--     -----  -----  ------------
20     SMITH    800          3000
30     ALLEN   1600          2850
30     WARD    1250          2850
20     JONES   2975          3000
30     MARTIN  1250          2850
30     BLAKE   2850          2850
10     CLARK   2450          5000
20     SCOTT   3000          3000
10     KING    5000          5000
30     TURNER  1500          2850
20     ADAMS   1100          3000
30     JAMES    950          2850
20     FORD    3000          3000
10     MILLER  1300          5000
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     JONES   2975          3000
20     FORD    3000          3000
20     ADAMS   1100          3000
20     SMITH    800          3000
20     SCOTT   3000          3000
30     WARD    1250          2850
30     TURNER  1500          2850
30     ALLEN   1600          2850
30     JAMES    950          2850
30     BLAKE   2850          2850
30     MARTIN  1250          2850
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. Ver punto funciones agregadas.
• 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
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   1600     2
30     TURNER  1500     3
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,3333333333333
10     CLARK    2450 66,6666666666667
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,6666666666667
30     MARTIN   1250 50             
30     WARD     1250 50             
30     TURNER   1500 66,6666666666667
30     ALLEN    1600 83,3333333333333
30     BLAKE    2850 100            
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/12/1980     800     800 
30     20/02/1981    1600    2400
30     22/02/1981    1250    3650
20     02/04/1981    2975    6625
30     01/05/1981    2850    9475
10     09/06/1981    2450   11925
30     08/09/1981    1500   13425
30     28/09/1981    1250   14675
10     17/11/1981    5000   19675
30     03/12/1981     950   20625
20     03/12/1981    3000   23625
10     23/01/1982    1300   24925
20     09/12/1982    3000   27925
20     12/01/1983    1100   29025
Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior
respectivamente del registro en el que estamos.
El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento, 
inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada
 departamento con mayor sueldo.
SELECT
   deptno dept,
   job,
   ename name,
   sal,
   dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END seniority_sal
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT   JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL
-----  ---------  ------  ----  ---------  ----------  ----------------
10     CLERK      MILLER  1300      3      23/01/1982        Yes
10     MANAGER    CLARK   2450      2      09/06/1981        No
10     PRESIDENT  KING    5000      1      17/11/1981        Yes
20     CLERK      SMITH   800       4      17/12/1980        No
20     CLERK      ADAMS   1100      3      12/01/1983        Yes
20     MANAGER    JONES   2975      2      02/04/1981        No
20     ANALYST    FORD    3000      1      03/12/1981        Yes
20     ANALYST    SCOTT   3000      1      09/12/1982        Yes
30     CLERK      JAMES   950       5      03/12/1981        Yes
30     SALESMAN   WARD    1250      4      22/02/1981        No
30     SALESMAN   MARTIN  1250      4      28/09/1981        Yes
30     SALESMAN   TURNER  1500      3      08/09/1981        Yes
30     SALESMAN   ALLEN   1600      2      20/02/1981        Yes
30     MANAGER    BLAKE   2850      1      01/05/1981        Yes

La clave de esta query es la función: 
LEAD(sal) OVER(PARTITION BY deptno ORDER BY hire_date)
Para que esta query funcione es obligatorio al igual que en RANK y DENSE_RANK introducir un orden.

Como vemos en la sintaxis de la función analítica la orden que se elige para una función es totalmente independiente de la orden que se elige para otra dentro de la misma query. Por lo tanto, su llamada para Lead mira la próxima hilera ordenada por hireate, mientras que su llamada para DENSE_RANK todavía clasifica hileras por sueldo.

Una consulta un poco mas complicada podría ser el cálculo de la media arítmetica del salario de los empleados contratados en un periodo comprendido entre los 6 anteriores y porteriores meses con respecto a la fecha de contratación de uno dado. Como vemos el periodo de comparación varía según la fecha de contratación del empleado que estemos tratando.

Para realizar esta query necesitamos el resultado de una función agregada que nos devuelva valores sobre un conjunto de datos variable por periodo de contratación.
ROUND(AVG(sal) OVER
   (
    ORDER BY hiredate
    RANGE BETWEEN
       hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
       AND
       ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
    ) moving_average

Las claves de esta función analítica son las siguientes:
1. La clausula RANGE BETWEEN define una especie de ventana movible que se calcula por cada
registro que nos devuelve la query.
2. La clausula ORDER BY hire_date especifica que hire_date es el campo clave para la ventana movible.
3. Las dos expresiones en la clausula RANGE BETWEEN una de PRECEDING y otro de FOLLOWING, nos establecen un periodo de aproximadamente 180 días cada una sobre el hire_date del registro en el que estemos.
Cuando la query se ejecuta, el  motor de base de datos resta 6 meses al valor de la fecha de contratación (hire_date) para determinar el comienzo de la ventana movible. Igualmente, la base de datos añade 6 meses al valor de hire_date para determinar el final de la ventana movible. Esta resta y suma se realiza por cada registro que nos devuelve la query.
4. No se ha incluido la clausula PARTITION BY por que queremos calcular la media de todos los empleados. Podríamos haver añadido PARTITION BY deptno si hubieramos querido que la media sea considerada por cada empleado dentro de cada departamento.
5. La función ROUND redondea el resultado a entero. 
SELECT
   deptno dept,
   job,
   ename name,
   sal, dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END
   seniority_sal,
   moving_average
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank,
       ROUND(AVG(sal) OVER
                         (ORDER BY hiredate
                          RANGE BETWEEN hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
                          AND ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
            ) moving_average
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT  JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL  MOVING_AVERAGE
----  ---------  ------  ----  ---------  ----------  -------------  --------------
10    CLERK      MILLER  1300      3      23/01/1982       Yes            2167
10    MANAGER    CLARK   2450      2      09/06/1981       No             2148
10    PRESIDENT  KING    5000      1      17/11/1981       Yes            2207
20    CLERK      SMITH   800       4      17/12/1980       No             1988
20    CLERK      ADAMS   1100      3      12/01/1983       Yes            2050
20    MANAGER    JONES   2975      2      02/04/1981       No             1834
20    ANALYST    FORD    3000      1      03/12/1981       Yes            2207
20    ANALYST    SCOTT   3000      1      09/12/1982       Yes            2050
30    CLERK      JAMES   950       5      03/12/1981       Yes            2207
30    SALESMAN   WARD    1250      4      22/02/1981       No             1988
30    SALESMAN   MARTIN  1250      4      28/09/1981       Yes            2364
30    SALESMAN   TURNER  1500      3      08/09/1981       Yes            2364
30    SALESMAN   ALLEN   1600      2      20/02/1981       Yes            1988
30    MANAGER    BLAKE   2850      1      01/05/1981       Yes            1834
   (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
--     -----  -----  ------------
20     SMITH    800          3000
30     ALLEN   1600          2850
30     WARD    1250          2850
20     JONES   2975          3000
30     MARTIN  1250          2850
30     BLAKE   2850          2850
10     CLARK   2450          5000
20     SCOTT   3000          3000
10     KING    5000          5000
30     TURNER  1500          2850
20     ADAMS   1100          3000
30     JAMES    950          2850
20     FORD    3000          3000
10     MILLER  1300          5000
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     JONES   2975          3000
20     FORD    3000          3000
20     ADAMS   1100          3000
20     SMITH    800          3000
20     SCOTT   3000          3000
30     WARD    1250          2850
30     TURNER  1500          2850
30     ALLEN   1600          2850
30     JAMES    950          2850
30     BLAKE   2850          2850
30     MARTIN  1250          2850

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. Ver punto funciones agregadas.
• 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

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   1600     2
30     TURNER  1500     3
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,3333333333333
10     CLARK    2450 66,6666666666667
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,6666666666667
30     MARTIN   1250 50             
30     WARD     1250 50             
30     TURNER   1500 66,6666666666667
30     ALLEN    1600 83,3333333333333
30     BLAKE    2850 100            
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/12/1980     800     800 
30     20/02/1981    1600    2400
30     22/02/1981    1250    3650
20     02/04/1981    2975    6625
30     01/05/1981    2850    9475
10     09/06/1981    2450   11925
30     08/09/1981    1500   13425
30     28/09/1981    1250   14675
10     17/11/1981    5000   19675
30     03/12/1981     950   20625
20     03/12/1981    3000   23625
10     23/01/1982    1300   24925
20     09/12/1982    3000   27925
20     12/01/1983    1100   29025

Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior respectivamente del registro en el que estamos.
El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento, 
inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada 
departamento con mayor sueldo.
SELECT
   deptno dept,
   job,
   ename name,
   sal,
   dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END seniority_sal
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT   JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL
-----  ---------  ------  ----  ---------  ----------  ----------------
10     CLERK      MILLER  1300      3      23/01/1982        Yes
10     MANAGER    CLARK   2450      2      09/06/1981        No
10     PRESIDENT  KING    5000      1      17/11/1981        Yes
20     CLERK      SMITH   800       4      17/12/1980        No
20     CLERK      ADAMS   1100      3      12/01/1983        Yes
20     MANAGER    JONES   2975      2      02/04/1981        No
20     ANALYST    FORD    3000      1      03/12/1981        Yes
20     ANALYST    SCOTT   3000      1      09/12/1982        Yes
30     CLERK      JAMES   950       5      03/12/1981        Yes
30     SALESMAN   WARD    1250      4      22/02/1981        No
30     SALESMAN   MARTIN  1250      4      28/09/1981        Yes
30     SALESMAN   TURNER  1500      3      08/09/1981        Yes
30     SALESMAN   ALLEN   1600      2      20/02/1981        Yes
30     MANAGER    BLAKE   2850      1      01/05/1981        Yes

La clave de esta query es la función: 
LEAD(sal) OVER(PARTITION BY deptno ORDER BY hire_date)
Para que esta query funcione es obligatorio al igual que en RANK y DENSE_RANK introducir un orden.


Como vemos en la sintaxis de la función analítica la orden que se elige para una función es totalmente independiente de la orden que se elige para otra dentro de la misma query. Por lo tanto, su llamada para Lead mira la próxima hilera ordenada por hireate, mientras que su llamada para DENSE_RANK todavía clasifica hileras por sueldo.

Una consulta un poco mas complicada podría ser el cálculo de la media arítmetica del salario de los empleados contratados en un periodo comprendido entre los 6 anteriores y porteriores meses con respecto a la fecha de contratación de uno dado. Como vemos el periodo de comparación varía según la fecha de contratación del empleado que estemos tratando.

Para realizar esta query necesitamos el resultado de una función agregada que nos devuelva valores sobre un conjunto de datos variable por periodo de contratación.
ROUND(AVG(sal) OVER
   (
    ORDER BY hiredate
    RANGE BETWEEN
       hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
       AND
       ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
    ) moving_average

Las claves de esta función analítica son las siguientes:
1. La clausula RANGE BETWEEN define una especie de ventana movible que se calcula por cada
registro que nos devuelve la query.
2. La clausula ORDER BY hire_date especifica que hire_date es el campo clave para la ventana movible.
3. Las dos expresiones en la clausula RANGE BETWEEN una de PRECEDING y otro de FOLLOWING, nos establecen un periodo de aproximadamente 180 días cada una sobre el hire_date del registro en el que estemos.
Cuando la query se ejecuta, el  motor de base de datos resta 6 meses al valor de la fecha de contratación (hire_date) para determinar el comienzo de la ventana movible. Igualmente, la base de datos añade 6 meses al valor de hire_date para determinar el final de la ventana movible. Esta resta y suma se realiza por cada registro que nos devuelve la query.
4. No se ha incluido la clausula PARTITION BY por que queremos calcular la media de todos los empleados. Podríamos haver añadido PARTITION BY deptno si hubieramos querido que la media sea considerada por cada empleado dentro de cada departamento.
5. La función ROUND redondea el resultado a entero. 

SELECT
   deptno dept,
   job,
   ename name,
   sal, dept_rank,
   hiredate,
   CASE WHEN
           sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
        THEN 'No'
        ELSE 'Yes'
   END
   seniority_sal,
   moving_average
FROM
   (SELECT
       deptno,
       job,
       ename,
       hiredate,
       sal,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank,
       ROUND(AVG(sal) OVER
                         (ORDER BY hiredate
                          RANGE BETWEEN hiredate - ADD_MONTHS(hiredate,-6) PRECEDING
                          AND ADD_MONTHS(hiredate,6) - hiredate FOLLOWING)
            ) moving_average
    FROM
       scott.emp
   )
WHERE
   dept_rank <= 5
ORDER BY
   dept,
   dept_rank DESC;
DEPT  JOB        NAME    SAL   DEPT_RANK  HIREDATE    SENIORITY_SAL  MOVING_AVERAGE
----  ---------  ------  ----  ---------  ----------  -------------  --------------
10    CLERK      MILLER  1300      3      23/01/1982       Yes            2167
10    MANAGER    CLARK   2450      2      09/06/1981       No             2148
10    PRESIDENT  KING    5000      1      17/11/1981       Yes            2207
20    CLERK      SMITH   800       4      17/12/1980       No             1988
20    CLERK      ADAMS   1100      3      12/01/1983       Yes            2050
20    MANAGER    JONES   2975      2      02/04/1981       No             1834
20    ANALYST    FORD    3000      1      03/12/1981       Yes            2207
20    ANALYST    SCOTT   3000      1      09/12/1982       Yes            2050
30    CLERK      JAMES   950       5      03/12/1981       Yes            2207
30    SALESMAN   WARD    1250      4      22/02/1981       No             1988
30    SALESMAN   MARTIN  1250      4      28/09/1981       Yes            2364
30    SALESMAN   TURNER  1500      3      08/09/1981       Yes            2364
30    SALESMAN   ALLEN   1600      2      20/02/1981       Yes            1988
30    MANAGER    BLAKE   2850      1      01/05/1981       Yes            1834

 
Publicado por en No hay comentarios:
Suscribirse a: Entradas (Atom)