Utilisation des fonctions de groupe

Reporting Données agrégées à l'aide des fonctions Groupe

SQL a de nombreuses fonctions d'agrégation prédéfinies qui peuvent être utilisées pour écrire des requêtes afin de produire exactement ce type d'informations.La clause GROUP BY spécifie comment regrouper les lignes d'une table de données lors de l'agrégation d'informations, tandis que la clause HAVING filtre les lignes qui n'appartiennent pas à groupes spécifiés.

Les fonctions d'agrégation exécutent diverses actions telles que le comptage de toutes les lignes d'une table, la moyenne des données d'une colonne et la somme des données numériques. Les agrégats peuvent également rechercher une table pour trouver les valeurs «MAX» les plus élevées ou «MIN» les plus basses d'une colonne. Comme pour les autres types de requêtes, vous pouvez restreindre ou filtrer les lignes sur lesquelles ces fonctions agissent avec la clause WHERE. Par exemple, si un responsable a besoin de savoir combien d'employés travaillent dans une organisation, la fonction d'agrégation nommée COUNT (*) peut être utilisée pour produire ces informations.La fonction COUNT (*) affichée dans l'instruction SELECT ci-dessous compte toutes les lignes dans un table.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

La table de résultats de la fonction COUNT (*) est une seule colonne d'une seule ligne appelée résultat ou valeur scalaire. Notez que la table de résultats a un en-tête de colonne qui correspond au nom de la fonction d'agrégation spécifiée dans la clause SELECT.

Certaines des fonctions d'agrégation couramment utilisées sont les suivantes:

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Les mots-clés ALL et DISTINCT sont facultatifs et fonctionnent comme ils le font avec les clauses SELECT que vous avez appris à écrire.Le mot-clé ALL est le mot-clé par défaut où l'option est autorisée.L'expression répertoriée dans la syntaxe peut être une constante, une fonction, ou toute combinaison de noms de colonnes, de constantes et de fonctions connectées par des opérateurs arithmétiques. Cependant, les fonctions d'agrégation sont le plus souvent utilisées avec un nom de colonne. À l'exception de la fonction COUNT, toutes les fonctions d'agrégation ne prennent pas en compte les valeurs NULL.

Il existe deux règles que vous devez comprendre et suivre lorsque vous utilisez des agrégats:

Les fonctions d'agrégation peuvent être utilisées à la fois dans les clauses SELECT et HAVING (la clause HAVING est traitée plus loin dans ce chapitre).
Les fonctions d'agrégation ne peuvent pas être utilisées dans une clause WHERE. Sa violation produira la fonction de groupe Oracle ORA-00934 n'est pas autorisée ici message d'erreur.

Illustrations

La requête SELECT ci-dessous compte le nombre d'employés dans l'organisation.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

La requête SELECT ci-dessous renvoie la moyenne des salaires des employés de l'organisation.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

La requête SELECT ci-dessous renvoie la somme des salaires des employés de l'organisation.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

La requête SELECT ci-dessous renvoie les dates d'embauche les plus anciennes et les plus récentes des employés de l'organisation.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

PAR GROUPE

Les fonctions d'agrégation sont normalement utilisées conjointement avec une clause GROUP BY. La clause GROUP BY vous permet d'utiliser des fonctions d'agrégation pour répondre à des questions de gestion plus complexes telles que:

Quel est le salaire moyen des employés dans chaque département?

Combien d'employés travaillent dans chaque département?

Combien d'employés travaillent sur un projet particulier?

La fonction Group by établit des groupes de données basés sur des colonnes et agrège les informations au sein d'un groupe uniquement. Le critère de regroupement est défini par les colonnes spécifiées dans la clause GROUP BY. Suivant cette hiérarchie, les données sont d'abord organisées dans les groupes, puis la clause WHERE restreint les lignes de chaque groupe.

Directives d'utilisation de la clause GROUP BY

(1) Toutes les colonnes ou colonnes dépendantes utilisées dans la fonction GROUP BY doivent former la base du regroupement et doivent donc également être incluses dans la clause GROUP BY.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) La clause GROUP BY ne prend pas en charge l'utilisation d'alias de colonne, mais les noms réels.

(3) La clause GROUP BY ne peut être utilisée qu'avec des fonctions d'agrégation telles que SUM, AVG, COUNT, MAX et MIN. Si elle est utilisée avec des fonctions à une seule ligne, Oracle lève une exception sous la forme "ORA-00979: pas une expression GROUP BY" .

(4) Les fonctions d'agrégation ne peuvent pas être utilisées dans une clause GROUP BY. Oracle renverra ici le message d'erreur "ORA-00934: fonction de groupe non autorisée".

La requête ci-dessous répertorie le nombre d'employés travaillant dans chaque service.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

De même, recherchez ci-dessous la requête pour trouver la somme des salaires pour les ID de poste respectifs dans chaque département. Notez que le groupe est établi en fonction du service et de l'ID de tâche. Ils apparaissent donc dans la clause GROUP BY.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

La requête ci-dessous produit également le même résultat. Veuillez noter que le regroupement est basé sur les colonnes ID de service et ID de poste, mais n'est pas utilisé à des fins d'affichage.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Utilisation de DISTINCT, ALL mots-clés avec des fonctions d'agrégation

En spécifiant le mot clé DISTINCT avec le paramètre d'entrée, la fonction group by considère uniquement la valeur unique de la colonne pour l'agrégation. En spécifiant le mot clé ALL avec le paramètre d'entrée, la fonction de groupe par fonction considère toutes les valeurs de la colonne pour l'agrégation, y compris les valeurs nulles et les doublons. ALL est la spécification par défaut.

La clause HAVING

La clause HAVING est utilisée pour les fonctions d'agrégation de la même manière qu'une clause WHERE est utilisée pour les noms de colonne et les expressions. . Bien qu'il puisse sembler qu'une clause HAVING filtre les groupes, ce n'est pas le cas, mais une clause HAVING filtre les lignes.

Lorsque toutes les lignes d'un groupe sont éliminées, le groupe l'est aussi. Pour résumer, les différences importantes entre les clauses WHERE et HAVING sont les suivantes:

Une clause WHERE est utilisée pour filtrer les lignes AVANT l'action GROUPING (c'est-à-dire avant le calcul des fonctions d'agrégation).

Une clause HAVING filtre les lignes APRÈS l'action GROUPING (c'est-à-dire après le calcul des fonctions d'agrégation).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

La clause HAVING est une option conditionnelle qui est directement liée à l'option de clause GROUP BY car une clause HAVING élimine les lignes d'une table de résultats en fonction du résultat d'une clause GROUP BY.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function