SQLShack (Português)

Neste artigo, vamos discutir a função SQL ROW_NUMBER. Esta é uma continuação da série SQL Essential. Neste guia, explicarei do que se trata uma função de janela e você verá exemplos de exemplos para entender os conceitos por trás da função SQL ROW_NUMBER.

Introdução

O mais A função comumente usada no SQL Server é a função SQL ROW_NUMBER. A função SQL ROW_NUMBER está disponível no SQL Server 2005 e versões posteriores.

ROW_NUMBER adiciona um número incremental exclusivo à grade de resultados. A ordem em que os números das linhas são aplicados é determinada pela expressão ORDER BY. Na maioria das vezes, uma ou mais colunas são especificadas na expressão ORDER BY, mas é possível usar expressões mais complexas ou até mesmo uma subconsulta. Portanto, ele cria um valor integral sempre crescente e sempre começa em 1 e as linhas subsequentes obtêm o próximo valor mais alto.

Você também pode usá-lo com uma cláusula PARTITION BY. Mas quando ele cruza um limite ou fronteira de partição, ele zera o contador e começa a partir de 1. Portanto, a partição pode ter os valores 1, 2, 3 e assim por diante e as segundas partições novamente iniciam o contador de 1, 2, 3 … e assim por diante.

Fundamentos:

1. A função SQL ROW_NUMBER é uma geração não persistente de uma sequência de valores temporários e é calculada dinamicamente quando a consulta é executada.
2. Não há garantia de que as linhas retornadas por uma consulta SQL usando a função SQL ROW_NUMBER serão ordenadas exatamente da mesma forma em cada execução.
3. As funções ROW_NUMBER e RANK são semelhantes. A saída de ROW_NUMBER é uma sequência de valores começa em 1 com um incremento de 1, mas enquanto a função RANK, os valores também são incrementados em 1, mas os valores se repetirão para os empates.
4. Se você uma experiência com a Oracle, então o ROWNUM é mais familiar para você. É uma pseudo-coluna. Começa com 1 e desce, aumentando em um, até ao fim da mesa.
5. A função SQL ROW_NUMBER é de natureza dinâmica e podemos redefinir os valores usando a cláusula PARTITION BY
6. A cláusula ORDER BY da consulta e a cláusula ORDER BY do OVER cláusula não tem nada a ver uma com a outra.

Sintaxe

ROW_NUMBER

ROW_NUMBER seguido pela função OVER e nos parênteses use uma cláusula ORDER BY. É necessário usar a cláusula ORDER BY para impor uma espécie de ordem para o conjunto de resultados.

OVER

A cláusula OVER define a janela ou conjunto de linhas que a janela função opera, então é muito importante que você entenda. Os possíveis componentes da cláusula OVER são ORDER BY e PARTITION BY.

A expressão ORDER BY da cláusula OVER é compatível quando as linhas precisam ser alinhadas de uma determinada maneira para que a função funcione.

PARTITION BY

A cláusula Partition By é opcional. Ao especificar o valor, ele divide o conjunto de resultados produzido pela cláusula FROM em partições às quais a função SQL ROW_NUMBER é aplicada. Os valores especificados na cláusula PARTITION definem os limites do conjunto de resultados. Se a cláusula PARTITION BY não for especificada, a cláusula OVER operará em todas as linhas do conjunto de resultados como um único conjunto de dados. Esta cláusula pode consistir em uma ou mais colunas, uma expressão mais complexa ou mesmo uma subconsulta.

order_by_clause

A cláusula Order by é uma cláusula obrigatória. Ele determina a sequência e a associação do valor temporário às linhas de uma partição especificada. A cláusula ORDER BY é uma expressão da cláusula OVER e determina como as linhas devem ser alinhadas de uma determinada maneira para a função.

Demo

Nesta seção, nós vamos dar uma olhada na função SQL ROW_NUMBER. Para toda a demonstração, usei o banco de dados AdventureWorks2016.

Como usar ROW_NUMBER na consulta SQL

Nos exemplos a seguir, veremos o uso da cláusula OVER.

Vamos obter a lista de todos os clientes projetando as colunas como SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue e RowNum. A função Row_Number é aplicada com a ordem da coluna CustomerID.O valor temporário começa em 1 atribuído com base na ordem do CustomerID e os valores continuam até as últimas linhas da tabela. A ordem do CustomerID não é garantida porque não especificamos a cláusula ORDER BY na consulta.

Como usar a cláusula Order by

O exemplo a seguir usa a cláusula ORDER BY na consulta. A cláusula ORDER BY na consulta aplicada na coluna SalesOrderID. Podemos ver que as linhas na saída ainda estão ordenadas e retornadas. O Row_Number ainda é aplicado ao CustomerID. A saída indica que ORDER BY da consulta e ORDER BY da cláusula OVER são independentes da saída.

Como usar várias colunas com a cláusula OVER

No exemplo a seguir, você pode ver que listamos customerID e OrderDate na cláusula ORDER BY. Isso fornece os detalhes do cliente com os detalhes do pedido mais recente junto com a sequência de números atribuídos a todo o conjunto de resultados.

Como usar a função SQL ROW_NUMBER com PARTITION

O exemplo a seguir usa a cláusula PARTITION BY nos campos CustomerID e OrderDate. Na saída, você pode ver que o cliente 11019 tem três pedidos para o mês de 2014 a junho. Neste caso, a partição é feita em mais de uma coluna.

A partição é uma combinação de OrderDate e CustomerID. O Row_Number será reiniciado para cada combinação exclusiva de OrderDate e CustomerID. Desta forma, é fácil encontrar o cliente que fez mais de um pedido no mesmo dia.

Como retornar um subconjunto de linhas usando CTE e ROW_NUMBER

No exemplo a seguir, vamos analisar SalesOrderHeader para exibir os cinco maiores pedidos feitos por cada cliente a cada mês. Usando a função Mês, as colunas orderDate são manipuladas para buscar a parte do mês.Desta forma, as vendas correspondentes a determinado mês (OrderDate) juntamente com o cliente (CustomerID) são particionadas.

Para listar os cinco maiores pedidos em cada mês para cada cliente, um CTE é usado. Uma janela é criada nos dados da partição e é atribuída com os valores e, em seguida, o CTE está sendo chamado para buscar os maiores pedidos.

Resumo

Até agora, revisamos a função SQL ROW_NUMBER em detalhes. Discutimos vários exemplos, do simples ao complexo. Além disso, discutimos como usar a função SQL ROW_NUMBER com CTEs (Expressões de tabela comuns). Na maioria dos casos, você sempre verá uma cláusula over em cada função da janela.

A cláusula over define a janela que cada linha vê. Dentro da cláusula over, há uma partição por, mais uma vez, é suportada por todas as funções de janela, seguida pela cláusula order by. Isso é tudo por agora … Espero que você tenha gostado de ler o artigo.

• Autor
• Postagens recentes

Sou um tecnólogo de banco de dados, com mais de 11 anos de experiência rica e prática em tecnologias de banco de dados. Sou Microsoft Certified Professional e sou graduado em Master of Computer Application.
Minha especialidade é projetar & implementação de soluções de alta disponibilidade e migração de banco de dados entre plataformas. As tecnologias atualmente em funcionamento são SQL Server, PowerShell, Oracle e MongoDB.
Ver todas as postagens de Prashanth Jayaram

Postagens mais recentes de Prashanth Jayaram (ver todos)

• Uma rápida visão geral da auditoria de banco de dados em SQL – 28 de janeiro de 2021
• Como configurar o Azure Data Sync entre os bancos de dados Azure SQL e o SQL Server local – 20 de janeiro de 2021
• Como realizar operações de importação / exportação de banco de dados Azure SQL usando o PowerShell – 14 de janeiro , 2021