Usar funções de classificação e conjunto de linhas
As funções de classificação e conjunto de linhas não são funções 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 relação a 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 no ListPrice, com o preço mais alto classificado em 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 ter a seguinte aparência:
ID do Produto
Nome
ListPrice
ClassificarPorPreço
749
Estrada-150 Vermelho, 62
3578.27
1
750
Road-150 Vermelho, 44
3578.27
1
751
Estrada-150 Vermelho, 48
3578.27
1
771
Montanha-100 Prata, 38
3399.99
4
772
Mountain-100 Prateado, 42
3399.99
4
775
Montanha-100 Preto, 38
3374.99
6
...
...
...
...
SOBRE
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ços para produtos dentro de 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 desta consulta podem ter o seguinte aspeto:
Categoria
Produto
ListPrice
ClassificarPorPreço
Bib-Calções
Bib-Shorts Masculino, S
89.99
1
Bib-Calções
Calções de Ciclismo para Homens, M
89.99
1
Suportes para bicicletas
Hitch Rack - 4 Bicicletas
120
1
Suportes para Bicicletas
All-Purpose Suporte para bicicletas
159
1
Garrafas e gaiolas
Suporte para garrafa de bicicleta de montanha
9.99
1
Garrafas e gaiolas
Gaiola de garrafa de estrada
8.99
2
Garrafas e gaiolas
Garrafa de Água - 30 oz.
4,99
3
Colchetes inferiores
Suporte inferior HL
121.49
1
Colchetes inferiores
Suporte inferior ML
101.24
2
Colchetes inferiores
LL Suporte inferior
53.99
3
...
...
...
...
Observação
Observe que várias linhas têm o mesmo valor de classificação e alguns valores são ignorados. Isso ocorre porque estamos usando apenas RANK. Dependendo do requisito, você pode querer evitar empates no mesmo valor de classificação. Você pode controlar o valor de classificação com outras funções, DENSE_RANK, NTIL e ROW_NUMBER, conforme necessário. Para obter detalhes sobre essas funções, consulte a documentação de referênciaTransact-SQL.
Funções de conjuntos de linhas
As funções de conjunto de linhas retornam uma tabela virtual que pode ser usada na cláusula FROM como uma fonte de dados. Essas funções usam parâmetros específicos para a própria função de conjunto 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 resultados. 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 onde está executando a consulta.
As funções OPENXML e OPENJSON permitem consultar dados estruturados em formato XML ou JSON e extrair valores em um conjunto de linhas tabular.
Uma exploração detalhada das funções do conjunto de linhas está além do escopo deste módulo. Para obter mais informações, consulte a documentação de referênciaTransact-SQL.