Comprendre la fonction SQL DECODE()

Comment utiliser la fonction SQL DECODE()

La fonction SQL DECODE() est prise en charge de manière native dans la base de données Oracle et est disponible dans le dialecte PL/SQL. La fonction DECODE() permet de mettre en œuvre une logique conditionnelle dans la requête. Cette technique est importante car elle permet de transformer les données lors de l'extraction d'enregistrements de la base de données.

Considérez le tableau employees ci-dessous, dans lequel department_id a des valeurs numériques. Nous souhaitons plutôt que department_id soit appelé department_name et que ses catégories soient plus compréhensibles.

Exemple de tableau à transformer à l'aide de la fonction SQL DECODE(). Image par l'auteur.

L'exemple ci-dessous montre comment utiliser la fonction DECODE() pour transformer les department_id en noms réels.

-- Using DECODE to transform department_id to department_name
SELECT 
    employee_id,
    first_name,
    last_name,
    DECODE(department_id, 
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown') AS department_name,
    salary,
    hire_date,
    city
FROM employees;

Exemple de tableau de sortie après utilisation de la fonction SQL DECODE(). Image par l'auteur.

Comprendre la fonction SQL DECODE()

La fonction DECODE() est disponible dans la base de données Oracle et vous permet de transformer une colonne à l'aide d'une logique conditionnelle. La fonction DECODE() permet d'effectuer plusieurs transformations conditionnelles à l'aide d'une simple requête.

La syntaxe de la fonction DECODE() est la suivante :

DECODE(expression, search1, result1, search2, result2, ..., default)

Où ?

• expression: La valeur à comparer.

• search: La valeur à comparer à l'expression.

• result: Valeur renvoyée si l'expression correspond à la valeur recherchée.

• default: La valeur renvoyée si aucune correspondance n'est trouvée (facultatif).

Techniques avancées et considérations sur la fonction SQL DECODE()

Certaines opérations SQL avancées peuvent être effectuées à l'aide de la fonction DECODE(). Les techniques avancées permettent une transformation plus complexe de la logique conditionnelle. 

DECODE() avec des fonctions agrégées

La fonction DECODE() d'Oracle peut être utilisée dans l'instruction SELECT pour recatégoriser une variable, et elle peut également être utilisée dans l'instruction SELECT avec des fonctions d'agrégation pour un regroupement plus complexe, ce qui améliore encore la flexibilité et l'interprétation des résultats. 

Dans la requête suivante, nous utilisons DECODE() pour transformer department_id en department_name. Nous utilisons également le site DECODE() pour classer les salaires moyens en deux catégories : "hauts salaires" et "bas salaires".

SELECT 
    department_id,
    
    -- Use DECODE to transform department_id into department_name
    DECODE(
        department_id,
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown'
    ) AS department_name,
    
    SUM(salary) AS total_salary, 
    
    -- Calculate the average salary for each department and round to nearest integer
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use DECODE to categorize average salary into 'High Paying' or 'Low Paying'
    DECODE(
        SIGN(AVG(salary) - 65000), 
        1, 'High Paying', 
        0, 'High Paying', 
        -1, 'Low Paying' 
    ) AS salary_category
FROM 
    employees
GROUP BY 
    department_id; 

Exemple de sortie de tableau après l'utilisation de plusieurs fonctions SQL DECODE(). Image par l'auteur

Instructions DECODE() imbriquées

La mise en œuvre de la fonction DECODE() avec des sous-requêtes vous permet d'effectuer des transformations logiques conditionnelles sophistiquées sur les données. L'instruction imbriquée DECODE() dans Oracle est utile lorsque nous voulons évaluer plusieurs conditions dans un tableau.

Dans l'exemple ci-dessous, nous avons utilisé l'instruction imbriquée DECODE() pour classer les employés en fonction de leur département et de leur salaire.

-- Select the columns
SELECT 
    employee_id,
    first_name, 
    last_name, 
    department_id,
    salary, 
    
    -- Use DECODE to categorize salary based on department and salary thresholds
    DECODE(department_id,
        
        1, DECODE( -- If department_id is 1 (IT)
            CASE 
                WHEN salary > 65000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'IT High Salary', 
            'Low', 'IT Low Salary' 
        ),
        
        2, DECODE( -- If department_id is 2 (HR)
            CASE 
                WHEN salary > 55000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'HR High Salary', 
            'Low', 'HR Low Salary' 
        ),
        
        3, DECODE( -- If department_id is 3 (Finance)
            CASE 
                WHEN salary > 70000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'Finance High Salary', 
            'Low', 'Finance Low Salary' 
        ),
        
        4, DECODE( -- If department_id is 4 (Marketing)
            CASE 
                WHEN salary > 68000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'Marketing High Salary', 
            'Low', 'Marketing Low Salary' 
        ),
        
        5, DECODE( -- If department_id is 5 (Sales)
            CASE 
                WHEN salary > 60000 THEN 'High' 
                ELSE 'Low'  
            END, 
            'High', 'Sales High Salary', 
            'Low', 'Sales Low Salary'
        ),
        
        'Unknown Department' -- Default value if department_id does not match any case
    ) AS salary_category
FROM 
    employees;

Exemple de sortie de tableau après utilisation de la fonction imbriquée DECODE(). Image par l'auteur

Comment DECODE() gère les valeurs NULL

La fonction DECODE() traite également les valeurs NULL de deux manières différentes. D'une part, DECODE() fait comme si deux valeurs NULL étaient équivalentes. Cette fonctionnalité signifie que les valeurs de NULL sont considérées comme correspondantes lorsque l'on compare les valeurs de la colonne à NULL. Toutefois, vous devez être attentif aux résultats inattendus lorsque le site NULL n'est pas explicitement pris en compte, car il peut masquer les valeurs du site NULL lors de la transformation.

Vous devez également savoir que, tout comme l'instruction CASE WHEN, la fonction DECODE() renvoie une valeur NULL si aucune correspondance n'est trouvée.

L'exemple ci-dessous montre comment la fonction DECODE() renvoie des valeurs nulles lorsque department_id et salary sont manquants.

-- Select columns
SELECT 
    employee_id,
    first_name,
    last_name, 
    department_id, 
    salary, 
    -- Determine the salary category based on the salary value
    DECODE(salary, 
        NULL, 'No Salary Information',
        60000, 'Standard Salary',
        'Other Salary') AS salary_category, 
    -- Determine the department name based on the department ID
    DECODE(department_id, 
        1, 'IT',
        2, 'HR',
        3, 'Finance',
        4, 'Marketing',
        5, 'Sales') AS department_name 
        -- If department_id does not match any of the above, return NULL
FROM 
    employees;

Exemple de sortie de tableau après utilisation de DECODE() pour gérer les valeurs NULL. Image par l'auteur

DECODE vs. CASE WHEN dans Oracle

La fonction DECODE() a été la première à être introduite, et même si elle a été remplacée par la déclaration CASE WHEN, elle est encore préférée dans certains contextes. Par exemple, la syntaxe de la fonction DECODE() est plus simple, donc si la logique est relativement simple, elle est plus facile à lire et à comprendre. 

En outre, la fonction DECODE() est généralement plus rapide car ses performances ont été optimisées en tant que fonction intégrée, bien que la différence puisse être négligeable. Enfin, dans les anciens systèmes Oracle, il se peut que vous deviez utiliser la fonction DECODE() comme fonction intégrée. 

Alternatives à DECODE() dans d'autres bases de données

La fonction DECODE() n'est prise en charge que dans la base de données Oracle. Cependant, SQL Server, PostgreSQL et MySQL proposent d'autres méthodes pour appliquer la logique de transformation conditionnelle des données.

La fonction DECODE() est utile dans la transformation des données car elle simplifie la logique complexe en éliminant l'utilisation des instructions IF ELSE ou CASE. La fonction DECODE() améliore également la lisibilité des requêtes et permet une manipulation efficace des données. Examinons les différents cas d'utilisation de la fonction DECODE().

Utilisation de CASE WHEN dans SQL Server, PostgreSQL et MySQL

L'instruction CASE WHEN offre une alternative à la fonction DECODE() pour effectuer la transformation logique conditionnelle de SQL. La syntaxe et l'implémentation de CASE WHEN sont cohérentes entre les bases de données SQL Server, PostgreSQL et MySQL.

L'exemple ci-dessous montre comment utiliser l'instruction CASE WHEN pour transformer des données dans l'instruction SELECT. La requête montre également comment combiner l'instruction CASE WHEN avec d'autres fonctions SQL pour une transformation conditionnelle complexe.

 -- Select department ID
SELECT 
    department_id,
    
    -- Use CASE to transform department_id into department_name
    CASE 
        WHEN department_id = 1 THEN 'IT'
        WHEN department_id = 2 THEN 'HR'
        WHEN department_id = 3 THEN 'Finance'
        WHEN department_id = 4 THEN 'Marketing'
        WHEN department_id = 5 THEN 'Sales'
        ELSE 'Unknown' 
    END AS department_name,
    
    SUM(salary) AS total_salary,
    
    -- Calculate the average salary for each department and round to the nearest whole number
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use CASE to categorize average salary into 'High Paying' or 'Low Paying'
    CASE 
        WHEN AVG(salary) > 65000 THEN 'High Paying'
        ELSE 'Low Paying' 
    END AS salary_category
FROM 
    employees
GROUP BY 
    department_id; -- Group results by department ID   

Conclusion et formation complémentaire

Il est important de comprendre la fonction Oracle DECODE() pour apprendre à transformer efficacement les données. La fonction DECODE() offre plusieurs cas d'utilisation pour l'application d'une logique conditionnelle pendant la transformation des données. Il est également important que vous appreniez la transformation conditionnelle alternative dans les bases de données SQL Server, PostgreSQL et MySQL. Je vous encourage à pratiquer la fonction DECODE() en utilisant différents ensembles de données et cas d'utilisation pour perfectionner vos compétences en matière de transformation des données.

Si vous souhaitez améliorer vos compétences en matière d'analyse de données, je vous recommande de suivre la formation Reporting in SQL de DataCamp pour renforcer vos compétences en matière d'analyse et de présentation. De même, vous devriez consulter notre parcours Associate Data Analyst in SQL pour vous aider à maintenir la progression du SQL nécessaire dans votre carrière d'analyste de données. Enfin, je vous recommande vivement de suivre le projet Data-Driven Decision Making in SQL de DataCamp pour démontrer votre maîtrise de l'utilisation de différentes techniques SQL pour l'analyse et le reporting afin de vous démarquer parmi les autres analystes de données.