Ir para o conteúdo principal

Função de agregação (count, sum, max, min, avg, partition by)

Confira algumas funções que ajudam nas consultas, onde é necessário criar agrupamentos de diferentes formas.

TABELA EXEMPLO

EX_ITENS

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE |
---------------------------------------------------
|      1 | 'Biscoito'	  | 'Comida' |         20 |
|      2 | 'Água'         | 'Bebida' |      47,95 |
|      3 | 'Suco'         | 'Bebida' |       23,4 |
|      4 | 'Pão'          | 'Comida' |         10 |
|      5 | 'Refrigerante' | 'Bebida' |      13,75 |

SQL de CRIAÇÃO/INSERÇÃO: 

CREATE TABLE ex_itens (
  codigo INT4,
  descricao TEXT,
  tipo VARCHAR(20),
  quantidade DECIMAL(15,2));
INSERT INTO ex_itens VALUES (1, 'Biscoito', 'Comida', 20),
                            (2, 'Água', 'Bebida', 47.95),
                            (3, 'Suco', 'Bebida', 23.4),
                            (4, 'Pão', 'Comida', 10),
                            (5, 'Refrigerante', 'Bebida', 13.75);
COMMIT;
Consulta com agrupamento agregado:
SELECT SUM(QUANTIDADE) VOLUME_POR_TIPO,
	   MIN(QUANTIDADE) MENOR_QTD_POR_TIPO,
	   MAX(QUANTIDADE) MAIOR_QTD_POR_TIPO,
	   TRUNC(AVG(QUANTIDADE), 2) MEDIA_QTD_POR_TIPO
FROM EX_ITENS
GROUP BY TIPO

Retorno: 

| QTD_POR_TIPO | VOLUME_POR_TIPO | MENOR_QTD_POR_TIPO | MAIOR_QTD_POR_TIPO | MEDIA_QTD_POR_TIPO |
-------------------------------------------------------------------------------------------------
|            3 |           85.10 |              13.75 |              47.95 |              28.36 |
|            2 |              30 |                 10 |                 20 |                 15 |
  • Count: Retorna quantidade de linhas no agrupamento definido;
  • Sum: Retorna soma de uma coluna das linhas no agrupamento definido;
  • Max: Retorna valor máximo do campo de entrada no agrupamento definido;
  • Min: Retorna valor mínimo do campo de entrada no agrupamento definido;
  • Avg: Retorna média dos valores do campo de entrada no agrupamento definido;
Consulta com agrupamento analítico:
SELECT CODIGO, DESCRICAO, TIPO, QUANTIDADE,
       DENSE_RANK() OVER (ORDER BY QUANTIDADE DESC) RANK_QTD,
       DENSE_RANK() OVER (PARTITION BY TIPO ORDER BY QUANTIDADE DESC) RANK_QTD_POR_TIPO,
       COUNT(QUANTIDADE) OVER (PARTITION BY TIPO) QTD_ITENS_POR_TIPO,
       SUM(QUANTIDADE) OVER (PARTITION BY TIPO) QTD_VOLUME_POR_TIPO,
       MIN(QUANTIDADE) OVER (PARTITION BY TIPO) MENOR_QTD_POR_TIPO,
       MAX(QUANTIDADE) OVER (PARTITION BY TIPO) MAIOR_QTD_POR_TIPO,
       TRUNC(AVG(QUANTIDADE) OVER (PARTITION BY TIPO), 2) MEDIA_QTD_POR_TIPO
  FROM EX_ITENS

Retorno: 

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE | RANK_QTD | RANK_QTD_POR_TIPO | QTD_ITENS_POR_TIPO | QTD_VOLUME_POR_TIPO | MENOR_QTD_POR_TIPO | MAIOR_QTD_POR_TIPO | MEDIA_QTD_POR_TIPO |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|      2 | 'Água'         | 'Bebida' |      47.95 |        1 |                 1 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      3 | 'Suco'         | 'Bebida' |       23.4 |        2 |                 2 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      1 | 'Biscoito'     | 'Comida' |         20 |        3 |                 1 |                  2 |                  30 |                 10 |                 20 |                 15 |
|      5 | 'Refrigerante' | 'Bebida' |      13.75 |        4 |                 3 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      4 | 'Pão'          | 'Comida' |         10 |        5 |                 2 |                  2 |                  30 |                 10 |                 20 |                 15 |
  • Dense_rank(): Retorna posição de rank definida pela ordenação na estrutura com Over;
  • Partition by: Utilizado para retornar um valor agrupado sem fazer um Distinct.

 

Voltar ao topo