09. Les fonctions

A présent les exemples seront illustrés avec des données provenant d'une entreprise d'e-commerce vendant des articles de sports. Il y a de nombreuses tables mais nous allons nous concentrer pour le moment sur :

• La table factInternetSale qui concerne les articles commandés.
• La table dimProduct qui donne les caractéristiques des différents produits.
  
  

Un aperçu de la table factInternetSale dans la base de données adventureWork.

Les fonctions

Les fonctions sont conçues pour traiter des données en entrée (input) et produire des données transformées en sortie (output).

La plupart des fonctions se terminent par des parenthèses, comme SUM(), DATE() ou MEDIAN(). À l'intérieur des parenthèses, on place ce qu'on appelle des arguments. Ce sont les informations ou valeurs que la fonction utilise pour effectuer son calcul ou sa transformation. Par exemple :

• Dans SUM(1, 2, 3), les arguments 1, 2, et 3 sont les valeurs que la fonction additionne.
• Dans DATE(2024, 11, 15), les arguments correspondent à l'année, le mois et le jour pour construire une date.
  

Les arguments sont séparés par des virgules ',' ce qui permet de passer plusieurs éléments à une même fonction.

Type de données en entrée

Les fonctions s'appliquent aux colonnes (ou champs) d'une table, chacune étant associée à un type de données spécifique comme VARCHAR, INT, ou DATE. Pour fonctionner correctement, une fonction attend que les données du champ respectent ce type. Ci-dessous, des exemples de type de données:

Bien que les fonctions soient beaucoup utilisées dans la clause SELECT, elles peuvent également être utilisées dans d'autres clauses telles que WHERE , GROUP BY ou HAVING (qui n'ont pas encore été vues). Ici on distinguera 3 types de fonctions, chacune ayant un rôle spécifique dans le traitement des données :

• Celles qui agissent sur les lignes,
• Celles qui agissent sur les agrégats,
• Celles qui agissent sur les fenêtres.

/* 
La colonne orderDate doit être une date afin que YEAR( ) 
puisse extraire l'année de la commande. 
*/
SELECT DISTINCT YEAR(orderDate)
FROM factInternetSale ;

Fonctions de Lignes

Les fonctions de ligne, aussi appelé "row level function" sont les plus simples et les plus communément utilisées. Elles renvoient une valeur transformée par ligne.

/* 
Affiche les colonnes mentionnées ainsi que la colonne qui compte 
le nombre de jours entre la prise de commande et l'envoie du colis. 
*/
SELECT orderDate, shipDate, DAY(shipDate - orderDate) AS preparationDay
FROM factInternetSale
LIMIT 3 ;

Question 9.1

Dans la table factInternetSale dans la base de données adventureWork, afficher les lignes où la préparation de commande (le temps entre l'enregistrement de la commande et l'envoie) prend moins d'une semaine. Pour accéder à la console SQL.
Rappel : Les fonctions peuvent aussi être utilisé dans le WHERE.

ROUND( )

Nous avons utilisé des fonctions de lignes sur des données de type date mais on peut également le faire pour d'autres type de fonction. À présent, nous allons utiliser la fonction ROUND( ) qui agit sur des données décimales. ROUND( ) prend 2 arguments, la colonne choisie et le nombre de décimales utilisées pour arrondir le résultat.

/* */
SELECT salesOrderNumber, ROUND(ProductStandardCost,0) as ProductStandardCost, 
       ROUND(UnitPrice,0) as UnitPrice
FROM factInternetSale
LIMIT 3 ;

Question 9.2

Dans la table factInternetSale , afficher la marge brute réalisée pour chaque type de produits de chaque commande enregistrée au mois de juillet 2011. Le résultat doit être affiché avec uniquement 2 chiffres après la virgule. Pour accéder à la console SQL.

• Rappel 1 : Une ligne correspond à un type de produit dans une commande.
• Rappel 2 : La marge brute est la différence (HT) entre le prix de vente et le coût de revient.

Question 9.3

Dans la table dimProduct, afficher tous les produits qui ont dans leur nom "ball" que ce soit en minuscule ou majuscule. Pour accéder à la console SQL.
Rappel : Selon la version de SQL utilisée, l'opérateur LIKE peut être sensible aux majuscules et minuscules. Afin d'être sûr de trouver un résultat, une bonne pratique est d'ajouter la fonction LOWER( ) qui transforme tous les caractères en minuscule, ensuite l'opérateur LIKE cherche les caractères correspondant en minuscule.

CONCAT( )

La fonction CONCAT( ) est utilisée pour combiner plusieurs chaînes de caractères, qu'elles proviennent de valeurs fixes ou de colonnes d'une table. Elle peut être appliquée à chaque ligne d'une table pour concaténer les valeurs de plusieurs colonnes en une seule chaîne. Par exemple, si vous avez une colonne pour les prénoms et une autre pour les noms, vous pouvez les combiner pour former un nom complet.

Question 9.4

La fonction CONCAT( ) permet de concaténer 2 chaines de caractères. Utilise cette fonction pour créer la colonne Newname dans la table dimProduct qui aura le format suivant :

Pour accéder à la console SQL.

CASE WHEN

Les fonctions appliquées aux lignes peuvent être utilisées avec tous types de données. La plupart d'entre elles nécessitent des parenthèses pour recevoir des arguments, bien qu'il existe quelques exceptions.
Parmi ces exceptions, l'expression conditionnelle CASE WHEN se distingue : elle permet d'exécuter différentes opérations selon une ou plusieurs conditions. Cette expression peut être comparée à une structure IF( ) dans des langages comme Excel, Python, R ou JavaScript.

Voici comment fonctionne la syntaxe générale de la fonction CASE WHEN.

CASE
   WHEN condition1 THEN résultat1
   WHEN condition2 THEN résultat2
   ...
   ELSE résultat_par_défaut
END

Évaluation séquentielle : Les conditions sont évaluées de manière séquentielle. La première condition qui est vraie détermine le résultat retourné et les autres conditions ne sont pas évaluées.

CASE
   WHEN x <= 10 THEN 'Un'
   WHEN x <= 20 THEN 'Deux'
   ELSE 'Autre'
END

Si x est inférieur ou égal à 10, le résultat est 'Un' et les conditions suivantes ne sont pas évaluées. Sinon, si x est inférieur ou égal à 20 (et par conséquent de la première condition, supérieur à 10), le résultat est 'Deux' et les conditions suivantes ne seront pas évaluées. Si les 2 cas précédents ne sont pas vérifiés, le résultat est 'Autre'.

Conditions multiples : Vous pouvez utiliser des opérateurs logiques tels que AND, OR et NOT pour créer des conditions plus complexes.

CASE
   WHEN (age >= 18 AND gender = 'Femme') OR (age >= 21 AND gender = 'Homme') THEN 'Admissible'
   ELSE 'Non Admissible'
END 

la condition ELSE permet de définir un résultat par défaut si aucune des conditions précédentes n'est vraie. Par conséquent, il est fortement recommandé de toujours mettre une valeur par défaut.

Si une personne est une femme de 18 ans ou plus ou un homme de 21 ans ou plus, le résultat est 'Admissible'. Sinon, le résultat est 'Non Admissible'.

SELECT salesOrderNumber, ProductStandardCost, UnitPrice, 
       ROUND(100*(UnitPrice - ProductStandardCost)/ProductStandardCost,2) AS pctProfit,
       CASE WHEN (UnitPrice - ProductStandardCost)/ProductStandardCost < 0.2 THEN 'petit'
            WHEN (UnitPrice - ProductStandardCost)/ProductStandardCost < 0.4 THEN 'moyen'
            WHEN (UnitPrice - ProductStandardCost)/ProductStandardCost < 0.6 THEN 'grand'
            ELSE 'très grand' 
            END AS profit
FROM factInternetSale
WHERE UnitPrice*(1-UnitPriceDiscountPct) > ProductStandardCost ;

Question 9.5

Le total des ventes dans l'exemple ci-dessus est surévalué, il ne tient pas compte des différents rabais attribués (UnitPriceDiscountPct). Recalcule le chiffre d'affaire total en prenant en compte les rabais. On garde 2 chiffres après la virgule. Pour accéder à la console SQL.

Fonction d'agregation

Les fonctions d'agrégation, également appelées "aggregate functions," permettent de calculer une valeur unique à partir d'un ensemble de lignes. Contrairement aux fonctions de ligne, elles résument les données, par exemple en calculant une moyenne AVG( ), une somme SUM( ), un maximum MAX( ) ou encore un décompte COUNT( ).

SUM( )

L'utilisation de la fonction SUM( ) sur les colonnes productStandardCost et orderQuantity, renvoie la somme des coûts de tous les produits vendus. Les décimales sont aussi affichées.

/* Affiche le total des coûts des produits vendus pour l'année 2011 */
SELECT SUM(orderQuantity*productStandardCost) as orderProductCost
FROM factInternetSale
WHERE OrderDateKey LIKE '2011%' ;

Question 9.6

Que fait cette requête? Pour accéder à la console SQL.

SELECT ROUND(SUM(OrderQuantity*ProductStandardCost),0) as orderProductCost
FROM factInternetSale
WHERE YEAR(OrderDate) = 2013 ;

Question 9.7

Comment afficher le chiffre d'affaire total sur le premier semestre 2012. Le chiffre d'affaire total sera renommé salesAmount, il doit être affiché sans chiffre après la virgule. Pour accéder à la console SQL.

MIN( ), MAX( ), AVG( )

Une même colonne peut être utilisée avec différentes statistiques. Si on veut connaitre le coût minimal, maximal et moyen on écrira:

-- Affiche la commande avec le coût minimal, maximal et moyen des produits vendus pour l'année 2011.
SELECT MIN(OrderQuantity*ProductStandardCost) as minOrderProductCost,
       MAX(OrderQuantity*ProductStandardCost) as maxOrderProductCost,
       AVG(OrderQuantity*ProductStandardCost) as avgOrderProductCost
FROM factInternetSale
WHERE OrderDateKey LIKE '2011%' ;

Attention : Lorsque des fonctions d'agrégation sont utilisées, on ne peut pas afficher des colonnes sans fonction d'agrégation sinon une erreur se produit. Pour palier à ce problème, il faut utiliser la clause GROUP BY qui sera vue par la suite.

Question 9.8

Affiche la marge totale réalisée sur l'ensemble des commandes du mois de juillet 2011. Le résultat doit être affiché avec uniquement 2 chiffres après la virgule. Pour accéder à la console SQL.

COUNT( )

La fonction COUNT( ) est souvent utilisé en SQL, elle permet de compter le nombre de lignes pour lesquelles la colonne spécifiée a une valeur non nulle. Illustrons l'utilisation de la fonction COUNT( ) avec la table prospects.

SELECT COUNT(telephone) 
FROM prospects ;

SELECT COUNT(*) 
FROM prospects ;

Question 9.9

Identifier la bonne table à sélectionner pour compter le nombre de produits dans la base de données adventureWork? Pour accéder à la console SQL.

A présent, si tu souhaites savoir combien de valeurs uniques existent dans une colonne, il faut utiliser COUNT( DISTINCT nom_colonne ). Par exemple, si tu souhaites savoir de combien de villes différentes proviennent tes prospects :

SELECT COUNT(DISTINCT VILLE) 
FROM prospects ;

Le résultat renvoyé sera 2. Chaque ville ne sera comptée qu'une seule fois, même si Paris et Marseille apparaissent plusieurs fois dans la table prospects.

Question 9.10

Compter le nombre de clients (uniques) ayant effectué un achat au cours de l'année 2011? Pour accéder à la console SQL.

En résumé

1. Pour compter le nombre total de lignes d'une table, COUNT( * ) est le bon choix.
2. Pour compter le nombre de lignes non nulles dans une certaine colonne alors utilise COUNT( nom_colonne ).
3. Pour connaitre le nombre valeurs uniques dans une colonne alors utilise COUNT( DISTINCT nom_colonne ).

Fonction de Fenêtre

Les fonctions de fenêtre, également appelées "Window Functions," permettent d'effectuer des calculs sur un ensemble de lignes tout en conservant le détail de chaque ligne. Par exemple, elles peuvent attribuer un classement RANK( ), calculer des totaux cumulés SUM( ) avec une fenêtre ou des moyennes mobiles AVG( ). Ces fonctions sont particulièrement puissantes pour analyser les données sans les regrouper, contrairement aux fonctions d'agrégation classiques. L'exemple classique est l'ajout d'une colonne attribuant un classement en fonction d'une colonne spécifique.

Imaginons que dans une table fictive nommée personnel, on veuille attribuer un classement des employé par salaires dans chaque service. Les colonnes à disposition sont id, nom, salaire et service.

La fonction de fenêtre va permettre d'ajouter la colonne rangSalaireService. Les lignes ne seront plus classées en fonction de l'identifiant mais en fonction du rang au sein de leur service respectif :

Voici comment écrire la fonction de fenêtre de notre exemple :

SELECT id, nom, salaire
  ROW_NUMBER( ) OVER (PARTITION BY service ORDER BY salaire DESC) AS rangSalaireService
FROM personnel ;

La fonction ROW_NUMBER( ) attribue un numéro de ligne basé sur l'ordre décroissant des salaires par service. De manière plus générale, la syntaxe d'une fonction de fenêtre est la suivante:

SELECT colonne1, colonne2,
  fonction_fenetre(colonne3) OVER (PARTITION BY colonne_de_partition ORDER BY colonne_ordre)
FROM la_table ;

/*
Affiche le rang correspondant à l'ordre dans lequel 
l'article commandé est affiché pour chaque client.
*/
SELECT customerKey, orderDate,  
        salesOrderNumber, UnitPrice, 
        ROW_NUMBER() OVER (PARTITION BY customerKey ORDER BY orderDate) AS num_order
FROM factInternetSale
LIMIT 10 ;

Ici la fonction ROW_NUMBER( ) est utilisée. Cependant si 2 lignes ayant la même valeur en critère de tri n'auront pas le même rang. Pour y remédier, il existe la fonction DENSE_RANK( ). Cette fonction attribue le même rang aux lignes partageant des valeurs égales pour le critère de tri. En d'autres termes, les commandes sur plusieurs lignes ou celles effectuées le même jour partageront le même rang.

SELECT customerKey, orderDate,  
        salesOrderNumber, UnitPrice, 
        DENSE_RANK() OVER (PARTITION BY customerKey ORDER BY orderDate) AS num_order
FROM factInternetSale
LIMIT 10 ;

Les lignes ayant la même date de commande auront le même rang.

SELECT customerKey, orderDate, ProductKey,  
       salesOrderNumber, UnitPrice, 
       ROUND(UnitPrice*OrderQuantity*(1-UnitPriceDiscountPct),2) as salesAmount,
       SUM(ROUND(UnitPrice*OrderQuantity*(1-UnitPriceDiscountPct),2)) OVER (PARTITION BY customerKey ORDER BY orderDate, ProductKey ) AS cumulOrderAmount
FROM factInternetSale
LIMIT 10 ;

Affiche le montant cumulé de tous les articles achetés pour chaque client au cours du temps.

Question 9.11

Dans la table factInternetSale, ajouter une colonne qui représente le classement de chaque article au sein d'une commande d'un client. Le classement se fera en commençant par l'objet au prix unitaire le plus élevé.
Pour accéder à la console SQL.

Filtrer les Résultats d'une Fonction de Fenêtre

Les fonctions de fenêtre ne peuvent pas être utilisées ailleurs que dans la clause SELECT alors comment filtrer les résultats? Soit il faut utiliser une CTE ou une sous-requête (des notions qui seront vues plus tard), soit en intégrant le mot-clef QUALIFY. Attention : QUALIFY n'est pas utilisable dans tous les SGBD. On peut cependant l'utiliser dans DuckDB, Teradata ou BigQuery.

En utilisant QUALIFY après la clause FROM, il y a la possibilité de spécifier des conditions précises sur les résultats générés par la fonction de fenêtre, offrant ainsi un contrôle plus granulaire sur les données extraites. Cela permet d'affiner les résultats en fonction de critères spécifiques, contribuant ainsi à une analyse plus précise et ciblée des données.

/* Affiche les 2 premières commandes de chaque client. */
SELECT customerKey, orderDate, dueDate,  
        salesOrderNumber, UnitPrice, 
        DENSE_RANK() OVER (PARTITION BY customerKey ORDER BY orderDate) AS num_order
FROM factInternetSale
QUALIFY num_order <= 2 ;

/* Affiche également les 2 premières commandes de chaque client
   mais l'écriture est plus directe. */
SELECT customerKey, orderDate, dueDate,  
        salesOrderNumber, UnitPrice 
FROM factInternetSale
QUALIFY DENSE_RANK() OVER (PARTITION BY customerKey ORDER BY orderDate) <= 2 ;

Ci-dessous, un tableau reprennant les usages des principales fonctions de fenêtre.

Question 9.12

Dans la table factInternetSale, afficher les 5 commandes au prix unitaire les plus élevées pour chaque mois de l'année 2014 (ordonner par date).

SQL Console

Question 9.1
Question 9.2
Question 9.3
Question 9.4
Question 9.5
Question 9.6
Question 9.7
Question 9.8
Question 9.9
Question 9.10
Question 9.11