Utilizar funções de classificação e de conjuntos de linhas
As funções de classificação e conjunto de linhas não são escalares porque não retornam um único valor. Essas funções aceitam um conjunto de linhas como entrada e retornam um conjunto de linhas como saída.
Funções de classificação
As funções de classificação permitem que você execute cálculos em um conjunto de linhas definido pelo usuário. Essas funções incluem funções de classificação, deslocamento, agregação e distribuição.
Este exemplo usa a função RANK para calcular uma classificação com base em ListPrice, com o preço mais alto classificado como 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Os resultados da consulta podem parecem com o seguinte:
ProductID
Nome
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
Você pode usar a cláusula OVER para definir partições ou agrupamentos dentro dos dados. Por exemplo, a consulta a seguir estende o exemplo anterior para calcular classificações baseadas em preço para produtos em cada categoria.
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;
Os resultados dessa consulta podem ter a seguinte aparência:
Categoria
Produto
ListPrice
RankByPrice
Bretelles
Shorts masculinos, S
89,99
1
Bretelles
Shorts masculinos, M
89,99
1
Racks de bicicleta
Rack para 4 bicicletas
120
1
Suportes de bicicleta
Suporte de bicicleta multifuncional
159
1
Garrafas e compartimentos
Mountain Bottle Cage
9,99
1
Garrafas e compartimentos
Road Bottle Cage
8,99
2
Garrafas e compartimentos
Garrafa de Água – 887 ml
4,99
3
Suportes inferiores
Colchete inferior HL
121.49
1
Suportes inferiores
Colchete inferior ML
101.24
2
Suportes inferiores
Colchete inferior LL
53,99
3
...
...
...
...
Observação
Observe que diversas linhas têm o mesmo valor de classificação e alguns valores são ignorados. Isso ocorre porque estamos usando somente RANK. Dependendo do requisito, é possível evitar empates no mesmo valor de classificação. É possível controlar o valor de classificação com outras funções, DENSE_RANK, NTILE e ROW_NUMBER, conforme necessário. Para saber mais sobre essas funções, confira a documentação de referência do Transact-SQL.
Funções do conjunto de linhas
As funções de conjunto de linhas retornam uma tabela virtual que pode ser usada na cláusula FROM como fonte de dados. Essas funções assumem parâmetros específicos para a própria função de conjuntos de linhas. Eles incluem OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML e OPENJSON.
As funções OPENDATASOURCE, OPENQUERY e OPENROWSET permitem que você passe uma consulta para um servidor de banco de dados remoto. O servidor remoto retornará um conjunto de linhas de resultado. Por exemplo, a consulta a seguir usa OPENROWSET para obter os resultados de uma consulta de uma instância do SQL Server chamada SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Para usar servidores remotos, você deve habilitar algumas opções avançadas na instância do SQL Server em que você está executando a consulta.
As funções OPENXML e OPENJSON permitem consultar dados estruturados em formato XML ou JSON e extrair valores para um conjuntos de linhas tabular.
Uma exploração detalhada das funções de conjuntos de linhas está além do escopo deste módulo. Para saber mais, confira a documentação de referência do Transact-SQL.