Module 3 : Fonctions temporaires & macros en SQL

🧩 Module 3 – Fonctions temporaires & macros en SQL

#

🎯 Objectifs du module

#

À la fin, tu sauras :

• Écrire des fonctions temporaires (inline ou persistantes) dans un dialecte SQL
• Comprendre la différence entre fonction et macro
• Créer des fonctions paramétrables réutilisables
• Utiliser ces fonctions dans DuckDB, PostgreSQL ou BigQuery

📌 1. Définition & utilité

#

🧠 Fonction SQL

#

Une fonction SQL te permet d’encapsuler une logique (calcul, expression, transformation) et de la réutiliser partout dans tes requêtes.

💡 Exemples :

• Nettoyage d’un champ (clean_name(text))
• Calculs de business (prix_total(qte, prix_unitaire))
• Formules de scoring

🧠 Macro SQL (DBT, BigQuery, DuckDB)

#

Une macro, c’est comme une fonction SQL mais au niveau du template, souvent utilisée dans DBT ou BigQuery.

• S’évalue avant l’exécution SQL (lors du rendering)
• Très utile pour générer dynamiquement des requêtes, noms de colonnes, clauses, etc.

✅ 2. Exemple simple de fonction

#

🔹 DuckDB / PostgreSQL :

#

CREATE OR REPLACE FUNCTION prix_total(qte INT, prix_unitaire DOUBLE)
RETURNS DOUBLE AS $$
  SELECT qte * prix_unitaire;
$$ LANGUAGE SQL;

💡 Utilisation :

SELECT prix_total(3, 19.90); -- renvoie 59.7

🔹 DuckDB (fonction temporaire inline dans Python) :

#

import duckdb

duckdb.sql("""
CREATE FUNCTION prix_total(qte INT, prix DOUBLE)
RETURNS DOUBLE AS $$
  SELECT qte * prix;
$$;
""")

duckdb.sql("SELECT prix_total(2, 5.5)").show()

✅ 3. Exemple d’usage avec une table

#

-- Exemple : calculer une remise de 10% si commande > 100 €
CREATE OR REPLACE FUNCTION prix_avec_remise(montant DOUBLE)
RETURNS DOUBLE AS $$
  SELECT CASE
    WHEN montant > 100 THEN montant * 0.9
    ELSE montant
  END;
$$ LANGUAGE SQL;

-- Utilisation
SELECT commande_id, prix_avec_remise(150) AS prix_final;

🧠 4. Exemple de macro (dans DBT ou BigQuery) (facultatif si tu n’utilises pas encore DBT)

#

-- dbt macro
{% macro clean_column(colname) %}
  LOWER(TRIM({{ colname }}))
{% endmacro %}

-- usage dans un SELECT
SELECT {{ clean_column("client_name") }} FROM clients;

🧯 5. Pièges à éviter

#

🏋️‍♂️ 6. Exercices

#

Exo 1

#

Créer une fonction nettoie_nom(text) qui :

• met en minuscules
• enlève les espaces

Exo 2

#

Créer une fonction statut_commande(qte INT) qui retourne :

• 'grosse commande' si qte > 10
• 'petite commande' sinon

Exo 3

#

Utilise une fonction dans une requête sur la table commandes.

🧰 7. Tester rapidement dans DuckDB (Python)

#

duckdb.sql("""
CREATE FUNCTION statut_commande(qte INT)
RETURNS TEXT AS $$
  SELECT CASE WHEN qte > 10 THEN 'grosse' ELSE 'petite' END;
$$;

SELECT statut_commande(5), statut_commande(20);
""").df()

📚 8. Ressources utiles

#

• 📘 DuckDB - CREATE FUNCTION
• 🧠 PostgreSQL - CREATE FUNCTION
• 🧱 DBT - Macros
• 🎮 SQLPad (Docker) ou DB Fiddle pour tester tes fonctions