Problema:

A veces, necesitamos encontrar los primeros o últimos n% registros de un conjunto de datos basado en una columna específica. Por ejemplo, un profesor quiere saber qué estudiantes están en el 25% inferior basado en las calificaciones. Por lo tanto, en tales situaciones, la función SQL NTILE() ayuda. Aprovecharemos este consejo para obtener los primeros o últimos n% registros utilizando NTILE() en SQL Server.

Solución:

La función NTILE de SQL Server es una herramienta poderosa que le permite dividir registros de un conjunto de datos en un número especificado de grupos basados en un ordenamiento especificado. Esto le permite analizar sus datos de manera más eficiente y realizar diversas operaciones y cálculos sobre los datos divididos.

La función NTILE divide un conjunto en rangos iguales, con cada división que consta de registros/filas aproximadamente iguales, lo cual es extremadamente útil cuando desea dividir los datos en grupos basados en percentiles, como cuartiles, deciles o cualquier número de ntiles que necesite. Esto le permite identificar tendencias/patrones dentro de sus datos y realizar operaciones en cada grupo por separado. Por lo tanto, la herramienta es poderosa para crear histogramas, analizar clasificaciones o incluso dividir los datos para permitir el procesamiento paralelo.

Sintaxis de NTILE:

NTILE(Buckets) OVER (PARTITION BY partition_expression ORDER BY order_expression)

Buckets especifica el número de grupos en los que dividir sus datos; por lo tanto, buckets debe ser un número entero mayor que cero. PARTITION BY es una cláusula opcional que le permite particionar sus datos en subconjuntos antes de aplicar la función NTILE al conjunto de datos. Luego, NTILE se aplicará a cada partición por separado. ORDER BY es una cláusula requerida que se utiliza para especificar la(s) columna(s) utilizada(s) para clasificar/ordenar los registros dentro de cada subconjunto/partición. En esencia, es el criterio en el que se divide el conjunto de datos en grupos.

En este consejo, nuestro enfoque principal será utilizar la función NTILE para clasificar registros en función de criterios específicos. Ahora que hemos dado una breve descripción de la función NTILE y cómo se utiliza, es hora de sumergirse en cómo utilizar esta función de SQL.

Crear tablas y datos para pruebas:

Antes de poder utilizar la función NTILE, necesitamos un conjunto de datos al que aplicarla. Creemos una nueva base de datos para las pruebas:

CREATE DATABASE Informacion;
GO;
USE Informacion;

A continuación, cree dos tablas dentro de esta base de datos, una para registros de empleados y otra para registros de estudiantes. Dado que estamos utilizando la función NTILE, cada tabla debe consistir al menos en una columna donde podamos clasificar nuestros registros. Para la clasificación, nuestra tabla Empleados puede tener una columna de Salario, mientras que nuestra tabla Estudiantes tendrá una columna de Calificaciones_Finales. También necesitamos una columna para particionar los datos en múltiples subconjuntos para demostrar cómo utilizar la cláusula PARTITION BY opcional. Para acomodar esto, incluyamos una columna de Departamento en la tabla Empleados y una columna de Curso en la tabla Estudiantes.

Aquí está el código para crear la tabla Informacion_Empleado:

CREATE TABLE Informacion_Empleado
(
   ID_Empleado INT PRIMARY KEY IDENTITY(1,1),
   Departamento VARCHAR(255),
   Salario INT
);

Aquí está el código para crear la tabla Informacion_Estudiante:

CREATE TABLE Informacion_Estudiante
(
    ID_Estudiante INT PRIMARY KEY IDENTITY(1,1),
    Curso VARCHAR(255),
    Calificaciones_Finales INT
);

Ahora que se han creado ambas tablas, vamos a poblarlas, comenzando con la tabla Informacion_Empleado:

INSERT INTO Informacion_Empleado (Departamento, Salario) VALUES
('IT', 60000),
('IT', 50000),
('IT', 36000),
('IT', 29000),
('IT', 72000),
('IT', 52000),
('Ventas', 23000),
('Ventas', 53000),
('Ventas', 45000),
('Ventas', 56000),
('Ventas', 54000),
('Ventas', 46000),
('Ventas', 57000),
('Ventas', 55000),
('Ventas', 47000),
('RRHH', 62500),
('RRHH', 58000),
('RRHH', 49000),
('RRHH', 61000),
('RRHH', 81000),
('RRHH', 59000),
('RRHH', 30000),
('RRHH', 62000),
('RRHH', 64000),
('RRHH', 60500),
('RRHH', 51000),
('RRHH', 63000);

Ahora, poblaremos la tabla Informacion_Estudiante:

INSERT INTO Informacion_Estudiante (Curso, Calificaciones_Finales) VALUES
('Matemáticas', 92),
('Matemáticas', 80),
('Matemáticas', 84),
('Matemáticas', 66),
('Matemáticas', 40),
('Matemáticas', 51),
('Matemáticas', 83),
('Matemáticas', 69),
('Matemáticas', 76),
('Matemáticas', 40),
('Matemáticas', 39),
('Matemáticas', 30),
('Inglés', 91),
('Inglés', 89),
('Inglés', 76),
('Inglés', 53),
('Inglés', 96),
('Inglés', 65),
('Inglés', 74),
('Inglés', 69),
('Inglés', 48),
('Inglés', 35),
('Inglés', 77),
('Inglés', 54);

Ejemplos de uso de NTILE en datos de empleados:

Supongamos que se le ha encomendado aumentar los salarios de los empleados cuyos salarios se encuentran en el primer cuartil y reducir los salarios de los empleados en el cuarto cuartil por valores específicos, independientemente de sus departamentos. Para hacer esto, debe obtener los empleados en el primer y cuarto cuartil antes de poder actualizar los datos. Y así, primero debe dividir el conjunto de datos de Empleados en cuatro grupos iguales. Recuerde la sintaxis para NTILE:

NTILE(Buckets) OVER (PARTITION BY partition_expression ORDER BY order_expression)

En este caso, Buckets será 4 (para 4 cuartiles). No utilizaremos la cláusula PARTITION BY para este problema. Dado que estamos clasificando a nuestros empleados por Salario, utilizaremos ORDER BY y la order_expression será Salario. Para aplicar la función en la tabla Empleados, use el siguiente código:

SELECT *, NTILE(4) OVER (ORDER BY Salario) AS Ranking_Empleado
FROM Informacion_Empleado;

El código anterior mostrará los salarios de los empleados divididos en cuatro grupos clasificados en orden ascendente. Cuanto mayor sea el salario, mayor será el número. Sin embargo, eso se debe a que la cláusula ORDER BY ordena la columna en orden ascendente de forma predeterminada. Puede agregar un parámetro DESC como ORDER BY order_expression DESC para hacer el orden descendente. También puede filtrar los resultados para obtener solo los cuartiles que desea actualizar, de la siguiente manera:

SELECT *
FROM
  (SELECT *, NTILE(4) OVER (ORDER BY Salario) AS Ranking_Empleado
   FROM Informacion_Empleado) AS Tabla_Temporal 
WHERE Ranking_Empleado = 4;

El código anterior devolverá los registros de empleados cuyo salario se encuentra en el cuarto o más alto cuartil. De manera similar, el código a continuación devolverá los registros de empleados cuyo salario se encuentra en el primer o más bajo cuartil.

SELECT *
FROM 
  (SELECT *, NTILE(4) OVER (ORDER BY Salario) AS Ranking_Empleado
   FROM Informacion_Empleado) AS Tabla_Temporal
WHERE Ranking_Empleado = 1;

Ejemplos de uso de NTILE en tabla de estudiantes:

Supongamos que tiene las calificaciones finales de los estudiantes y desea calificarlos según un esquema de calificación relativa: A – Cuarto Cuartil, B – Tercer Cuartil, C – Segundo Cuartil, F – Primer Cuartil. Primero, debe dividir las calificaciones de sus estudiantes en cuatro grupos utilizando la función NTILE, con el parámetro buckets igual a 4, como se muestra a continuación:

SELECT *, NTILE(4) OVER (ORDER BY Calificaciones_Finales) AS Calificacion_Estudiante
FROM Informacion_Estudiante;

Observe cómo nuestro conjunto de datos consta de dos cursos; sin embargo, no utilizamos la cláusula PARTITION BY, por lo que los cuartiles se hicieron independientemente de qué curso se ingresaron las calificaciones. Esto se hizo para enfatizar la importancia de la cláusula PARTITION BY. Los datos pueden no tener sentido siempre sin ser particionados en relación con otras columnas, como se muestra en la imagen anterior, y para corregir este problema, ahora utilizaremos la cláusula PARTITION BY. Convencionalmente, se utiliza un número menor para representar un rango más alto, por lo que utilizaremos DESC en la cláusula ORDER BY:

SELECT *, NTILE(4) OVER (PARTITION BY Curso ORDER BY Calificaciones_Finales DESC) AS Calificacion_Estudiante
FROM Informacion_Estudiante;

Esta consulta devuelve el resultado que deseamos. Ahora, la columna Calificacion_Estudiante tiene sentido, ya que cada curso se está calificando por separado. Filtraremos nuestros resultados para poder asignar las calificaciones más adelante.

Los estudiantes que recibirán una calificación 'F' son:
SELECT *
FROM
  (SELECT *, NTILE(4) OVER (PARTITION BY Curso ORDER BY Calificaciones_Finales DESC) AS Calificacion_Estudiante
   FROM Informacion_Estudiante) AS Tabla_Temporal
WHERE Calificacion_Estudiante = 4;

Los estudiantes que recibirán una calificación 'C' son:
SELECT *
FROM
  (SELECT *, NTILE(4) OVER (PARTITION BY Curso ORDER BY Calificaciones_Finales DESC) AS Calificacion_Estudiante
   FROM Informacion_Estudiante) AS Tabla_Temporal
WHERE Calificacion_Estudiante = 3;

Los estudiantes que recibirán una calificación 'B' son:
SELECT *
FROM
  (SELECT *, NTILE(4) OVER (PARTITION BY Curso ORDER BY Calificaciones_Finales DESC) AS Calificacion_Estudiante
   FROM Informacion_Estudiante) AS Tabla_Temporal
WHERE Calificacion_Estudiante = 2;

Los estudiantes que recibirán una calificación 'A' son:
SELECT *
FROM
  (SELECT *, NTILE(4) OVER (PARTITION BY Curso ORDER BY Calificaciones_Finales DESC) AS Calificacion_Estudiante
   FROM Informacion_Estudiante) AS Tabla_Temporal
WHERE Calificacion_Estudiante = 1;

Conclusión:

Ha aprendido cómo la función NTILE agrupa registros y particiona los datos. Ahora puede aplicar esta función a sus conjuntos de datos según su problema específico y lograr los resultados deseados. Luego puede actualizar y manipular sus datos y realizar las operaciones que desee en su conjunto de datos. También puede analizar la información que recupere.

Artículo actualizado por última vez: 2023-07-05