Utiliser les fonctions d’agrégation et les fonctions scalaires SQL

Télécharger le PDF du cours


Livret PDF du cours PHP et MySQL
Le SQL et le MySQL possèdent de nombreuses fonctions prêtes à l’emploi qui vont nous permettre de retourner certaines données en particulier ou d’effectuer des manipulations sur les données récupérées depuis notre base de données.

Parmi toutes ces fonctions, deux grands groupes de fonctions se dessinent : les fonctions d’agrégation et les fonctions scalaires.

 

Les fonctions d’agrégation

Les fonctions d’agrégation vont être utilisées pour réaliser des opérations à partir de différentes valeurs dans une colonne et ne vont retourner qu’une valeur. Ces fonctions « agrègent » donc plusieurs valeurs d’entrée en une valeur de sortie.

Dans cette leçon, nous allons étudier les fonctions d’agrégation suivantes :

  • Les fonctions min() et max() ;
  • La fonction count() ;
  • La fonction avg() ;
  • La fonction sum().

Les fonctions min() et max()

La fonction SQL min() va retourner la valeur la plus petite dans une colonne sélectionnée.

La fonction SQL max() va elle retourner la valeur la plus grande dans une colonne sélectionnée.

Nous pouvons utiliser ces fonctions pour voir par exemple quelle est la plus grosse commande passée sur notre site, quel est l’utilisateur le plus âgé, etc.
Notez que ces fonctions vont également fonctionner sur des chaines de caractères. Dans ce cas, le « a » sera considéré plus petit que « b » et etc.

Pour tester les différentes fonctions de cette partie, nous allons à nouveau utiliser notre table « users » dans laquelle nous allons ajouter une colonne « age » et également modifier l’âge de nos utilisateurs déjà inscrits.

Vous pouvez faire cela directement dans phpMyAdmin en ajoutant une colonne dans notre table à partir de l’onglet « Structure » puis ne modifiant la valeur de la colonne de chaque ligne.

Voici ce qu’on obtient :

Ajout d'une colonne dans une table MySQL depuis phpMyAdmin

Nous pouvons ensuite par exemple sélectionner le plus petit âge de notre colonne avec la fonction min(). Attention, cette fonction ne va retourner que la valeur la plus petite de la colonne et non pas afficher toutes les informations relatives à l’entrée possédant cette valeur.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              //Sélectionne la valeur la plus petite dans la colonne age
              $sth = $dbco->prepare("SELECT MIN(age) FROM users");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL min avec PDO PHP

Notez ici qu’on utilise fetch() et non pas fetchAll() pour afficher les données tout simplement car les fonctions d’agrégat ne renvoient qu’une valeur en résultat.

La fonction count()

La fonction SQL count() va retourner le nombre d’entrées d’une colonne. Nous l’utiliserons généralement avec une clause WHERE pour qu’elle retourne le nombre d’entrées qui vont satisfont à une certaine condition.

Cette fonction va être très utile d’un point de vue statistique, pour savoir par exemple combien de vos clients sont des hommes, ou habitent à Paris, etc.

Nous allons par exemple pouvoir savoir combien de nos utilisateurs ont plus de 30 ans :

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              //Retourne le nb d'utiilsateurs qui ont plus de 30 ans
              $sth = $dbco->prepare("
                SELECT COUNT(age)
                FROM users
                WHERE age > 30
              ");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL count avec PDO PHP

La fonction avg()

La fonction SQL avg() retourne la valeur moyenne d’une colonne contenant des valeurs numériques.

On va donc pouvoir d’un coup d’œil connaitre l’âge moyen de nos utilisateurs.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              //Retourne l'âge moyen de nos utilisateurs
              $sth = $dbco->prepare("SELECT AVG(age) FROM users");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL avg avec PDO PHP

La fonction sum()

La fonction SQL sum() retourne la somme des valeurs d’une colonne contenant des valeurs numériques.

Cette fonction va être utile pour faire un inventaire des produits vendus par exemple.

Pour la tester, nous pouvons dans notre table additionner les âges même si, je dois le reconnaitre, ça ne sert pas à grand-chose en pratique !

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              //Retourne la somme des ages de nos utilisateurs
              $sth = $dbco->prepare("SELECT SUM(age) FROM users");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL sum avec PDO PHP

 

Les fonctions scalaires

Les fonctions scalaires sont un sous type de UDF (« User Defined Functions » ou « Fonctions définies par l’Utilisateur » en français) en ce sens où ces fonctions sont basées sur les données envoyées par l’utilisateur.

Nous allons découvrir les fonctions scalaires suivantes :

  • Les fonctions lcase() et ucase() ;
  • La fonction length() ;
  • La fonction round() ;
  • La fonction now().

Les fonctions lcase() et ucase()

Les fonctions SQL lcase() et ucase() servent respectivement à convertir une chaine de caractères en minuscules ou en majuscules.

Ces fonctions peuvent être utiles pour pré-formater des résultats qu’on souhaite manipuler par la suite.

Nous allons ainsi par exemple pouvoir récupérer tous les prénoms de notre table « users » en majuscule.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              //Retourne les prénoms en majuscules
              $sth = $dbco->prepare("SELECT UCASE(prenom) FROM users");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL ucase avec PDO PHP

La fonction length()

La fonction MySL length() permet de calculer la longueur d’une chaîne de caractères.

Attention : notez que cette fonction se base sur le nombre de Bytes ou d’octets (1 Byte = 8 bits = 1 octet) pour le calcul et va bien retourner une taille en Bytes. Ainsi, un caractère multi-Bytes (comme les caractères accentués par exemple) est compté comme plus de 1 Byte.

On peut ainsi par exemple retourner le prénom de notre premier utilisateur ainsi que la longueur de celui-ci :

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              /*Retourne le prénom et la longueur du prénom
               *de la première entrée de la table*/
              $sth = $dbco->prepare("
                SELECT prenom, LENGTH(prenom)
                FROM users
                WHERE id = 1
              ");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL length avec PDO PHP

La fonction round()

La fonction SQL round() va nous permettre d’arrondir une valeur en choisissant le nombre de décimales voulues.

Cette fonction va donc prendre en paramètres une valeur à arrondir (ou le nom d’une colonne) ainsi que le nombre de décimales auxquelles on souhaite arrondir la ou les valeurs.

Attention cependant : les règles d’arrondis (pour les valeurs médianes comme 0.5) vont être différentes selon les situations :

  • Si on passe un chiffre exact, la règle d’approximation pour une commande SQL de type SELECT va être d’arrondir à la valeur supérieure ;
  • Si on passe une « valeur approximative » (expression utilisant une exponentielle par exemple), la règle d’arrondi va dépendre de la librairie C utilisée. Généralement, la règle sera d’arrondir au nombre pair le plus proche pour une commande SQL de type SELECT ;
  • Dans le cas d’une commande de type INSERT, alors la règle d’arrondi sera d’arrondi au nombre le plus éloigné de zéro dans tous les cas.

Testons cela immédiatement en passant une valeur exacte et deux valeurs approximatives à round() et en lui demandant d’arrondir ces valeurs à 1 décimale.

Pour cette fois, nous ne prendrons pas de valeurs dans notre table mais allons passer les valeurs directement à notre fonction.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              /*Retourne une valeur exacte arrondie (1 décimale) et une
              valeur approximative arrondie (1 décimale)*/
              $sth = $dbco->prepare("SELECT ROUND(2.55, 1), ROUND(24.5E-1, 1), ROUND(25.5E-1, 1)");
              $sth->execute();
              
              $resultat = $sth->fetch(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL round avec PDO PHP

Comme vous pouvez le voir, dans le cas de notre valeur exacte (2,55), round() arrondi à la valeur supérieure (2,6). En revanche, dans le cas de nos valeurs approximatives, l’arrondi va être fait au nombre pair le plus proche. Pour cette raison, 24,5E-1 est arrondi à 2,4 et 25,5E-1 est arrondi à 2,6.

Note : Pensez bien à utiliser des points et non pas des virgules pour représenter des nombres décimaux : on travaille toujours avec les notations anglo-saxonnes en informatique.

La fonction now()

La fonction SQL now() est utilisée pour retourner la date courante.

Cette fonction va être utile pour contextualiser une sélection de données en datant la date d’export par exemple.

On peut par exemple sélectionner tous les prénoms de notre table « users » en précisant la date de sélection :

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
          $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
          
          try{
              $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
              $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              
              /*Retourne les prénoms avec la date d'extraction*/
              $sth = $dbco->prepare("SELECT prenom, NOW() FROM users");
              $sth->execute();
              
              $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
              
              echo '<pre>';
              print_r($resultat);
              echo '</pre>';
          }
                
          catch(PDOException $e){
              echo "Erreur : " . $e->getMessage();
          }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de la fonction SQL now avec PDO PHP

 

Les fonctions d’agrégation et les critères de sélection

Il existe deux autres critères de sélection et de tri très courants dont je n’ai pas encore parlé jusqu’à présent pour la simple et bonne raison qu’on va principalement les utiliser avec les fonctions d’agrégation et qui sont GROUP BY et HAVING.

L’instruction GROUP BY

L’instruction SQL GROUP BY va généralement être utilisée avec les fonctions d’agrégation et en combinaison avec ORDER BY.

Cette instruction va nous permettre de grouper les résultats renvoyés selon une colonne.

En pratique, cette instruction va être utile pour regrouper des clients selon leur pays par exemple, ou encore selon le montant de leurs commandes, etc.

Dans notre table « users », nous allons ainsi par exemple pouvoir regrouper nos utilisateurs selon leur prénom et afficher le nombre de fois où un même prénom a été trouvé en utilisant la fonction count()

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Retourne tous les prénoms différents de la table users
                 *ainsi que le nombre de fois où un prénom a été trouvé*/
                $sth = $dbco->prepare("
                  SELECT COUNT(id), prenom FROM users
                  GROUP BY prenom
                  ORDER BY COUNT(id)
                ");
                $sth->execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de l'instruction SQL group by avec PDO PHP

Ici, on commence par compter combien d’id (et donc combien d’entrées) nous avons dans la table avec COUNT(id). On regroupe ensuite les résultats sélectionnés selon la valeur dans la colonne « prenom » de notre table. Finalement, on organise ces résultats selon le nombre de fois où un même prénom a été trouvé du plus grand nombre de fois au plus petit nombre de fois.

L’ordre des instructions est très important en SQL : si vous indiquez les différentes instructions dans n’importe quel ordre, vous avez de grandes chances pour que votre requête ne fonctionne pas du tout !

La clause SQL HAVING

La clause SQL HAVING remplace la clause WHERE dans le cas d’une utilisation avec les fonctions d’agrégation.

En effet, on ne peut tout simplement pas utiliser de clause WHERE avec des fonctions d’agrégation car la clause WHERE fonctionne avec les données de chaque ligne mais pas avec des données agrégées.

Nous allons par exemple pouvoir sélectionner uniquement les prénoms qui ne sont présents qu’une seule fois dans notre table et organiser le tout selon l’alphabet.

<!DOCTYPE html>
<html>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset='utf-8'>
    </head>
    <body>
        <h1>Bases de données MySQL</h1>  
        <?php
            $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root";
            
            try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*N'affiche que les prénoms uniques dans notre table*/
                $sth = $dbco->prepare("
                  SELECT COUNT(id), prenom FROM users
                  GROUP BY prenom
                  HAVING COUNT(id) < 2
                  ORDER BY prenom
                ");
                $sth->execute();
                
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
                
                echo '<pre>';
                print_r($resultat);
                echo '</pre>';
            }
                    
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }
        ?>
    </body>
</html>

 

Présentation et exemple d'utilisation de l'instruction SQL having avec PDO PHP

Précédent
Suivant

Laisser un commentaire

Logo Pierre Giraud Connexion Confidentialité CGV Sitemap Facebook Pierre Giraud Twitter Pierre Giraud Youtube Pierre Giraud Linkedin Pierre Giraud

© Pierre Giraud - Toute reproduction interdite - Mentions légales

13979