Cómo utilizar una ventana de sql para crear un conjunto de resultados

Ventanas y funciones de la ventana se introdujeron por primera vez en SQL: 1999. Con una ventana, un usuario puede particionar, opcionalmente, un conjunto de datos, opcionalmente ordenar las filas en cada partición, y especificar un conjunto de filas (marco de la ventana) que se asocia con una fila dada.

El marco de la ventana de una fila R es un subconjunto de la partición que contiene R. Por ejemplo, el marco de la ventana puede consistir en todas las filas desde el principio de la partición hasta e incluyendo R, basado en la forma en que las filas se ordenan en la partición .

Una función de ventana calcula un valor para una fila R, basado en las filas en el marco de ventana de R.

Por ejemplo, suponga que tiene una tabla de ventas que tiene columnas de CustID, InvoiceNo y VentaTotal. Su gerente de ventas puede querer saber cuáles fueron las ventas totales de cada cliente en un rango específico de números de factura. Usted puede obtener lo que quiere con el siguiente código SQL:

SELECT CustID, InvoiceNo, SUM (VentaTotal) OVER (PARTITION BY CustIDORDER POR InvoiceNoROWS BETWEENUNBOUNDED PRECEDINGAND ACTUAL fila) de ventas-

los ENCIMA cláusula determina cómo se dividen las filas de la consulta antes de ser procesados, en este caso por el SUM función. Una partición se le asigna a cada cliente. Dentro de cada partición será una lista de números de factura, y asociado con cada uno de ellos será la suma de todas las VentaTotal valores en el rango especificado de filas, para cada cliente.

SQL: 2011 ha añadido varias mejoras importantes a la funcionalidad original de la ventana, la incorporación de nuevas palabras clave.

Cómo dividir una ventana en cubos con NTILE

los NTILE función de ventana reparte una partición ventana ordenado en algún número entero positivo n de cucharones, numerar las cubetas de 1 a n. Si el número de filas de una partición m no es divisible por n, a continuación, después de la función NTILE llena los cubos de manera uniforme, el resto de m / n, llamado r, se reparte a la primera r cubos, haciéndolos más grandes que los otros cubos.

Suponga que desea clasificar a sus empleados por el salario, la partición de ellos en cinco cubos, de mayor a menor. Puede hacerlo con el siguiente código:

SELECT Nombre, Apellido, NTILE (5) OVER (ORDER BY Salario DESC) AS BUCKETFROM Empleado-

Si hay, por ejemplo, 11 empleados, cada cubeta se llena con de dos a excepción de la primera cubeta, que se llena con tres. El primer cubo contendrá los tres empleados mejor pagados, y el quinto cubo contendrá los dos empleados peor pagados.

Cómo funciones de la ventana de nido

A veces, para obtener el resultado que necesita, la forma más fácil es para anidar una función dentro de otra. SQL: 2011 ha añadido la capacidad de hacer tales anidación con funciones de la ventana.

Como ejemplo, considere un caso en que un inversionista común está tratando de determinar si es un buen momento para comprar una acción en particular. Para tener una idea de esto, ella decide comparar el precio actual de las acciones al precio se vendió por sobre las 100 operaciones inmediatamente anteriores. Se pregunta, ¿cuántas veces en las 100 operaciones anteriores se vendió por menos del precio actual.

Para llegar a una respuesta, ella hace la siguiente consulta:

SaleTime SELECT SUM (CASO CUANDO SalePrice 
La ventana abarca las 100 filas que preceden a la fila actual, que corresponden a los 100 ventas inmediatamente anteriores al momento actual. Cada vez que se evalúa una fila donde el valor de Precio de venta es menor que el precio más reciente, se suma 1 a la suma.
El resultado final es un número que indica el número de ventas del anterior cientos que se hicieron a un precio más bajo que el precio actual.
¿Cómo evaluar grupos de filas
A veces la clave de ordenación que ha elegido para colocar una partición con el fin tendrá duplicados. Es posible que desee evaluar todas las filas que tienen la misma clave de clasificación como grupo. En estos casos se puede utilizar el GRUPOS opción. Con él usted puede contar grupos de filas donde las claves de clasificación son idénticos.
He aquí un ejemplo:

SELECT CustomerID, Fecha de Venta, SUM (TotalFactura) OVER (PARTITION BY CustomerIDORDER POR SaleDateGROUPS ENTRE 2 Y ANTERIOR 2 SIGUIENTE) DE clientes-

El marco de la ventana en este ejemplo consiste en un máximo de cinco grupos de filas: dos grupos ante el grupo que contiene la fila actual, el grupo que contiene la fila actual, y dos grupos siguientes el grupo que contiene la fila actual. Las filas en cada grupo tienen el mismo Fecha de venta, y el Fecha de venta asociado con cada grupo es diferente de la Fecha de venta valores para los otros grupos.