na lição de hoje, você vai aprender sobre o agrupamento de resultados retornados de suas consultas usando o grupo SQL por cláusula.
os objectivos da lição de hoje são:
- aprender a agrupar os resultados usando o grupo por
- Usar as funções agregadas para realizar cálculos
- entender como filtrar os grupos usando a cláusula que tem
importante! Por favor, siga e faça os exemplos em sua base de dados. Se ainda não o fez, Inscreva-se no meu guia para começar com o servidor SQL., Você recebe instruções sobre como instalar as ferramentas gratuitas e banco de dados de amostra.
SQL GROUP BY Clause
the SQL GROUP BY Clause is used to output a row across specified column values. É tipicamente usado em conjunto com funções agregadas como soma ou contagem para resumir valores. Em grupos SQL são combinações únicas de campos. Ao invés de retornar cada linha em uma tabela, quando os valores são agrupados, apenas as combinações únicas são retornadas.
O grupo por cláusula é adicionado à declaração SQL após a cláusula WHERE., Aqui está um exemplo onde estamos listando SalesOrderID, excluindo quantidades superiores a 100.
Há um par de coisas para observação. Primeiro, as colunas que queremos resumir estão listadas, separadas por vírgulas, no grupo por cláusula. Em segundo lugar, esta mesma lista de colunas deve ser listada na instrução select; caso contrário, a instrução falha.,
Quando esta instrução é executada, nem todas as linhas filtradas são devolvidas. Apenas combinações únicas de SalesOrderID são incluídas no resultado. Esta declaração é muito semelhante a
Mas há uma diferença chave., O modificador distinto pára na saída de uma combinação única de linhas, enquanto, com o grupo por instrução, podemos calcular valores com base nas linhas filtradas subjacentes para cada combinação única.
Em outras palavras, usando o nosso exemplo, com o GRUPO, podemos calcular o número ou OrderDetails por ordem seguinte:
CONTAGEM é um exemplo de uma função de agregação, estes são os que realmente dão o GRUPO de instrução de seu valor especial.
funções agregadas SQL
algumas funções, como a soma, são usadas para realizar cálculos sobre um grupo de linhas, estas são chamadas funções agregadas. Na maioria dos casos, estas funções operam em um grupo de valores que são definidos usando o grupo por cláusula., Quando não há um grupo por cláusula, é geralmente entendido que a função agregada se aplica a todos os resultados filtrados.
Alguns dos mais comuns funções de agregação incluem:
| AVG(expressão) | Calcular a média de expressão. |
| Contagem(expressão) | Contagem de ocorrências de valores não nulos devolvidos pela expressão. |
| Contagem (*) | conta todas as linhas do quadro especificado., |
| MIN(expressão) | encontra o valor de expressão mínima. |
| MAX(expressão) | encontra o valor de expressão máxima. |
| soma(expressão) | calcular a soma da expressão. |
Estas funções podem ser usadas sozinhas em conjunto com a cláusula GROUP BY. Por conta própria, eles operam em toda a tabela; no entanto, quando usados com o grupo por, seus cálculos são “reset” cada vez que o agrupamento muda., Desta forma eles agem como subtotais.
função agregada simples
ao usar a função agregada você pode calcular o resultado em todos os valores ou valores distintos. Por exemplo, para contar todos os registros de vendas, poderíamos usar a expressão:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
isto resulta em uma contagem de 121317 sendo devolvido.
para contar as ordens distintas que compõem os detalhes que usaríamos o seguinte:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
a contagem agora é 31465.,
SQL GROUP BY and Aggregate Functions
To aggregate means to make whole from individual parts. Funções agregadas são funções que trabalham em mais de uma linha para retornar um resultado.
AVG e SUM
a função SOMA acumula os valores retornados, de forma semelhante AVG calcula a média.
Let’s see if we can calculate the total OrderDetails., A partir de lições anteriores sabemos como calcular a quantidade total de cada detalhe como:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
Como podemos aplicar a função de agregação de expressões, podemos configurar um agrupamento em Códigodopedido para calcular o preço total, por ordem
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
podemos mesmo classificar o total para chegar ao topo ordens primeiro
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
da mesma forma, podemos calcular a média de detalhes do pedido por meio do AVG. Por que você tenta lá em baixo! Se você ficar preso comuta a resposta.
Para os mais curiosos, pois a média é calculada como a soma da amostra dividido pelo exemplo de contagem, em seguida, usando o AVG na declaração acima é o mesmo que:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Nós vimos um monte nesta seção., Aqui estão alguns pontos-chave:
- Uma função de agregação pode avaliar uma expressão, tais como a SOMA(A + B)
- Você deve alias funções de agregação, de modo que os nomes de coluna são significativas
- Quando se trabalha com funções de agregação e de GRUPO, é, às vezes, é mais fácil pensar sobre os detalhes primeira, que é a escrita de uma instrução SELECT simples, inspecionar os resultados, em seguida, adicione as coisas extravagantes. Eu cubro isto em Como Escrever melhores consultas.
Contagem de SQL
a função de contagem é usada quando você precisa saber quantos registros existem em uma tabela ou dentro de um grupo., A contagem (*) contará todos os registros no agrupamento; enquanto a contagem(expressão) conta todos os registros onde o resultado da expressão não é nulo. Você também pode usar distinto com contagem para encontrar o número de valores únicos dentro de um grupo.
Para encontrar o número de SalesOrderDetail Linhas por ordem
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Para encontrar o número de pedidos de vendas por produto
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN e MAX.
Use MIN e MAX para encontrar o maior e menor valores de, respectivamente, dentro de uma tabela ou de um grupo.,
Por exemplo, para encontrar as menores e maiores quantidades de produto ordenadas dentro de uma ordem tente
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Você também pode encontrar o valor mínimo ou máximo de um cálculo. Aqui encontramos a maior quantidade de produto ordenada dentro de um produto:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
tendo Cláusula
a cláusula SQL tendo é usada para filtrar grupos de acordo com os resultados das funções agregadas. Isso torna possível resolver problemas como selecionar todas as ordens que tenham mais de duas linhas de detalhes de ordem.,
o exemplo Que parece
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
Se quisermos encontrar todas as encomendas mais de us $1000 escrevêssemos
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
Note que podemos usar o alias TotalPrice na cláusula ORDER BY, mas, em seguida, ter uma cláusula de usar a expressão.
para martelar para casa tendo, eu quero mostrar um último exemplo. Aqui você verá que a instrução ter inclui uma função agregada que não está na lista selecionada.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
na consulta acima estamos recebendo o preço total para as encomendas em que a quantidade média de Vendederdetail é superior a $500,00.
declaração Final sobre ter
apesar de desempenharem uma função semelhante, há uma distinção chave entre a cláusula onde e ter. A cláusula “WHERE” para filtrar os registos individuais; considerando que a cláusula “HAVING” filtra os grupos.para mantê-lo direito na minha cabeça, gosto de pensar na cláusula onde está a fazer o seu trabalho antes de qualquer agrupamento ocorrer, e depois na cláusula de ter a tomada a cargo após a formação dos grupos.,
exercícios
é importante praticar! Use a base de dados de amostras para responder a estas perguntas.a HR quer um relatório do número de empregados activos por título de trabalho. Que SQL usarias?
Leave a Reply