Redgate Hub (Español)

he pasado los últimos seis años viajando por los EE. Estoy sorprendido de cómo pocas personas han oído hablar de estas funciones y aún menos que las están utilizando. Al final de cada presentación, una o más personas se acercan a decir que deseaban haber aprendido sobre estas funciones años antes porque podrían haber sido beneficiosos para tantas consultas.,

estas funciones se han promovido para mejorar el rendimiento sobre otros métodos más tradicionales. Estoy parcialmente de acuerdo. Hacen que muchas consultas sean más fáciles de escribir y, a veces, mejoran el rendimiento.

nada que ver con el sistema operativo Windows

estas funciones forman parte de los estándares ANSI SQL 2003 y, en el caso de SQL Server, son funciones T-SQL utilizadas para escribir consultas. No tienen nada que ver con el sistema operativo Windows ni con ninguna llamada a la API. Otros sistemas de bases de datos, como Oracle, también los han incluido como parte de su propio lenguaje SQL.,

Las funciones Window (también, windowing o windowed) realizan un cálculo sobre un conjunto de filas. Me gusta pensar en «mirar a través de la ventana» en las filas que se devuelven y tener una última oportunidad de realizar un cálculo. La ventana está definida por la cláusula OVER que determina si las filas están divididas en conjuntos más pequeños y si están ordenadas. De hecho, si usa una función window siempre usará una cláusula OVER. La cláusula OVER también forma parte del siguiente valor de sintaxis requerido para el objeto sequence, pero, de lo contrario, se usa con las funciones window.,

la cláusula OVER puede contener una opción PARTITION BY. Esto divide las filas en conjuntos más pequeños. Podrías pensar que esto es lo mismo que GROUP BY, pero no lo es. Al agrupar, se devuelve una fila por grupo único. Cuando se usa PARTITION BY, todas las filas de detalles se devuelven junto con los cálculos. Si tiene una ventana en su hogar que está dividida en paneles, cada panel es una ventana. Cuando se piensa en las funciones de ventana, todo el conjunto de resultados es una partición, pero cuando se usa PARTITION BY, cada partición también se puede considerar una ventana., La partición BY es compatible-y opcional – para todas las funciones de ventanas.

la cláusula OVER también puede contener una opción ORDER BY. Esto es independiente de la cláusula ORDER BY de la consulta. Algunas de las funciones requieren orden por, y no es compatible con los demás. Cuando el orden de las filas es importante al aplicar el cálculo, se requiere el orden por.

Las funciones de ventana solo se pueden usar en las cláusulas SELECT y ORDER BY de una consulta. Se aplican después de cualquier Unión, filtrado o agrupación.,

funciones de clasificación

Las funciones de ventana más utilizadas, funciones de clasificación, han estado disponibles desde 2005. Fue entonces cuando Microsoft introdujo ROW_NUMBER, RANK, DENSE_RANK y NTILE. ROW_NUMBER se usa con mucha frecuencia, para agregar números de fila únicos a una partición o a todo el conjunto de resultados. Agregar un número de fila, o una de las otras funciones de clasificación, no suele ser el objetivo, pero es un paso en el camino hacia la solución.

ORDER BY se requiere en la cláusula OVER cuando se usa ROW_NUMBER y las otras funciones de este grupo., Esto le indica al motor de base de datos el orden en el que se deben aplicar los números. Si los valores de las columnas o expresiones utilizadas en el orden BY no son únicos, entonces RANK y DENSE_RANK se ocuparán de los lazos, mientras que ROW_NUMBER no se preocupa por los lazos. NTILE se utiliza para dividir las filas en cubos basados en el orden por.

una ventaja de ROW_NUMBER es la capacidad de convertir filas no únicas en filas únicas. Esto podría usarse para eliminar filas duplicadas, por ejemplo.

para mostrar cómo funciona esto, comience con una tabla temporal que contenga filas duplicadas., El primer paso es crear la tabla y rellenarla.

agregar ROW_NUMBER y particionar por cada columna reiniciará los números de fila para cada conjunto único de filas. Puede identificar las filas únicas encontrando aquellas con un número de fila igual a uno.,

Ahora, todo lo que tienes que hacer es eliminar todas las filas que tienen una fila número mayor que uno., El problema es que no puede agregar funciones de ventana a la cláusula WHERE.,

1 2	DELETE #Duplicates WHERE ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) <> 1;

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., A continuación, puede eliminar las filas directamente desde el CTE.

el Éxito! Las filas adicionales se eliminaron, y un conjunto único de filas permanece.

para ver la diferencia entre ROW_NUMBER, RANK y DENSE_RANK, ejecute esta consulta:

El ORDER BY para cada cláusula OVER es OrderDate, que no es única. Este cliente realizó dos pedidos el 24-10-2013. ROW_NUMBER simplemente continuó asignando números y no hizo nada diferente a pesar de que hay una fecha duplicada., RANK asignó 6 a ambas filas y luego alcanzó a ROW_NUMBER con un 8 en la siguiente fila. DENSE_RANK también asignó 6 a las dos filas, pero asignó 7 a la siguiente fila.

dos explican la diferencia, piensa en ROW_NUMBER como posicional. El rango es tanto posicional como lógico. Esas dos filas se clasifican lógicamente de la misma manera, pero la siguiente fila se clasifica por la posición en el conjunto. DENSE_RANK los clasifica lógicamente. La orden 2013-11-04 es la 7a fecha única.

la función final en este grupo se llama NTILE. Asigna números de cubo a las filas en lugar de números de fila o rangos., He aquí un ejemplo:

NTILE tiene un parámetro, en este caso 4, que es el número de cubos que usted quiere ver en los resultados. El pedido por se aplica a la suma de las ventas. Las filas con el 25% más bajo se asignan 1, las filas con el 25% más alto se asignan 4. Finalmente, los resultados de NTILE se multiplican por 1000 para obtener el monto del bono. Dado que 14 no se puede dividir equitativamente entre 4, una fila adicional entra en cada uno de los dos primeros cubos.

agregados de ventanas

los agregados de ventanas también se introdujeron con SQL Server 2005., Esto hace que escribir algunas consultas difíciles sea fácil, pero a menudo se desempeñará peor que las técnicas más antiguas. Le permiten agregar su función agregada favorita a una consulta no agregada. Digamos, por ejemplo, que le gustaría mostrar todos los pedidos de los clientes junto con el subtotal para cada cliente., Mediante la adición de una SUMA usando la cláusula OVER, esto se puede lograr muy fácilmente:

1 2 3

SELECT Idcliente, Fechapedido, SalesOrderID, TotalDue, SUM(TotalDue) OVER(PARTITION BY CustomerID) COMO SubTotal DE las Ventas.,SalesOrderHeader;

Por la adición de la PARTICIÓN, un subtotal se calcula para cada cliente. Se puede utilizar cualquier función aggregate y no se admite ORDER BY en la cláusula OVER.

mejoras de agregados de ventana en 2012

a partir de 2012, puede agregar un ORDER BY a la cláusula OVER a los agregados de ventana para producir totales corrientes y medias móviles, por ejemplo. Al mismo tiempo, Microsoft introdujo el concepto de framing. Agregar una partición por ES como dividir una ventana en paneles., Agregar marcos es como crear una vidriera. Cada fila tiene una ventana individual donde se aplicará la expresión.

con esta mejora, puede crear totales en ejecución incluso sin agregar la sintaxis de encuadre., Here is an example that returns a running total by customer:

1 2 3 4

SELECT CustomerID, OrderDate, SalesOrderID, TotalDue, SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS RunningTotal FROM Sales.,SalesOrderHeader;

el marco predeterminado, que se usa si no se especifica un marco, es el rango entre la fila anterior y la actual sin límites. Desafortunadamente, esto no funcionará tan bien como si especifica este marco en su lugar: filas entre la fila anterior y la actual sin límites. La diferencia son las filas de palabras. RANGE solo se implementa parcialmente en este momento, y está diseñado para trabajar con períodos de tiempo, mientras que ROWS es posicional., El marco, filas entre la fila anterior y la actual sin límite, significa que la ventana consiste en la primera fila de la partición y todas las filas hasta la fila actual. Cada cálculo se realiza sobre un conjunto diferente de filas. Por ejemplo, al realizar el cálculo para la fila 4, se utilizan las filas 1 a 4.

cuando se realiza el cálculo para la fila 5, las filas son 1 a 5. La ventana se hace más grande a medida que se mueve de una fila a la siguiente.,

También puede utilizar la sintaxis filas entre N precediendo y fila actual o filas entre fila actual y N siguiente. Esto podría ser útil para calcular una media móvil de tres meses, por ejemplo. La siguiente figura representa las filas entre 2 fila anterior y actual.

Cuando 5 es la fila actual, la ventana se mueve, no cambia de tamaño.,

Aquí está la lista de términos que necesita saber al escribir la opción de encuadre:

admito que esta sintaxis es un poco confusa, pero el uso de SQL Prompt ayuda a escribir la opción de encuadre más fácil!

funciones de desplazamiento

También se incluyen con el lanzamiento de SQL Server 2012 cuatro funciones que le permiten incluir valores de otras filas, sin hacer una unión automática. Microsoft llama a estas’ funciones analíticas’, pero siempre me refiero a ellos como’ funciones de desplazamiento ‘ cuando se presenta en este tema., Dos de las funciones le permiten extraer columnas o expresiones de una fila antes (LAG) o después (LEAD) de la fila actual. Las otras dos funciones le permiten devolver valores desde la primera fila de la partición (FIRST_VALUE) o la última fila de la partición (LAST_VALUE). FIRST_VALUE y LAST_VALUE también requieren enmarcado, así que asegúrese de incluir el marco cuando use estas funciones. Las cuatro funciones requieren el orden por opción de la cláusula OVER. Eso tiene sentido, porque el motor de base de datos debe conocer el orden de las filas para averiguar qué fila contiene el valor a devolver.,

algunas personas tienen una banda favorita; algunas personas tienen una película favorita. Tengo una función favorita – LAG. Es fácil de usar (sin marco!) y funciona muy bien., Here is an example:

LAG y PLOMO requieren un argumento – la columna o expresión que desea devolver. De forma predeterminada, LAG devuelve el valor de la fila anterior y LEAD devuelve el valor de la fila siguiente. Puede modificar esto proporcionando un valor para el parámetro OFFSET, que es 1 por defecto. Observe que la primera fila de la partición devuelve NULL. Si desea anular los valores nulos, puede proporcionar un valor predeterminado., Here is a similar query that goes back two rows and has a default value:

1 2 3 4

SELECT CustomerID, OrderDate, SalesOrderID, LAG(SalesOrderID,2,0) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS Back2Orders FROM Sales.,SalesOrderHeader;

FIRST_VALUE y LAST_VALUE puede ser utilizado para encontrar un valor de la primera fila o la última fila de la partición. Asegúrese de especificar el marco, no solo por razones de rendimiento, sino porque el marco predeterminado no funciona como cabría esperar con LAST_VALUE. El marco predeterminado, rango entre fila anterior y actual sin límites, solo sube a la fila actual. La última fila de la partición no está incluida., Para obtener los resultados esperados, asegúrese de especificar filas entre la fila actual y las siguientes sin límites cuando use LAST_VALUE., Aquí hay un ejemplo usando FIRST_VALUE:

1 2 3 4 5

SELECT Idcliente, Fechapedido, SalesOrderID, FIRST_VALUE(SalesOrderID) OVER(PARTITION BY CustomerID PEDIDO POR SalesOrderID FILAS ENTRE ILIMITADO ANTERIOR Y ACTUAL de la FILA) COMO FirstOrder DE las Ventas.,SalesOrderHeader;

funciones estadísticas

Microsoft agrupa estas cuatro funciones – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, percentile_cont – junto con las funciones offset que llaman a las ocho funciones analíticas. Dado que me gusta distinguir estos de las funciones de desplazamiento, llamo a estos estadísticos.

PERCENT_RANK y CUME_DIST proporcionan una clasificación para cada fila sobre una partición. Difieren ligeramente. PERCENT_RANK devuelve el porcentaje de filas que se clasifican por debajo de la fila actual., «Mi puntuación es superior al 90% de las puntuaciones.»CUME_DIST, o distribución acumulativa, devuelve el rango exacto. «Mi puntuación está en el 90% de las puntuaciones.»Aquí está un ejemplo usando la temperatura alta promedio en St. Louis para cada mes. Tenga en cuenta que los rangos fueron determinados por la temperatura Fahrenheit.

Las filas no están determinados por los valores relativos, pero por las posiciones de las filas. Tenga en cuenta que marzo y noviembre tienen el mismo promedio de alta temperatura, por lo que se clasificaron de la misma manera.

Puede que te estés preguntando cómo calcular PERCENT_RANK y CUME_DIST., Aquí están las fórmulas:

PERCENTILE_DISC y PERCENTILE_CONT de trabajo en el sentido opuesto. Dado un rango porcentual, encuentre el valor en ese rango., Difieren en que PERCENTILE_DISC devolverá un valor que existe en el conjunto, mientras que PERCENTILE_CONT calculará un valor exacto si ninguno de los valores en el conjunto cae precisamente en ese rango. Puede usar PERCENTILE_CONT para calcular una mediana suministrando 0.5 como el rango porcentual. Por ejemplo, ¿qué temperatura se sitúa en el 50% en St. Louis?

la función PERCENTILE_CONT toma el promedio de los dos valores más cercanos al medio, 67 y 69, y los promedia. PERCENTILE_DISC devuelve un valor exacto, 67., Observe también que estas dos funciones tienen una cláusula extra no vista en las otras funciones, dentro del grupo, que contiene el orden por en lugar de dentro de la cláusula OVER.

resumen

este artículo es un resumen muy rápido de las funciones de la ventana de T-SQL. Se lanzaron dos tipos de funciones con SQL Server 2005, las funciones de clasificación y los agregados de ventanas. Con 2012, ha mejorado el agregado de ventanas con el encuadre y las funciones analíticas. Me gusta separar las funciones analíticas en dos grupos, las funciones offset y estadísticas., Las funciones de ventana hacen que muchas consultas sean más fáciles de escribir, y creo que ese es el principal beneficio. En algunos casos, las consultas también funcionarán mejor, pero eso es una discusión para otro día.

¡espero que este artículo te haya inspirado a aprender más sobre estas fantásticas funciones!