Funciones de consulta
| ||||
Las Funciones de consultas son funciones que solamente pueden ser invocadas como parte de una sentencia Select y solo en algunos contextos. De las mismas existen dos tipos diferentes, los cuales se clasifican según el modo en que opera cada una de ellas. Los dos tipos de funciones de consulta utilizados en SQL son Las funciones de agrupación, las cuales pueden aparecer solamente en la lista de elementos del SELECT, en las cláusulas HAVING, WINDOW u ORDER BY,y las funciones OLAP, las cuales pueden solo aparecer en la lista de elementos del SELECT, o en el ORDER BY.
Sumario
Tipos de funciones
Existen dos tipos de funciones de reportes:
- Funciones de agrupación, que computa una operación de agrupación o una función agregada de un grupo de una tabla agrupada.
- Funciones OLAP, que computa un rango, un número de filas o una función agregada en ventana.
Funciones de agrupación:
Existen dos tipos de funciones de agrupación, la operación agrupación y las funciones de agregación. La única operación de agrupación que utiliza SQL es GROUPING(<column>), la cual retorna un valor de 1 para indicar una fila agregada sobre la columna a la que se hace referencia durante la ejecución de una consulta agrupada conteniendo CUBE y ROLLUP, y retorna 0 en caso contrario. Las funciones de agregación, por su parte, son las ya conocidas COUNT(), SUM(), AVG(), MIN(), MAX(), EVERY(), ANY(), SOME().
Funciones OLAP:
Existen tres tipos de funciones OLAP: funciones de rango, función número de fila, y funciones de agregación con ventanas. Las funciones de rangos computan el rango ordinal de una fila R dentro de la partición de R como fue definida en el descriptor de la ventana, de acuerdo al orden de esas filas especificado dentro del descriptor de la ventana. Existen dos variantes en estas funciones de rango: RANK y DESERANK.
- Función RANK: Es el rango o jerarquía que ocupa una tupla R teniendo en cuenta las tuplas que la preceden. El rango si encuentra dos tuplas en una posición empatada, les asigna el mismo número por ejemplo el 1, y a la próxima que encuentra le asigna el número 3.
- Función DENSERANK: El rango de la tupla R es definido como el número de filas que preceden e incluyendo las tuplas que no se acogen al ordenamiento. A diferencia del rango, el DENSERANK (rango denso) le asigna la posición 1 por ejemplo a dos tuplas que están empatadas enla primera posición, y el número 2 a la próxima que encuentre.
- Función ROWNUMBER: computa un número de fila secuencial comenzando con 1 en la primera tupla en cada partición y de acuerdo al ordenamiento de las tuplas en la partición.
- Las funciones de agregación con ventanas: Computan un valor de agregado (COUNT, SUM, AVG, etc) de la misma manera que la función de agregación excepto que se computa más allá de un grupo o tabla agrupada.
Utilización de ventanas
Un elemento fundamental para comprender el trabajo con las funciones OLAP es la definición de ventanas en la sentencia Select. Una ventana es una estructura temporal de datos, es usada para definir particiones y grupos de agregación que se utilizan luego en las funciones OLAP.
Tiene dos formas de definirse:
- de manera explícita mediante una definición de ventana o
- de manera implícita mediante una especificación en línea de ventana.
Sintaxis
SELECT elements FROM tables WHERE conditions GROUP BY elements HAVING conditions WINDOW definition ORDER BY elements LIMIT nro OFFSET pos;
Como se puede observar la definición de ventanas se ubica luego del Having en la sentencia Select y antes del order by. En esta parte se pueden definir cuantas ventanas sean necesarias separadas por coma, cada una con un nombre y su correspondiente definición. Una definición de ventana puede especificar tres componentes, cada uno de
manera opcional:
Ejemplo
Ejemplo 1: Promedio de ventas sobre los últimos tres meses.
SELECT d.territorio, d.mes, d.ventas, AVG (d.ventas) OVER W1 as Promedio FROM Ventas_historicas d WINDOW W1 AS (PARTITION BY d. territorio ORDER BY d.mes ASC ROWS 2 PRECEDING);
En la parte “AVG (d.ventas) OVER W1” al estar una función de agregación seguida de la palabra OVER y el nombre de una ventana, es la forma de reconocer que es una función OLAP. La especificación de ventana de W1 especifica de manera sencilla los tres elementos que puede contener una ventana. En este caso la cláusula de Particionado indica que las tuplas entregadas por la cláusula FROM deben ser asignadas a particiones basadas en sus territorios. La cláusula de Ordenamiento indica que las tuplas son organizadas de manera ascendente por el mes en cada partición. La línea final de la especificación de la ventana define un grupo de agrupación. En este ejemplo el grupo de agrupación consiste en la tupla actual y las dos precedentes en la partición, de acuerdo al ordenamiento en la partición.
Cláusulas
WINDOW window_name AS (PARTITION BY columns ORDER BY columns ASC/DESC ROWS ... / RANGE ...);
- La cláusula PARTITION BY se escribe similar al GROUP BY, sin embargo no significa esto que sea lo mismo una sentencia que otra. La diferencia radica en que el particionado no agrupa en una única tupla todos los elementos encontrados, sino que asocia la tupla a un particionado específico.
- La cláusula ORDER BY es similar al ORDER BY que aparece en la sentencia Select fuera de la especificación de ventana. Sin embargo hay diferencias debido a que el order by especificado en la ventana no ordena de manera visible las tuplas, sino que las ordena para definir con esto el valor de la función OLAP que utilice esta ventana; otra diferencia es que el ordenamiento dentro de la ventana se aplica a cada particionado por separado y no a todas las tuplas como el otro order by.
