0
Shares
0
0
2

El post que se desarrolla a continuación, es el primero de dos publicaciones que pretende realzar el potencial de las funciones de ventana (Windows Functions), ya que representan una herramienta poderosa en escenarios en los que queremos aplicar cálculos agregados a un subconjunto de filas y que devuelven dicho cálculo para cada fila incluida en la agrupación.

En este primer post, se presenta la estructura general y funcionalidades principales para rematar con un segundo post en el que se desarrollará casos de uso reales en las que podemos aplicarlas.

Uso de Funciones de Ventana frente a Cláusula GROUP BY

Antes de empezar, veamos conceptualmente la diferencia entre las funciones agregadas con uso de GROUP BY, frente al uso de funciones de ventana.

En el caso de cálculos agregados con uso de cláusula GROUP BY, se obtiene como resultado una fila por cada valor diferente del atributo usado en el GROUP BY. La siguiente ilustración, muestra cómo funciona una consulta con funciones agregadas haciendo uso de la cláusula GROUP BY.

Group by windows functions
 
select city, count(*) as ContCity
from [HR].[Employees]
group by city
 
Windows Functions, las grandes olvidadas en TSQL (I)

Sin embargo, con el uso de las windows functions, la cosa cambia. En la siguiente ilustración, se muestra la diferencia del uso con estas funciones. En este caso, se devuelven todas las filas con el resultado de aplicar el cálculo de agrupación.

Group by windows functions results
SELECT [empid]
,[lastname]
,[firstname]
,[title]
,[birthdate]
,[hiredate]
,[address]
,[city]
,[country]
,[phone]
,count(*) over (Partition by city) as CountCity
FROM [TSQLV5].[HR].[Employees]
 
Windows Functions, las grandes olvidadas en TSQL (I)

Incluso podemos hacer uso de funciones de ventanas sin definir un grupo (partición) de datos, mostrando el cálculo aplicado sobre todas las filas de la tupla.

SELECT empid, ordermonth, qty,
SUM(qty) OVER() as total
FROM Sales.EmpOrders;
 
Windows Functions, las grandes olvidadas en TSQL (I)

Nota: Se muestra un subconjunto de filas del resultado de ejecutar la query anterior.

En algunos escenarios, podemos plantear la misma consulta con GROUP BY y Windows Functions. En el siguiente ejemplo, se analizan dos consultas para obtener el mismo resultado con versiones GROUP BY y Windows Functions. En dicho análisis, se puede comprobar que la versión con Windows Functions es más eficiente que la versión con GROUP BY.

Group by windows functions table query
set statistics io on
set statistics time on
 
— Versión con uso de GROUP BY
SELECT O1.empid, O1.ordermonth, O1.qty,
SUM(O2.qty) AS runqty
FROM Sales.EmpOrders AS O1
INNER JOIN Sales.EmpOrders AS O2
ON O2.empid = O1.empid
AND O2.ordermonth <= O1.ordermonth
GROUP BY O1.empid, O1.ordermonth, O1.qty;
 
set statistics io off
set statistics time off
 
(192 rows affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘OrderDetails’. Scan count 2, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Orders’. Scan count 2, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
(1 row affected)
 
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 17 ms.
 
set statistics io on
set statistics time on
 
— Versión con uso de Windows Functions
SELECT empid, ordermonth, qty,
SUM(qty) OVER(PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;
 
set statistics io off
set statistics time off
 
(192 rows affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘OrderDetails’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Orders’. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
(1 row affected)
 
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 12 ms.
 

¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?

Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.

Voy a echar un vistazo

Definición de un Subconjunto de Datos. Cláusula Partition

Como hemos visto en los ejemplos anteriores, la cláusula Partition, permite restringir las filas sobre las que se aplica el resultado de la función agregada mostrando los mismos valores en las columnas de partición de ventana.

SELECT orderid, custid, val,
CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall,
CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust
FROM Sales.OrderValues;
 
Group by windows functions orderid

Nota: Se muestra un subconjunto de datos representativos del total devuelto por la consulta

En el ejemplo anterior, se muestran dos cálculos agregados:

  • Porcentaje del valor de venta de cada fila con respecto al total de ventas
CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall
 
  • Porcentaje del valor de venta de cada fila, con respecto al total de cada cliente (PARTITION BY custid). En este caso, el cálculo agregado 100. * val / SUM(val), solo se calcularía sobre la base de aquellas filas con el mismo valor de campo custid (partición).
CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust

Definición de Frames

Los Frames permiten definir, de forma más granular, las filas sobre la que aplicamos funciones agregadas, dentro de una partición o grupo de datos.

Para la definición de Frames, es necesario el uso de la cláusula ORDER BY para ordenar las filas de cada partición permitiendo confinar un marco de filas entre dos delimitadores.

SELECT empid, ordermonth, qty,
SUM(qty) OVER(PARTITION BY empid ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;
 

Con la consulta anterior, obtenemos la suma del campo qty con el acumulado de la fila anterior, aplicado sobre los grupos de filas con el mismo valor del campo empid, ordenados por el campo ordermonth.

Analicemos el resultado obtenido para la partición con valor de empid=1, que es aplicable al resto de particiones.

Windows Functions, las grandes olvidadas en TSQL (I)

Uso de Cláusulas ROWS y RANGE

  • ROWS: restringe las filas dentro de una partición especificando un número fijo de filas delante y detrás de la fila actual.
  • RANGE: restringe lógicamente las filas de una partición especificando un rango de valores con respecto al valor de la fila actual

Analicemos el comportamiento de uno y otro caso con la consulta que se muestra a continuación:

use [AdventureWorks2014]
SELECT t.OrderYear, OrderMonth, t.TotalDue,
SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth (ROWS|RANGE) BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ‘RunningTotal’
FROM
(
SELECT
YEAR(OrderDate) AS ‘OrderYear’,
MONTH(OrderDate) AS ‘OrderMonth’,
SalesPersonID,
TotalDue
FROM Sales.SalesOrderHeader
) AS t
WHERE
t.SalesPersonID = 274 and
t.OrderYear = 2011
GO
 

En las siguientes ilustraciones se muestra el comportamiento de la consulta anterior con ROWs y RANGE.

Uso de Cláusula ROWS:

Group by windows functions clausula rows

Uso de Cláusula Range:

Windows Functions, las grandes olvidadas en TSQL (I)

En las siguientes ilustraciones, se muestran las opciones que se disponen para definir Frames.

group Windows Functions Unbounded
group Windows Functions Unbounded following

Algunas Funciones Interesantes

Funciones de clasificación (Ranking Functions) - ROW_NUMBER, RANK, DENSE_RANK, NTILE

  • ROW_NUMBER asigna enteros únicos a partir del 1 y en base a los campos definidos en la cláusula ORDER BY. No determinista si ORDER BY no es único.
  • RANK asigna a cada fila 1 + el número de filas anteriores que tienen valor inferior en el order by.
  • DENSE_RANK asigna a cada fila 1 + el número de valores de ordenación inferiores distintos.
  • NTILE(n) asigna total_num_of_rows / n (división de enteros). Si hay un resto (r), se agrega una fila adicional a las primeras r islas. No determinista si ORDER BY no es único.
SELECT empid, qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownum,
RANK() OVER(ORDER BY qty) AS rnk,
DENSE_RANK() OVER(ORDER BY qty) AS densernk,
NTILE(4) OVER(ORDER BY qty) AS ntile4
FROM [Sales].[EmpOrders]
where qty <=50
 
Windows Functions, las grandes olvidadas en TSQL (I)

Funciones de Offset (LAG, LEAD)

Funciones que permiten extraer valores de otras filas: LAG, LEAD: Permiten extraer valores de filas anteriores (LAG) o siguientes (LEAD).

Puede indicarse desplazamiento explícito y valor predeterminado si la fila no existe, por ejemplo, LAG(cantidad, 3, 0)

SELECT custid, orderid, orderdate, qty,
LAG(qty) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS prevqty,
LEAD(qty) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS nextqty
FROM [Sales].[OrderValues]
 
Group by windows functions offset

Nota: Se muestran solo las primeras 20 filas de la tupla.

Funciones First Value y Last Value

First_value. Devuelve el primer valor de un conjunto ordenado de valores.

Last_value. Devuelve el último valor de un conjunto ordenado de valores.

SELECT custid, orderid, orderdate, qty,
FIRST_VALUE(qty) OVER(PARTITION BY custid ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstqty,
LAST_VALUE(qty) OVER(PARTITION BY custid ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastqty
FROM [Sales].[OrderValues]
order by 1, 3, 2
 

Nota: Para ver el resultado de aplicar las funciones FIRST_VALUE y LAST_VALUE de forma más clara, se ordena la consulta por los valores afectados (custid, orderdate y orderid).

Group by windows functions first value

Conclusiones

En el post presentado, se plantean las funciones de ventanas como herramienta para la obtención de datos agregados junto al conjunto de datos que conforman la tupla sobre las que se aplican. Entre los aspectos analizados están:

  • Diferencia de alcance en el uso de agregados con GROUP BY y funciones de ventana.
  • Definición de partición y frame para la aplicación de funciones de ventana.
  • Cláusula ROWS y RANGE para el procesamiento de funciones de ventana.
  • Funciones específicas de particiones y frames: funciones de clasificación (ROW_NUMBER, RANK, DENSE_RANK y NTILE), funciones de offset (LAG, LEAD), entre otras.

En este primer post, se presenta el paradigma de las funciones de ventana para plantear en publicaciones posteriores, casos de uso reales en los que podemos aplicar los conceptos que aquí se desarrollan.

¡Has llegado al final! Parece que te ha gustado nuestro post sobre TSQL

Recuerda, si tienes dudas o algún problema en concreto que te impide avanzar en la optimización de tus bases de datos y tu proyecto de SQL, no dudes en contactar con nosotros. No esperes más para acelerar tus procesos de Business Analytics y tomar mejores decisiones.

Voy a Echar un vistazo
2 Shares:
Share 0
Tweet 0
Share 2

Soy Ingeniero Técnico en Informática y Máster en diseño y gestión de proyectos, con más de 20 años de experiencia en informática, desempeñando diversas funciones en el sector. Actualmente soy Arquitecto de Plataformas de Datos en Verne Technology Group realizando análisis, implementación, puesta a punto, etc. de proyectos en plataformas con SQL Server.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

Conociendo Azure Synapse Analytics

Next article —

Cómo Instalar Cluster Kubernetes con kubeadm

You May Also Like
AArtículos de Data
Leer más
  • 3 minute read

Power BI Dataflows: Machine Learning en dos clicks!

En esta entrada continuaremos con la saga "en dos clicks", en la entrada anterior explicamos como hacer análisis de sentimiento en dos clicks con Power BI dataflows y ahora es el turno de mostrar cómo crear modelos de machine learning de forma automática utilizando la nueva funcionalidad abierta a través de los Power BI Dataflows.
AArtículos de Data
Leer más
  • 3 minute read

Estudio de la competencia con Power BI

El estudio de la competencia siempre ha sido un aspecto tratado e importante para cualquier empresa: uno de los primeros pasos para poner en marcha cualquier tipo de negocio, o una parte del plan de marketing de una empresa en activo que permite dar contexto para definir las acciones. Sin entrar demasiado en detalles sobre cómo llevarla a cabo (eso queda en manos de los departamentos de marketing ? ), el objetivo de estos estudios de la competencia, muy a grandes rasgos, pretende identificar: dónde y con quién compites oportunidades de negocio detectar aspectos para diferenciarte
AArtículos de Data
Panel de criptomonedas con PowerBI
Leer más
  • 4 minute read

Panel de criptomonedas con PowerBI

Crearemos un panel para una criptomoneda con Power BI donde podremos analizar su evolución. La criptomoneda que vamos a poner en análisis es el Ripple, criptomoneda muy bien valorada por su método de Blockchain llamado RippleNet.
AArtículos de Data
Leer más
  • 2 minute read

Hackea tu destino!

Hackea tu destino! Este año fue la primera vez que se celebraba en Torrevieja un Hackathon en el que se aunó la innovación alrededor de la tecnología y el turismo. Un maratón de 36 horas en la que varios equipos trataron de crear aplicaciones informáticas y herramientas para ponerlas al servicio del desarrollo turístico, y como sabéis, nosotros no podíamos faltar.