realizamos cálculos de datos utilizando varias funciones agregadas, como Max, Min y AVG. Obtenemos una sola fila de salida usando estas funciones. SQL Sever proporciona funciones de rango SQL para especificar el rango de campos individuales según las categorizaciones. Devuelve un valor agregado para cada fila participante. Las funciones SQL RANK también se conocen como funciones de ventana.
- Nota: El término de Windows en esto no se relaciona con el sistema operativo Microsoft Windows., Estas son funciones SQL RANK.
Tenemos las siguientes funciones de clasificación.
- ROW_NUMBER ()
- RANK ()
- DENSE_RANK ()
- NTILE ()
en las funciones SQL RANK, usamos la cláusula OVER () para definir un conjunto de filas en el conjunto de resultados. También podemos usar la cláusula SQL PARTITION BY para definir un subconjunto de datos en una partición. También puede usar la cláusula Order by para ordenar los resultados en orden descendente o ascendente.
antes de explorar estas funciones SQL RANK, preparemos datos de muestra., En esta muestra de datos, Tenemos resultados de exámenes para tres estudiantes en Matemáticas, Ciencias e inglés.
tenemos los siguientes datos de muestra en la tabla ExamResult.
usemos cada función SQL Rank en los próximos ejemplos.
ROW_Number() SQL RANK function
utilizamos la función ROW_NUMBER () SQL RANK para obtener un número secuencial único para cada fila en los datos especificados. Da el rango uno para la primera fila y luego incrementa el valor por uno para cada fila. Obtenemos diferentes rangos para la fila que tiene valores similares también.,
ejecute la siguiente consulta para obtener un rango para los estudiantes según sus calificaciones.,
|
1
2
3
4
5
|
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;
|
By default, it sorts the data in ascending order and starts assigning ranks for each row., En la captura de pantalla anterior, obtenemos el número de fila 1 para marcas 50.
podemos especificar el orden descendente con la cláusula Order By, y cambia el rango en consecuencia.,ae»>
|
1
2
3
4
5
|
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumber
FROM ExamResult;
|
RANK() SQL RANK Function
We use RANK() SQL Rank function to specify rank for each row in the result set., Tenemos los resultados de los estudiantes de tres asignaturas. Queremos clasificar el resultado de los estudiantes según sus calificaciones en las asignaturas. Por ejemplo, en la siguiente captura de pantalla, la estudiante Isabella obtuvo las calificaciones más altas en la asignatura de inglés y las más bajas en la asignatura de matemáticas. Según las notas, Isabella obtiene el primer rango en inglés y el 3er lugar en la asignatura de matemáticas.
Ejecute la consulta siguiente para obtener este conjunto de resultados.,v id=»c5a1f1b829″>
|
1
2
3
4
5
6
7
|
SELECT Studentname,
Subject,
Marks,
RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Studentname,
Rank;
|
Let’s execute the following query of SQL Rank function and look at the result set., En esta consulta, no especificamos la cláusula SQL PARTITION By para dividir los datos en un subconjunto más pequeño. Usamos la función SQL Rank con la cláusula over en la cláusula Marks (en orden descendente) para obtener rangos para las filas respectivas.,div id=»7d9d5b54ae»>
|
1
2
3
4
5
6
|
SELECT Studentname,
Subject,
Marks,
RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;
|
In the output, we can see each student get rank as per their marks irrespective of the specific subject., Por ejemplo, las puntuaciones más altas y más bajas en el conjunto de resultados completo son 90 y 50 respectivamente. En el conjunto de resultados, la marca más alta obtiene el rango 1, y la marca más baja obtiene el rango 9.
si dos estudiantes obtienen las mismas calificaciones (en nuestro ejemplo, números de fila 4 y 5), sus rangos también son los mismos.
función de rango SQL DENSE_RANK ()
utilizamos la función DENSE_RANK() para especificar un número de rango único dentro de la partición según el valor de columna especificado. Es similar a la función Rank con una pequeña diferencia.,
en la función SQL RANK DENSE_RANK (), si tenemos valores duplicados, SQL también asigna diferentes rangos a esas filas. Idealmente, deberíamos obtener el mismo rango para valores duplicados o similares.
ejecutemos la siguiente consulta con la función DENSE_RANK ().,
|
1
2
3
4
5
6
|
SELECCIONE Studentname,
Sujeto,
Marca
DENSE_RANK() OVER(ORDER BY Marcas DESC) Clasificación
DE ExamResult
ORDEN de Clasificación;
|
En la salida, usted puede ver, tenemos el mismo valor para ambos Lily y a Isabel, que anotó 70 puntos.,
usemos la función DENSE_RANK en combinación con la cláusula SQL PARTITION BY.,v id=»2d0aafc924″>
no Tenemos dos estudiantes con marcas similares; por lo tanto conjunto de resultados similar a la Función de CLASIFICACIÓN en este caso.,
Vamos a actualizar el estudiante marca con la siguiente consulta y vuelva a ejecutar la consulta.
|
1
|
Actualización Examresult conjunto de Marcas=70 donde Studentname=’Isabel’ y Asunto=’Matemáticas’
|
podemos ver que en el grupo de alumnos, Isabella se obtuvo un rendimiento similar en Matemáticas y Ciencias., El rango también es el mismo para ambos sujetos en este caso.
veamos la diferencia entre la función Rank() y DENSE_RANK() SQL Rank con la siguiente consulta.,b829″>
|
1
2
3
4
5
6
7
|
SELECCIONE Studentname,
Sujeto,
Marca
DENSE_RANK() OVER(PARTITION BY StudentName ORDEN POR las Marcas ) Clasificación
DE ExamResult
PEDIDO POR Studentname,
Rango;
|
En la salida, usted puede ver la diferencia en el rango de la función de producción dentro de las particiones., No tenemos ninguna brecha en la función DENSE_RANK.
en la siguiente captura de pantalla, puede ver que Isabella tiene números similares en los dos sujetos. Una función de rango asigna rango 1 para valores similares sin embargo, internamente ignora rango dos, y la siguiente fila obtiene rango tres.
en la función Dense_Rank, mantiene el rango y no da ningún espacio para los valores.
función de rango SQL NTILE(n)
utilizamos la función NTILE(N) para distribuir el número de filas en el número de grupos especificado (N)., Cada grupo de filas obtiene su rango según la condición especificada. Necesitamos especificar el valor para el número deseado de grupos.
en mi ejemplo, tenemos nueve registros en la tabla ExamResult. El NTILE (2) muestra que requerimos un grupo de dos registros en el resultado.,
|
1
2
3
4
5
|
SELECT *,
NTILE(2) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;
|
In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.,
de manera similar, NTILE(3) divide el número de filas de tres grupos que tienen tres registros en cada grupo.,
|
1
2
3
4
5
|
SELECT *,
NTILE(3) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;
|
We can use SQL PARTITION BY clause to have more than one partition., En la siguiente consulta, cada partición en los sujetos se divide en dos grupos.,
|
1
2
3
4
|
SELECT *,
NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY subject, rank;
|
Practical usage of SQL RANK functions
We can use SQL RANK function to fetch specific rows from the data., Supongamos que queremos obtener los datos de los estudiantes de los rangos 1 a 3. En la siguiente consulta, usamos expresiones de tabla comunes(CTE) para obtener datos usando la función ROW_NUMBER () y luego filtramos el resultado de CTE para satisfacer nuestra condición.,d>
podemos usar el comando offset fetch a partir de SQL Server 2012 para obtener un número específico de Registros.,v id=»0fa57edc44″>
a quick summary of SQL Rank functions
|
ROW_NUMBER |
asigna el número de rango secuencial a cada registro único., |
|
CLASIFICACIÓN |
Se asigna el número de clasificación para cada fila en una partición. Omite el número para valores similares. |
|
Dense_RANK |
Se asigna el número de clasificación para cada fila en una partición. No omite el número para valores similares. |
|
NTILE(N) |
divide el número de filas como por partición especificada y se asigna un valor único en la partición., |
conclusión
en este artículo, exploramos las funciones de rango SQL y la diferencia entre estas funciones. Es útil que los desarrolladores de sql estén familiarizados con estas funciones para explorar y administrar bien sus datos. Si tiene algún comentario o pregunta, no dude en dejarlos en los comentarios a continuación.,
- Autor
- mensajes Recientes
Ver todos los mensajes por Rajendra Gupta
- la Realización de menor y mayor actualización de la versión para AWS RDS SQL Server – 29 de enero de 2021
- la Implementación de AWS RDS para PostgreSQL instancias – 27 de enero de 2021
- la Migración de las instalaciones de la bases de datos de SQL AWS RDS de SQL Server mediante el uso de AWS DMS – 25 de enero de 2021