Aprenda a usar el rango, el rango denso, el número de fila, la distribución acumulativa, el rango de percentiles, los cuartiles, los percentiles y más
Foto de Austris Augusts en UnsplashLas funciones de numeración asignan un número (o decimales) a cada registro de una tabla. Se utilizan principalmente para clasificar o asignar un número secuencial a los datos para su posterior procesamiento (deduplicación, filtrado, agrupación). Por lo general, deben ordenarse por una dimensión específica (fecha, ingresos, salario, ID, etc.). Pueden ser utilizado para responder a las siguientes preguntas:¿Cuáles son los principales países generadores de ingresos?¿Cómo clasifico a los jugadores de voleibol por división y salario?¿Cuáles son los países con mejor desempeño N por categoría de producto?¿Qué filas se duplican en función de una fecha de ingesta?Este artículo se dividirá en dos secciones La primera sección cubrirá los mecanismos de RANK(), DENSE_RANK() y ROW_NUMBER(), ya que tienen un propósito muy similar pero un resultado y un mecanismo ligeramente diferentes. La segunda sección cubrirá PERCENT_RANK, CUME_DIST y NTILE , que tienen diferentes propósitos, mecanismos y resultados. También sugiero leer la excelente documentación de Google. Para comprender mejor las diferencias entre estas funciones, consultaremos el siguiente conjunto de datos que contiene las ventas de Google Merchandise Shop, para diferentes países y categorías de productos.Nuestra tabla base para casos de uso de análisis simples. (Imagen del autor)
NUMERO DE FILA
La función ROW_NUMBER() siempre devolverá un número único comenzando desde 1 e incrementando (1,2,3,4…,8,9…) en secuencia. No es necesario especificar un orden y el número de salida siempre será único, incluso si las filas o los valores son similares. Si no utiliza una cláusula ORDER BY, los resultados serán no deterministalo que significa que los resultados serán diferentes incluso con los mismos datos de entrada. Veamos dos ejemplos:ROW_NUMBER() devuelve un número consecutivo y único. (Imagen del autor) En este ejemplo, usamos una cláusula OVER() vacía, lo que significa que la función revisará la tabla y atribuirá un número al azar para cada fila (aunque podría haber cierta lógica sobre cómo BigQuery lo asigna). mire lo que sucede en nuestro segundo ejemplo cuando agregamos una cláusula ORDER BY al campo de ingresos.ROW_NUMBER() devuelve un número consecutivo y único ordenado por ingresos. (Imagen del autor) El número de fila ahora está ordenado por ingresos, y queríamos que los resultados estuvieran en orden descendente, por lo que agregamos un comando DESC (por defecto, es ascendente). Para el dos filas que tienen el mismo valorIndonesia y Taiwán, el número de salida de la función sigue siendo incremental. Implícitamente, también hay un orden alfabético, y esto se puede cambiar agregando manualmente otro parámetro de clasificación.
ROW_NUMBER() OVER(ORDEN POR ingresos DESC, país DESC)
Digamos que queremos desglosar el número de fila por categoría de producto. Para eso, podemos usar una cláusula PARTITION BY y ordenar por valor de ingresos descendente.ROW_NUMBER() devuelve un número consecutivo y único a partir de 1 nuevamente, por partición. (Imagen del autor) Esto puede ser muy beneficioso para clasificar/asignar un orden dentro de diferentes grupos o categorías que pueda tener disponibles en su conjunto de datos.
RANGO y DENSO_RANGO
Las funciones RANK() y DENSE_RANK() actuarán de forma idéntica a ROW_NUMBER() con dos excepciones: cómo números de secuencia y como ellos administrar valores similares.Para RANK(), las filas similares recibirán el mismo número de rango, pero la función dejará un espacio después de dos o más filas idénticas. Para DENSE_RANK(), las filas similares recibirán el mismo número de rango, pero el número de rango siempre se incrementa por 1 y no habrá espacio en nuestra secuencia numérica. Ilustremos el tres funciones en una consulta:Las tres funciones están ordenadas por ingresos. (Imagen del autor) Aquí está el resultado de nuestras diferentes funciones. Puedes centrarte en cómo funciona la función. en las filas 5 y 6 para el país Venezuela (que son duplicados) y después de:ROW_ NUMBER() mantiene su secuencia incremental (1,2,3,4,5,6,7)RANK() da el mismo valor de salida (5,5) pero luego pierde su secuencia incremental (1,2,3,4,5,5,7)DENSE_RANK() da el mismo valor de salida (5,5) pero mantiene su secuencia incremental (1,2,3,4,5,5,6)Los mismo mecanismo ocurre para las filas 10 y 11, ya que estamos ordenando por ingresos y tienen los mismos ingresos. Este mecanismo de secuencia incremental funcionará igual para cualquier número de valores de pares.¿Por qué no usar ROW_NUMBER en lugar de RANK o DENSE_RANK?Podría usar ROW_NUMBER() como una función de clasificación, pero a veces es interesante mantener el mismo valor de clasificación para todas las filas similares/pares. Para ciertos casos de uso, mantener la secuencia numérica siempre incrementada en 1 con DENSE_RANK() podría ser una buena elección. .
CUME_DIST
La función CUME_DIST() calcula la distribución acumulada de valores dentro de un conjunto de datos o una partición. Devuelve valores de 0 a 1 (>0 y ≤1). Esta función requiere una cláusula ORDER BY para clasificar los valores. Según la documentación de Google, se calcula mediante la fórmula: NP/NR. Así es como podemos tratar de explicarlo:NP es el número de filas que vienen antes o son similares a la fila actualNR es el número total de filas (de todo el conjunto de datos o una partición)Te mostrará cómo los valores en tu conjunto de datos se distribuyen. Como ejemplo, la distribución de nuestras filas de conjuntos de datos en función de los ingresos:Distribución acumulativa para todas las filas ordenadas por ingresos. (Imagen del autor) Usamos la función ROUND() y la multiplicación *100 para convertir los datos en un formato de porcentaje más legible. Hagamos el cálculo manualmente. Para nuestra primera fila, solo hay 1 valor y ningún ingreso debajo 1323. esto nos da 1/12 = 8% (tenemos 12 filas en nuestro conjunto de datos). Ahora, veamos la fila 4, Nigeria, hay 3 filas con un valor por debajo de 3314 + esta fila actual. esto nos da 4/12 = 33%.La parte interesante es para filas 2 y 3 (o filas 7 y 8). Tienen el mismo valor de ingresos. Entonces, si observamos la fila 2, podríamos esperar un cálculo de 2/12 = 16%. Pero, como la fila 3 es similar, ambas filas darán como resultado 3/12 = 25%.
NTILE
La función NTILE() le permite dividir un conjunto de puntos de datos clasificados en cubos distribuidos uniformemente. Usted puede saber esto como cuantilesque pueden ser de diferentes tipos:Cuartiles (4 cuantiles)deciles (10 cuantiles)percentiles (100 cuantiles) Por ejemplo, cuartiles divida su conjunto de datos en cuatro cubos de igual tamaño. Esto significa que el primer cuartil (Q1) contiene el 25 % de los puntos de datos. Apliquemos cuartiles a nuestra tabla:Nuestras filas se dividen en 4 cubos de igual tamaño según los ingresos. (Imagen del autor) En este caso, dividimos nuestros puntos de datos en cuatro cubos iguales en función de los ingresos solicitados (cada cubo tiene 3 filas, que es 3/12 = 25%).Tenga en cuenta esto no es un porcentaje del total. Si nosotros sume los ingresos de los países del cuarto cuartil (Francia, Japón y Estados Unidos), representa el 90 % de los ingresos totales. Al utilizar esta función, debe proporcionar un número de entrada, por ejemplo: NTILE(4). No puede dejar este parámetro vacío, dar un número de entrada que sea 0 o un valor negativo sin ver un error. Puede usar este método para encontrar valores atípicos en sus datos (puntos de datos por encima del percentil del 95 %), categoriza a tus clientes en función de su valor de compra (el 25% superior), y así sucesivamente.
PERCENT_RANK
La función PERCENT_RANK() calcula la distribución percentil del valor dentro de un conjunto de valores. Devuelve valores de 0 a 1. Esta función requiere una cláusula ORDER BY para ordenar los valores.El rango de percentil, ordenado por ingresos, para todas las filas (Imagen por autor) Nuevamente, usamos la función ROUND() y la multiplicación *100 para convertir los datos en un formato de porcentaje más legible. Mirando el resultado, Alemania tiene los ingresos más bajos (no mayor que cualquier otro país), por lo que el rango percentil es cero.Por otro lado, Estados Unidos tiene los mayores ingresos de todos (más que cualquier otro país), de ahí la rango percentil es 1 (o 100%).Para Francia, el rango percentil es 82%. Eso significa que sus ingresos son superiores al 82% de todos los demás países. Desde una experiencia práctica, las funciones más utilizadas son ROW_NUMBER(), RANK(), DENSE_RANK() y NTILE(). Como ejemplo, una de mis tareas requería usar DENSE_RANK() para identificar productos de adquisición, básicamente clasificando los productos en general de los pedidos de los clientes para identificar cuáles fueron los primeros productos comprados. Esta función nos permitió mantener la secuencia incrementada en uno, tratar con múltiples productos dentro de un solo pedido y aún poder contar el número correcto de pedidos totales para un cliente. En otra misión, NTILE() ayudó a clasificar clientes en cubos de compradores recientes y frecuentes (como un modelo RFM (Recency, Frequency, Monetary)) que se usaría para crear segmentos en nuestro sistema de proveedor de servicios de correo electrónico. Puede encontrar estas funciones generalmente disponibles en otros sistemas de bases de datos (Amazon Redshift, MySQL, Postgres, Snowflake, etc.) ya que son populares Funciones de ventana SQL (al menos para clasificación y números de fila).
Referencias y conjunto de datos
El conjunto de datos que se usa en este artículo proviene de los datos públicos de BigQuery y es una muestra de los datos de análisis de Google bajo la licencia CC BY 4.0.