Este artigo cobrirá a cláusula SQL PARTITION BY e, em particular, a diferença com GROUP BY em uma instrução select. Também exploraremos vários casos de uso de SQL PARTITION BY.
Usamos SQL PARTITION BY para dividir o conjunto de resultados em partições e realizar cálculos em cada subconjunto de dados particionados.
Preparando a amostra Dados
Vamos criar uma tabela Pedidos em meu banco de dados de amostra SQLShackDemo e inserir registros para escrever outras consultas.
Eu uso o ApexSQL Generate para inserir dados de amostra neste artigo. Clique com o botão direito na tabela Pedidos e Gerar dados de teste.
Ele inicia o Gerar ApexSQL. Eu gerei um script para inserir dados na tabela Pedidos. Execute este script para inserir 100 registros na tabela Pedidos.
Depois de executarmos as instruções de inserção, podemos ver os dados na tabela Pedidos na imagem a seguir.
Usamos a cláusula SQL GROUP BY para agrupar os resultados por coluna especificada e usamos funções agregadas como Avg (), Min (), Max () para calcular os valores necessários.
Agrupar por sintaxe de função
|
1
2
3
4
|
Expressão SELECT, função agregada ()
tabelas FROM
Condições WHERE
expressão GROUP BY
|
Suponha que desejemos encontrar os seguintes valores na tabela Pedidos
- Valor mínimo do pedido em uma cidade
- Valor máximo do pedido em uma cidade
- Valor médio do pedido em uma cidade
Execute a seguinte consulta com a cláusula GROUP BY para calcular esses valores.
|
1
2
3
4
5
6
|
SELECIONE Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
Na captura de tela a seguir, podemos ver Média, Mínima e valores máximos agrupados por CustomerCity.
Agora, queremos adicionar as colunas CustomerName e OrderAmount também na saída. Vamos adicionar essas colunas na instrução select e executar o seguinte código.
|
1
2
3
4
5
6
|
SELECIONE Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
DE.
GROUP BY Customercity;
|
Assim que executamos esta consulta, obtemos uma mensagem de erro . Na cláusula SQL GROUP BY, podemos usar uma coluna na instrução select se ela for usada na cláusula Group by também. Não permite nenhuma coluna na cláusula select que não faça parte da cláusula GROUP BY.
Podemos usar o SQL Cláusula PARTITION BY para resolver este problema. Vamos explorá-lo mais na próxima seção.
SQL PARTITION BY
Podemos usar a cláusula SQL PARTITION BY com a cláusula OVER para especificar a coluna na qual precisamos realizar a agregação . No exemplo anterior, usamos Group By com a coluna CustomerCity e calculamos os valores médio, mínimo e máximo.
Vamos executar novamente este cenário com a cláusula SQL PARTITION BY usando a seguinte consulta.
|
1
2
3
4
5
|
SELECIONE Customercity,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Na saída, obtemos valores agregados semelhantes a um GRUPO por cláusula. Você pode notar uma diferença na saída da saída da cláusula SQL PARTITION BY e GROUP BY.
|
Agrupar por |
SQL PARTITION BY |
|
Obtemos um número limitado de registros usando a cláusula Group By |
Obtemos todos os registros em uma tabela usando a cláusula PARTITION BY. |
|
Isso nos dá um linha por grupo no conjunto de resultados. Por exemplo, obtemos um resultado para cada grupo de CustomerCity na cláusula GROUP BY. |
Ele fornece colunas agregadas com cada registro na tabela especificada. Temos 15 registros na tabela Pedidos. Na saída da consulta de SQL PARTITION BY, também obtemos 15 linhas junto com os valores Mín, Máx e médio. |
No exemplo anterior, obtemos uma mensagem de erro se tentarmos adicionar uma coluna que não faz parte da cláusula GROUP BY.
Podemos adicionar colunas obrigatórias em uma instrução select com a cláusula SQL PARTITION BY . Vamos adicionar as colunas CustomerName e OrderAmount e executar a seguinte consulta.
|
1
2
3
4
5
6
7
|
SELECIONE Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Obtemos a coluna CustomerName e OrderAmount junto com a saída de a função agregada. Também obtemos todas as linhas disponíveis na tabela Pedidos.
Na captura de tela a seguir, você pode para CustomerCity Chicago, ele executa agregações (Avg, Min e Max) e fornece valores nas respectivas colunas.
Da mesma forma, podemos usar outras funções de agregação, como como contagem para descobrir o número total de pedidos em uma determinada cidade com a cláusula SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELECIONE Customercity,
CustomerName,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Podemos ver a contagem de pedidos para uma determinada cidade. Por exemplo, temos dois pedidos da cidade de Austin; ele mostra o valor 2 na coluna CountofOrders.
cláusula PARTITION BY com ROW_NUMBER ()
Podemos use a cláusula SQL PARTITION BY com a função ROW_NUMBER () para ter um número de linha de cada linha. Definimos os seguintes parâmetros para usar ROW_NUMBER com a cláusula SQL PARTITION BY.
- Coluna PARTITION BY – Neste exemplo, queremos particionar dados na coluna CustomerCity
- Ordenar por : Na coluna ORDER BY, definimos uma coluna ou condição que define o número da linha. Neste exemplo, queremos classificar os dados na coluna OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELECIONE Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Na captura de tela a seguir, vemos para CustomerCity Chicago , temos a linha número 1 para o pedido com o valor mais alto 7577,90. fornece o número da linha com OrderAmount decrescente.
Cláusula PARTITION BY com valor total cumulativo
Suponha que nós deseja obter um total cumulativo para os pedidos em uma partição.O total cumulativo deve ser da linha atual e a linha seguinte na partição.
Por exemplo, na cidade de Chicago, temos quatro pedidos.
|
CustomerCity |
CustomerName |
Classificação |
OrderAmount |
Total cumulativo de linhas |
Total cumulativo |
|
Chicago |
Marvin |
Posição 1 +2 |
|||
|
Chicago |
Lawrence |
Classificação 2 + 3 |
|||
|
Chicago |
Alex |
Classificação 3 + 4 |
|||
|
Chicago |
Jerome |
Classificação 4 |
Na consulta a seguir, a cláusula ROWS especificada para se selecione a linha atual (usando CURRENT ROW) e a próxima linha (usando 1 FOLLOWING). Além disso, calcula a soma dessas linhas usando sum (Orderamount) com uma partição em CustomerCity (usando OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECIONE Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (PARTIÇÃO POR Customercity
ORDENAR POR OrderAmount DESC LINHAS ENTRE LINHA ATUAL E 1 SEGUINTE), 1) AS CumulativeTotal,
|
Da mesma forma, podemos calcular a média cumulativa usando o seguinte consulta com a cláusula SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELECIONE Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS ENTRE A LINHA ATUAL E 1 SEGUINTE) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING com a cláusula PARTITION BY
Podemos usar ROWS UNBOUNDED PRECEDING com a cláusula SQL PARTITION BY para selecionar uma linha em uma partição antes da linha atual e o maior valor r ow após a linha atual.
Na tabela a seguir, podemos ver para a linha 1; ele não possui nenhuma linha com um valor alto nesta partição. Portanto, o valor médio cumulativo é o mesmo da linha 1 OrderAmount.
Para a linha 2, ele procura o valor da linha atual (7199,61) e o valor mais alto da linha 1 (7577,9). Ele calcula a média para esses dois valores.
Para a linha 3, ele procura o valor atual (6847,66) e um valor de valor superior a esse valor que é 7199,61 e 7577,90. Ele calcula a média desses e retorna.
|
CustomerCity |
CustomerName |
Classificação |
OrderAmount |
Linhas de média cumulativa |
Média cumulativa |
|
|
Chicago |
Marvin |
Posição 1 |
||||
|
Chicago |
Lawrence |
Classificação 1 + 2 |
||||
|
Chicago |
Alex |
Classificação 1 + 2 + 3 |
||||
|
Chicago |
Jerome |
Classificação 1 + 2 + 3 + 4 |
Execute a seguinte consulta para obter este resultado com nossos dados de amostra.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED ANTES), 1) AS CumulativeAvg
FROM.;
|
Conclusão
Neste artigo, exploramos a cláusula SQL PARTIION BY e sua comparação com a cláusula GROUP BY. Também aprendemos seu uso com alguns exemplos. Espero que você ache este artigo útil e fique à vontade para fazer qualquer pergunta nos comentários abaixo
- Autor
- Postagens recentes
Ele é o criador de uma das maiores coleções online gratuitas de artigos sobre um único tópico, com sua série de 50 partes sobre Grupos de Disponibilidade AlwaysOn do SQL Server. Com base em sua contribuição para a comunidade do SQL Server, ele foi reconhecido com vários prêmios, incluindo o prestigioso “Melhor autor do ano” continuamente em 2020 e 2021 na SQLShack.
Raj está sempre interessado em novos desafios, então se você precisar de consultoria ajuda em qualquer assunto abordado em seus escritos, ele pode ser contatado em [email protected]
Veja todas as postagens de Rajendra Gupta
- Tempos limite de sessão no SQL Server sempre disponível Grupos – 8 de fevereiro de 2021
- Executando atualizações de versões secundárias e principais para AWS RDS SQL Server – 29 de janeiro de 2021
- Implantando instâncias AWS RDS PostgreSQL – 27 de janeiro de 2021