Skill SQL Queries

Écrire du SQL correct, performant et lisible sur tous les dialectes des principaux data warehouses.

Référence Spécifique aux Dialectes

PostgreSQL (y compris Aurora, RDS, Supabase, Neon)

Date/heure :

-- Date/heure actuelle
CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

-- Arithmétique de dates
date_column + INTERVAL '7 days'
date_column - INTERVAL '1 month'

-- Tronquer à une période
DATE_TRUNC('month', created_at)

-- Extraire des parties
EXTRACT(YEAR FROM created_at)
EXTRACT(DOW FROM created_at)  -- 0=dimanche

-- Formater
TO_CHAR(created_at, 'YYYY-MM-DD')

Fonctions de chaîne :

-- Concaténation
first_name || ' ' || last_name
CONCAT(first_name, ' ', last_name)

-- Correspondance de motif
column ILIKE '%pattern%'  -- insensible à la casse
column ~ '^regex_pattern$'  -- regex

-- Manipulation de chaîne
LEFT(str, n), RIGHT(str, n)
SPLIT_PART(str, delimiter, position)
REGEXP_REPLACE(str, pattern, replacement)

Arrays et JSON :

-- Accès JSON
data->>'key'  -- texte
data->'nested'->'key'  -- json
data#>>'{path,to,key}'  -- texte imbriqué

-- Opérations sur les arrays
ARRAY_AGG(column)
ANY(array_column)
array_column @> ARRAY['value']

Conseils de performance :

• Utilisez EXPLAIN ANALYZE pour profiler les requêtes
• Créez des index sur les colonnes fréquemment filtrées/jointes
• Préférez EXISTS à IN pour les sous-requêtes corrélées
• Index partiels pour les conditions de filtre courantes
• Utilisez le connection pooling pour l'accès concurrent

Snowflake

Date/heure :

-- Date/heure actuelle
CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()

-- Arithmétique de dates
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)

-- Tronquer à une période
DATE_TRUNC('month', created_at)

-- Extraire des parties
YEAR(created_at), MONTH(created_at), DAY(created_at)
DAYOFWEEK(created_at)

-- Formater
TO_CHAR(created_at, 'YYYY-MM-DD')

Fonctions de chaîne :

-- Insensible à la casse par défaut (dépend du collation)
column ILIKE '%pattern%'
REGEXP_LIKE(column, 'pattern')

-- Parser JSON
column:key::string  -- notation pointée pour VARIANT
PARSE_JSON('{"key": "value"}')
GET_PATH(variant_col, 'path.to.key')

-- Aplatir arrays/objets
SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f

Données semi-structurées :

-- Accès au type VARIANT
data:customer:name::STRING
data:items[0]:price::NUMBER

-- Aplatir les structures imbriquées
SELECT
    t.id,
    item.value:name::STRING as item_name,
    item.value:qty::NUMBER as quantity
FROM my_table t,
LATERAL FLATTEN(input => t.data:items) item

Conseils de performance :

• Utilisez des clustering keys sur les grandes tables (pas d'index traditionnel)
• Filtrez sur les colonnes de clustering key pour l'élagage de partitions
• Définissez la taille appropriée du warehouse selon la complexité de la requête
• Utilisez RESULT_SCAN(LAST_QUERY_ID()) pour éviter de relancer des requêtes coûteuses
• Utilisez les tables transient pour le staging/données temporaires

BigQuery (Google Cloud)

Date/heure :

-- Date/heure actuelle
CURRENT_DATE(), CURRENT_TIMESTAMP()

-- Arithmétique de dates
DATE_ADD(date_column, INTERVAL 7 DAY)
DATE_SUB(date_column, INTERVAL 1 MONTH)
DATE_DIFF(end_date, start_date, DAY)
TIMESTAMP_DIFF(end_ts, start_ts, HOUR)

-- Tronquer à une période
DATE_TRUNC(created_at, MONTH)
TIMESTAMP_TRUNC(created_at, HOUR)

-- Extraire des parties
EXTRACT(YEAR FROM created_at)
EXTRACT(DAYOFWEEK FROM created_at)  -- 1=dimanche

-- Formater
FORMAT_DATE('%Y-%m-%d', date_column)
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)

Fonctions de chaîne :

-- Pas de ILIKE, utilisez LOWER()
LOWER(column) LIKE '%pattern%'
REGEXP_CONTAINS(column, r'pattern')
REGEXP_EXTRACT(column, r'pattern')

-- Manipulation de chaîne
SPLIT(str, delimiter)  -- retourne ARRAY
ARRAY_TO_STRING(array, delimiter)

Arrays et structs :

-- Opérations sur les arrays
ARRAY_AGG(column)
UNNEST(array_column)
ARRAY_LENGTH(array_column)
value IN UNNEST(array_column)

-- Accès aux struct
struct_column.field_name

Conseils de performance :

• Filtrez toujours sur les colonnes de partition (généralement la date) pour réduire les octets scannés
• Utilisez le clustering pour les colonnes fréquemment filtrées dans les partitions
• Utilisez APPROX_COUNT_DISTINCT() pour les estimations de cardinalité à grande échelle
• Évitez SELECT * -- la facturation se fait par octet scanné
• Utilisez DECLARE et SET pour les scripts paramétrés
• Prévisualisez le coût de la requête avec un dry run avant d'exécuter des requêtes volumineuses

Redshift (Amazon)

Date/heure :

-- Date/heure actuelle
CURRENT_DATE, GETDATE(), SYSDATE

-- Arithmétique de dates
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)

-- Tronquer à une période
DATE_TRUNC('month', created_at)

-- Extraire des parties
EXTRACT(YEAR FROM created_at)
DATE_PART('dow', created_at)

Fonctions de chaîne :

-- Insensible à la casse
column ILIKE '%pattern%'
REGEXP_INSTR(column, 'pattern') > 0

-- Manipulation de chaîne
SPLIT_PART(str, delimiter, position)
LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)

Conseils de performance :

• Concevez les distribution keys pour les jointures co-localisées (DISTKEY)
• Utilisez sort keys pour les colonnes fréquemment filtrées (SORTKEY)
• Utilisez EXPLAIN pour vérifier le plan de requête
• Évitez le mouvement de données inter-nœuds (attention aux DS_BCAST et DS_DIST)
• Exécutez ANALYZE et VACUUM régulièrement
• Utilisez les late-binding views pour la flexibilité de schéma

Databricks SQL

Date/heure :

-- Date/heure actuelle
CURRENT_DATE(), CURRENT_TIMESTAMP()

-- Arithmétique de dates
DATE_ADD(date_column, 7)
DATEDIFF(end_date, start_date)
ADD_MONTHS(date_column, 1)

-- Tronquer à une période
DATE_TRUNC('MONTH', created_at)
TRUNC(date_column, 'MM')

-- Extraire des parties
YEAR(created_at), MONTH(created_at)
DAYOFWEEK(created_at)

Fonctionnalités Delta Lake :

-- Time travel
SELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'
SELECT * FROM my_table VERSION AS OF 42

-- Décrire l'historique
DESCRIBE HISTORY my_table

-- Merge (upsert)
MERGE INTO target USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Conseils de performance :

• Utilisez OPTIMIZE et ZORDER de Delta Lake pour la performance des requêtes
• Tirez parti du moteur Photon pour les requêtes gourmandes en calcul
• Utilisez CACHE TABLE pour les datasets fréquemment accédés
• Partitionnez par colonnes de dates à faible cardinalité

Motifs SQL Courants

Window Functions

-- Classement
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
RANK() OVER (PARTITION BY category ORDER BY revenue DESC)
DENSE_RANK() OVER (ORDER BY score DESC)

-- Totaux cumulés / moyennes mobiles
SUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
AVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d

-- Lag / Lead
LAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_value
LEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value

-- Première / dernière valeur
FIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Pourcentage du total
revenue / SUM(revenue) OVER () as pct_of_total
revenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category

CTEs pour la Lisibilité

WITH
-- Étape 1 : Définir la population de base
base_users AS (
    SELECT user_id, created_at, plan_type
    FROM users
    WHERE created_at >= DATE '2024-01-01'
      AND status = 'active'
),

-- Étape 2 : Calculer les métriques au niveau utilisateur
user_metrics AS (
    SELECT
        u.user_id,
        u.plan_type,
        COUNT(DISTINCT e.session_id) as session_count,
        SUM(e.revenue) as total_revenue
    FROM base_users u
    LEFT JOIN events e ON u.user_id = e.user_id
    GROUP BY u.user_id, u.plan_type
),

-- Étape 3 : Agréger au niveau résumé
summary AS (
    SELECT
        plan_type,
        COUNT(*) as user_count,
        AVG(session_count) as avg_sessions,
        SUM(total_revenue) as total_revenue
    FROM user_metrics
    GROUP BY plan_type
)

SELECT * FROM summary ORDER BY total_revenue DESC;

Rétention par Cohorte

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', first_activity_date) as cohort_month
    FROM users
),
activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_date) as activity_month
    FROM user_activity
)
SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id) as cohort_size,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month THEN a.user_id
    END) as month_0,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id
    END) as month_1,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id
    END) as month_3
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Analyse de Funnel

WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,
        MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,
        MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,
        MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) as total_users,
    SUM(step_1_view) as viewed,
    SUM(step_2_start) as started_signup,
    SUM(step_3_complete) as completed_signup,
    SUM(step_4_purchase) as purchased,
    ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,
    ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,
    ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct
FROM funnel;

Dédoublonnage

-- Conserver l'enregistrement le plus récent par clé
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY entity_id
            ORDER BY updated_at DESC
        ) as rn
    FROM source_table
)
SELECT * FROM ranked WHERE rn = 1;

Gestion des Erreurs et Débogage

Quand une requête échoue :

1. Erreurs de syntaxe : Vérifiez la syntaxe spécifique au dialecte (par exemple, ILIKE non disponible dans BigQuery, SAFE_DIVIDE uniquement dans BigQuery)
2. Colonne introuvable : Vérifiez les noms de colonnes dans le schéma -- vérifiez les fautes de frappe, la sensibilité à la casse (PostgreSQL est sensible à la casse pour les identifiants entre guillemets)
3. Incompatibilités de type : Effectuez des conversions explicites lors de la comparaison de types différents (CAST(col AS DATE), col::DATE)
4. Division par zéro : Utilisez NULLIF(denominateur, 0) ou la division sûre spécifique au dialecte
5. Colonnes ambiguës : Qualifiez toujours les noms de colonnes avec l'alias de table dans les JOINs
6. Erreurs GROUP BY : Toutes les colonnes non agrégées doivent être dans GROUP BY (sauf dans BigQuery qui permet le groupement par alias)