Home-theater-designers
Muito do poder dos bancos de dados relacionais vem da filtragem de dados e da junção de tabelas. É por isso que representamos essas relações em primeiro lugar. Mas os sistemas de banco de dados modernos fornecem outra técnica valiosa: agrupamento.
O agrupamento permite que você extraia informações resumidas de um banco de dados. Ele permite combinar resultados para criar dados estatísticos úteis. O agrupamento evita que você escreva códigos para casos comuns, como listas de cálculo de médias. E pode tornar os sistemas mais eficientes.
O que a cláusula GROUP BY faz?
GROUP BY, como o nome sugere, agrupa os resultados em um conjunto menor. Os resultados consistem em uma linha para cada valor distinto da coluna agrupada. Podemos mostrar seu uso examinando alguns dados de amostra com linhas que compartilham alguns valores comuns.
como fazer uma lista numerada no excel
O seguinte é um banco de dados muito simples com duas tabelas que representam os álbuns de discos. Você pode configurar esse banco de dados por escrever um esquema básico para o sistema de banco de dados escolhido. o álbuns tabela tem nove linhas com uma chave primária Eu iria coluna e colunas para nome, artista, ano de lançamento e vendas:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+o artistas mesa é ainda mais simples. Possui sete linhas com colunas de id e nome:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+Você pode entender vários aspectos do GROUP BY com apenas um conjunto de dados simples como este. Claro, um conjunto de dados da vida real teria muitas, muitas mais linhas, mas os princípios permanecem os mesmos.
Agrupamento por uma única coluna
Digamos que queremos descobrir quantos álbuns temos para cada artista. Comece com um típico SELECIONE consulta para buscar a coluna artist_id:
SELECT artist_id FROM albumsIsso retorna todas as nove linhas, conforme o esperado:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+Para agrupar esses resultados pelo artista, acrescente a frase GROUP BY artist_id :
SELECT artist_id FROM albums GROUP BY artist_idO que dá os seguintes resultados:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+Existem sete linhas no conjunto de resultados, reduzidas do total de nove no álbuns tabela. Cada um único artist_id tem uma única linha. Finalmente, para obter as contagens reais, adicione CONTAR(*) às colunas selecionadas:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+Os resultados agrupam dois pares de linhas para os artistas com ids 2 e 6 . Cada um possui dois álbuns em nosso banco de dados.
Relacionado: A folha de referências dos comandos SQL essenciais para iniciantes
Como acessar dados agrupados com uma função agregada
Você pode ter usado o CONTAR função antes, particularmente no CONTAR(*) formulário como visto acima. Ele busca o número de resultados em um conjunto. Você pode usá-lo para obter o número total de registros em uma tabela:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+COUNT é uma função agregada. Este termo se refere a funções que convertem valores de várias linhas em um único valor. Eles são freqüentemente usados em conjunto com a instrução GROUP BY.
Em vez de apenas contar o número de linhas, podemos aplicar uma função agregada a valores agrupados:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+As vendas totais mostradas acima para os artistas 2 e 6 são as vendas de seus vários álbuns combinadas:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+Agrupamento por múltiplas colunas
Você pode agrupar por mais de uma coluna. Basta incluir várias colunas ou expressões, separadas por vírgulas. Os resultados serão agrupados de acordo com a combinação dessas colunas.
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, salesIsso normalmente produzirá mais resultados do que agrupar por uma única coluna:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+Observe que, em nosso pequeno exemplo, apenas dois álbuns têm o mesmo ano de lançamento e contagem de vendas (28 em 1977).
Funções de agregação úteis
Além de COUNT, várias funções funcionam bem com GROUP. Cada função retorna um valor com base nos registros pertencentes a cada grupo de resultados.
- COUNT () retorna o número total de registros correspondentes.
- SUM () retorna o total de todos os valores na coluna fornecida somados.
- MIN () retorna o menor valor em uma determinada coluna.
- MAX () retorna o maior valor em uma determinada coluna.
- AVG () retorna a média média. É o equivalente a SUM () / COUNT ().
Você também pode usar essas funções sem uma cláusula GROUP:
como fazer login automático no Windows 10
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+Usando GROUP BY com uma cláusula WHERE
Assim como com um SELECT normal, você ainda pode usar WHERE para filtrar o conjunto de resultados:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+Agora você tem apenas os álbuns lançados após 1990, agrupados por artista. Você também pode usar uma junção com a cláusula WHERE, independentemente do GROUP BY:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+Observe, no entanto, que se você tentar filtrar com base em uma coluna agregada:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;Você receberá um erro:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'Colunas baseadas em dados agregados não estão disponíveis para a cláusula WHERE.
Usando a cláusula HAVING
Então, como você filtra o conjunto de resultados após um agrupamento ter ocorrido? o TENDO cláusula trata desta necessidade:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;Observe que a cláusula HAVING vem depois de GROUP BY. Caso contrário, é essencialmente uma simples substituição de ONDE por HAVING. Os resultados são:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+Você ainda pode usar uma condição WHERE para filtrar os resultados antes do agrupamento. Ele funcionará junto com uma cláusula HAVING para filtragem após o agrupamento:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;Apenas um artista em nosso banco de dados lançou mais de um álbum depois de 1990:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+Combinando resultados com GROUP BY
A instrução GROUP BY é uma parte incrivelmente útil da linguagem SQL. Ele pode fornecer informações resumidas de dados, para uma página de conteúdo, por exemplo. É uma excelente alternativa para buscar grandes quantidades de dados. O banco de dados lida bem com essa carga de trabalho extra, pois seu próprio design o torna ideal para o trabalho.
Depois de entender o agrupamento e como unir várias tabelas, você poderá utilizar a maior parte do poder de um banco de dados relacional.
Compartilhado Compartilhado Tweet O email Como consultar várias tabelas de banco de dados ao mesmo tempo com associações SQLAprenda a usar junções SQL para agilizar consultas, economizar tempo e fazer você se sentir um usuário avançado de SQL.
iphone se preso no logotipo da appleLeia a seguir Tópicos relacionados
- Programação
- SQL
Bobby é um entusiasta da tecnologia que trabalhou como desenvolvedor de software por quase duas décadas. Ele é apaixonado por jogos, trabalha como Editor de Críticas na Switch Player Magazine e está imerso em todos os aspectos da publicação online e desenvolvimento web.
Mais de Bobby JackAssine a nossa newsletter
Junte-se ao nosso boletim informativo para dicas de tecnologia, análises, e-books grátis e ofertas exclusivas!
Clique aqui para se inscrever