6 Ejemplos de la Función NTILE() en SQL

La función SQL NTILE() puede simplificar enormemente el análisis de datos y la elaboración de informes. Siga estos seis ejemplos para aprender qué es y cuándo utilizarla.

Las funciones de ventana son muy útiles y proporcionan una gran capacidad expresiva a SQL. En este artículo, hablaremos de la función NTILE(), que permite dividir un conjunto de registros en subconjuntos de tamaño aproximadamente igual. Esta función es muy utilizada en cálculos financieros o económicos.

Antes de entrar en la función NTILE(), repasemos rápidamente las funciones de ventana en SQL.

¿Qué es SQL Funciones de ventana?

Las funciones ventana son funciones que trabajan sobre un grupo de filas llamado ventana; devuelven un valor basado en ese grupo. La función NTILE(N) función recibe un parámetro entero(N) y divide el conjunto completo de filas en N subconjuntos. Cada subconjunto tiene aproximadamente el mismo número de filas y se identifica con un número entre 1 y N. Este número de identificación es el que devuelve NTILE().

Si quieres aprender las funciones de ventana de SQL, te recomiendo nuestro curso interactivo. Contiene 218 ejercicios prácticos que le ayudarán a practicar diferentes funciones de ventana. Al final, te sentirás cómodo utilizando esta técnica avanzada de SQL.

Si quieres ver las funciones ventana en acción, consulta nuestro artículo Ejemplo de función ventana SQL con explicaciones. Y si desea explorar las diferencias entre GROUP BY y las funciones ventana, lea SQL Funciones de ventana vs. GROUP BY.

Conjunto de Datos de Ejemplo: Una Tienda de Fútbol

En el resto del artículo, basaremos nuestros ejemplos en la base de datos de una tienda de fútbol ficticia. Nos centraremos en la tabla sales que tiene la siguiente estructura y datos:

Creo que todo en esta tabla se explica por sí mismo, así que pasemos a los ejemplos.

Ejemplo #1: Dividiendo Filas en 2 Grupos

Comenzaremos con un ejemplo muy simple. Queremos dividir las filas de la sales en dos grupos: grupo #1 y grupo #2. Entonces la siguiente consulta hace eso:

SELECT NTILE(2) OVER() AS group, 
       sale_date, 
       product_id,
 soccer_team 
FROM sales;

En la consulta, la expresión NTILE(2) OVER() devuelve 1 para el primer 50% de las filas del conjunto de resultados y 2 para el segundo 50% de las filas. Las filas se asignan a cada grupo de forma no determinista, es decir, no hay ningún criterio para asignar filas a un grupo concreto. Aquí se muestra un resultado parcial de la consulta, mostrando cada grupo en un color diferente:

Ejemplo nº 2: Distribución de las ventas de 2023

La tabla sales almacena los registros de cada venta realizada. El departamento de marketing está interesado en analizar la distribución de las ventas en función del importe gastado. Piden un informe que agrupe todas las ventas del año 2023 en cuatro grupos del mismo tamaño (el número de ventas en cada grupo debe ser el mismo). Cada venta debe asignarse en función del importe de la venta.

El primer grupo (sale_group #1) debe tener las ventas con el importe más bajo, y el último grupo (sale_group #4) debe tener las ventas con el importe más alto. Para cada venta, el informe debe incluir el número de grupo de venta, el customer_id, el product_id, y el soccer_team. La consulta para obtener este resultado es la siguiente:

  SELECT
      NTILE(4) OVER ( ORDER BY amount ) AS sale_group,
      product_id,
      product_category,
      soccer_team,
      amount as sales_amount
  FROM sales
  WHERE sale_date >= '2023-12-01' AND sale_date <= '2023-12-31';

Esta consulta utiliza una cláusula WHERE para filtrar las ventas que se produjeron en 2023. A continuación, la función NTILE(4) intenta crear cuatro grupos de filas con el mismo número de filas cada uno. Utilizamos "intenta" porque no siempre es posible crear grupos del mismo tamaño; algunos grupos pueden tener una fila menos que los demás.

¿Cómo definimos a qué grupo pertenece cada fila? La cláusula OVER (ORDER BY amount) indica que, antes de asignar grupos a las filas, hay que ordenar todas las filas en función del importe de la venta. Una vez ordenadas, la función NTILE(4) tomará el primer trimestre de las ventas y le asignará el valor 1, luego tomará el siguiente trimestre de las ventas y le asignará el valor 2, y así sucesivamente. A continuación se muestra un resultado parcial con los registros de cada sale_group:

Podemos ver que en el grupo más barato (1), sólo tenemos ventas de la categoría Accesorios. Esto se debe a que los productos de accesorios suelen ser los más baratos, mientras que las camisetas suelen ser más caras. También podemos ver que los productos para Barcelona están en los cuatro grupos de venta, lo que sugiere que este equipo tiene una oferta en todos los puntos de precio.

Ejemplo nº 3: Ventas mensuales de cada categoría y equipo

En la siguiente consulta, crearemos un informe sobre las ventas mensuales. El departamento de marketing quiere dividir las ventas mensuales de cada categoría de producto y equipo en cuatro grupos. El primer grupo tendrá las categorías de productos, los equipos de fútbol y los meses con las ventas totales más bajas. El siguiente grupo tendrá el siguiente nivel de total_sales, y así sucesivamente. De esta forma, los responsables de marketing podrán analizar la distribución de las ventas en los distintos meses y categorías. A continuación se muestra la consulta:

WITH monthly_sales_stats AS (
   SELECT
        EXTRACT(MONTH FROM sale_date) as month,
        product_category,
        soccer_team,
        SUM(amount) AS total_sales
   FROM sales
   WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
   GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
SELECT
     NTILE(4) OVER ( ORDER BY total_sales ) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
FROM monthly_sales_stats
ORDER BY group DESC, total_sales DESC;

La consulta tiene una expresión de tabla común (CTE) llamada monthly_sales_stats, que calcula las ventas totales para cada combinación de mes, product_category, y soccer_team.

Observe que filtramos las filas del año 2023 en la cláusula WHERE. La expresión EXTRACT(MONTH FROM sale_date) se utiliza para obtener el valor del mes en el campo sale_date.

En la consulta principal, llamamos a NTILE(4) OVER (ORDER BY total_sales) para asignar a cada fila del CTE un número de grupo. A continuación se muestra un resultado parcial de la consulta que muestra las tres primeras filas de cada grupo:

En el resultado, se puede ver que las mayores ventas para cada equipo de fútbol se producen en diciembre, probablemente debido a las compras navideñas. La categoría "Accesorios" está al final de la tabla de resultados, porque los accesorios suelen tener precios más bajos.

Ejemplo nº 4: Ventas más bajas y más altas de los equipos

Al igual que otras funciones de ventana, puede utilizar NTILE() con la cláusula PARTITION BY. He aquí un ejemplo.

El equipo de marketing quiere investigar cómo se distribuyen las ventas dentro de los artículos para cada equipo de fútbol. La idea es dividir las ventas de cada equipo en conjuntos basados en la cantidad. Una vez más, pondremos las ventas más bajas en el primer conjunto y luego subiremos hasta las ventas más altas en el cuarto conjunto.

Esta es la consulta:

SELECT	soccer_team,
      	NTILE(4) OVER (PARTITION BY soccer_team ORDER BY amount) AS group_number,
product_id, 
product_category,
amount 
FROM sales ; 

La consulta es muy simple; tiene un SELECT con una lista de columnas y un FROM con un nombre de tabla. La función NTILE() utiliza PARTITION BY y ORDER BY. El PARTITION BY soccer_team pone todas las filas con el mismo valor en soccer_team en la misma partición. La cantidad ORDER BY ordena los registros en el conjunto de cada equipo, poniendo primero los que tienen los valores más bajos. Entonces NTILE(4) devuelve 1 para el primer 25% de las filas del grupo de filas, 2 para el segundo 25% de las filas del grupo, y así sucesivamente.

A continuación se muestran algunos resultados parciales:

Ejemplo nº 5: Comportamiento de las ventas navideñas por equipo de fútbol

Este ejemplo es muy similar al Ejemplo 3, con la diferencia de que la función NTILE() utiliza una subcláusula PARTITION BY soccer_team. Esto significa que NTILE() creará grupos de filas para cada soccer_team en lugar de crear grupos a partir del conjunto de resultados completo (como en el Ejemplo 3). Como resultado, cada equipo de fútbol tendrá cuatro conjuntos.

La consulta es:

WITH monthly_sales_stats AS (
       SELECT
            EXTRACT(MONTH FROM sale_date) as month,
            product_category,
            soccer_team,
            SUM(amount) AS total_sales
      FROM sales
      WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
      GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
  SELECT
     NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
  FROM monthly_sales_stats
  ORDER BY total_sales DESC;

El CTE monthly_sales_stats es exactamente el mismo que en el ejemplo anterior. Tiene una cláusula GROUP BY que ayuda a calcular la cantidad total de ventas para cada combinación de mes, product_category, y soccer_team.

Después de crear el CTE, escribimos un SELECT con la siguiente expresión NTILE():

NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group

Esta expresión divide las filas del CTE en conjuntos que tienen el mismo valor en el campo soccer_team. Para cada conjunto de equipos de fútbol, NTILE(4) intenta crear cuatro subconjuntos iguales, asignando a cada fila un valor del 1 al 4.

El resto de las columnas del resultado proceden del CTE. A continuación se muestran resultados parciales que muestran dos filas por cada grupo para los equipos Barcelona y Manchester.

Ejemplo nº 6: Obtener grupos de clientes distribuidos uniformemente

Supongamos que el departamento de marketing desea crear tres grupos de clientes distribuidos uniformemente para realizar tres campañas de marketing diferentes; cada campaña irá dirigida a un grupo. Los clientes de cada grupo se eligen al azar. A continuación, el departamento de marketing comparará los resultados de las campañas y evaluará qué campaña es mejor.

Para simular una selección aleatoria de clientes, una idea es utilizar los segundos de la última vez que cada cliente compró algo. Y ordenados por los segundos, crearemos tres grupos de clientes. Veamos la consulta:

WITH customer_last_transaction_timestamp AS (
  SELECT customer_id, 
       max(sales_date) AS last_ts
  FROM   sales
  GROUP BY customer_id
)
SELECT NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts)) AS group_number, 
       customer_id 
FROM   customer_last_transaction_timestamp 
ORDER BY group_number;

La consulta anterior devuelve todos los clientes con un group_number de 1 a 3; esto representa el grupo de marketing al que se asignó el cliente. El CTE customer_last_transaction_timestamp almacena cada cliente con su última fecha y hora de transacción (obtenida con MAX(sales_date)).

La consulta principal utiliza la función NTILE(3) para crear tres grupos de clientes de aproximadamente el mismo tamaño:

NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts))

La sentencia anterior devuelve 1 para el 33% de los clientes con un valor de marca de tiempo en el tercio inferior. Devuelve 2 para el 33% siguiente, y así sucesivamente. La subexpresión EXTRACT(SECOND FROM last_ts) toma la parte de los segundos (es decir, 22) de una marca de tiempo (es decir, '2023-03-30 10:30:22'). A continuación se muestra un resultado parcial:

Continúe aprendiendo sobre NTILE() y otros SQL Funciones de ventana

En este artículo, mostramos varios ejemplos del uso de la función de ventana NTILE(). También demostramos diferentes cláusulas OVER. La cláusula OVER es común a todas las funciones de ventana de SQL. Si desea obtener experiencia práctica con estos comandos, le sugiero nuestro curso interactivo Funciones de ventana interactivo.

Si eres un usuario frecuente de las funciones de ventana, nuestra hoja de trucos gratuita de SQL Funciones de ventana es un recurso muy útil. De hecho, yo la tengo en la pared de mi despacho, lista para usarla cuando tengo dudas sobre sintaxis. La recomiendo encarecidamente.

Por último, el artículo Las 10 mejores preguntas de la entrevista sobre las funciones de ventana de SQL es genial si tienes una entrevista de trabajo y quieres estar preparado para temas de SQL.

Feliz aprendizaje, ¡y sigue progresando con las funciones de ventana de SQL!