CTE et pipelines analytiques

Lorsque le calcul devient complexe, on le décompose en étapes lisibles avec WITH, qui définit des CTE (Common Table Expressions) : une étape pour les rendements, une pour les moyennes, une pour les signaux, une pour la performance. SQL devient alors un pipeline de transformation.

WITH returns AS (
    SELECT *, close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) AS ret
    FROM prices
),
ma AS (
    SELECT *, AVG(close) OVER (PARTITION BY ticker ORDER BY date
                               ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5
    FROM returns
),
signal AS (
    SELECT *, CASE WHEN close > ma5 THEN 1 ELSE 0 END AS signal
    FROM ma
)
SELECT *, signal * ret AS strategy_ret
FROM signal

Lecture détaillée

1. returns : on introduit la dépendance au passé ;
2. ma : on introduit une structure locale glissante ;
3. signal : on transforme une mesure en action ;
4. sélection finale : on produit la contribution de stratégie.

Chaque CTE joue le rôle d'un opérateur intermédiaire. On peut représenter le pipeline comme une composition T = T4 ∘ T3 ∘ T2 ∘ T1 où T1 calcule les rendements, T2 les moyennes, T3 construit un signal et T4 calcule la performance. La requête n'est pas une suite de boucles écrites explicitement : c'est une description déclarative d'une chaîne de calcul.

Ajouter la performance cumulée

On ajoute une CTE strategy (signal * ret AS strategy_ret) puis on termine par :

SELECT *, SUM(strategy_ret) OVER (PARTITION BY ticker ORDER BY date
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM strategy

Le pipeline se lit alors : prix → retours → tendance locale → signal → résultat instantané → résultat cumulé. L'intérêt pédagogique du SQL analytique est qu'il rend visible la structure logique du calcul.