Ce chapitre contient une discussion sur les fonctions. On s’intéresse comment obtenir des informations globales sur les groupes de ligne. (Exemple: la valeur moyenne d’un groupe des entrées). On s’intéresse aussi sur comment grouper les entrées des tables dans des données convenables et stables pour les critères de recherché dans ces données.
Les fonctions habituelles font des opérations sur des lignes, que nous mettent dans un problème a l’utilisation des valeurs retournées d’eux. Dans le cas des fonctions de groupe nous avons un résultat unique après le travail sur les données sur plusieurs lignes. D’ici on peut avoir un problème spécial que va être discuter plus tard.
Les fonctions de groupe peuvent être introduits avec la clause SELECT d’une requête SQL. La syntaxe des fonctions de groupe est:
SELECT [column,] group_function(argument)
FROM table
[WHERE condition(s)]
[GROUP BY column]
[ORDER BY group_function(argument)] ;
argument peut-être un nom de colonne, une expression ou une constante.
Les fonctions de groupe ne peuvent pas être utilisées dans les clauses FROM, WHERE et GROUP BY.
Les 7 fonctions de groupe sont AVG, COUNT, MAX, MIN, STDDEV, SUM et VARIANCE.
Ce tableau au dessous présente une vue de l’ensemble de ces fonctions, sur les paramètres qu’il permet et sur les types des données retournées. Chaque fonction va être discutée individuellement dans les paragraphes suivantes.
| Fonction | description | syntaxe | arguments |
| SUM | Retourne la somme de toutes les valeurs du groupe n | SUM( [DISTINCT | ALL] n ) | Valeurs numériques |
| MAX | Retourne la plus grande valeur du groupe expr | MAX( [DISTINCT | ALL] expr ) | N’importe quel type de données |
| MIN | Retourne la plus petite valeur du groupe expr |
MIN( [DISTINCT | ALL] expr ) | N’importe quel type de données |
| COUNT | Retourne le nombre d’enregistrements contenus dans le groupe expr.COUNT(*) retourne le nombre total d'enregistrements retournés en incluant les valeurs nulles et les doublons. | COUNT( { * | [DISTINCT | ALL] expr } ) | N’importe quel type de données |
| AVG | Retourne la moyenne des valeurs du groupe n | AVG( [DISTINCT | ALL] n ) | Valeurs numériques |
| STDDEV | Retourne la déviance standard de x | STDDEV( [DISTINCT | ALL] x ) | Valeurs numériques |
| VARIANCE | Retourne la variance de x | VARIANCE( [DISTINCT | ALL] x ) | Valeurs numériques |
DISTINCT est utilisée pour éliminer des calcules les valeurs en double.
ALL demande a la fonction de prendre en considération toutes les valeurs, inclusive les valeurs en double. ALL est la valeur implicite de modificateur et ne doit pas etre spécifiée explicite.
Exemple :
Pour savoir le salaire moyen des employés de département 10
on peut utiliser la fonction AVG :
Remarque : AVG ne peut être utiliser que sur la colonne que contient des données numériques.
La fonction SUM Retourne la somme de toutes les valeurs du groupe n spécifiées dans l’argument, en ignorant toutes les valeurs NULL. SUM peut être utilisé pour traiter les données numériques.
Syntaxe :
SUM([DISTINCT|ALL] n)
Le modificateur DISTINCT est utilisé pour éliminer les valeurs en double.
Le modificateur ALL demande a la fonction de prendre en considération toutes les valeurs, inclusivement ceux en double. ALL est une valeur implicite de modificateur et ne doit pas être spécifiée explicite.
Exemple :
On peut utiliser la fonction SUM pour calculer la somme des salaires des managers
de la compagnie :
Remarque :
SUM peut être utiliser juste dans la colonne que contient des données
numériques.
La fonction MIN retourne la valeur minimum des valeurs de l’argument en ignorant les valeurs de type NULL.
Syntaxe :
MIN([DISTINCT|ALL],expression)
Le modificateur DISTINCT est utilisé pour éliminer les valeurs en double.
Le modificateur ALL demande a la fonction de prendre en considération toutes les valeurs, inclusivement ceux en double. ALL est une valeur implicite de modificateur et ne doit pas être spécifiée explicite.
Exemple :
Pour l’exemple ce dessous, MIN est utilisée pour extraire la
date de l’employée le plus vieille.
Remarque : La fonction MIN peut être opérer sur n’importe quel type de donnee.
La fonction MAX retourne la valeur maximum des valeurs spécifiées dans l’argument, en ignorant toutes les valeurs de type NULL.
Syntaxe
MAX([DISTINCT|ALL], expression)
Exemple :
Dans cet exemple la fonction MAX retourne l’employé le plus nouveau.
Remarque : La fonction MAX peut être utiliser sur toutes les types des
données.
Attention ! Dans le cas des données de type des fonctions MIN et MAX on prend en considération l’ordre alphabétique usuel, de même comme en cas des indexes d’un dictionnaire. L’exemple suivant retourne le premier et respectivement le dernier nom de famille d’une liste alphabétique des employés de la table emp.
La fonction STDDEV retourne la déviation statistique standard de l’argument, en ignorant les cas ou cette dernière prend la valeur nulle.
Syntaxe :
STDDEV([DISTINCT|ALL] x)
Definitie:
La deviaton statistique standard est definie comme la racine careede la variance. Elle est la plus utilisee mesure de la dispersion.
Sintaxa:
STDDEV([DISTINCT|ALL] x)
Exemplu:
Pour trouver la deviation standard des salaires des employees du departement 10 on peut utiliser la fonction STDDEV:
La fonction variance calcule la variance de l’argument, en ignorant les cas ou cette valeur est nulle.
Définition :
La variance est une fonction statistique qui se calcule comme déviation moyenne carrée face a des valeurs moyennes de distribution.
La variation se calcule suivant la formule S(x-u)^2/N
Ou u est une valeur moyenne et N est le nombre des valeurs.
Syntaxe :
VARIANCE([DISTINCT|ALL] x)
Exemple :
Pour savoir la variation des salaires des employés de département 10, on peut utiliser la fonction VARIANCE :
la fonction COUNT calcule le nombre de toutes les lignes pour lequel la valeur spécifiée de l’expression de l’argument ne soit pas nulle ; dans le cas d’utilisation de l’argument * on calcule le nombre de toutes les lignes, inclusivement les valeurs en double et celles nulles.
Syntaxe :
COUNT({*|[DISTINCT|ALL] expression})
La fonction COUNT se comporte différemment de l’argument fournit
en appel. On a a faire avec 3 situations possible :
1. COUNT(*)
2. COUNT(expression)
3. COUNT(DISTINCT expression)
COUNT(*) retourne le nombre des lignes qui satisfait le critère déclarée par SELECT, inclusivement les lignes en double et aussi nulles. Si la requête SQL contient la clause WHERE, COUNT(*) retourne le nombre des lignes qui satisfont cette condition.
Par exemple, la requête ci-dessous retourne le nombre des employés avec un salaire supérieur a 1000$.
COUNT(expression) retourne le nombre des lignes pour une colonne identifiée de l’expression.
L’exemple suivant affiche le nombre des employés qui peuvent avoir une commission.
COUNT(DISTINCT expression) retourne le nombre des lignes pour lesquels la colonne identifiée de l’expression unique différente de NULL.
Voila, en continuation un exemple, cette requête retourne le nombre d’enregistrements dans la table EMP dont la colonne DEPTNO a pour valeur 30.
Cette requête retourne le nombre de départements (doublons inclus) dans la table EMP.
Le problème c’est que trop des employés appartiennent a
un seul département, mais la fonction COUNT n’élimine pas
en mode implicite les doubles. Pour cela la solution correcte est la suivante
:
Toutes les fonctions de groupe ignorent les valeurs nulles d’une colonne. Pour les prendre en calcule, il faut placer une valeur valide de point de vue arithmétique (NULL est différent de 0). Pour ce but on peut utiliser les fonctions NVL. NVL détermine chaque valeur nulle trouvée dans la colonne spécifiée avec le 2eme argument. Par exemple, pour calculer la commission moyenne de nombre total des employés (et pas seulement celui qui prend commission) on peut introduire cette requête :
Cette requête retourne la moyenne des commissions touchées par les employés en tenant compte des valeurs nulles. En effet, la fonction NVL substitue, le temps de la requête, les valeurs nulles par la valeur 0, ce qui permet de prendre les quatorze employés en compte pour le calcul de la moyenne
Jusqu'à maintenant tous ces exemples ont traiter les tables avec des grands groupes des informations. Pour cela il est nécessaire qu’ils soient partagés dans des sous groupes plus petites. Cette fonction peut-être faite avec la clause GROUP BY
13.1. La clause GROUP BY
La clause GROUP BY permet de diviser les enregistrements d’une table en groupes. Les fonctions de groupe peuvent être alors utilisées pour retourner les informations relatives à chaque groupe.
13.2. Syntaxe de la clause GROUP BY:
SELECT [column1,] group_function(column2)
FROM table_name
[WHERE condition(s)]
[GROUP BY column1]
[ORDER BY column2] ;
13.3. Quelques règles :
- La clause WHERE peut être utilisée pour pré exclure des
enregistrements avant la division en groupes.
- Les colonnes de la clause FROM qui ne sont pas inclues dans une fonction
de groupe doivent être présentes dans la clause GROUP BY.
- Les alias de colonne ne peuvent pas être utilisés dans la clause
GROUP BY.
- Par défaut, la clause GROUP BY classe les enregistrements par ordre
croissant. L'ordre peut être changé en utilisant la clause ORDER
BY (cf. cours "Ordres SELECT Basiques" paragraphe 3.4.1 "La
clause ORDER BY").
Alors quant on utilise la clause GROUP BY il faut s’assurer que toutes
les colonnes de la clause SELECT, autres que celles que représentent
les résultats une fonction de groupe, ils sont compris dans la clause
ROUP BY. Cette requête affiche la moyenne des salaires des employés
pour chaque département présent dans la table EMP.
L’évaluation de la requête se fait comme suit :
•
La clause SELECT spécifie les colonnes que doivent être extraits.
On parle ici de nombre de département (département_id) et de
salaire moyenne (AVG (salaire))
•
La clause FROM indique la source d’où on va extraire les données
(table d’employés)
•
Le manque de clause WHERE indique le fait que les données vont être
extraites de toute les lignes ( si elle existait, elle va éliminer quelques
données d’elles)
•
La clause GROUP BY indique le mode de grouper les lignes. Elles sont groupées
suivant le nombre de département AVG(salaire) va calculer une salaire
moyenne pour chaque département.
La colonne contenue dans la clause GROUP BY n’a pas obligatoirement
besoin de se trouver dans la clause FROM. La clause ORDER BY peut accueillir
la ou les fonctions de groupe contenues dans la clause FROM.
Les colonnes inclus dans la clause SELECT doit être dans la clause GROUP
BY car le moteur de base de données ne peut pas retourner une ligne
dans lequel pour un seul résultat de la fonction de groupe on a plusieurs
valeurs des colonnes respectives. La clause GROUP BY assure la correspondance
un par un entre les valeurs retournees par la fonction de groupe et les valeurs
des autres colonnes. La réciproque n’est pas vraie. Les colonnes
spécifiées dans GROUP BY peuvent être utilisées
pour grouper les données dans des sous-groupes aussi qu’ils n’apparent
pas dans la clause SELECT (car on n’aime pas les inclure avec le résultat).
13.4. Groupement sur plusieurs colonnes
Plusieurs colonnes peuvent être spécifiées dans la clause GROUP BY, ce qui permet de récupérer des informations d’un groupe intégré dans un autre groupe. (Organiser les données en sous-groupe).
SELECT column1, column2,group_function(column)
FROM table_name
WHERE condition(s)
GROUP BY column1,column2
ORDER BY column;
La clause GROUP BY peut être appliqué sur plusieurs colonnes
successives. L’ordre dans lequel le serveur organise les groupes est
celle ou sont spécifiées les colonnes de la clause GROUP BY.
Il est important de retenir que le moteur utilise la colonne numéro
n de la liste pour créer les sous-groupes des groupes qui sont déjà sorties
sur la colonne numéro n-1. Donc on parle ici comment créer un
sous-groupe a un autre groupe, et non pas de dérouler une opération
de groupage des lignes de la table. Autrement plusieurs groupes de la ligne
zéro de la table conduit a la même inconsistance algébrique
avec celle ou il provient le besoin de répéter les colonnes de
la clause SELECT dans la clause GROUP BY, si dans la première se trouve
aussi des fonctions de groupes.
Cet exemple de ci-dessous calcule le salaire moyen suivant la fonction occupée
dans le cadre de chaque département.
Les données seront organisées en groupes par rapport à la
colonne column1. Puis chaque groupe sera à nouveau organisé en
sous-groupes par rapport à la colonne column2.
Les colonnes individuelles peuvent apparaître avec les fonctions de groupe dans la clause SELECT seulement s’ils sont utilisés pour le groupage des lignes dans la clause GROUP BY. Si elles ne se trouvent pas dans cette clause, le moteur de base de données va générer une erreur : « not a single group function”. Dans le message d’erreur un astérisque va être afficher sous le nom de colonne(s) qui ont génère l‘erreur. Donc on retient que toutes les colonnes ou expressions dans la clause SELECT, qui ne sont pas le résultat d’une fonction de groupe, doivent être présentes dans la clause GROUP BY.
Un alias de colonne ne peut pas être utilisé dans le GROUP BY.
Sinon l’erreur suivante se produit : " invalide column name ".
La clause HAVING ne peut pas être utilisée sans la clause GROUP BY.
Une fonction de groupe ne peut pas être utilisée dans la clause WHERE.
La clause WHERE n’acceptant pas les fonctions de groupes, la restriction
du résultat des fonctions de groupes se fera dans la clause HAVING.
L’ordre dans lequel Oracle effectue les opérations est :
1. Grouper les lignes
2. Les fonctions de groupe appliquent le groupement.
3. Les lignes que vérifient les conditions de la clause HAVING sont
retournées
SELECT column,group_function(argument)
FROM table
WHERE condition
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Comme dans les clauses WHERE et GROUP BY, les alias de colonne ne peuvent pas être utilisés dans la clause HAVING. La clause HAVING peut être utilisée sans la présence de fonctions de groupe dans la clause FROM.
La différence entre HAVING et WHERE :
- WHERE restreint les enregistrements
- HAVING restreint les groupes d’enregistrements et peut-être utilisée
pour restreindre les enregistrements.
Le moteur de base de données choisi l’ordre comment effectuer les opérations dans les fonctions de type clause, et non pas dans les fonctions de l’ordre dans lequel les clauses sont introduites dans la requête. Pour cela on peut placer la clause HAVING avant la clause GROUP BY.
Des fonctions de groupe ayant comme argument le résultat d’une
fonction de groupe sont appelées fonctions imbriquées (nested
functions).Dans cette exemple ci dessous AVG(sal) calcule la moyenne des salaires
dans chaque département grâce à la clause GROUP BY. MAX(AVG(sal))
retourne la moyenne des salaires la plus élevée.
• Il existe 7 fonctions de groupe en SQL : AVG, COUNT, MAX, MIN, SUM,
STDDEV si VARIANCE.
•
On peut créer des groupes en utilisant la clause GROUP BY. Les groupes
peuvent être restreintes en utilisant la clause HAVING.
•
L’utilisation de GROUP BY et HAVING suivant la clause WHERE. Laisser
la clause ORDER BY a la fin.
•
Le serveur Oracle s’évolue suivant cet ordre :
o Si la requête a une clause WHERE, cette dernière est pour rendre
stabile les lignes conçues pour le travailLe serveur forme des groupes
spécifiés dans la clause GROUP BY.Le résultat est restreint
avec les conditions spécifiées dans la clause HAVING.
Des Exercices pratiques
Présentation
Les exercices pratiques dans cette section occupent les sujets suivants :
Ecrire une requête que utilisent les fonctions de groupe
•
Grouper dans des lignes pour obtenir un résultat multiple ;Restreindre
les groupes en utilisant la clause HAVING.
•
Restreindre les groupes en utilisant HAVING
•
A la fin de ces exercices il faut que vous maîtrisez l’utilisation
des fonctions de groupe.
Questions
Déterminez la validation de ces 3 annonces :
1. Les fonctions de groupe font les opérations sur plusieurs lignes pour produire un résultat unique.
Vrai/faux
2. les fonctions de groupe prennent en considération les valeurs nulles (NULL)
Vrai/faux
3. la clause WHERE restreint les lignes avant les inclure dans le groupe.
Vrai/faux
Exercices de base
4. affichez le salaire maximum, le salaire minimum, le salaire moyen et la somme des tous les salires des employes dans la firme. Nommez les colonnes « Maxim », « Minim », « Moyenne », et respectivement « Total ». Faites le rend des resultats pour qu’ils soient des valeurs entiers.
5. Modifiez la requete de la question precedente pour obtenir les meme indicateurs
pour chaque type d’employes.
6. Ecrivez une requete qu retourne le nombre des employes pour chaque type de position.
7. Déterminez le nombre des managers de la compagnie sans les lister.
Indication : Penser a la colonne « MGR »
8. Ecrivez une requête que retourne la différence entre le plus petit salaire dans la firme et le plus grand. La colonne de résultat va être nommée « Différence »
9. Afficher Les indicateurs des managers et les salaires les plus mouvais payées employes. Exclus les employes ou les managers ne sont pas connus comme les groupes ou le salaire minimum est plus petit ou égal a $1000. Faire sortitr en ordre descendante des salaires.
Exercices suplementaires
10. Ecrivez une requête que liste le nombre des employés de la firme. Aussi les nombre des personnes employées dans la période entre 1995 et 1998 inclusivement. Créez une colonne avec les noms respectifs.
11. Trouvez tous les employés qui étaient dans la première moitié de mois.
12. Affichez les noms des tous les employés, ses salaires et la somme d’argents qu’ils gagnent (en mille dollars)
13. Affichez tous les employés lesquels ses managers ont le salaires plus grand que $5000. Il faut afficher les données suivantes : nom d’employé, nom de manager, le salaire de manager aussi la classe des salaires de manager.
14. Afficher le nombre, le nom, le nombre des employes et les saliares moyens de chaque departement comme les noms, les salaires et les posts de tous les employes qui travaillent dans ce departement.
15. Afficher les nombre de departement et le plus petit salaire a un des ses employes pour le departement avec la plus grand salaire moyen.