funciones avanzadas

SQL - consultas jerarquicas usando la cláusula START WITH y CONNECT BY

En algunas ocasiones, es necesario recuperar los datos de una tabla cuya relación es recursiva, para mostrar pertenencias y/o jerarquías. Tales jerarquías

generalmente son representadas a través de estructuras tipo árbol, en donde la raíz (o nodo principal) tiene ramas (nodos descendientes) y cada una de estas

ramas puede a su vez generar más ramas, hasta llegar a un punto final en donde no hay mas divisiones. Esos nodos terminales son pues llamados también

hojas.

Para el ejemplo, supongamos que se tiene una tabla donde se tiene almacenados los datos familiares de personas en una tabla como la siguiente:

idPersona Nombres hijoDe

1 Juan Carlos

2 Luis 5

3 Pedro 5

4 Jaime Alberto 9

5 Olga María 1

6 Marta Edith 8

7 Lucía 9

8 Nelly 1

9 Felipe 8

10 Edgar Nelson 8

La anterior tabla, almacena los datos acerca de los padres de cada persona. De esta manera, es posible construir un árbol como el siguiente:

Desde la versión 9i, el lenguaje sql de oracle provee la posibilidad de recuperar los datos de una tabla, vista, o tabla externa, simulando el recorrido de un

árbol, el cual puede hacerse comenzando por la raía hasta llegar a todas las hojas, o desde las hojas hasta llegar a la raíz.

Por lo tanto, La consulta:

SELECT nombres, LEVEL

FROM persona

START WITH nombres='Juan Carlos'

CONNECT BY PRIOR idPersona = hijode;

Retornará el nombre de todas las personas y el id del respectivo padre; empezando por el nombre Juan Carlos. La Pseudocoluma LEVEL indica el nivel en el

que se encuentra el registro, tras haber construido el arbol. START WITH indica el registro raiz; es decir, donde empieza el arbol. CONNECT BY PRIOR indica las

columnas en las cuales existe la relación padre-hijo

Salida:

Si se desea recuperar la descendencia a partir de “Felipe”, la consulta sería:

Obsérvese que en la anterior consulta el valor de LEVEL para “Felipe” es igual a 1, ya en el árbol que construye esta consulta, el nodo “Felipe” se encuentra en

el primer nivel.

Si por el contrario, se desean conocer los padres de felipe, la consulta sería:

Obséverse en el anterior ejemplo, que el recorrido se hizo de abajo hacia arriba (en el árbol). El orden de las columnas en la clausula CONNECT BY PRIOR,

especifica el orden del recorrido. En el primer ejemplo se tenía:CONNECT BY PRIOR idPersona=hijoDe y se tuvo el recorrido del arbol de arriba a abajo. En el

ultimo ejemplo se tuvo CONNECT BY PRIOR hijoDe =idPersona, y el recorrido fue de abajo hacia arriba.

De igual manera, desde la versión 9i, fue incluida la función SYS_CONNECT_BY_PATH(), que concatena los valores de las ramas del arbol en el recorrido.

El siguiente es un ejemplo de su utilización:

Es muy fácil entonces hacer consultas jerárquicas con la utilización de la cláusula START WITH y CONNECT BY y la función SYS_CONNECT_BY_PATH.

Rollup Note: ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

The action of ROLLUP is straight forward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of time, region, and department (n=3), the result set will include rows at four aggregation levels.

Mejoras para la cláusula GROUP BYObjetivosDespués de completar este capítulo conocerá el uso de lo siguiente:Uso de la operación ROLLUP para obtener subtotalesUso de la operación CUBE para obtener operaciones cruzadasUso de la función GROUPING para identificar los valores de las filascreadas por las operaciones ROLLUP y CUBEUso de GROUPING SETS para producir un solo resultadoFunciones de GrupoSe puede usar la cláusula GROUP BY para dividir las filas en grupos las filasde una tabla. Se pueden usar las funciones de grupo para obtener informaciónresumida por cada grupo. Las funciones de grupo pueden estar en la selecciónde la lista y en las cláusulas ORDER BY y HAVING. El servidor de Oracleemplea las funciones de grupo para cada grupo de filas y obtiene un resultadopor cada grupo.Tipos de funciones de grupoCada función de grupo AVG, SUM, MAX, MIN, COUNT, STDDEV y VARIANCEaceptan un argumento. Las funciones AVG, SUM, STDDEV y VARIANCEtrabajan solo con valores numéricos. MAX y MIN pueden trabajar en números,caracteres y fechas. COUNT obtiene el número de filas no nulas para unaexpresión. En el ejemplo se calcula el salario promedio, desviación estándardel salario, número de empleados que tienen una comisión y la fecha decontratación máxima para aquellos empleados que su puesto inicie con ‘SA’.Parcial II: 4 Lección 172Normas para el uso de funciones de grupoLos tipos de datos para los argumentos pueden ser CHAR, VARCHAR2,NUMBER o DATE.Todas las funciones de grupo con excepción de COUNT(*) ignoranvalores nulos. Para sustituir un valor de valores nulos, utilice la funciónNVL. COUNT obtiene algún número o cero.El servidor de Oracle implícitamente ordena los resultadosascendentemente de acuerdo a los grupos de las columnasespecificadas, cuando se usa la cláusula GROUP BY. Para evitar esteordenamiento por defecto, se puede usar DESC en una cláusulaORDER BY.Revisando la cláusula GROUP BYEl ejemplo ilustra como evalúa el servidor de Oracle:La cláusula SELECT especifica que las siguientes columnas sonrecuperadas.o Las columnas DEPARTMENT_ID y JOB_ID de la tablaEMPLOYEES

o La suma de todos los salarios y el número de empleados en cadagrupo que se tiene especificado en la cláusula GROUP BYParcial II: 4 Lección 173La cláusula GROUP BY especifica como las filas serán agrupadas. Elsalario total y el número de empleados es calculado por cada puesto decada departamento. Las filas son agrupadas por departamento yagrupadas por puesto para cada departamento.La cláusula HAVINGLos grupos formados y las funciones de grupo son calculados antes de que lacláusula HAVING sea aplicada a los grupos. La cláusula HAVING puede estarantes de la cláusula GROUP BY, pero es recomendado poner primero lacláusula GROUP BY puesto que es más lógico.El servidor de Oracle ejecuta los siguientes pasos cuando se usa la cláusulaHAVING:1. Agrupa las filas2. Aplica las funciones de grupo a los grupos y despliega los grupos quecorresponden al criterio de la cláusula HAVINGParcial II: 4 Lección 174Agrupando con los operadores ROLLUP y CUBESe puede especificar los operadores ROLLUP y CUBE en la cláusula GROUPBY de una consulta. Agrupando con ROLLUP se produce un conjunto deresultados conteniendo la agrupación de filas y un subtotal de las mismas. Laoperación CUBE en la cláusula GROUP BY agrupa las filas seleccionadas conbase en los valores de todas las posibles combinaciones de expresiones en suespecificación y obtiene una fila con información del resumen por cada grupo.Se puede usar el operador CUBE para producir tabulaciones cruzadas de filas.Nota: Cuando se trabaja con ROLLUP y CUBE, asegúrese de que lascolumnas seguidas de la cláusula GROUP BY tengan significado, unacorrelación lógica con las otras; de otra manera los operadores obtieneninformación irrelevante.Los operadores ROLLUP y CUBE están disponibles solo en versiones deOracle8i y posteriores.Operador ROLLUPEl operador ROLLUP entrega totales y subtotales para expresiones con unasentencia GROUP BY. El operador ROLLUP puede ser usado por reportespara extraer información estadísticas y resúmenes del conjunto de resultados.Los conjuntos acumulados pueden ser usados en reportes y gráficas.El operador ROLLUP crea agrupaciones en una sola dirección de derecha aizquierda, a través de la lista de columnas especificadas en la cláusula GROUPBY. Si es utilizada la función acumula esas agrupaciones.Nota: Para producir subtotales en n dimensiones (que es, n columnas en lacláusula GROUP BY) sin un operador ROLLUP, n + 1 sentencias SELECTdeben ser ligadas con la cláusula UNION ALL. Esto hace que la ejecución de laconsulta sea ineficiente, puesto que cada sentencia SELECT causa un acceso

a la tabla. El operador ROLLUP recoge este resultado con un solo acceso a laParcial II: 4 Lección 175tabla. El operador ROLLUP es útil si hay varias columnas involucradas en lageneración de los subtotales.En el ejemplo anterior:El salario total por cada puesto en un departamento cuyo identificadordel departamento es menor que 60 es desplegado por la cláusulaGROUP BY (Etiqueta 1)El operador ROLLUP despliega:o El salario total para aquellos departamentos cuyoDEPARTMENT_ID es menor a 60 (Etiqueta 2)o El salario total para todos los departamentos cuyoDEPARTMENT_ID es menor que 60 sin tomar en cuenta elJOB_ID (Etiqueta 3)Todas las filas indicadas como 1 son filas regulares y todas las filasindicadas como 2 y 3 son filas totalizadas.El operador ROLLUP crea subtotales que van desde el nivel mas detalladohasta un gran total, siguiendo la lista de agrupación especificada en la cláusulaGROUP BY. Primero se calculan los valores de los grupos especificados en lacláusula GROUP BY (en el ejemplo, la suma de salarios agrupados en cadapuesto dentro de un departamento). Entonces se crea progresivamentesubtotales de alto nivel, moviéndose de derecha a izquierda a través de la listade columnas agrupadas. (En el ejemplo anterior, la suma de salarios por cadadepartamento es calculado seguido de la suma de salarios para todos losdepartamentos)Proporcionando n expresiones en el operador ROLLUP de la cláusulaGROUP BY, la operación obtiene n + 1 = 2 + 1 = 3 agrupaciones.Las filas basadas en los valores de las primeras n expresiones sonllamadas filas regulares y las otras son llamadas filas totalizadas.Parcial II: 4 Lección 176Operador CUBEEl operador CUBE es un interruptor adicional de la cláusula GROUP BY en unasentencia SELECT. El operador CUBE puede ser aplicado a todas lasfunciones de grupo, incluyendo AVG, SUM, MAX, MIN y COUNT. Esta esusada para producir un conjunto de resultados que típicamente son utilizadospara reportes cruzados. Mientras ROLLUP produce solo una parte de posiblessubtotales, CUBE produce subtotales para todas las posibles combinacionesde agrupaciones especificadas en la cláusula GROUP BY y un gran total.El operador CUBE es usado con una función de grupo para generar filasadicionales en un conjunto de resultados. Las columnas incluidas en la cláusulaGROUP BY son una referencia cruzada para producir un súper conjunto degrupos. Las funciones de grupo especificadas en la lista seleccionada sonaplicadas a esos grupos para producir valores resumidos para las filasagregadas. El número de grupos extra en el conjunto de resultados es

determinado por el número de columnas incluidas en la cláusula GROUP BY.En efecto, cada posible combinación de columnas o expresiones en la cláusulaGROUP BY es usada para producir nuevos grupos. Si tienes n columnas oexpresiones en una cláusula GROUP BY, puedes tener 2n posiblescombinaciones de nuevas columnas. Matemáticamente, estas combinacionesforman un cubo de n dimensiones, por lo que de allí proviene su nombre.Para usar aplicaciones o herramientas de programación, estos valoresagregados pueden ser provistos en gráficas y diagramas que conduzcan losresultados y relaciones visualmente y efectivamente.Parcial II: 4 Lección 177El resultado de la sentencia SELECT del ejemplo puede ser interpretado comosigue:El salario total para cada puesto en un departamento (para aquellosdepartamentos cuyo DEPARTMENT_ID sea menor a 60) es desplegadopor la cláusula GROUP BY (Etiqueta 1)El salario total para aquellos departamentos cuyo DEPARTMENT_ID esmenor que 60 (Etiqueta 2)El salario total para cada puesto sin tomar en cuenta el departamento(Etiqueta 3)El salario total para aquellos departamentos cuyo DEPARTMENT_ID esmenor a 60 independientemente del puesto (Etiqueta 4)En el ejemplo anterior, todas las filas indicadas con 1 son filas regulares, todaslas filas indicadas con 2 y 4 son filas totalizadas, y todas las filas indicadas con3 son valores cruzados.El operador CUBE tiene también un buen desempeño como el operadorROLLUP para desplegar subtotales para aquellos departamentos cuyoDEPARTMENT_ID es menor a 60 y el salario total para aquellosdepartamentos cuyo DEPARTMENT_ID es menor que 60, independientementede los puestos. Adicionalmente, el operador CUBE muestra el salario total paracada puesto independientemente del departamento.Nota: Similar al operador ROLLUP, produciendo subtotales en n dimensiones(que es n columnas en la cláusula GROUP BY) sin un operador CUBE serequiere 2n sentencias SELECT para ser relacionadas con UNION ALL. Así, unreporte con tres dimensiones requiere 23 = 8 sentencias SELECT unidas conUNION ALL.

Funciones Analíticas en Oracle

Oracle ha mejorado las capacidades de procesamiento analítico introduciendo una nueva familia de funciones SQL analíticas. Estas funciones analíticas le

permites a usted calcular:

• Funciones de Rangos y Percentíles

• Movimiento de ventanas de cálculos

• Análisis lag/lead

• Análisis Primero/Ultimo

• Estadísticas y Regresiones Lineales

Funciones Anal íticas y Uso :

Tipo Utilizada para

Rangos(Ranking) Cálculos de rangos, percentiles, y n-tiles de los valores en un conjunto de resultados.

Windowing Cálculos de acumulados y movimientos agregados. Trabajando con estas funciones : SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, y nuevas funciones estad ísticas.

Reporting Cálculos compartidos por ejemplo, mercados compartidos. Trabajando con estas funciones : SUM, AVG, MIN, MAX, COUNT (con o sin DISTINCT), VARIANCE, STDDEV, RATIO_TO_REPORT y nuevas funciones estad ísticas

Lag-Lead Encontrando un valor en una fila un número especifico de filas desde una fila actual.

First-Last Primer o Ultimo valor en un grupo ordenado

Regresión Lineal Calculando la regresión lineal y otras estadísticas (desviación, intercepción y otras)

Percentil Inverso El valor en un conjunto de datos que corresponde a un percentil especifico

Rango hipotético y distribución El rango o percentil que una fila debería tener si es insertada en un conjunto de datos específico.

Orden de Procesamiento

El procesamiento de las consultas utilizando funciones anal íticas se lleva a cabo en tres partes :

• Todos los JOINS, WHERE, GROUP BY y HAVING son realizadas.

• El resultado es hecho disponible a las funciones analíticas y es donde todos los cálculos toman lugar.

• Si la consulta tiene una cláusula ORDER BY, el ordenamiento es ejecutado para permitir el ordenamiento de los resultados.

Particiones del conjunto de resultados

Las funciones analíticas permiten a los usuarios dividir los conjuntos de resultados de una consulta en grupos de datos llamados particiones. El termino

partición utilizado en las consultas analíticas no tiene nada que ver con la característica del particionamiento de tablas en Oracle. Las particiones son creadas

después de que se definan los grupos con la cláusula GROUP BY, así que estos están disponibles para cualquier resultado agregado como Sumas, y Promedios.

Las divisiones de las particiones pueden estar basadas sobre las columnas o expresiones deseadas. El resultado de una consulta puede ser particionado en

solo una partición manteniendo todas las filas.

Ventana

Para cada fila en una partición, usted puede definir una ventana deslizante de datos. La ventana determina el rango de filas utilizado para realizar los cálculos

para la fila actual. Los tama ños de las ventanas pueden estar basados en cualquier grupo físico de números, o cualquier intervalo lógico como el tiempo. La

ventana tiene una fila de partida y una fila de fin dependiendo de esta definición la ventana puede tener uno o varios finales.

Fila actual

Cada cálculo realizado con una función analítica esta basado en la fila actual de una partición.

Funciones de Rango

Una función de rango calcula el rango de un registro comparado a otros registros en un conjunto de datos sobre los valores de un conjunto de medidas. Los

tipos de funciones de ranking son:

• Funciones de Rango y Rango Denso

• Funciones (Cume – Dist)

• Funciones de Rango de Porcentaje

• Funciones de NTILE

• Funciones del numero de filas (Row Number)

Funciones de Rango y Rango Denso

Las funciones de rango y rango denso le permiten a usted clasificar ítems dentro de un grupo, por ejemplo, encontrando los primeros tres productos vendidos

en un área durante el último a ño. Existen dos funciones que realizan el ranking como se muestra en la siguiente sintaxis :

• RANK () OVER ([ query_partition_clause] ORDER BY clause])

• DENSE_RANK () OVER ([query_partition_clause] ORDER BY clause])

La diferencia entre RANK y DENSE RANK es que en el DENSE RANK no deja brechas entre el rango de secuencias cuando hay empates. Es decir, si usted esta

clasificando un conjunto de datos usando DENSE_RANK y tiene tres elementos empatados en segundo lugar, usted podría decir que los tres estuvieron en

segundo lugar y que la siguiente persona estará en el tercer lugar. La función RANK también puede permitir a tres personas en segundo lugar pero la siguiente

persona que llegue estará en el quinto.

Puntos clave sobre los rangos son:

• El orden ascendente es el ordenamiento por defecto

• Las expresiones en la cl á usula PARTITION BY dividen el conjunto de resultados en grupos sobre los cuales opera la función RANK

• Si la cláusula PARTITION BY no esta definida en la sentencia los rangos son computados sobre el conjunto entero de datos (el conjunto entero de datos se

toma como un único rango).

• La cláusula ORDER BY especifica las medidas sobre las cuales se esta realizando la clasificación, y el orden en que deben ordenarse las columnas en cada

grupo.

• La cláusula NULLS FIRST | NULLS LAST indica la posición de los valores nulos dentro del rango de datos comparados con los valores no nulos.

EJEMPLO:

Listado de todos los empleados realizando una clasificaci ón según el salario y el departamento en el que labora cada empleado.

SELECT first_name , last_name , salary , department_id , DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

Listado de los empleados del departamento 90 realizando una clasificación seg ún el salario y el departamento en el que labora cada empleado (Utilizando

RANK).

SELECT first_name , last_name , salary , department_id , RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

WHERE department_id = 90 ;

Listado de los empleados del departamento 90 realizando una clasificación seg ún el salario y el departamento en el que labora cada empleado (Utilizando

DENSE_RANK).

SELECT first_name , last_name , salary , department_id , DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees


Funciones (Cume-Dist)

La funci ó n CUME_DIST definida por algunos libros de estadística como el percentil inverso, calcula la posición de un valor específico en relación a un conjunto

de valores. El orden puede ser ascendente o descendente. El ordenamiento por defecto es ascendente. El rango de valores para CUME_DIST va desde cero (0)

a uno (1). Para calcular el percentil inverso (CUME_DIST) de un valor x en un conjunto S de tama ño N, se utiliza la fórmula :

CUME_DIST (x) = numero de valores en S antes de x (incluyendo a x y en el orden específico) / N

La sintaxis en Oracle es:

CUME_DIST () OVER ([Query partition_clause ] ORDER BY clause)

EJEMPLO:

C álculo del percentil inverso para cada todo el grupo de empleados

SELECT first_name , last_name , salary , department_id , CUME_DIST () OVER (ORDER BY salary DESC) "Rank"

FROM employees

Cálculo del percentil inverso de los empleados particionando por el departamento al que pertenece el empleado.

SELECT first_name , last_name , salary , department_id , CUME_DIST () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

Funciones de rango de porcentaje (Percent-Rank)

PERCENT_RANK es similar a CUME_DIST, pero este usa rangos de valores a diferencia que el conteo de filas en este numerador. ???

Sin embargo, este retorna el rango de porcentaje de un valor relacionado a un grupo de valores. La función esta disponible en muchas hojas de c álculo

populares. El valor PERCENT_RANK de una fila es calculado como :

Rango de la fila en la partici ó n – 1 / numero de filas en la partición – 1

PERCENT_RANK retorna valores en el rango de cero a uno. Las filas con un rango de 1 tendr án un rango de porcentaje de cero (0 ). Su sintaxis es:

PERCENT_RANK OVER ([query_partition_clause] ORDER BY clause)

EJEMPLO:

Calcular el rango de porcentaje de todo el grupo de empleados.

SELECT first_name , last_name , salary , department_id ,

PERCENT_RANK () OVER (ORDER BY salary ) "Percent Rank"

FROM employees ;

Calcular el rango de porcentaje de todo el grupo de empleados particionando por el departamento donde trabaja cada empleado para el departamento con el

código 90.

SELECT first_name , last_name , salary , department_id ,

PERCENT_RANK () OVER (PARTITION BY department_id ORDER BY salary ) "Percent Rank"

FROM employees


Funciones NTILE (Percent-Rank)

NTILE permite el fácil cálculo de tertiles, cuadriles, deciles y otras estadísticas comunes. Esta función divide una partición ordenada en un número específico

de grupos llamados buckets y asigna un número de bucket para cada fila en la partición. NTILE es un cálculo muy útil porque permite a los usuarios dividir un

conjunto de datos en cuartos, terceros y otros agrupamientos.

Los buckets son calculados de forma que cada bucket tiene exactamente el mismo número de filas asignadas a este o por lo menos una fila más que los otros.

Por ejemplo si usted tiene 100 registros en una partición y utiliza una función NTILE con cuatro buckets, a 25 filas se les asignara un valor de 1, 25 filas

tendrán el valor 2, y así… Estos buckets son referidos como bucket equidimensional.

Si el número de filas en la partición no divide exactamente el número de buckets, entonces el número de filas asignado para cada bucket diferirá en por lo

menos uno. La filas extras serán distribuidas una por bucket empezando desde el numero de bucket con el valor mas bajo. Por ejemplos si hay 103 filas en una

partición la cual tiene una función NTILE(5), las primeras 21 filas estarán en el primer bucket, las siguientes 21 en el siguiente bucket, las siguientes 21 en el

tercer bucket, las siguientes 20 en el cuarto bucket, y al final 20 en el quinto bucket.

La función NTILE tiene la siguiente sintaxis :

NTILE (expr) OVER ([partition_query_clause] ORDER BY clause)

En esta, la N en NTILE(N) puede ser una constante (por ejemplo 5) o una expresión.

Esta función como RANK, y CUME_DIST tiene una cláusula PARTITION BY por cada grupo calculado, una cláusula ORDER BY para especificar las medidas y su

forma de ordenamiento, y las cláusulas NULLS FIRST | LAST FIRST para el tratamiento de valores nulos.

EJEMPLO:

Dividir el grupo de empleados en tertiles;

SELECT first_name , last_name , salary , department_id , NTILE ( 3 ) OVER (PARTITION BY department_id ORDER BY salary ) "Rank"

FROM employees

ORDER BY "Rank"

Dividir el grupo de empleados en cuadriles;


FROM employees

ORDER BY "Rank"

Dividir el grupo de empleados en deshiles;


FROM employees

ORDER BY "Rank"

Funciones de Numero de Filas (ROW_NUMBER)

Las funciones ROW_NUMBER asignan un numero único (secuencialmente, iniciando desde 1, como se defina en la cláusula ORDER BY) para cada fila dentro de

la partición. Esta tiene la siguiente sentencia.

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

EJEMPLO:

Calcular el ROW_NUMBER para todo el grupo de empleados particionando por el departamento donde labora cada empleado.

SELECT first_name , ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY salary DESC)

FROM employees

Calcular el rango de porcentaje de todo el grupo de empleados particionando por el departamento donde laboran los empleados y para el departamento 90.

SELECT first_name , ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY salary DESC)

FROM employees


Funciones de Ventana Agregada

Las funciones de ventanas pueden ser utilizadas para la realización de cálculos, movimiento y centrado de agregados. Ellas retornan un valor para cada fila en

la tabla el cual depende de otras filas en la correspondiente ventana. Estas funciones incluyen sumas móviles, promedios móviles, mínimos y máximos

móviles, sumas acumulativas y también funciones estadísticas. Estas pueden ser usadas únicamente en las cláusulas SELECT y ORDER BY de una consulta.

Otras dos funciones también están disponibles FIRST_VALUE, el cual retorna el primer valor en la ventana, y LAST_VALUE el cual retorna el útimo valor en la

ventana. Estas funciones facilitan el acceso a más de una fila en una tabla sin hacer auto-joins. Las funciones de ventana pueden ser de tipo ROW o tipo

RANGE. La sintaxis es la siguiente:

ROW

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2,..> ROWS BETWEEN <start_expr> AND <end_expr>)

ó

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2,..> ROWS [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

RANGE

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)

Ó

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

EJEMPLO:

Contar los empleados que ganan m á s del salario promedio, y menos del salario promedio para cada deparamento

SELECT

COUNT(*) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND AVG(salary) PRECEDING) Menos

COUNT(*) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN

AVG(salary) FOLLOWING AND UNBOUNDED FOLLOWING) Mas

FROM employees

ORDER BY department_id, salary;

funciones avanzadas

Documents