Utilizar 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 categoría
Las funciones de clasificación permiten realizar cálculos en un conjunto de filas definido por el usuario. Estas funciones incluyen funciones de clasificación, desplazamiento, agregado 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 este aspecto:
ProductID
Nombre
ListPrice
RankByPrice
749
Road-150 Red, 62
3578,27
1
750
Road-150 Red, 44
3578,27
1
751
Road-150 Red, 48
3578,27
1
771
Mountain-100 Silver, 38
3399,99
4
772
Mountain-100 Silver, 42
3399,99
4
775
Mountain-100 Black, 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 dentro 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 parecido al siguiente:
Category
Producto
ListPrice
RankByPrice
Pantalones de ciclismo
Pantalones de ciclismo, S
89,99
1
Pantalones de ciclismo
Pantalones de ciclismo, M
89,99
1
Bastidores de bicicletas
Bastidores de bicicletas, 4 bicicletas
120
1
Soportes de bicicletas
All-Purpose Bike Stand
159
1
Bidones y soportes
Mountain Bottle Cage
9,99
1
Bidones y soportes
Road Bottle Cage
8,99
2
Bidones y soportes
Water Bottle - 30 oz.
4,99
3
Ejes pedalier
Ejes pedalier HL
121,49
1
Ejes pedalier
Ejes pedalier ML
101,24
2
Ejes pedalier
Ejes pedalier LL
53,99
3
...
...
...
...
Nota
Observe que varias filas tienen el mismo valor de clasificación y que se han omitido algunos valores. Esto se debe a que solo estamos usando 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 obtener más información sobre estas funciones, consulte la documentación de referencia de Transact-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 de 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 llamada 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 en la que 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 tabular.
En este módulo no entraremos en detalle en las funciones de conjunto de filas. Para obtener más información, consulte la documentación de referencia de Transact-SQL.