Como Usar GROUP BY e ORDER BY no SQL

Introdução

Bancos de dados SQL (Structured Query Language) podem armazenar e gerenciar uma grande quantidade de dados em várias tabelas. Com conjuntos de dados grandes, é importante entender como classificar os dados, especialmente para analisar conjuntos de resultados ou organizar dados para relatórios ou comunicações externas.

Dois comandos comuns em SQL que ajudam na classificação dos seus dados são GROUP BY e ORDER BY. Um comando GROUP BY classifica os dados agrupando-os com base na(s) coluna(s) que você especifica na consulta e é usado com funções de agregação. Um ORDER BY permite que você organize conjuntos de resultados em ordem alfabética ou numérica e em ordem ascendente ou descendente.

Neste tutorial, você vai classificar os resultados das consultas em SQL usando os comandos GROUP BY e ORDER BY. Você também vai praticar a implementação de funções de agregação e a cláusula WHERE em suas consultas para classificar ainda mais os resultados.

Pré-requisitos

Para seguir este guia, você precisará de um computador executando algum tipo de sistema de gerenciamento de banco de dados relacional (RDBMS) que utilize SQL. As instruções e exemplos neste tutorial foram validados usando o seguinte ambiente:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL instalado e seguro no servidor. Siga nosso guia Como Instalar o MySQL no Ubuntu 20.04 para configurá-lo. Este guia pressupõe que você também configurou um usuário MySQL não root, conforme descrito no Passo 3 deste guia.

Para praticar a classificação dos resultados de dados neste tutorial, você precisará de um banco de dados e tabela carregados com dados de amostra. Se você não tiver um pronto para inserir, você pode ler a seguinte seção Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra para aprender como criar um banco de dados e uma tabela. Este tutorial fará referência a este banco de dados e tabela de amostra ao longo.

Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra

Se seu banco de dados SQL é executado em um servidor remoto, faça SSH para o seu servidor a partir da sua máquina local:

ssh sammy@your_server_ip

Em seguida, abra o prompt do MySQL, substituindo sammy pelas informações da sua conta de usuário MySQL:

mysql -u sammy -p

Crie um banco de dados chamado movieDB:

CREATE DATABASE movieDB;

Se o banco de dados for criado com sucesso, você receberá a seguinte saída:

Output
Query OK, 1 row affected (0.01 sec)

Para selecionar o banco de dados movieDB, execute a seguinte instrução USE:

USE movieDB;

Output
Database changed

Após selecionar o banco de dados, crie uma tabela dentro dele. Para o exemplo deste tutorial, criaremos uma tabela que armazena informações sobre as sessões de um cinema local. Esta tabela terá as seguintes sete colunas:

• theater_id: armazena valores do tipo de dados int para as salas de exibição de cada cinema e servirá como a chave primária da tabela, significando que cada valor nesta coluna funcionará como um identificador exclusivo para sua respectiva linha.
• date: utiliza o tipo de dados DATE para armazenar a data específica do ano, mês e dia em que um filme foi exibido. Este tipo de dados adere aos seguintes parâmetros: quatro dígitos para o ano e um máximo de dois dígitos para o mês e o dia (AAAA-MM-DD).
• time: representa o horário programado da exibição do filme com o tipo de dados TIME, por horas, minutos e segundos (HH:MM:SS).
• movie_name: armazena o nome do filme usando o tipo de dados varchar com um máximo de 40 caracteres.
• movie_genre: utiliza o tipo de dados varchar com um máximo de 30 caracteres, para armazenar informações sobre o gênero de cada filme.
• guest_total: mostra o número total de convidados que compareceram a uma exibição de filme com o tipo de dados int.
• ticket_cost: utiliza o tipo de dados decimal, com uma precisão de quatro e uma escala de um, significando que os valores nesta coluna podem ter quatro dígitos e dois dígitos à direita do ponto decimal. Esta coluna representa o custo do ingresso para a exibição específica do filme.

Crie uma tabela chamada movie_theater que contenha cada uma dessas colunas executando o seguinte comando CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Em seguida, insira alguns dados de exemplo na tabela vazia:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Depois de inserir os dados, você estará pronto para começar a ordenar os resultados da consulta em SQL.

Usando GROUP BY

A função de uma declaração GROUP BY é agrupar registros com valores compartilhados. Uma declaração GROUP BY é sempre usada com uma função de agregação em uma consulta. Como você pode lembrar, uma função de agregação resume informações e retorna um único resultado. Por exemplo, você pode consultar o total ou a soma de uma coluna e isso produzirá um único valor em seu resultado. Com uma cláusula GROUP BY, você pode implementar a função de agregação para obter um valor de resultado para cada grupo desejado.

GROUP BY é útil para retornar vários resultados desejados ordenados pelos grupos especificados, em vez de apenas uma coluna. Além disso, GROUP BY deve sempre vir após a declaração FROM e a cláusula WHERE, se você optar por usá-la. Aqui está um exemplo de como uma consulta com GROUP BY e função de agregação é estruturada:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Para ilustrar como você pode usar declarações GROUP BY, digamos que você está liderando a campanha de lançamento de vários filmes e deseja avaliar o sucesso de seus esforços de marketing. Você pede a um cinema local para compartilhar os dados que coletaram dos convidados na sexta e no sábado. Comece revisando os dados executando SELECT e o símbolo * para selecionar “cada coluna” da tabela movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Embora esses dados sejam úteis, você deseja realizar uma avaliação mais aprofundada e classificar os resultados para algumas colunas específicas.

Como você trabalhou em filmes de alguns gêneros diferentes, está interessado em saber quão bem eles foram recebidos pelos frequentadores de cinema. Especificamente, você quer saber a quantidade média de pessoas que assistiram a cada gênero de filme. Use SELECT para recuperar os vários tipos de filmes da coluna movie_genre. Em seguida, aplique a função de agregação AVG na coluna guest_total, use AS para criar um alias para uma coluna chamada média e inclua a declaração GROUP BY para agrupar os resultados por movie_genre. Agrupando dessa forma, você obterá os resultados médios para cada gênero de filme:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Esta saída fornece as quatro médias para cada gênero dentro do grupo movie_genre. Com base nessas informações, os filmes de Ação atraíram o maior número médio de convidados por exibição.

Em seguida, vamos supor que você queira medir as receitas do teatro ao longo de dois dias separados. A seguinte consulta retorna valores da coluna date, bem como valores retornados pela função de agregação SUM. Especificamente, a função de agregação SUM irá envolver uma equação matemática entre parênteses para multiplicar (usando o operador *) o número total de convidados pelo custo de um ingresso, representado como: SUM(guest_total * ticket_cost). Esta consulta inclui a cláusula AS para fornecer o alias total_revenue para a coluna retornada pela função de agregação. Em seguida, complete a consulta com a declaração GROUP BY para agrupar os resultados da consulta pela coluna date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Como você utilizou o GROUP BY para agrupar a coluna date, a saída fornece os resultados para a receita total em vendas de ingressos para cada dia, neste caso, $7.272 para Sexta-feira, 27 de Maio, e $9.646 para Sábado, 28 de Maio.

Agora imagine que você queira focar e analisar um único filme: Os Vilões. Neste cenário, você deseja descobrir como o momento e os pontos de preço impactam a escolha de uma família para assistir a um filme animado. Para esta consulta, use a função de agregação MAX para recuperar o ticket_cost máximo, garantindo incluir AS para criar o alias para a coluna price_data. Em seguida, use a cláusula WHERE para restringir os resultados pelo movie_name exclusivamente para “Os Vilões”, e use AND também para determinar os horários de filmes mais populares com base nos números de guest_total que foram mais de 100 com o operador de comparação >. Então, conclua a consulta com a declaração GROUP BY e agrupe-a por time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

De acordo com esta saída, mais convidados compareceram ao filme Os Vilões no horário matinê de 9:00 am, que tinha o ponto de preço mais acessível de $8.00 por ingresso. No entanto, esses resultados também mostram que os convidados do filme pagaram o preço do ingresso mais alto de $13.00 às 5:00 pm, sugerindo que as famílias preferem exibições que não sejam muito tarde no dia e pagarão um pouco mais por um ingresso. Isso parece ser uma avaliação justa quando comparado ao horário de 10:00 pm, quando o filme Os Vilões teve apenas 83 convidados e o preço por ingresso foi de $18.00. Esta pode ser uma informação útil para fornecer ao gerente do cinema evidências de que abrir mais slots de tempo para matinês e início da noite pode aumentar a frequência de famílias que estão fazendo uma escolha com base em um horário e ponto de preço preferidos.

Por favor, note que mesmo que GROUP BY seja quase sempre usado com uma função de agregação, pode haver exceções, embora sejam improváveis. No entanto, se você desejar agrupar seus resultados sem uma função de agregação, pode usar a instrução DISTINCT para obter o mesmo resultado. A cláusula DISTINCT remove quaisquer duplicatas em um conjunto de resultados retornando os valores únicos na coluna, e só pode ser usada com uma instrução SELECT. Por exemplo, se você quisesse agrupar todos os filmes juntos pelo nome, poderia fazer isso com a seguinte consulta:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Como você se lembra de visualizar todos os dados na tabela, havia duplicatas nos nomes dos filmes, já que houve várias exibições. Portanto, DISTINCT removeu essas duplicatas e agrupou efetivamente os valores únicos na única coluna movie_name. Isso é efetivamente idêntico à seguinte consulta, que inclui uma declaração GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Agora que você praticou o uso de GROUP BY com funções de agregação, em seguida, aprenderá como ordenar os resultados da sua consulta com a instrução ORDER BY.

USANDO ORDER BY

A função da declaração ORDER BY é classificar os resultados em ordem ascendente ou descendente com base na(s) coluna(s) que você especifica na consulta. Dependendo do tipo de dados armazenado pela coluna que você especifica após ela, ORDER BY os organizará em ordem alfabética ou numérica. Por padrão, ORDER BY classificará os resultados em ordem ascendente; se você preferir a ordem descendente, no entanto, deve incluir a palavra-chave DESC em sua consulta. Você também pode usar a declaração ORDER BY com GROUP BY, mas ela deve vir depois para funcionar corretamente. Semelhante ao GROUP BY, ORDER BY também deve vir depois da declaração FROM e da cláusula WHERE. A sintaxe geral para usar ORDER BY é a seguinte:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Vamos continuar com os dados de exemplo para o cinema e praticar a classificação dos resultados com ORDER BY. Comece com a seguinte consulta que recupera valores da coluna guest_total e organiza esses valores numéricos com uma declaração ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Já que sua consulta especificou uma coluna com valores numéricos, a declaração ORDER BY organizou os resultados por ordem numérica e ascendente, começando com 25 sob a coluna guest_total.

Se preferir ordenar a coluna em ordem decrescente, você adicionaria a palavra-chave DESC no final da consulta. Além disso, se você quisesse ordenar os dados pelos valores de caracteres em movie_name, você especificaria isso em sua consulta. Vamos realizar esse tipo de consulta usando ORDER BY para ordenar a coluna movie_name com valores de caracteres em ordem decrescente. Ordene os resultados ainda mais incluindo uma cláusula WHERE para recuperar os dados sobre filmes exibidos às 22:00 na coluna time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Este conjunto de resultados lista as quatro diferentes exibições de filmes às 22:00 em ordem alfabética decrescente, começando por Top Gun Maverick até Downtown Abbey A New Era.

Para esta próxima consulta, combine as declarações ORDER BY e GROUP BY com a função de agregação SUM para gerar resultados sobre a receita total recebida para cada filme. No entanto, vamos dizer que o cinema contou erroneamente o total de convidados e esqueceu de incluir festas especiais que tinham comprado antecipadamente e reservado ingressos para um grupo de 12 pessoas em cada exibição.

Nesta consulta, use SUM e inclua os 12 convidados adicionais em cada sessão de filme implementando o operador de adição + e, em seguida, adicionando 12 ao guest_total. Certifique-se de colocar isso entre parênteses. Em seguida, multiplique esse total pelo ticket_cost com o operador * e conclua a equação matemática fechando o parêntese no final. Adicione a cláusula AS para criar o alias para a nova coluna intitulada total_revenue. Em seguida, use GROUP BY para agrupar os resultados de total_revenue para cada filme com base nos dados recuperados da coluna movie_name. Por último, use ORDER BY para organizar os resultados sob a nova coluna total_revenue em ordem ascendente:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Este conjunto de resultados nos diz a receita total para cada filme com as vendas de ingressos adicionais de 12 convidados e organiza as vendas totais de ingressos em ordem ascendente, da menor para a maior. A partir disso, aprendemos que Top Gun Maverick recebeu as maiores vendas de ingressos, enquanto Men recebeu as menores. Enquanto isso, os filmes The Bad Guys e Downton Abbey A New Era estavam muito próximos em vendas totais de ingressos.

Nesta seção, você praticou várias maneiras de implementar a declaração ORDER BY e como especificar a ordem que preferir, como ordens ascendente e descendente para ambos os valores de dados alfanuméricos e numéricos. Você também aprendeu como incluir a cláusula WHERE para reduzir seus resultados e realizou uma consulta usando tanto as declarações GROUP BY quanto ORDER BY com uma função de agregação e uma equação matemática.

Conclusão

Compreender como usar as instruções GROUP BY e ORDER BY é importante para classificar seus resultados e dados. Se você deseja organizar múltiplos resultados sob um grupo, organizar uma de suas colunas em ordem alfabética e decrescente, ou fazer ambos simultaneamente; depende de você e do(s) resultado(s) desejado(s). Você também aprendeu sobre outras maneiras de classificar seus resultados ainda mais com a cláusula WHERE. Se deseja aprender mais, confira nosso tutorial sobre Como Usar Wildcards em SQL para praticar a filtragem de resultados com a cláusula LIKE.