Filtrando Datos con TOP y OFFSET-FETCH

En un post anterior se cubrió el filtrado de datos utilizando predicados, y en otro se cubrió el ordenamiento de datos. Este post en un sentido mezcla los conceptos de filtrado y de ordenamiento. A menudo, necesita filtrar los datos basados en un ordenamiento dado y un número especificado de filas. Piense acerca de las solicitudes como "retornar los tres pedidos más recientes" y "retornar los cinco productos más caros". El filtrado implica alguna especificación de ordenamiento y un número solicitado de filas. T-SQL proporciona dos opciones para manejar tales necesidades de filtrado: una es la opción propietaria TOP y la otra es la opción estándar OFFSET-FETCH que fue introducida en SQL Server 2012.

Filtrando Datos con TOP

Con la opción TOP, puede filtrar un número o porcentaje solicitado de filas del resultado de la consulta, basado en el ordenamiento indicado. Se especifica la opción TOP en la cláusula SELECT seguido por el número solicitado de filas entre paréntesis (tipo de datos BIGINT). La especificación de ordenamiento del filtro TOP es basado en la misma cláusula ORDER BY que es normalmente utilizada para el orden de presentación.

A modo de ejemplo, la siguiente consulta retorna los tres pedidos más recientes.

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

Especifica 3 como el número de filas que desea filtrar y orderdate DESC como la especificación de orden. Para que pueda obtener las tres filas con las fechas de pedido más recientes. Aquí está la salida de esta consulta.

orderid      orderdate               custid      empid

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

11077       2008-05-06 00:00:00.000  65        1

11076       2008-05-06 00:00:00.000  9          4

11075       2008-05-06 00:00:00.000  68        8

TOP y Paréntesis

T-SQL soporta especificar el número de filas a filtrar, usando la opción TOP en las consultas SELECT sin paréntesis, pero eso es sólo por razones de compatibilidad con versiones anteriores. La sintaxis correcta es con paréntesis.

También puede especificar un porcentaje de filas a filtrar en lugar de un número. Para ello, especifique un valor FLOAT en el rango de 0 a 100 en el paréntesis, y la palabra clave PERCENT después del paréntesis, de la siguiente manera.

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

La opción PERCENT coloca un tope al número de filas resultante si no es total. En este ejemplo, sin la opción TOP, el número de filas en el resultado es 830. Filtrando el 1 por ciento da 8.3, y luego el tope de este valor da 9; por lo tanto, la consulta retorna 9 filas.

orderid     orderdate               custid      empid

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

11076       2008-05-06 00:00:00.000     9         4

11077       2008-05-06 00:00:00.000     65        1

11075       2008-05-06 00:00:00.000     68        8

11074       2008-05-06 00:00:00.000     73        7

11070       2008-05-05 00:00:00.000     44        2

11071       2008-05-05 00:00:00.000     46        1

11073       2008-05-05 00:00:00.000     58        2

11072       2008-05-05 00:00:00.000     20        4

11067       2008-05-04 00:00:00.000     17        1

La opción TOP no está limitada a una entrada constante; en cambio, le permite especificar una expresión autosuficiente. Desde una perspectiva práctica, esta capacidad es especialmente importante cuando necesita pasar un parámetro o una variable como entrada, como el código siguiente demuestra.

DECLARE @n AS BIGINT = 5;

SELECT TOP (@n) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

Esta consulta genera la siguiente salida.

orderid     orderdate               custid      empid

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

11076       2008-05-06 00:00:00.000     9         4

11077       2008-05-06 00:00:00.000     65        1

11075       2008-05-06 00:00:00.000     68        8

11074       2008-05-06 00:00:00.000     73        7

11070       2008-05-05 00:00:00.000     44        2

En la mayoría de los casos, necesita su opción TOP para confiar en alguna especificación de orden, pero resulta que, una cláusula ORDER BY no es obligatoria. Por ejemplo, la siguiente consulta es técnicamente válida.

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders;

Sin embargo, la consulta no es determinística. La consulta filtra tres filas, pero no tiene garantía de que tres filas serán retornadas. Termina obteniendo las tres filas que SQL Server accede primero, y eso es dependiente de la optimización. Por ejemplo, esta consulta dio la siguiente salida en un sistema.

orderid     orderdate               custid      empid

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

11011       2008-04-09 00:00:00.000     1         3

10952       2008-03-16 00:00:00.000     1         1

10835       2008-01-15 00:00:00.000     1         1

Pero no hay alguna garantía de que las mismas filas serán retornadas si ejecuta la consulta de nuevo. Realmente después de tres filas arbitrarias, podría ser una buena idea añadir una cláusula ORDER BY con la expresión (SELECT NULL) para permitir que la gente sepa que su elección es intencional y no un descuido. Así es como su consulta se vería.

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY (SELECT NULL);

Note que incluso cuando tiene una cláusula ORDER BY, a fin de que la consulta sea completamente determinística, el ordenamiento debe ser único. Por ejemplo, considere de nuevo la primera consulta de esta sección.

SELECT TOP (3) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

La columna orderdate no es única, por lo que el ordenamiento en caso de empate es arbitrario. Cuando esta consulta fue ejecutada, el sistema retornó la siguiente salida.

orderid     orderdate               custid      empid

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

11077      2008-05-06 00:00:00.000      65           1

11076      2008-05-06 00:00:00.000      9            4

11075      2008-05-06 00:00:00.000      68           8

¿Pero pasa que si hay otras filas en el resultado sin TOP que tienen la misma fecha de pedido como en la última fila aquí? No siempre le preocupa garantizar resultados determinísticos o repetibles; pero si lo hace, hay dos opciones disponibles. Una opción es preguntar para incluir todos los empates con la última fila por agregar la opción WITH TIES, de la siguiente manera.

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

Por supuesto, esto podría resultar en retornar más filas de las que pidió, como la salida de esta consulta muestra.

orderid     orderdate               custid      empid

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

11077       2008-05-06 00:00:00.000     65           1

11076       2008-05-06 00:00:00.000     9            4

11075       2008-05-06 00:00:00.000     68           8

11074       2008-05-06 00:00:00.000     73           7

La otra opción para garantizar el determinismo es romper los empates por agregar un desempate que hace el ordenamiento único. Por ejemplo, en caso de empate en la fecha de pedido, suponga que desea la fila con el orderid más grande a "ganador". Para ello, agregue orderid DESC a su cláusula ORDER BY, de la siguiente manera.

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC;

Aquí está la salida de esta consulta.

orderid     orderdate                 custid      empid

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

11077      2008-05-06 00:00:00.000      65           1

11076      2008-05-06 00:00:00.000      9            4

11075      2008-05-06 00:00:00.000      68           8

La consulta es ahora determinística, y los resultados están garantizados que sean repetibles, siempre y cuando los datos originarios no cambien.

Para concluir esta sección, sólo quisiera señalar que la opción TOP también puede ser utilizada en las sentencias de modificación para limitar cuantas filas serán modificadas.

Filtrando Datos con OFFSET-FETCH

La opción OFFSET-FETCH es una opción de filtrado que, como TOP, se puede utilizar para filtrar datos basados en un número especificado de filas y de ordenamiento. Pero a diferencia de TOP, es estándar, y también tiene una capacidad de salto, haciéndolo útil para los propósitos de paginación ad-hoc.

Las cláusulas OFFSET y FETCH aparecen justo después de la cláusula ORDER BY, y de hecho, en T-SQL, requieren que una cláusula ORDER BY esté presente. Primero se especifica la cláusula OFFSET indicando cuantas filas desea saltar (o si no desea saltar alguna); entonces, opcionalmente especifica la cláusula FETCH indicando cuantas filas quiere filtrar. Por ejemplo, la siguiente consulta define el ordenamiento basado en orderdate descendente, seguido por el orderid descendente; entonces salta 50 filas y recupera las próximas 25 filas.

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

Esto es una forma abreviada de la salida.

orderid     orderdate               custid      empid

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

11027       2008-04-16 00:00:00.000     10           1

11026       2008-04-15 00:00:00.000     27           4

...

11004       2008-04-07 00:00:00.000     50           3

11003       2008-04-06 00:00:00.000     78           3

La cláusula ORDER BY ahora desempeña dos roles: Un rol le dice a la opción OFFSET-FETCH cuales filas necesita filtrar. Otro rol está determinando el orden de presentación en la consulta.

Como se ha mencionado, en T-SQL, la opción OFFSET-FETCH requiere que una cláusula ORDER BY esté presente. También, en T-SQL, contrario al SQL estándar, una cláusula FETCH requiere que una cláusula OFFSET esté presente. Si desea filtrar algunas filas, pero sin omitir alguna, aún necesita especificar la cláusula OFFSET con 0 ROWS.

Con el fin de hacer la sintaxis intuitiva, puede utilizar las palabras clave NEXT o FIRST indistintamente. Cuando salta algunas filas, podría serle más intuitivo utilizar las palabras clave FETCH NEXT para indicar cuantas filas filtrar; pero cuando no salta alguna fila, podría ser más intuitivo utilizar las palabras claves FETCH FIRST, de la siguiente manera.

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

Por razones similares, puede utilizar la forma singular ROW o la forma plural ROWS, indistintamente, tanto para el número de filas a saltar y para el número de filas a filtrar. Pero no es que obtendrá un error si dice FETCH NEXT 1 ROWS o FETCH NEXT 25 ROW. Todo depende de utilizar una forma adecuada, al igual que con el inglés.

Mientras que en T-SQL, una cláusula FETCH requiere una cláusula OFFSET, y la cláusula OFFSET no requiere una cláusula FETCH. En otras palabras, por indicar una cláusula OFFSET, está solicitando saltar algunas filas; entonces, por no indicar una cláusula FETCH, está solicitando retornar todas las filas restantes. Por ejemplo, la siguiente consulta solicita saltar 50 filas, retornando todo el resto.

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET 50 ROWS;

Esto es una forma abreviada de la salida.

orderid     orderdate               custid      empid

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

11027      2008-04-16 00:00:00.000      10           1

11026      2008-04-15 00:00:00.000      27           4

...

10249      2006-07-05 00:00:00.000      79           6

10248      2006-07-04 00:00:00.000      85           5

(780 row(s) affected)

Como se mencionó anteriormente, la opción OFFSET-FETCH requiere una cláusula ORDER BY. Pero ¿Qué pasa si necesita filtrar un determinado número de filas basado en un orden arbitrario? Para ello, puede especificar la expresión (SELECT NULL) en la cláusula ORDER BY, de la siguiente manera.

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY (SELECT NULL)

OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

Este código simplemente filtra tres filas arbitrarias. Aquí está la salida de un sistema retornado después de ejecutar el código.

orderid     orderdate               custid      empid

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

11011      2008-04-09 00:00:00.000      1            3

10952      2008-03-16 00:00:00.000      1            1

10835      2008-01-15 00:00:00.000      1            1

Con ambas cláusulas OFFSET y FETCH, puede utilizar expresiones como entradas. Esto es muy práctico cuando necesita calcular los valores de entrada dinámicamente. Por ejemplo, suponga que está implementando un concepto de paginado, donde retorna al usuario una página de filas a la vez. El usuario pasa como parámetros de entrada a su procedimiento o a una función, el número de página que esta después (parámetro @pagenum) y el tamaño de página (parámetro @pagesize). Esto significa que necesita saltar tantas filas como @pagenum menos uno, por @pagesize, y buscar las siguientes filas @pagesize. Esto puede ser implementado usando el código siguiente (usando variables locales por simplicidad).

DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3;

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC

OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

Con estas entradas, el código retorna la siguiente salida.

orderid     orderdate               custid      empid

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

11027      2008-04-16 00:00:00.000      10           1

11026      2008-04-15 00:00:00.000      27           4

...

10454      2007-02-21 00:00:00.000      41           4

10453      2007-02-21 00:00:00.000      4            1

(25 row(s) affected)

Puede sentirse libre para cambiar los valores de entrada y ver cómo el resultado cambia en consecuencia.

Dado que la opción OFFSET-FETCH es estándar y TOP no lo es, en los casos donde son lógicamente equivalentes, es recomendable atenerse a la primera. Recuerde que OFFSET-FETCH también tiene una ventaja sobre TOP en el sentido de que soporte una capacidad de saltar. Sin embargo, por ahora, OFFSET-FETCH no soporta opciones similares a PERCENT y WITH TIES de TOP.

Desde el punto de vista de rendimiento, debería evaluar la indexación de las columnas ORDER BY para soportar las opciones TOP y OFFSET-FETCH. Tal indexación sirve a un propósito muy similar a la indexación de las columnas filtradas y puede ayudar a evitar el rastreo innecesario de datos, así como la ordenación.

Ejercicio 1: Utilizar la Opción TOP

En este ejercicio, practica utilizando la opción TOP para filtrar los datos.

1.    Abra el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Su tarea es escribir una consulta sobre la tabla Production.Products, retornando los cinco productos más caros de la categoría 1. Escriba la siguiente consulta.

SELECT TOP (5) productid, unitprice

FROM Production.Products

WHERE categoryid = 1

ORDER BY unitprice DESC;

Obtendrá el siguiente conjunto de resultados.

productid   unitprice

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

38          263.50

43          46.00

2           19.00

1           18.00

35          18.00

Esta consulta retorna el resultado deseado, excepto que no tiene algún manejo de empates. En otras palabras, el ordenamiento sobre los productos con el mismo precio unitario es no determinístico.

3.    Se le solicitó proporcionar soluciones para convertir la consulta anterior en una determinística, una solución que incluye empates y otra que rompe los empates. Primero, dirija la versión que incluye todos los empates por utilizar la opción WITH TIES. Agregar esta opción a la consulta, como sigue.

SELECT TOP (5) WITH TIES productid, unitprice

FROM Production.Products

WHERE categoryid = 1

ORDER BY unitprice DESC;

Obtiene la siguiente salida, que incluye vínculos.

productid   unitprice

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

38          263.50

43          46.00

2           19.00

1           18.00

39          18.00

35          18.00

76          18.00

4.    Abordar la segunda versión que rompe los empates utilizando productid, descendente, como sigue.

SELECT TOP (5) productid, unitprice

FROM Production.Products

WHERE categoryid = 1

ORDER BY unitprice DESC, productid DESC;

Esta consulta genera el siguiente resultado.

productid   unitprice

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

38          263.50

43          46.00

2           19.00

76          18.00

39          18.00

Ejercicio 2: Utilizar la Opción OFFSET-FETCH

En este ejercicio, practica utilizando la opción OFFSET-FETCH para filtrar los datos.

1.    Abra el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Se le solicita escribir un conjunto de consultas que página a través de los productos, cinco a la vez, en el orden de precio unitario, utilizando el productid como la rotura de empate. Empiece por escribir una consulta que retorne los primeros cinco productos.

SELECT productid, categoryid, unitprice

FROM Production.Products

ORDER BY unitprice, productid

OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Podría haber utilizado la palabra clave FIRST o la NEXT, pero decide utilizar FIRST porque fue la opción más natural cuando no salta alguna fila. Esta consulta genera el siguiente resultado.

productid   categoryid  unitprice

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

33           4           2.50

24           1           4.50

13           8           6.00

52           5           7.00

54           6           7.45

3.    Luego, escriba una consulta que retorne las próximas cinco filas (filas 6 al 10) utilizando la siguiente consulta.

SELECT productid, categoryid, unitprice

FROM Production.Products

ORDER BY unitprice, productid

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Esta vez, utilice la palabra clave NEXT porque va a omitir algunas filas. Esta consulta genera la siguiente salida.

productid   categoryid  unitprice

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

75           1           7.75

23           5           9.00

19           3           9.20

45           8           9.50

47           3           9.50

4.    Del mismo modo, escribir la siguiente consulta para retornar las filas del 11 al 15:

SELECT productid, categoryid, unitprice

FROM Production.Products

ORDER BY unitprice, productid

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Esta consulta genera la siguiente salida.

productid   categoryid  unitprice

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

41           8           9.65

3            2           10.00

21           3           10.00

74           7           10.00

46           8           12.00

Podría seguir un proceso similar para las páginas subsiguientes.