Entendendo a função SQL DECODE()

Entender como implementar a lógica condicional diretamente nas consultas SQL é uma habilidade necessária para analistas de dados, cientistas e engenheiros. Este tutorial fornece um guia abrangente para você usar a função SQL DECODE() no Oracle. Também compararei DECODE() com CASE WHEN no Oracle e ajudarei você a entender quando usar cada função. Por fim, fornecerei o código para as transformações equivalentes no SQL Server, PostgreSQL e MySQL. 

Antes de vermos os exemplos práticos, recomendo que você faça o curso Introduction to Oracle SQL da DataCamp para atualizar suas habilidades no uso do dialeto PL/SQL. Além disso, nosso curso de SQL intermediário equipará você com as habilidades avançadas de SQL necessárias ao lidar com consultas com a função DECODE() porque, como veremos, DECODE() é frequentemente usado com sintaxe mais avançada, como subconsultas.

Como usar a função SQL DECODE()

A função SQL DECODE() tem suporte nativo no banco de dados Oracle e está disponível no dialeto PL/SQL. A função DECODE() permite a implementação de lógica condicional na consulta. Essa técnica é importante porque ajuda na transformação de dados ao recuperar registros do banco de dados.

Considere a tabela employees abaixo, onde o department_id tem valores numéricos. Em vez disso, queremos que o site department_id seja chamado de department_name e tenha categorias mais compreensíveis.

Exemplo de tabela para você transformar usando a função SQL DECODE(). Imagem do autor.

O exemplo abaixo mostra como você pode usar a função DECODE() para transformar o department_id em nomes reais.

-- 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;Tecnologia

Exemplo de tabela de saída após você usar a função SQL DECODE(). Imagem do autor.

Entendendo a função SQL DECODE()

A função DECODE() está disponível no banco de dados Oracle e permite que você transforme uma coluna usando lógica condicional. A função DECODE() permite várias transformações condicionais usando uma consulta simples.

A sintaxe da função DECODE() é mostrada abaixo:

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

Onde:

• expression: O valor a ser comparado.

• search: O valor a ser comparado com a expressão.

• result: O valor retornado se a expressão corresponder ao valor da pesquisa.

• default: O valor retornado se nenhuma correspondência for encontrada (opcional).

Técnicas e considerações avançadas com a função SQL DECODE()

Algumas operações SQL avançadas podem ser realizadas com a função DECODE(). As técnicas avançadas permitem uma transformação lógica condicional mais complexa. 

DECODE() com funções agregadas

A função DECODE() no Oracle pode ser usada na instrução SELECT para recategorizar uma variável e também pode ser usada na instrução SELECT com funções agregadas para um agrupamento mais complicado, o que melhora ainda mais a flexibilidade e a interpretação dos resultados. 

Na consulta a seguir, usamos DECODE() para transformar department_id em department_name. Também usamos o site DECODE() para categorizar o salário médio em "Alta remuneração" ou "Baixa remuneração".

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; Tecnologia

Exemplo de saída de tabela após o uso de várias funções SQL DECODE(). Imagem do autor

Instruções DECODE() aninhadas

A implementação da função DECODE() com subconsultas permite que você realize transformações sofisticadas de lógica condicional nos dados. A instrução DECODE() aninhada no Oracle ajuda quando você deseja avaliar várias condições em uma tabela.

No exemplo abaixo, usamos a instrução DECODE() aninhada para categorizar os funcionários com base em seu departamento e salário.

-- 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;Tecnologia

Exemplo de saída de tabela após você usar a função DECODE() aninhada. Imagem do autor

Como DECODE() lida com valores NULL

A função DECODE() também trata os valores de NULL de duas maneiras diferentes. Por um lado, DECODE() age como se dois valores NULL fossem equivalentes. Essa funcionalidade significa que os valores de NULL são considerados uma correspondência quando você compara os valores da coluna com NULL. Ainda assim, você deve ficar atento a resultados inesperados quando o NULL não for explicitamente considerado, pois ele pode mascarar os valores do NULL durante a transformação.

Além disso, você deve saber que, assim como a instrução CASE WHEN, a função DECODE() retorna um valor NULL se nenhuma correspondência for encontrada.

O exemplo abaixo mostra como a função DECODE() retorna valores nulos quando department_id e salary estão ausentes.

-- 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;Tecnologia

Exemplo de saída de tabela após você usar DECODE() para lidar com valores NULL. Imagem do autor

DECODE vs. CASE WHEN no Oracle

A função DECODE() foi a primeira a ser introduzida e, embora tenha sido substituída pela declaração CASE WHEN, ela ainda é preferida em alguns contextos. Por exemplo, a sintaxe da função DECODE() é mais simples, portanto, se a lógica for bastante simples, será mais fácil de ler e entender. 

Além disso, a função DECODE() geralmente é mais rápida, pois seu desempenho foi otimizado como uma função incorporada, embora a diferença possa ser insignificante. Por fim, em sistemas Oracle legados, talvez você precise usar a função DECODE() como função interna. 

Alternativas ao DECODE() em outros bancos de dados

A função DECODE() só é compatível com o banco de dados Oracle. No entanto, o SQL Server, o PostgreSQL e o MySQL oferecem métodos alternativos para aplicar a lógica de transformação de dados condicionais.

A função DECODE() é útil na transformação de dados, pois simplifica a lógica complexa ao eliminar o uso de instruções IF ELSE ou CASE. A função DECODE() também melhora a legibilidade da consulta, permitindo a manipulação eficiente dos dados. Vamos examinar os diferentes casos de uso da função DECODE().

Usando CASE WHEN no SQL Server, PostgreSQL e MySQL

A instrução CASE WHEN oferece uma alternativa à função DECODE() para executar a transformação da lógica condicional do SQL. A sintaxe e a implementação do CASE WHEN são consistentes nos bancos de dados SQL Server, PostgreSQL e MySQL.

O exemplo abaixo mostra como você pode usar a instrução CASE WHEN para transformar dados na instrução SELECT. A consulta também mostra como você pode combinar a instrução CASE WHEN com outras funções SQL para obter uma transformação condicional complexa.

 -- 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   Tecnologia

Conclusão e aprendizado adicional

Entender a função Oracle DECODE() é importante para que você aprenda a transformar dados de forma eficaz. A função DECODE() oferece vários casos de uso para aplicação de lógica condicional durante a transformação de dados. Também é importante que você aprenda a transformação condicional alternativa nos bancos de dados SQL Server, PostgreSQL e MySQL. Recomendo que você pratique a função DECODE() usando diferentes conjuntos de dados e casos de uso para aperfeiçoar suas habilidades de transformação de dados.

Se você deseja aprimorar suas habilidades de análise de dados, recomendo que faça o curso Reporting in SQL da DataCamp para desenvolver suas habilidades de análise e apresentação. Da mesma forma, você deve conferir nosso programa de carreira Associate Data Analyst in SQL para ajudá-lo a manter o progresso do SQL necessário em sua carreira como analista de dados. Por fim, recomendo enfaticamente que você participe do projeto DataCamp's Data-Driven Decision Making in SQL para demonstrar que domina o uso de diferentes técnicas de SQL para análise e geração de relatórios e se destacar entre outros analistas de dados.