Las consultas Top-N proporcionan un método para limitar el número de filas devueltas de conjuntos de datos ordenados. Son extremadamente útiles cuando desea devolver el número «N» superior o inferior de filas de un conjunto o cuando está hojeando datos. Este artículo presenta varios métodos para implementar consultas Top-N.
- Preparar
- ¡Qué no hacer!
- Cláusula de limitación de filas (12c en adelante)
- Vista en línea y ROWNUM
- Cláusula WITH y ROWNUM
- RANK
- DENSE_RANK
- ROW_NUMBER
- PERCENT_RANK
- NTILE
Preparar
Primero debemos crear y completar una tabla de prueba
DROP TABLE rownum_order_test; CREATE TABLE rownum_order_test ( val NUMBER ); INSERT ALL INTO rownum_order_test INTO rownum_order_test SELECT level FROM dual CONNECT BY level <= 10; COMMIT;
La siguiente consulta muestra que tenemos 20 filas con 10 valores distintos.
SELECT val FROM rownum_order_test ORDER BY val; VAL ---------- 1 1 2 2 3 3 4 4 5 5 6 VAL ---------- 6 7 7 8 8 9 9 10 10 20 rows selected. SQL>
¡Qué no hacer!
El siguiente ejemplo muestra una trampa común en la que caen las personas cuando no comprenden la forma en que interactúan la pseudocolumna ROWNUM y la cláusula ORDER BY. Supongamos que queremos devolver los 5 valores principales en la columna ID. Podríamos decidir ordenar los datos por ID descendente y seleccionar las primeras cinco filas. Eso suena correcto, así que seguimos adelante y emitimos la siguiente consulta.
SELECT val FROM rownum_order_test WHERE rownum <= 5 ORDER BY val DESC; VAL ---------- 5 4 3 2 1 5 rows selected. SQL>
¡Eso no hizo lo que queríamos!
El problema es que la asignación ROWNUM se realiza antes de la operación ORDER BY, lo que da como resultado que se devuelvan datos potencialmente aleatorios.
Cláusula de limitación de filas (12c en adelante)
Si está utilizando Oracle 12c en adelante, las consultas de Top-N se han vuelto mucho más fáciles con la introducción de la cláusula de limitación de filas. Si la compatibilidad con versiones anteriores no es su problema, cambie y use esta nueva cláusula. Hay un ejemplo rápido a continuación, pero el artículo separado explica todas las formas de usar esta nueva sintaxis.
SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY; VAL ---------- 10 10 9 9 8 5 rows selected. SQL>
Vista en línea y ROWNUM
La consulta clásica de estilo Top-N usa una vista en línea ordenada para forzar los datos en el orden correcto, luego usa la verificación ROWNUM para limitar los datos devueltos.
SELECT val FROM (SELECT val FROM rownum_order_test ORDER BY val DESC) WHERE ROWNUM <= 5; VAL ---------- 10 10 9 9 8 5 rows selected. SQL>
Como los datos están en el orden deseado antes de que se realice la verificación ROWNUM, obtenemos el resultado que queríamos. Observe que pedimos 5 filas y obtuvimos cinco, aunque hay una segunda fila con el valor «8».
Podemos devolver los 5 valores más pequeños modificando la cláusula ORDER BY a ascendente.
SELECT val FROM (SELECT val FROM rownum_order_test ORDER BY val) WHERE rownum <= 5; VAL ---------- 1 1 2 2 3 5 rows selected. SQL>
Este método también se puede utilizar para paginar datos, como informes web paginados.
SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM rownum_order_test ORDER BY val) WHERE rownum <= 8) WHERE rnum >= 4; VAL ---------- 2 3 3 4 4 5 rows selected. SQL>
Esto parece que podría no funcionar bien, pero podemos ver en el plan de ejecución que Oracle puede empujar los predicados hacia abajo en las vistas en línea para hacerlas mucho más eficientes, por lo que esta es la mejor manera de buscar datos usando SQL.
Execution Plan ---------------------------------------------------------- Plan hash value: 2927523340 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 208 | 4 (25)| 00:00:01 | |* 1 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 100 | 1300 | 4 (25)| 00:00:01 | |* 4 | SORT ORDER BY STOPKEY| | 100 | 1300 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | ROWNUM_ORDER_TEST | 100 | 1300 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RNUM">=4) 2 - filter(ROWNUM<=8) 4 - filter(ROWNUM<=8)
Cláusula WITH y ROWNUM
El ejemplo anterior se puede reescribir para usar una cláusula WITH en lugar de la vista en línea.
WITH ordered_query AS (SELECT val FROM rownum_order_test ORDER BY val DESC) SELECT val FROM ordered_query WHERE rownum <= 5; VAL ---------- 10 10 9 9 8 5 rows selected. SQL>
RANK
La función analítica RANK asigna un rango secuencial para cada valor distinto en la ventana especificada.
SELECT val FROM (SELECT val, RANK() OVER (ORDER BY val DESC) AS val_rank FROM rownum_order_test) WHERE val_rank <= 5; VAL ---------- 10 10 9 9 8 8 6 rows selected. SQL>
A primera vista, parece que puede haber un problema, pero mostrar la información de clasificación nos muestra lo que está sucediendo.
SELECT val, val_rank FROM (SELECT val, RANK() OVER (ORDER BY val DESC) AS val_rank FROM rownum_order_test) WHERE val_rank <= 5; VAL VAL_RANK ---------- ---------- 10 1 10 1 9 3 9 3 8 5 8 5 6 rows selected. SQL>
A partir de esto, podemos ver que a las filas duplicadas se les asigna el mismo rango, seguido de un salto en la secuencia para mantener el rango consistente. Similar a los lugares para medallas olímpicas. Esto significa que la función RANK no nos da las «primeras N filas» o los «primeros N valores distintos». El número de filas devueltas depende del número de duplicados en los datos.
Hay un artículo sobre la función analítica RANK aquí.
DENSE_RANK
La función analítica DENSE_RANK es similar a la función analítica RANK en que asigna un rango secuencial para cada valor distinto en la ventana especificada. La diferencia es que los rangos están compactados, por lo que no hay brechas.
SELECT val FROM (SELECT val, DENSE_RANK() OVER (ORDER BY val DESC) AS val_rank FROM rownum_order_test) WHERE val_rank <= 5; VAL ---------- 10 10 9 9 8 8 7 7 6 6 10 rows selected. SQL>
La visualización de la información de clasificación nos muestra lo que está sucediendo.
SELECT val, val_rank FROM (SELECT val, DENSE_RANK() OVER (ORDER BY val DESC) AS val_rank FROM rownum_order_test) WHERE val_rank <= 5; VAL VAL_RANK ---------- ---------- 10 1 10 1 9 2 9 2 8 3 8 3 7 4 7 4 6 5 6 5 10 rows selected. SQL>
Una vez más, a los valores duplicados se les asigna el mismo rango, pero no hay brecha en la secuencia de rango. Como resultado, DENSE_RANK siempre nos da un resultado de «N valores distintos superiores».
Hay un artículo sobre la función analítica DENSE_RANK aquí.
ROW_NUMBER
La función analítica ROW_NUMBER es similar a la pseudocolumna ROWNUM en que asigna un número único para cada fila devuelta, pero como todas las funciones analíticas, su acción puede limitarse a una ventana específica de datos en el conjunto de resultados y en función del orden de los datos en esa ventana. En este ejemplo simple, usando una ventana de todo el conjunto de resultados, funciona igual que la columna pseudocolumna ROWNUM.
SELECT val FROM (SELECT val, ROW_NUMBER() OVER (ORDER BY val DESC) AS val_row_number FROM rownum_order_test) WHERE val_row_number <= 5; VAL ---------- 10 10 9 9 8 5 rows selected. SQL>
Hay un artículo sobre la función analítica ROW_NUMBER aquí.
PERCENT_RANK
La función analítica PERCENT_RANK asigna un valor entre 0-1 que representa la posición de la fila actual en relación con el conjunto como porcentaje. El siguiente ejemplo muestra el 80% superior de las filas según el valor.
SELECT val FROM (SELECT val, PERCENT_RANK() OVER (ORDER BY val) AS val_percent_rank FROM rownum_order_test) WHERE val_percent_rank >= 0.8; VAL ---------- 9 9 10 10 4 rows selected. SQL>
Hay un artículo sobre la función analítica PERCENT_RANK aquí.
NTILE
La función analítica NTILE divide las filas en el número especificado de depósitos, cada uno con aproximadamente el mismo número de filas. El siguiente ejemplo divide los resultados en tres grupos y muestra las filas del tercer grupo o del tercio superior de los resultados.
SELECT val FROM (SELECT val, NTILE(3) OVER (ORDER BY val) AS val_ntile FROM rownum_order_test) WHERE val_ntile = 3; VAL ---------- 8 8 9 9 10 10 6 rows selected. SQL>
Hay un artículo sobre la función analítica NTILE aquí.
¡Hola! Me llamo Laura y estoy encantada de saludarte.
Quería escribirte porque me ha parecido interesante comentar contigo la posibilidad de que tu negocio aparezca cada mes en *periódicos digitales como noticia* para posicionar en los primeros lugares de internet, es decir, con artículos reales dentro del periódico que no se marcan como publicidad y que no se borran.
La noticia es publicada por más de sesenta periódicos de gran autoridad para mejorar el *posicionamiento de tu web* y la reputación.
¿Podrías facilitarme un teléfono para ofrecerte *hasta dos meses gratuitos*?
Gracias