- Introducción
- Preparar
- Consultas Top-N
- Paginación a través de datos
- Información extra
- Transformación de consultas
Introducción
Una consulta Top-N se utiliza para recuperar las N filas superiores o inferiores de un conjunto ordenado. La combinación de dos consultas Top-N le brinda la posibilidad de recorrer un conjunto ordenado. Este concepto no es nuevo. De hecho, Oracle ya ofrece varias formas de realizar consultas Top-N, como se explica aquí. Estos métodos funcionan bien, pero parecen bastante complicados en comparación con los métodos proporcionados por otros motores de bases de datos. Por ejemplo, MySQL usa una cláusula LIMIT para desplazarse por un conjunto de resultados ordenado.
SELECT * FROM my_table ORDER BY column_1 LIMIT 0 , 40
Oracle 12c ha introducido la cláusula de limitación de filas para simplificar las consultas Top-N y la paginación a través de conjuntos de resultados ordenados.
Preparar
Para ser coherentes, usaremos la misma tabla de ejemplo utilizada en el artículo de Consultas Top-N.
Crea y completa 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>
Consultas Top-N
La sintaxis de la cláusula de limitación de filas parece un poco complicada a primera vista.
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
En realidad, para la consulta clásica de Top-N es muy simple. El siguiente ejemplo devuelve los 5 valores más grandes de un conjunto ordenado. El uso de la cláusula ONLY limita el número de filas devueltas al número exacto solicitado.
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>
El uso de la cláusula WITH TIES puede dar como resultado que se devuelvan más filas si varias filas coinciden con el valor de la fila N-ésima. En este caso, la quinta fila tiene el valor «8», pero hay dos filas que empatan en el quinto lugar, por lo que se devuelven ambas.
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;
VAL
----------
10
10
9
9
8
8
6 rows selected.
SQL>
Además de limitar por número de filas, la cláusula de limitación de filas también nos permite limitar por porcentaje de filas. La siguiente consulta devuelve el 20% inferior de filas.
SELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
SQL>
Paginación a través de datos
Paginar a través de un conjunto de resultados ordenado fue un poco molesto con el enfoque clásico de consulta Top-N, ya que requería dos consultas Top-N, una anidada dentro de la otra. Por ejemplo, si quisiéramos el segundo bloque de 4 filas, podríamos hacer lo siguiente.
SELECT val
FROM (SELECT val, rownum AS rnum
FROM (SELECT val
FROM rownum_order_test
ORDER BY val)
WHERE rownum <= 8) WHERE rnum >= 5;
VAL
----------
3
3
4
4
4 rows selected.
SQL>
Con la cláusula de limitación de filas podemos lograr el mismo resultado utilizando la siguiente consulta.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
SQL>
El punto de partida para FETCH es OFFSET + 1.
El OFFSET siempre se basa en un número de filas, pero esto se puede combinar con un FETCH usando un PERCENT.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
SQL>
No es sorprendente que el desplazamiento, el recuento de filas y el porcentaje puedan, y probablemente deberían, ser variables de vinculación.
VARIABLE v_offset NUMBER;
VARIABLE v_next NUMBER;
BEGIN
:v_offset := 4;
:v_next := 4;
END;
/
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET :v_offset ROWS FETCH NEXT :v_next ROWS ONLY;
VAL
----------
3
3
4
4
SQL>
Información extra
- Las palabras clave ROW y ROWS se pueden usar indistintamente, al igual que las palabras clave FIRST y NEXT. Elija los que escaneen mejor al leer el SQL como una oración.
- Si no se especifica el desplazamiento, se supone que es 0.
- Los valores negativos para el desplazamiento, el recuento de filas o el porcentaje se tratan como 0.
- Los valores nulos para el desplazamiento, el recuento de filas o el porcentaje hacen que no se devuelvan filas.
- Las porciones fraccionarias de desplazamiento, recuento de filas o porcentaje se truncan.
- Si el desplazamiento es mayor o igual que el número total de filas del conjunto, no se devuelve ninguna fila.
- Si el recuento de filas o el porcentaje son mayores que el número total de filas después del desplazamiento, se devuelven todas las filas.
- La cláusula de limitación de filas no se puede utilizar con la cláusula FOR UPDATE, las pseudocolumnas de secuencia CURRVAL y NEXTVAL o en una vista materializada de actualización rápida.
Transformación de consultas
Vale la pena tener en cuenta que esta nueva funcionalidad es una transformación de consulta. Si tomamos una de las consultas anteriores y realizamos un rastreo 10053, podemos ver esto.
Compruebe el archivo de seguimiento de la sesión.
SELECT value FROM v$diag_info WHERE name = 'Default Trace File'; VALUE ---------------------------------------------------------------- /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_15539.trc 1 row selected. SQL>
Realice un rastreo 10053 de la declaración.
ALTER SESSION SET EVENTS '10053 trace name context forever'; SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY; ALTER SESSION SET EVENTS '10053 trace name context off';
La sección que comienza con «Consulta final después de las transformaciones» muestra la declaración que se procesó realmente, después de la transformación de la consulta.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."VAL" "VAL"
FROM (SELECT "ROWNUM_ORDER_TEST"."VAL" "VAL",
"ROWNUM_ORDER_TEST"."VAL" "rowlimit_$_0",
ROW_NUMBER() OVER ( ORDER BY "ROWNUM_ORDER_TEST"."VAL" DESC ) "rowlimit_$$_rownumber"
FROM "TEST"."ROWNUM_ORDER_TEST" "ROWNUM_ORDER_TEST") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=5
ORDER BY "from$_subquery$_002"."rowlimit_$_0" DESC
Como puede ver, la declaración se ha reescrito a un formulario que podríamos haber utilizado antes de 12c.
Pingback: Consultas Top-N - DBandTech.com