Criando grupos de dados
Introdução ao Oracle SQL
Hadrien Lacroix
Content Developer
Agrupando dados
Informações por grupo
| Composer | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086 |
| Pearl Jam | 122,801 |
| Pearl Jam | 65,593 |
| Jimmy Page | 401,920 |
| Jimmy Page | 386,063 |
| Jimmy Page | 132,702 |
| Jimmy Page | 189,675 |
| Jimmy Page | 126,641 |
| Carlos Santana | 318,432 |
| Carlos Santana | 296,437 |
| Carlos Santana | 882,834 |
| ... | ... |
Qual é o tempo médio de faixa das músicas de cada compositor?
GROUP BY- divide as linhas de uma tabela em grupos
- use funções de grupo para obter um resumo de cada grupo
GROUP BY
SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY e WHERE
SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY e ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY e ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY e ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average
| Composer | Average |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
Diretrizes
Qualquer coluna ou expressão no SELECT que não seja função de agregação deve estar na cláusula GROUP BY
SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer
column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice
Diretrizes
Qualquer coluna ou expressão na lista do SELECT que não seja função de agregação deve estar na cláusula GROUP BY
SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0 | 0.99 |
| Pearl Jam | 94,197.0 | 0.99 |
| Jimmy Page | 474,888.3 | 0.99 |
| Carlos Santana | 499,234.3 | 0.99 |
| ... | ... | ... |
Diretrizes
Expressões especificadas no GROUP BY não precisam estar no SELECT
SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer
| AVG(Milliseconds) |
|-------------------|
| 199,086.0 |
| 94,197.0 |
| 474,888.3 |
| 499,234.3 |
| ... |
Várias colunas
SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City
| Country | City | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires | 1 |
| Australia | Sidney | 1 |
| Austria | Vienne | 1 |
| Belgium | Brussels | 1 |
| Brazil | Brasilia | 1 |
| Brazil | São José dos Campos | 1 |
| Brazil | São Paulo | 2 |
| ... | ... | ... |
Vamos praticar!
Introdução ao Oracle SQL
