Consultas Top-N

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í.