En el ejemplo de hoy vamos a ver como resolver el siguiente error utilizando Count Distinct y Window Functions. Es un error que hemos tenido con unas consultas y queremos compartirlo con vosotros. Recordemos que si necesitáis ayuda adicional, no dudéis en contactar con nosotros.
El error parte del siguiente mensaje de alerta.

Preparando el ejemplo
Para llegar a la conclusión anterior y resolverla, primero construyamos un escenario. Vamos a trabajar con la BBDD de Adventure Works (https://docs.microsoft.com/es-es/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms)
Usemos las tablas Product y SalesOrderDetail , ambas en el esquema SalesLT . Cada fila de detalles del pedido es parte de un pedido y está relacionada con un producto incluido en el pedido. El producto tiene una categoría y un color.
Utilizaremos la siguiente query para emepezar:
SELECCIONE salesorderid , Count ( * ) AS ItemsPerOrder , Sum ( unitprice * orderqty ) AS Total FROM saleslt . producto p INNER JOIN saleslt . salesorderdetail s ON p . productid = s . productid GROUP BY salesorderid
Esta consulta da como resultado el recuento de artículos en cada pedido y el valor total del pedido.
Agreguemos algunos cálculos más a la consulta, ninguno de ellos representa un desafío:
SELECT salesorderid , Count ( * ) AS ItemsPerOrder , Sum ( unitprice * orderqty ) AS Total , Count ( DISTINCT productcategoryid ) CategoriesPerOrder , Count ( DISTINCT color ) ColorPerOrder FROM saleslt . producto p INNER JOIN saleslt . salesorderdetail s ON p . productid = s . productid GROUP BY salesorderid
Identificando el problema de Count Distinct y Window Functions
Ahora, imaginemos que, junto con esta información, también nos gustaría saber la cantidad de colores distintos por categoría que hay en este orden.
El grupo de solo tiene SalesOrderId. Debido a eso, nuestra primera conclusión natural es probar una partición de ventana, como esta:
SELECT SalesOrderID ,
Count ( * ) AS ItemsPerOrder ,
Sum ( preciounitario * OrderQty ) COMO total ,
Count ( DISTINCT productcategoryid ) CategoriesPerOrder ,
Count ( DISTINCT de color ) ColorPerOrder ,
Count ( DISTINCT de color )
OVER (
partición POR productcategoryid ) ColorPerCategory
DE saleslt. producto p
INNER JOIN saleslt . salesorderdetail s
ON p . productid = s . productid
GROUP BY salesorderid
Nuestro problema comienza con esta consulta. Count Distinct no es compatible con la partición de ventanas , necesitamos encontrar una forma diferente de lograr el mismo resultado
Planificación de la solución
Estamos contando las filas, por lo que podemos usar DENSE_RANK para lograr el mismo resultado, extrayendo el último valor al final, podemos usar un MAX para eso. Esto funciona de manera similar al recuento distinct porque todos los coincidentes, los registros con el mismo valor, reciben el mismo valor de clasificación, por lo que el valor más grande será el mismo que el recuento distinto.
Hay dos funciones de clasificación: RANK y DENSE_RANK . La diferencia es cómo tratan los lazos.
RANGO: Después de un empate, la cuenta salta el número de artículos empatados, dejando un hueco.
DENSE_RANK: Sin salto después de un empate, el conteo continúa secuencialmente
La siguiente consulta es un ejemplo de la diferencia:
SELECT ProductID , de color , Rango ( ) OVER ( ORDEN POR el color ) [rango] , DENSE_RANK ( ) OVER ( ORDEN POR el color ) [DENSE_RANK] DE saleslt . producto DONDE el color NO ES NULO

SELECT salesorderid , Count ( * ) AS ItemsPerOrder , Sum ( unitprice * orderqty ) AS Total , Count ( DISTINCT productcategoryid ) CategoriesPerOrder , Count ( DISTINCT color ) ColorPerOrder , Dense_rank ( ) OVER ( partición POR productcategoryid ORDER BY color ) ColorPerCategory FROM saleslt . producto p INNER JOIN saleslt . salesorderdetail s ON p . productid = s . productid GROUP BY salesorderid
Sin embargo, el resultado no es el que esperaríamos:

Los campos utilizados en la cláusula over también deben incluirse en el grupo, para que la consulta funcione.
Resolviendo la Solución
El primer paso para resolver el problema es agregar más campos al grupo. Por supuesto, esto afectará a todo el resultado, no será lo que realmente esperamos. La consulta será así:
SELECT SalesOrderID , productcategoryid , Count ( * ) AS ItemsPerOrder , Sum ( preciounitario * OrderQty ) COMO total , 1 ColorPerOrder , DENSE_RANK ( ) OVER ( partición POR SalesOrderID , productcategoryid ORDEN POR el color ) ColorPerCategory DE saleslt . producto p INNER JOIN saleslt .salesorderdetail s ON p . productid = s . productid GROUP BY salesorderid , productcategoryid , color
Hay dos cambios interesantes en el cálculo:
- CategoriesPerOrder se eliminó debido a que el «group by» está algunos niveles por debajo de este cálculo.
- ColorPerOrder es un valor fijo, porque estamos agrupando por color.
Necesitamos hacer más cálculos sobre el resultado de esta consulta, la mejor solución para esto es el uso de CTE – Common Table Expressions .
2º Nivel de consulta
El 2º nivel de cálculos será agregar los datos por ProductCategoryId , la eliminación de uno de los niveles de agregación.
Count Distinct y Window Functions
;WITH ranking AS (SELECT salesorderid, productcategoryid, Count(*) AS ItemsPerOrder, Sum(unitprice * orderqty) AS Total, Count(DISTINCT color) ColorPerOrder, Dense_rank() OVER ( partition BY salesorderid, productcategoryid ORDER BY color) ColorPerCategory FROM saleslt.product p INNER JOIN saleslt.salesorderdetail s ON p.productid = s.productid GROUP BY salesorderid, productcategoryid, color) SELECT salesorderid, Sum(itemsperorder) ItemsPerOrder, Sum(total) Total, 1 CategoriesPerOrder, Sum(colorperorder) ColorPerOrder, Max(colorpercategory) ColorPerCategory FROM ranking GROUP BY salesorderid, productcategoryid
Los cálculos de la consulta se definen por cómo se realizaron las agregaciones en la primera consulta:
- ItemsPerOrder: Hacemos una SUMA de los resultados del COUNT, esto agregará los diferentes conteos.
- Total: Una SUMA simple sobre la SUMA ya realizada.
- CategoriesPerOrder: puede tener un número fijo de 1, ya que todavía estamos agregando por categoría
- ColorPerOrder: Hacemos una SUMA sobre el COUNT ya existente de la consulta anterior
- ColorPerCategory: Después de hacer DENSE_RANK , ahora necesitamos extraer el valor MAX para tener el mismo efecto que COUNT DISTINCT
3 er Nivel de consulta
En el tercer paso reducimos la agregación, la consecución de nuestro resultado final, la agregación por SalesOrderID:
;WITH cte AS (SELECT salesorderid, productcategoryid, Count(*) AS ItemsPerOrder, Sum(unitprice * orderqty) AS Total, Count(DISTINCT color) ColorPerOrder, Dense_rank() OVER ( partition BY salesorderid, productcategoryid ORDER BY color) ColorPerCategory FROM saleslt.product p INNER JOIN saleslt.salesorderdetail s ON p.productid = s.productid GROUP BY salesorderid, productcategoryid, color), cte2 AS (SELECT salesorderid, Sum(itemsperorder) ItemsPerOrder, Sum(total) Total, 1 CategoriesPerOrder, Sum(colorperorder) ColorPerOrder, Max(colorpercategory) ColorPerCategory FROM cte GROUP BY salesorderid, productcategoryid) SELECT salesorderid, Sum(itemsperorder) ItemsPerOrder, Sum(total) Total, Sum(categoriesperorder) CategoriesPerOrder, Sum(colorperorder) ColorPerOrder, Sum(colorpercategory) ColorPerCategory FROM cte GROUP BY salesorderid
Una vez más, los cálculos se basan en las consultas anteriores. Algunos de ellos son los mismos de la segunda consulta, agregando más filas. Sin embargo, hay algunos cálculos diferentes:
- CategoriesPerOrder: se convierte en SUM para lograr el resultado que nos gustaría.
- ColorPerCategory: se convierte en SUM, agregando todos los resultados de conteo distintos de cada categoría.
El plan de ejecución
El plan de ejecución generado por esta consulta no es tan malo como podríamos imaginar. Esta consulta podría beneficiarse de índices adicionales y mejorar el JOIN, pero además de eso, el plan parece bastante correcto.
La combinación se realiza mediante el campo ProductId , por lo que un índice en la tabla SalesOrderDetail por ProductId y que cubra los campos adicionales utilizados ayudará a la consulta
Podemos crear el índice en la declaración:
CREATE INDEX indorderdetail
ON saleslt.salesorderdetail (productid)
include (orderqty, unitprice)
Lo que es interesante notar en este plan de consulta es el SORT, que ahora toma el 50% de la consulta. Esto no significa que el tiempo de ejecución del SORT haya cambiado, esto significa que el tiempo de ejecución para toda la consulta se redujo y el SORT se convirtió en un porcentaje más alto del tiempo total de ejecución.

Esperamos que os sea de utilidad. Si no quieres perderte nuestras entradas. Apúntate a nuestra newsletter mensual, para estar al día de todas nuestras publicaciones. Solo recibirás un email al mes.
Un saludo.