Capire la funzione SQL DECODE()

Come usare la funzione SQL DECODE()

La funzione SQL DECODE() è supportata nativamente nel database Oracle ed è disponibile nel dialetto PL/SQL. La funzione DECODE() consente di implementare logica condizionale all'interno della query. Questa tecnica è importante perché aiuta nella trasformazione dei dati durante il recupero dei record dal database.

Considera la tabella employees qui sotto, in cui department_id ha valori numerici. Invece, vogliamo che department_id si chiami department_name e abbia categorie più comprensibili.

Tabella di esempio da trasformare usando la funzione SQL DECODE(). Immagine dell'autore.

L'esempio seguente mostra come usare la funzione DECODE() per trasformare department_id nei nomi reali.

-- 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;Offerto da

Tabella di output di esempio dopo l'uso della funzione SQL DECODE(). Immagine dell'autore.

Capire la funzione SQL DECODE()

La funzione DECODE() è disponibile nel database Oracle e ti permette di trasformare una colonna usando logica condizionale. La funzione DECODE() consente più trasformazioni condizionali con una sola query semplice.

La sintassi della funzione DECODE() è la seguente:

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

Dove:

• expression: Il valore da confrontare.

• search: Il valore con cui confrontare l'espressione.

• result: Il valore restituito se l'espressione corrisponde al valore di ricerca.

• default: Il valore restituito se non viene trovata alcuna corrispondenza (opzionale).

Tecniche avanzate e considerazioni con la funzione SQL DECODE()

Alcune operazioni SQL avanzate possono essere eseguite usando la funzione DECODE(). Le tecniche avanzate consentono trasformazioni logiche condizionali più complesse. 

DECODE() con funzioni di aggregazione

La funzione DECODE() in Oracle può essere usata nella clausola SELECT per ricategorizzare una variabile, e può anche essere usata nella clausola SELECT insieme a funzioni di aggregazione per un raggruppamento più complesso, migliorando ulteriormente la flessibilità e l'interpretazione dei risultati. 

Nella query seguente, usiamo DECODE() per trasformare department_id in department_name. Usiamo anche DECODE() per categorizzare lo stipendio medio in "High Paying" o "Low Paying".

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; Offerto da

Esempio di output della tabella dopo l'uso di più funzioni SQL DECODE(). Immagine dell'autore

Istruzioni DECODE() annidate

Implementare la funzione DECODE() con subquery ti permette di effettuare trasformazioni di logica condizionale sofisticate sui dati. L'istruzione DECODE() annidata in Oracle è utile quando vogliamo valutare più condizioni in una tabella.

Nell'esempio seguente, abbiamo usato l'istruzione DECODE() annidata per categorizzare i dipendenti in base al loro reparto e stipendio.

-- 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;Offerto da

Esempio di output della tabella dopo l'uso della funzione DECODE() annidata. Immagine dell'autore

Come DECODE() gestisce i valori NULL

La funzione DECODE() gestisce anche i valori NULL in due modi diversi. Per prima cosa, DECODE() si comporta come se due valori NULL fossero equivalenti. Questa funzionalità significa che i valori NULL sono considerati una corrispondenza quando si confrontano i valori della colonna con NULL. Tuttavia, fai attenzione a risultati inattesi quando il NULL non è gestito in modo esplicito, perché potrebbe mascherare i valori NULL durante la trasformazione.

Inoltre, sappi che, proprio come l'istruzione CASE WHEN, la funzione DECODE() restituisce un valore NULL se non viene trovata alcuna corrispondenza.

L'esempio seguente mostra come la funzione DECODE() restituisce valori null dove mancano department_id e salary.

-- 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;Offerto da

Esempio di output della tabella dopo aver usato DECODE() per gestire i valori NULL. Immagine dell'autore

DECODE vs. CASE WHEN in Oracle

La funzione DECODE() è stata introdotta per prima e, anche se è stata superata dall'istruzione CASE WHEN, è ancora preferita in alcuni contesti. Ad esempio, la sintassi della funzione DECODE() è più semplice, quindi se la logica è piuttosto semplice è più facile da leggere e comprendere. 

Inoltre, la funzione DECODE() di solito è più veloce poiché le sue prestazioni sono state ottimizzate come funzione integrata, anche se la differenza potrebbe essere trascurabile. Infine, nei sistemi Oracle legacy potresti dover usare la funzione DECODE() come funzione integrata. 

Alternative a DECODE() in altri database

La funzione DECODE() è supportata solo nel database Oracle. Tuttavia, SQL Server, PostgreSQL e MySQL forniscono metodi alternativi per applicare logica condizionale di trasformazione dei dati.

La funzione DECODE() è utile nella trasformazione dei dati perché semplifica la logica complessa eliminando l'uso di istruzioni IF ELSE o CASE. La funzione DECODE() migliora anche la leggibilità delle query, consentendo una manipolazione efficiente dei dati. Vediamo i diversi casi d'uso della funzione DECODE().

Uso di CASE WHEN in SQL Server, PostgreSQL e MySQL

L'istruzione CASE WHEN offre un'alternativa alla funzione DECODE() per eseguire la logica condizionale in SQL. La sintassi e l'implementazione di CASE WHEN sono coerenti tra i database SQL Server, PostgreSQL e MySQL.

L'esempio seguente mostra come usare l'istruzione CASE WHEN per trasformare i dati all'interno della clausola SELECT. La query mostra anche come combinare l'istruzione CASE WHEN con altre funzioni SQL per trasformazioni condizionali complesse.

 -- 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   Offerto da

Conclusione e approfondimenti

Capire la funzione DECODE() di Oracle è importante per imparare a trasformare i dati in modo efficace. La funzione DECODE() offre vari casi d'uso per applicare logica condizionale durante la trasformazione dei dati. È anche importante imparare alternative di trasformazione condizionale nei database SQL Server, PostgreSQL e MySQL. Ti incoraggio a esercitarti con la funzione DECODE() usando diversi dataset e casi d'uso per perfezionare le tue competenze di trasformazione dei dati.

Se vuoi far progredire le tue competenze di analisi dei dati, ti consiglio il corso di DataCamp Reporting in SQL per sviluppare le tue capacità di analisi e presentazione. Allo stesso modo, dai un'occhiata al nostro career track Associate Data Analyst in SQL per aiutarti a tenere il passo con l'SQL necessario nella tua carriera come data analyst. Infine, ti consiglio vivamente il progetto di DataCamp Data-Driven Decision Making in SQL per mostrare la tua padronanza nell'uso di diverse tecniche SQL per analisi e reporting, così da distinguerti tra gli altri data analyst.