Varias funciones de clasificación comunes en SQL

El propósito de la función de clasificación es devolver un valor ordenado según el conjunto de resultados. El valor de clasificación es un número, normalmente un valor de fila que comienza en 1 y aumenta a partir de 1. La determinación del valor de clasificación mediante una función de clasificación permite que el conjunto de resultados actual o algunas filas de datos tengan el mismo valor de clasificación único. En la siguiente sección, cubriré las diferentes funciones de clasificación y cómo usarlas.

Ejemplo de uso de la función RANK La función RANK ordena cada partición comenzando desde 1. "Partición" se refiere a un conjunto de filas de datos que tienen los mismos valores de columna para una partición específica. Si una partición tiene el mismo valor de columna de clasificación (esta columna se especifica después de ORDER BY), a las filas con el mismo valor de columna de clasificación se les asignará el mismo valor de clasificación. Esto es un poco complicado, así que para comprender mejor cómo usarlo, veamos la siguiente sintaxis:

RANK()OVER([PARTITIONBYlt;partition_columngt;]ORDERBYlt;order_by_columngt;) Aquí hay algunos parámetros :

lt; partición_columngt;: Especifique uno o más nombres de columnas utilizadas como datos de partición

lt; ordenar por columnagt;: Determine una o más columnas y luego use Ordenar los datos de salida. para cada partición

Nota: La cláusula PARTITION BY es opcional. Si no se utiliza esta cláusula, todos los datos se ordenarán en una partición. Si se especifica la cláusula PARTITION BY, el conjunto de datos para cada partición se ordenará comenzando desde 1.

Ahora que comprende la sintaxis de la función RANK y cómo funciona, ejecute los dos ejemplos de funciones siguientes. Es importante tener en cuenta que mis ejemplos se ejecutan en la base de datos AdventureWorks2012, que se puede descargar desde el sitio web /releases/view/93587.

Aquí está el primer ejemplo que utiliza la función RANK:

1234567USE AdventureWorks2012;GOSELECTPostalCode,StateProvinceID,RANK() OVER(ORDERBYPostalCodeASC)ASRankingValueFROMPerson. AddressWHEREEStateProvinceIDIN(23, 46);

Código 1: solo la función RANK no está particionada

Después de ejecutar el código, el conjunto de resultados es el siguiente:

PostalCodeStateProvinceIDRankingValue

------------------------------------------ ------ --

03064461

03064461

03106463

03276464

03865465

83301236

83402237

83501238

83702239

838642310 Como se muestra arriba, el conjunto de resultados se ordena por RankingValue columna en la función RANGO. En el ejemplo, la clasificación se basa en la columna PostalCode. Aquí, el código postal es 03054 y hay dos filas de datos, ambas con un RankingValue de 1. RankingValue 2 se omite porque hay dos unos. El resto de la clasificación continuará a lo largo de la secuencia.

Dado que no existe una cláusula de partición que utilice la función RANK, todo el conjunto de resultados se trata como una sola partición. Si tuviera que particionar por StateProvinceID independiente y luego ordenar, podría seguir este ejemplo:

12345678USE AdventureWorks2012;GOSELECTPostalCode,StateProvinceID,RANK() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46) ;

Código 2: Uso de términos de partición

El conjunto de resultados después de ejecutar el código:

PostalCodeStateProvinceIDRankingValue

------- -- -------------------------------------

83301231

83402232

83501233

83702234

83864235

03064461

03064461

03106463

03276464

03865465

La salida se divide en dos particiones, el StateProvinceID de una partición es 23 y el StateProvinceID de la otra partición a 46.

Usando la función DENSE_RANK

Al ejecutar la función RANK, la salida omite un valor de clasificación de 2 debido al mismo código postal. Al usar la función DENSE_RANK, puedo generar una clasificación. valor que no se salta el mismo Orden de valores ordenados. La sintaxis de esta función es la siguiente:

DENSE_RANK()OVER([PARTIIONBYlt;partition_columngt;]ORDERBYlt;order_by_columngt;)

La única diferencia en la sintaxis es que el nombre de la función tiene cambió. AddressWHEREStateProvinceIDIN(23, 46);

Código 3: uso de DENSE_RANK

El conjunto de resultados es el siguiente:

PostalCodeStateProvinceIDRankingValue

-- -------------------------------------------------- --

83301231

83402232

83501233

83702234

83864235

03064461

03064461

03106462

03276463

03865464

Según el conjunto de resultados, se puede ver que el valor de clasificación del código postal 03064 es el mismo, pero el valor de clasificación para el siguiente código postal es 2 en lugar de 3. La función RANK se diferencia en que garantiza que no falten rangos en la secuencia ordenada cuando se repiten los valores ordenados.

Uso de la función NTILE Esta función divide la recopilación de datos en grupos. El número de grupos está determinado por el número entero especificado.

La siguiente es la sintaxis de la función NTILE:

NTILE(integer_expression)OVER([PARTIIONBYlt;partition_columngt;]ORDERBYlt;order_by_columngt;)donde:

lt;integer_expressiongt;: Determina el número de grupos diferentes a crear

lt;partition_columngt;: Determina la columna utilizada para particionar los datos

lt;order by columngt;: Determina la columna utilizada para ordenar los datos de salida de cada partición

lt; p>

Para comprender mejor, revisemos algunos ejemplos diferentes. Ejecute el siguiente código:

1234567USE AdventureWorks2012; GOSELECTPostalCode, StateProvinceID, NTILE(2) OVER(ORDERBYPostalCodeASC) ASNTileValueFROMPerson.AddressWHEREEStateProvinceIDIN(23, 46);

Código4: Utilice la consulta de función NTILE

Los resultados de la ejecución son los siguientes:

PostalCodeStateProvinceIDNTileValue

-------------------- ------ --------------------------

03064461

03064461

03106461

03276461

03865461

83301232

83402232

83501232

83702232

83864232

Al observar el conjunto de resultados, es fácil encontrar que hay dos columnas NTileValue diferentes, 1 y 2. La razón por la que hay dos valores NTileValue diferentes es porque especifiqué "NTILE (2)" en la declaración de consulta. El valor entre paréntesis es una expresión entera que especifica el número de grupos que se crearán. Cuando ve 10 filas de datos en el conjunto de resultados, las primeras cinco filas tienen un valor NTileValue de 1 y las últimas cinco filas tienen un valor NTileValue de 2.

Si hay un parámetro que no se puede dividir en partes iguales entre los grupos, por ejemplo, si hay un parámetro que no se puede dividir por un número entero, entonces el conjunto de resultados no se dividirá por un número entero. En este caso, las filas que no son divisibles se colocan en cada grupo por turno hasta que se hayan asignado todas las filas restantes.

AddressWHEREStateProvinceIDIN(46, 23);

Código 5: la consulta NTile no pudo distribuir uniformemente el conjunto de resultados

El código de ejecución es el siguiente:

PostalCodeStateProvinceIDNTileValue

--------------------------------------------- --- -

03064461

03064461

03106461

03276462

03865462

83301232

83402233

83501233

83702234

83864234

Aquí vamos directo a El punto, entre las 10 filas de resultados, las filas con el parámetro 4 deben dividirse en 4 grupos, por lo que el resto de 10 dividido por 4 es 2. Esto significa que los dos primeros grupos tendrán una fila más que los dos últimos grupos. Como se muestra arriba, en este resultado, hay 3 filas para el grupo 1 y 2, y luego solo 2 filas para el grupo con NTileValue de 3 y 4.

Al igual que la función RANK, también podemos crear una función NTILE bajo una partición usando la cláusula PARTITION BY. Cuando se introduce la cláusula PARTITION BY, el orden NTILE dentro de cada partición comienza desde 1. AddressWHEREStateProvinceIDIN(46, 23);

Código 6: use NTile para consultar agrupaciones desiguales después de usar la cláusula de partición

El código de ejecución es el siguiente:

PostalCodeStateProvinceIDNTileValue

----------------------------------------- ----- ------- -----

83301231

83402231

83501232

83702232

83864233

03064461

03064461

03106462

03276462

03865463

El conjunto de resultados le permite ver los resultados en NTILE El efecto de agregar una cláusula de partición a la función. Si observa el resultado de la columna NTileValue, puede ver que la clasificación comienza en StateProvinceID 46 y termina en 1. Este es el efecto de agregar la cláusula "PARTICIÓN POR StateProvinceID", dividir primero y luego ordenar por grupo.

Uso de la función ROW_NUMBER Cuando desee generar números de fila para las filas de salida, los números de fila se incrementarán en pasos de 1.