Uso de funciones de clasificación y conjunto de filas
Las funciones de clasificación y conjunto de filas no son funciones escalares porque no devuelven un solo valor. Estas funciones aceptan un conjunto de filas como entrada y devuelven un conjunto de filas como salida.
Funciones de clasificación
Las funciones de clasificación permiten realizar cálculos en un conjunto de filas definido por el usuario. Estas funciones incluyen clasificación, desplazamiento, agregación y distribución.
En este ejemplo se usa la función RANK para calcular una clasificación basada en ListPrice, con el precio más alto clasificado en 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Los resultados de la consulta pueden tener el siguiente aspecto:
Id. de producto
Nombre
ListPrice
ClasificarPorPrecio
749
Carretera-150 Rojo, 62
3578,27
1
750
Carretera-150 Rojo, 44
3578,27
1
751
Camino 150 Rojo, 48
3578,27
1
771
Montaña-100 Plata, 38
3399,99
4
772
Montaña-100 Plata, 42
3399,99
4
775
Mountain-100 en negro, talla 38
3374,99
6
...
...
...
...
OVER
Puede usar la cláusula OVER para definir particiones o agrupaciones dentro de los datos. Por ejemplo, la consulta siguiente amplía el ejemplo anterior para calcular las clasificaciones basadas en precios de los productos de cada categoría.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
Los resultados de esta consulta podrían tener un aspecto similar al siguiente:
Categoría
Producto
ListPrice
ClasificarPorPrecio
Bib-Shorts
Bib-Shorts de hombres, S
89.99
1
Bib-Shorts
Bib-Shorts de hombres, M
89.99
1
Bastidores de bicicletas
Bastidor hitch - 4-Bike
120
1
Soportes de bicicletas
All-Purpose soporte para bicicletas
159
1
Botellas y jaulas
Jaula de botella de montaña
9.99
1
Botellas y jaulas
Jaula de botella de carretera
8,99
2
Botellas y jaulas
Botella de agua - 30 oz.
4,99
3
Corchetes inferiores
Corchete inferior de HL
121.49
1
Corchetes inferiores
Corchete inferior de ML
101.24
2
Corchetes inferiores
Corchete inferior ll
53.99
3
...
...
...
...
Nota:
Observe que varias filas tienen el mismo valor de clasificación y se omiten algunos valores. Esto se debe a que solo usamos RANK. En función del requisito, es posible que quiera evitar vínculos con el mismo valor de clasificación. Puede controlar el valor de clasificación con otras funciones, DENSE_RANK, NTILE y ROW_NUMBER, según sea necesario. Para más información sobre estas funciones, consulte la documentación de referencia deTransact-SQL.
Funciones de conjuntos de filas
Las funciones de conjunto de filas devuelven una tabla virtual que se puede usar en la cláusula FROM como origen de datos. Estas funciones toman parámetros específicos de la propia función del conjunto de filas. Incluyen OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML y OPENJSON.
Las funciones OPENDATASOURCE, OPENQUERY y OPENROWSET permiten pasar una consulta a un servidor de bases de datos remoto. A continuación, el servidor remoto devolverá un conjunto de filas de resultados. Por ejemplo, la consulta siguiente usa OPENROWSET para obtener los resultados de una consulta de una instancia de SQL Server denominada SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Para usar servidores remotos, debe habilitar algunas opciones avanzadas en la instancia de SQL Server donde se ejecuta la consulta.
Las funciones OPENXML y OPENJSON permiten consultar datos estructurados en formato XML o JSON y extraer valores en un conjunto de filas tabulares.
Una exploración detallada de las funciones de conjunto de filas está fuera del ámbito de este módulo. Para obtener más información, consulte la documentación de referencia deTransact-SQL.