COUNT OVER PARTITION BY: Una Explicación con 3 Ejemplos Prácticos

En SQL, la combinación de la función COUNT() con la cláusula OVER() y PARTITION BY abre la puerta a una forma totalmente diferente de contar filas. En este artículo, aprenderás a realizar varios COUNTs en una sola consulta y a calcular expresiones basadas en el resultado de la función COUNT.

En este artículo, veremos cómo utilizar la función COUNT() combinada con las cláusulas OVER() y PARTITION BY. Para saber más sobre este tema, te recomiendo nuestro curso interactivo Funciones de ventana. Contiene más de 200 ejercicios interactivos sobre el uso de la cláusula OVER() con funciones de ventana. Después de terminar este curso, abarcará este tema con facilidad y se sentirá cómodo usando funciones ventana en bases de datos SQL.

Ejemplo #1: Introducción al Uso de COUNT OVER PARTITION BY

Supongamos que tenemos una tabla llamada order con un registro para cada orden de venta recibida en una tienda de mascotas. La tabla tiene columnas como order_id, order_date, customer_id, salesperson_id, ship_address, ship_state y amount_paid.

La siguiente consulta muestra los pedidos recibidos por la empresa durante el primer semestre de 2023. Nótese que añadimos una columna extra llamada orders_this_customer que muestra el número total de pedidos enviados por cada cliente durante este periodo.

SELECT 
  order_id,
  order_date,
  customer_id,
  amount_paid,
 COUNT(*) OVER (PARTITION BY customer_id) AS orders_this_customer
FROM order
WHERE order_date >= '2023-01-01' AND order_date <= '2023-06-30';

En SQL, utilizamos la función COUNT() sola o combinada con la cláusula GROUP BY para contar filas en un conjunto de resultados o en un grupo de filas. OVER() y PARTITION BY aplica la función COUNT() a un grupo o filas definidos por PARTITION BY. En nuestro ejemplo, el grupo está definido por los valores de la columna customer_id. La función COUNT() cuenta el número de pedidos con el mismo customer_id.

La combinación de COUNT() y OVER(PARTITION BY) es más potente que el uso de la función COUNT() por sí sola, ya que nos permite obtener el número de filas para cada valor específico de una columna.

Al utilizar OVER() y PARTITION BY, no necesitamos utilizar la cláusula GROUP BY para agrupar registros; esto nos permite tener conjuntos de resultados a nivel de fila. Cada fila del conjunto de resultados tendrá información a nivel de pedido, pero el valor de la columna orders_this_customer tendrá el número total de pedidos realizados por cada cliente.

En otras palabras, estamos combinando datos a nivel de informe con datos a nivel de cliente en la misma fila. Y podemos añadir datos de otro nivel utilizando diferentes columnas en la cláusula PARTITION BY. A continuación se muestra un resultado parcial de esta consulta:

Antes de terminar esta sección, me gustaría sugerir el artículo La función SQL COUNT() explicada con 7 ejemplos, donde se pueden encontrar muchos ejemplos de consultas utilizando la función COUNT(). Para aquellos lectores que quieran profundizar en el tema, les recomiendo el artículo Cómo Usar la Función SQL PARTITION BY With OVER donde podrán encontrar una clara explicación con ejemplos de las cláusulas OVER() y PARTITION BY.

Ejemplo #2: Calcular Totales Basados en Diferentes Criterios

Antes de continuar, vamos a mostrar el orden completo de la tabla. Cada fila de la tabla representa un pedido recibido por la tienda de mascotas. Una fila de pedido tiene un customer_id, un salesperson_id, un order_date, un ship_state, y un ship_city, entre otras columnas; las demás columnas se explican por sí mismas. He aquí una vista parcial de la tabla:

Supongamos que la tienda de animales, a final de mes, quiere tener un informe con todos los pedidos. Para cada fila, también quieren mostrar dos campos calculados: el número total de pedidos vendidos en este día y el número total de pedidos vendidos por este vendedor. La consulta para obtener este informe es

SELECT 
  order_id,
  order_date,
  customer_id,
  salesperson_id,
  COUNT(1) OVER (PARTITION BY order_date) as orders_per_day,
  COUNT(1) OVER (PARTITION BY salesperson_id) as orders_per_salesperson
FROM order
WHERE order_date between '2023-06-01' AND '2023-06-30';

El resultado de la consulta se muestra a continuación:

En la consulta anterior, hemos utilizado la expresión COUNT(1), que funciona igual que COUNT(*). La cláusula PARTITION BY indica el criterio de agrupación de las filas a contar.

Para obtener el campo orders_per_day, utilizamos la cláusula OVER (PARTITION BY order_date); para el campo orders_per_salesperson, utilizamos la cláusula OVER (PARTITION BY salesperson_id).

Aquí, observe que la única diferencia es el campo que ponemos después de la cláusula PARTITION BY. Este campo define el criterio para contar las filas; es decir, todas las filas con el mismo valor en este campo se contarán juntas.

El efecto de estos dos campos en el resultado de la consulta es que añadimos dos campos de distintos niveles de granularidad a la fila; el campo orders_per_salesperson es un total agrupado por vendedor, mientras que el campo orders_per_day es un total agrupado por fecha de pedido.

Nota: COUNT(DISTINCT) no funciona con OVER(PARTITION BY)

Existen algunas variaciones de la función COUNT(), como COUNT(*), COUNT(1) o COUNT(DISTINCT). Si desea obtener más información sobre ellas, consulte nuestro artículo ¿Cuál es la diferencia entre COUNT(*), COUNT(1), COUNT(nombre de columna) y COUNT(DISTINCT nombre de columna)? Encontrará una explicación clara de estas distintas formas de utilizar la función COUNT().

En algunos casos - por ejemplo, si necesitamos contar cuántas personas diferentes realizaron pedidos en un día determinado - podríamos pensar en utilizar la expresión COUNT(DISTINCT customer_id) OVER (PARTITION BY order_date). Sin embargo, es importante aclarar que COUNT(DISTINCT) OVER(PARTITION BY) no es soportada por la mayoría de las bases de datos populares (como PostgreSQL, SQL Server y Snowflake, entre otras).

Ejemplo 3: Uso de COUNT() con expresiones OVER In

En el siguiente ejemplo de consulta, calcularemos algunos porcentajes utilizando expresiones aritméticas con dos COUNTs diferentes.

Supongamos que el departamento de marketing desea dirigir específicamente la próxima campaña de marketing a un grupo preciso de clientes. Para ello, necesitan algunas métricas sobre los resultados comerciales: el porcentaje de pedidos recibidos de cada estado, el porcentaje de pedidos recibidos para cada familia de productos y la combinación de ambas métricas (por ejemplo, el porcentaje de pedidos recibidos de Texas para la familia de productos DOG). La consulta para obtener dicho informe es:

SELECT DISTINCT
  ship_state,
  product_family,
  COUNT(1) OVER () AS total_orders,
  COUNT(1) OVER (PARTITION BY ship_state) state_orders,
  COUNT(1) OVER (PARTITION BY ship_state) / COUNT(1) OVER () AS state_percentage,
  COUNT(1) OVER (PARTITION BY product_family) AS family_orders,
  COUNT(1) OVER (PARTITION BY product_family) / COUNT(1) OVER () AS family_percentage
FROM   order
ORDER BY ship_state, product_family

En la consulta anterior, el campo total_orders utiliza la expresión COUNT(1) OVER () para calcular la cantidad total de pedidos recibidos por la empresa. A continuación, en el campo state_orders, se utiliza la expresión COUNT() para obtener la cantidad de pedidos recibidos del estado de la fila actual (por ejemplo, si el valor ship_state de la fila actual es TX (Texas), se mostrarán todos los pedidos de Texas); este campo es muy similar al que hemos calculado en ejemplos anteriores.

La parte interesante de este ejemplo de consulta está en la expresión del campo state_percentage, que utiliza dos COUNTs para calcular un porcentaje. En otras palabras, estamos dividiendo el número de pedidos del estado actual (la expresión roja) por el número total de pedidos (la expresión azul). En los dos últimos campos, repetimos el mismo enfoque para el campo product_family.

A continuación se muestra el resultado de la consulta:

Uso de OVER PARTITION BY con COUNT() y otras funciones

En este artículo, hemos tratado la función COUNT() combinada con las cláusulas OVER y PARTITION BY. Sin embargo, en SQL existen muchas otras funciones que pueden combinarse con PARTITION BY. Estas funciones se denominan funciones ventana, y puedes encontrar excelente material de aprendizaje sobre ellas en nuestro cursoFunciones de ventana . Tiene más de 200 ejercicios interactivos y ofrece un tutorial completo sobre las funciones ventana.

Si ya conoces las funciones de ventana y buscas más práctica, te recomiendo nuestro Funciones de ventana Practice Set. Este curso ha sido diseñado para ofrecer una práctica completa de las funciones de ventana en ejemplos reales. Sus 100 ejercicios sobre tres conjuntos de datos diferentes se asemejan a problemas que verás en el mundo real.

Si buscas más recursos sobre funciones de ventana, echa un vistazo a nuestra hoja de trucos de SQL Funciones de ventana y a las 10 mejores preguntas de entrevista de SQL Funciones de ventana , donde encontrarás material para prepararte para una entrevista de trabajo de SQL. ¡A por ello, aprende SQL e invierte en ti!