Fonctions temporaires & macros

📌 Définitions

#

Fonction SQL

#

Une fonction SQL permet d’encapsuler une logique (calcul, expression, transformation) et de la réutiliser partout dans les 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 n’est pas une fonction SQL classique.
C’est un bloc de code qui génère du SQL avant exécution.

On peut la voir comme :

Une fonction côté template, qui produit du SQL dynamique. Elle est évaluée avant que la requête ne parte vers la base.

👉 Utile quand on veut écrire du SQL qui écrit du SQL :

• automations de clauses répétitives
• génération dynamique de colonnes
• factorisation de patterns complexes
• adaptation des requêtes selon les paramètres

Les macros ne s’exécutent donc pas dans la base, mais dans l’outil qui prépare la requête (DBT, BigQuery scripting, DuckDB Python, etc.).

Synthèse

#

✅ Exemples de fonctions

#

• DuckDB / PostgreSQL :

  -- Creation
  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()
  

• Calcul d’une remise de 10% si commande > 100 €

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

✅ Exemples de macro dans DBT

#

• Normalisation d’une colonne texte (trim, lowercase, retirer accents…).

  -- macros/clean_text.sql
  {% macro clean_text(col) %}
      lower(trim({{ col }}))
  {% endmacro %}
  

Puis utilisation dans une requête :

SELECT
    {{ clean_text("client_name") }} AS client_clean
FROM clients;

→ Au moment du rendering, DBT remplace la ligne par :

lower(trim(client_name)) AS client_clean

➡️ On ne réécrit pas la transformation à chaque fois. ➡️ Le SQL final exécuté par la base ne contient plus la macro, seulement le code généré.

✅ Exemples de macro dans BigQuery

#

BigQuery n’a pas de macros “DBT”, mais propose des templates via scripting :

CREATE TEMP FUNCTION clean_text(x STRING)
RETURNS STRING AS (
  LOWER(TRIM(x))
);

SELECT clean_text(client_name) FROM clients;

Différence importante : ici, c’est une vraie fonction exécutée dans BigQuery, pas du templating.

🧯 Pièges à éviter

#

🏋️‍♂️ Exercices

#

• Créer une fonction nettoie_nom(text) qui :
  • met en minuscules
  • enlève les espaces
• Créer une fonction statut_commande(qte INT) qui retourne :
  • ‘grosse commande’ si qte > 10
  • ‘petite commande’ sinon
• Utilise une fonction dans une requête sur la table commandes.

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 les fonctions