OCA Oracle Database 11g: SQL Fundamentos I [1Z0-051] – Parte VI

OCA Oracle Database 11g: SQL Fundamentos I [1Z0-051] – Parte VI

OI pessoal, hoje iremos falar dos seguintes tópico:

Reporting Aggregated Data Using the Group Functions

(Relatórios com Dados Agregados Usando as Funções de Grupo)

Describe the Group Funcions

(Descrevendo as Funções de Grupo)

Identify the Available Group Functions

(Identificar as Funções de Grupo Disponíveis)

Group Data Using the GROUP BY Clause

(Agrupando Dados Usando a Cláusula GROUP BY)

Include or Exclude Grouped Rows Using the HAVING Clause

(Incluir ou excluir linhas agrupadas usando a cláusula HAVING)

Descrevendo as Funções de Grupo

• Definição de Funções de Grupo

Operações de funções de grupo é uma agregação de dados e o retorno de um resultado único por grupo. Funções de grupo pode ser definida usando a seguinte fórmula: F(g1, g2, g3, …, gn) = result1, result2, result3, …, resultn. Um ou mais funções de grupo podem aparecer em uma lista SELECT como a seguir:

SELECT group_function(column or expression), ... FROM table [WHERE...] [ORDER BY ...]

• Tipos e Sintaxe de Funções de Grupo

A função COUNT conta o número de linhas em um um grupo. Sua sintaxe é a seguinte: COUNT({* | [DISTINCT | ALL] expr});

1. COUNT(*): Todas as linhas em um grupo, incluindo as com valores nulos ou valores duplicados são contados.

2. COUNT(DISTINCT expr): Somente as ocorrências unicas de expr serão contados.

3. COUNT(ALL exp): Conta o número de ocorrências não nulas de expr.

4. COUNT(exp): Conta o número de ocorrências não nulas de expr.

A função AVG calcula o valor médio de uma coluna numérica ou expressão em um grupo. A sintaxe é a seguinte:AVG([DISTINCT | ALL] expr).

1. AVG[DISTINCT expr]: Os valores distintos de expr são somados e divididos pelo número de ocorrências unicas de expr.

2. AVG(ALL expr): Soma os valores não nulos deexpr e divide pela soma de numero de linhas não nulas em um grupo.

3. AVG(expr): Soma os valores não nulos de expr e divide pela soma de numero de linhas não nulas em um grupo.

A função SUM retorna o total agregado dos valores não nulos de expressão numérica em um grupo. Sua sintaxe é: SUM([DISTINCT | ALL] expt).

1. SUM(DISTINCT expr): Fornece um total somando todos os valores originais devolvidos após avaliados os expr é para cada linha no grupo

2. SUM(ALL expr): Providencia um total somandoexpr para cada linha no grupo. Valores nulos são ignorados.

3. SUM(expr): Providencia um total somando expr para cada linha no grupo. Valores nulos são ignorados.

As funções MAX e MIN retornam o máximo e minimo valor de expr em um grupo. Sua sintaxe é a seguinte: MAX([DISTINCT | ALL] expr); MIN([DISTINCT | ALL] expr).

1. MAX(DISTINCT expr); MIN(DISTINCT expr): Examina os valores de expr em um grupo de linhas e retorna o maior ou menor valor. Valores nulos são ignorados.

2. MAX(ALL expr); MIN(ALL expr): Examina os valores de expr em um grupo de linhas e retorna o maior ou menor valor. Valores nulos são ignorados.

3. MAX(expr); MIN(expr): Examina os valores de expr em um grupo de linhas e retorna o maior ou menor valor. Valores nulos são ignorados.

As Funções STDDEV e VARIANCE são duas das muitas funções de grupo estáticos que a Oracle fornece. A VARIANCE estatística refere-se a uma variação de pontuação em um exemplo ou grupo de dados. A VARIANCE tem a seguinte sintaxe: VARIANCE([DISTINCT | ALL] expr):

1. VARIANCE(DISTINCT expr): Retorna a variação de dados unicos e não nulos em um grupo.

2. VARIANCE(ALL expr): Retorna a variação de dados não nulos em um grupo.

3. VARIANCE(expr): Retorna a variação de dados não nulos em um grupo.

A função STDDEV calcula o desvio padrão estatístico, que é o grau de desvio do valor médio de um grupo de dados. Sua sintaxe é: STDDEV([DISTINCT | ALL] expr).

1. STDDED(DISTINCT expr): Retorna o desvio padrão de dados unicos e não nulos em um grupo.

2. STDDEV(ALL expr): Retorna o desvio padrão de dados não nulos em um grupo.

3. STDDEV(expr): Retorna o desvio padrão de dados não nulos em um grupo.

Identificar as Funções de Grupo Disponíveis

• Usando Funções de de Grupo

A aplicação prática de funções de grupo é demonstrada usando AVG, SUM, MIN, MAX e COUNT.

1. A Função COUNT

A execução da função COUNT em uma coluna ou uma expressão retorna um valor inteiro que representa o número de linhas em um grupo.

SELECT COUNT(*) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

2. A Função SUM

A soma total de uma coluna ou expressão é calculado com a função SUM.

SELECT SUM(2) FROM employees;

SELECT SUM(salary) FROM employees;

3. A Função AVG

O valor médio de uma coluna ou expressão é a divisão da soma pelo números de linhas não nulas em um grupo.

SELECT AVG(2) FROM employees;

SELECT AVG(salary) FROM employees;

4. As Funções MAX e MIN

As funções MAX e MIN operam em tipos de dados NUMBER, DATE, CHAR e VARCHAR2. Eles retornam um valor de um mesmo tipo de dado que o argumento de entrada, com o maior ou menor itens em um grupo.

SELECT MAX(salary) FROM employees;

SELECT MIN(commission_pct), MAX(commission_pct)  FROM employees;

• Funções de Grupo Aninhadas

Lembre-se que um funções de linhas únicas podem ser aninhadas ou incorporado para qualquer nível de profundidade. As funções de grupo só podem ser aninhadas dois níveis de profundidade.

G1(group_item = result

G1(G2(group_item)) = result

G1(G2(G3(group_item))) Não é permitido

Agrupando Dados Usando a Cláusula GROUP BY

• A Cláusula GROUP BY

A instrução SELECT é aumentada pela adição de uma cláusula GROUP BY. A cláusula facilita a criação de grupos. E aparece depois de uma cláusula WHERE mais antes da cláusula ORDER BY, a sintaxe é a seguinte:

SELECT {column | expression | group_function(column | expression [alias],...)} FROM table [WHERE condition(s)] [GROUP BY {col(s) | expr}] [ORDER BY {col(s) | expr | numeric_pos} [ASC | ESC] [NULLS FIRST | LAST]];

Qualquer item na lista SELECT que não é uma função do grupo deve ser um atributo de agrupamento da cláusula GROUP BY.

• Agrupando por Múltiplas Colunas

Uma extensão poderosa para uma cláusula GROUP BY usa atributos de agrupamento múltiplos. Exemplo:

SELECT department_id, SUM(commission_pct) FROM employees WHERE commision_pct IS NOT NULL GROUP BY department_id;

SELECT department_id, job_id, SUM(commission_pct) FROM employees WHERE commission_pct IS NOT NULL GROUP BY department_id, job_id;

Incluir ou excluir linhas agrupadas usando a cláusula HAVING

• Restringindo o Grupo de Resultados

Linhas de agrupamento utilizando a cláusula GROUP BY e aplicar uma função agregada a esses resultados retorna grupos muitas vezes referida como resultados de nível de grupo. A cláusula HAVING providência uma  linguagem para restringir o resultado de nível de grupo.

• A Cláusula HAVING

A forma geral de um instrução SELECT é reforçada pela adição de uma cláusula HAVING, sua sintaxe segue a baixo:

SELECT {column | expression | group_function(column | expression [alias],...)} FROM table [WHERE condition(s)] [GROUP BY {col(s) | expr} [HAVING group_condition(s)]] [ORDER BY {col(s) | expr | numeric_pos} [ASC | ESC] [NULLS FIRST | LAST]];

Uma importante diferença entre a cláusula HAVING e as outras cláusula da instrução SELECT é que ela somente pode ser especificada se uma cláusula GROUP BY estiver presente.

Bom pessoal fica ai nosso roteiro de hoje.

Bons Estudos a todos e um grande abraço.