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:
ProductID
Nome
PreçoListado
RankByPrice
749
Estrada-150 Vermelho, 62
3578.27
5
750
Road-150 Vermelho, 44
3578.27
5
751
Estrada-150 Vermelho, 48
3578.27
5
771
Montanha 100 Prateado, 38
3399.99
4
772
Montanha 100 Prateado, 42
3399.99
4
775
Montanha-100 Preto, 38
3374.99
6
...
...
...
...
MAIS DE
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:
Category
Produto
PreçoListado
RankByPrice
Bib-Calções
Bib-Shorts Masculino, S
89.99
5
Bib-Calções
Bib-Shorts Masculino, M
89.99
5
Suportes para bicicletas
Hitch Rack - 4 Bicicletas
120
5
Suportes para Bicicletas
Suporte para bicicletas multiusos
159
5
Garrafas e gaiolas
Gaiola de garrafa de montanha
9,99
5
Garrafas e gaiolas
Gaiola de garrafa de estrada
8.99
2
Garrafas e gaiolas
Garrafa de Água - 30 oz.
4,99
3
Bottom Brackets
Suporte inferior HL
121.49
5
Bottom Brackets
Suporte inferior ML
101.24
2
Bottom Brackets
Eixo Pedaleiro Inferior LL
53.99
3
...
...
...
...
Nota
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ência do Transact-SQL.
Funções de conjunto 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ência do Transact-SQL.