Éviter les fonctions coûteuses dans CASE

-- Version non-optimisée
SELECT 
    customer_id,
    CASE 
        WHEN UPPER(TRIM(customer_name)) LIKE '%ENTERPRISE%' THEN 'B2B'
        WHEN LENGTH(customer_name) > 50 THEN 'Long Name'
        ELSE 'Standard'
    END AS customer_type
FROM customers;

-- Version optimisée avec colonnes précalculées
ALTER TABLE customers ADD COLUMN name_normalized VARCHAR(100);
ALTER TABLE customers ADD COLUMN name_length INT;

UPDATE customers 
SET name_normalized = UPPER(TRIM(customer_name)),
    name_length = LENGTH(customer_name);

CREATE INDEX idx_name_normalized ON customers(name_normalized);
CREATE INDEX idx_name_length ON customers(name_length);

SELECT 
    customer_id,
    CASE 
        WHEN name_normalized LIKE '%ENTERPRISE%' THEN 'B2B'
        WHEN name_length > 50 THEN 'Long Name'
        ELSE 'Standard'
    END AS customer_type
FROM customers;

CASE WHEN avec fonctions d'agrégation

La combinaison de CASE WHEN avec les fonctions d'agrégation permet de créer des analyses pivot sophistiquées sans utiliser la syntaxe PIVOT, plus complexe et moins portable.

Analyse des ventes par période et segment

SELECT 
    product_category,
    COUNT(*) as total_orders,
    SUM(CASE WHEN QUARTER(order_date) = 1 THEN order_amount ELSE 0 END) AS q1_revenue,
    SUM(CASE WHEN QUARTER(order_date) = 2 THEN order_amount ELSE 0 END) AS q2_revenue,
    SUM(CASE WHEN QUARTER(order_date) = 3 THEN order_amount ELSE 0 END) AS q3_revenue,
    SUM(CASE WHEN QUARTER(order_date) = 4 THEN order_amount ELSE 0 END) AS q4_revenue,
    COUNT(CASE WHEN customer_tier = 'Premium' THEN 1 END) AS premium_orders,
    COUNT(CASE WHEN customer_tier = 'Standard' THEN 1 END) AS standard_orders,
    AVG(CASE WHEN order_amount > 1000 THEN order_amount END) AS avg_high_value_order,
    STDDEV(CASE WHEN order_amount BETWEEN 100 AND 1000 THEN order_amount END) AS mid_range_volatility
FROM orders 
WHERE order_date >= '2026-01-01'
GROUP BY product_category
HAVING COUNT(*) > 100;

Cette requête produit un tableau de bord complet avec une seule passe sur les données, évitant les jointures coûteuses de multiples sous-requêtes.

Calculs de taux de conversion complexes

WITH funnel_analysis AS (
    SELECT 
        traffic_source,
        COUNT(*) as total_visitors,
        COUNT(CASE WHEN page_views >= 2 THEN 1 END) as engaged_visitors,
        COUNT(CASE WHEN time_on_site >= 120 THEN 1 END) as qualified_visitors,
        COUNT(CASE WHEN cart_created = 1 THEN 1 END) as cart_creators,
        COUNT(CASE WHEN purchase_completed = 1 THEN 1 END) as purchasers,
        SUM(CASE WHEN purchase_completed = 1 THEN purchase_amount ELSE 0 END) as total_revenue
    FROM user_sessions 
    WHERE session_date >= '2026-04-01'
    GROUP BY traffic_source
)
SELECT 
    traffic_source,
    total_visitors,
    ROUND(100.0 * engaged_visitors / total_visitors, 2) as engagement_rate,
    ROUND(100.0 * qualified_visitors / total_visitors, 2) as qualification_rate,
    ROUND(100.0 * cart_creators / qualified_visitors, 2) as cart_conversion_rate,
    ROUND(100.0 * purchasers / cart_creators, 2) as purchase_conversion_rate,
    ROUND(total_revenue / purchasers, 2) as avg_order_value,
    CASE 
        WHEN purchasers / total_visitors >= 0.05 THEN 'High Converting'
        WHEN purchasers / total_visitors >= 0.02 THEN 'Medium Converting'
        ELSE 'Low Converting'
    END AS source_quality
FROM funnel_analysis
ORDER BY total_revenue DESC;

Gestion des valeurs NULL et cas limites

La gestion des valeurs NULL avec CASE WHEN nécessite une attention particulière pour éviter des résultats inattendus dans vos analyses.

SELECT 
    customer_id,
    last_purchase_date,
    total_spent,
    CASE 
        WHEN last_purchase_date IS NULL THEN 'Never Purchased'
        WHEN total_spent IS NULL OR total_spent = 0 THEN 'No Value Customer'
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) > 365 
             AND total_spent < 100 THEN 'Inactive Low Value'
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) > 365 
             AND total_spent >= 100 THEN 'Inactive High Value - Reactivation Target'
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 30 
             AND total_spent >= 1000 THEN 'VIP Active'
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 90 THEN 'Active Customer'
        ELSE 'At Risk Customer'
    END AS customer_status,
    CASE 
        WHEN total_spent IS NULL THEN 0
        WHEN total_spent > 0 THEN 
            CASE 
                WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) IS NULL THEN total_spent
                ELSE total_spent / GREATEST(DATEDIFF(CURRENT_DATE, last_purchase_date), 1)
            END
        ELSE 0
    END AS daily_value_score
FROM customers;

Cette approche garantit que tous les cas edge sont gérés explicitement, évitant les surprises en production.

CASE WHEN dans les jointures conditionnelles

Une technique avancée consiste à utiliser CASE WHEN directement dans les conditions de jointure pour créer des logiques d'association dynamiques.

SELECT 
    o.order_id,
    o.customer_id,
    o.order_amount,
    d.discount_percentage,
    p.promotion_name
FROM orders o
LEFT JOIN discounts d ON o.customer_id = d.customer_id
    AND CASE 
        WHEN o.order_amount >= 1000 THEN d.tier = 'premium'
        WHEN o.order_amount >= 500 THEN d.tier IN ('premium', 'standard')
        ELSE d.tier IN ('premium', 'standard', 'basic')
    END
LEFT JOIN promotions p ON o.product_category = p.category
    AND o.order_date BETWEEN p.start_date AND p.end_date
    AND CASE 
        WHEN o.customer_tier = 'VIP' THEN p.promotion_type != 'new_customer'
        WHEN o.is_new_customer = 1 THEN p.promotion_type = 'new_customer'
        ELSE p.promotion_type = 'general'
    END
WHERE o.order_date >= '2026-04-01';

Cette technique permet de créer des jointures intelligentes qui s'adaptent aux caractéristiques de chaque ligne, particulièrement utile pour les systèmes de pricing dynamique.

Techniques de debugging et maintenance

Le debugging des requêtes CASE WHEN complexes nécessite des stratégies spécifiques pour identifier rapidement les problèmes de logique.

Debugging par étapes

-- Version de debug avec toutes les conditions exposées
SELECT 
    customer_id,
    age,
    total_purchases,
    last_purchase_date,
    DATEDIFF(CURRENT_DATE, last_purchase_date) as days_since_last_purchase,
    -- Exposer chaque condition pour debug
    (age BETWEEN 25 AND 45) as is_core_age,
    (total_purchases >= 1000) as is_high_value,
    (DATEDIFF(CURRENT_DATE, last_purchase_date) <= 30) as is_recent,
    -- Logique finale
    CASE 
        WHEN age BETWEEN 25 AND 45 AND total_purchases >= 1000 
             AND DATEDIFF(CURRENT_DATE, last_purchase_date) <= 30 THEN 'Target Premium'
        WHEN age BETWEEN 25 AND 45 AND total_purchases >= 500 THEN 'Target Standard'
        WHEN total_purchases >= 1000 THEN 'High Value'
        ELSE 'Standard'
    END AS customer_segment
FROM customers
WHERE customer_id IN (12345, 67890, 11111)  -- Test sur échantillon
ORDER BY customer_id;

Cette approche permet de vérifier rapidement si chaque condition se comporte comme attendu avant de finaliser la requête.

Documentation des règles métier

/*
RÈGLES DE SEGMENTATION CLIENT - Version 2.1 (2026-04-21)
=======================================================
1. Target Premium: Age 25-45 + CA>1000€ + Achat<30j
2. Target Standard: Age 25-45 + CA>500€  
3. High Value: CA>1000€ (tout âge)
4. Standard: Autres cas

HISTORIQUE:
- v2.0: Ajout critère récence d'achat
- v2.1: Modification seuil Target Standard 300€→500€
*/
SELECT 
    customer_id,
    CASE 
        WHEN age BETWEEN 25 AND 45 
             AND total_purchases >= 1000 
             AND DATEDIFF(CURRENT_DATE, last_purchase_date) <= 30 
        THEN 'Target Premium'  -- Règle 1
        
        WHEN age BETWEEN 25 AND 45 
             AND total_purchases >= 500 
        THEN 'Target Standard'  -- Règle 2
        
        WHEN total_purchases >= 1000 
        THEN 'High Value'      -- Règle 3
        
        ELSE 'Standard'        -- Règle 4 (défaut)
    END AS customer_segment
FROM customers;

Cette documentation directement dans le code facilite la maintenance et l'évolution des règles métier. Pour approfondir vos compétences sur les bonnes pratiques de documentation, consultez notre guide sur les bonnes pratiques SQL pour développeurs et data analysts.

Comparaison avec les alternatives

Approche	Lisibilité	Performance	Maintenabilité	Cas d'usage optimal
CASE WHEN imbriqué	Moyenne	Bonne	Difficile	Logiques complexes ponctuelles
Multiples LEFT JOIN	Bonne	Variable	Bonne	Règles en base de données
Fonctions stockées	Très bonne	Excellente	Excellente	Logiques métier réutilisables
Vues matérialisées	Excellente	Excellente	Moyenne	Calculs coûteux fréquents

-- Analyser la rétention par cohorte d'inscription
WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_FORMAT(registration_date, '%Y-%m') as cohort_month,
        registration_date
    FROM users
),
user_activities AS (
    SELECT 
        u.user_id,
        u.cohort_month,
        u.registration_date,
        a.activity_date,
        TIMESTAMPDIFF(MONTH, u.registration_date, a.activity_date) as month_number
    FROM user_cohorts u
    LEFT JOIN user_activities a ON u.user_id = a.user_id
        AND a.activity_date >= u.registration_date
        AND a.activity_date < DATE_ADD(u.registration_date, INTERVAL 12 MONTH)
)
SELECT 
    cohort_month,
    COUNT(DISTINCT user_id) as cohort_size,
    COUNT(DISTINCT CASE WHEN month_number = 0 THEN user_id END) as month_0,
    COUNT(DISTINCT CASE WHEN month_number = 1 THEN user_id END) as month_1,
    COUNT(DISTINCT CASE WHEN month_number = 3 THEN user_id END) as month_3,
    COUNT(DISTINCT CASE WHEN month_number = 6 THEN user_id END) as month_6,
    COUNT(DISTINCT CASE WHEN month_number = 12 THEN user_id END) as month_12,
    -- Calculs de taux de rétention
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 1 THEN user_id END) / 
          COUNT(DISTINCT user_id), 2) as retention_month_1,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 6 THEN user_id END) / 
          COUNT(DISTINCT user_id), 2) as retention_month_6
FROM user_activities
GROUP BY cohort_month
ORDER BY cohort_month;

SELECT 
    customer_id,
    -- Score de récence (0-40 points)
    CASE 
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 30 THEN 40
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 90 THEN 30
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 180 THEN 20
        WHEN DATEDIFF(CURRENT_DATE, last_purchase_date) <= 365 THEN 10
        ELSE 0
    END AS recency_score,
    
    -- Score de fréquence (0-30 points)
    CASE 
        WHEN total_orders >= 20 THEN 30
        WHEN total_orders >= 10 THEN 25
        WHEN total_orders >= 5 THEN 20
        WHEN total_orders >= 2 THEN 15
        WHEN total_orders = 1 THEN 10
        ELSE 0
    END AS frequency_score,
    
    -- Score monétaire (0-30 points)
    CASE 
        WHEN total_spent >= 5000 THEN 30
        WHEN total_spent >= 2000 THEN 25
        WHEN total_spent >= 1000 THEN 20
        WHEN total_spent >= 500 THEN 15
        WHEN total_spent >= 100 THEN 10
        ELSE 5
    END AS monetary_score
FROM customer_summary;