Count Distinct y Window Functions

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:

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.