Erreurs typiques lors de l'utilisation des fonctions window

Bon, il est temps de parler des galères qui peuvent arriver quand tu utilises les fonctions window. Comme souvent en dev (et dans la vie), c'est mieux d'apprendre sur les fails des autres. On va décortiquer les erreurs classiques que font les débutants (et même parfois les devs expérimentés), et voir comment les éviter.

Erreur n°1 : Mauvaise utilisation de PARTITION BY

Un des fails les plus fréquents, c’est d’oublier ou de mal configurer le paramètre PARTITION BY, surtout si tu veux splitter tes données en groupes. Sans ça, PostgreSQL va voir toutes les lignes comme un seul gros groupe, et du coup, les résultats risquent d’être bien chelous par rapport à ce que tu voulais.

Imaginons qu’on a une table sales qui contient des infos sur les ventes :

Tu veux calculer la somme cumulée (SUM()) des ventes par mois pour chaque région. Tu pourrais écrire une requête comme ça :

SELECT
    region,
    month,
    SUM(total) OVER (ORDER BY month) AS running_total
FROM 
    sales;

Résultat :

À première vue, ça a l’air OK. Mais en vrai, le résultat ne colle pas à ce qu’on attend, parce que la somme cumulée est faite sur toutes les lignes, pas par région. Le souci, c’est qu’on a zappé PARTITION BY region.

Code corrigé :

SELECT 
    region,
    month,
    SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM 
    sales;

Résultat :

Maintenant, c’est bon : les données sont groupées par région, et la somme cumulée est calculée séparément pour chaque région.

Erreur n°2 : Mauvais ordre dans ORDER BY

ORDER BY à l’intérieur de OVER() gère l’ordre des lignes dans la fenêtre. Si tu te plantes dans l’ordre, les résultats risquent d’être bizarres.

Tu veux calculer les sommes cumulées des ventes, triées par mois décroissant. Tu pourrais écrire une requête comme ça :

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month DESC) AS running_total
FROM 
    sales;

Résultat :

À première vue, ça a l’air correct, mais fais gaffe : les lignes sont groupées par mois, mais la somme n’est pas calculée correctement à cause de l’ordre décroissant. C’est pour ça que les résultats sont chelous.

Correction : réécris la requête en utilisant le bon ordre dans ORDER BY :

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month ASC) AS running_total
FROM 
    sales;

Erreur n°3 : Utiliser les fonctions window sans index

Les fonctions window bossent souvent sur des gros volumes de données, et si t’as pas d’index sur les colonnes clés, tu risques de flinguer les perfs.

Exemple : on a une table large_sales avec des millions de lignes, et on veut calculer le rang des ventes :

SELECT
    id,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank
FROM 
    large_sales;

Sur un petit jeu de données, ça passe vite, mais sur des millions de lignes, ça peut prendre une éternité.

Correction : ajoute un index sur la colonne utilisée dans ORDER BY :

CREATE INDEX idx_total ON large_sales(total DESC);

Maintenant, la requête va tourner beaucoup plus vite.

Erreur n°4 : Mal comprendre la fenêtre définie par ROWS ou RANGE

Quand tu utilises ROWS et RANGE, c’est super important de piger comment ils calculent la fenêtre de lignes. Si tu piges pas bien ces mots-clés, tu risques d’avoir des résultats inattendus.

Exemple : tu veux calculer la moyenne mobile des ventes pour le mois courant et les deux précédents :

SELECT
    month,
    AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Si à la place de ROWS tu mets RANGE :

SELECT
    month,
    AVG(total) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Le résultat sera différent, parce que RANGE bosse sur des plages de valeurs, pas sur un nombre précis de lignes.

Erreur n°5 : Utiliser trop de fonctions window

Si tu utilises plusieurs fonctions window dans une même requête, tu risques de faire des calculs en double et de ralentir grave tes perfs.

Exemple :

SELECT 
    id,
    total,
    SUM(total) OVER (PARTITION BY region) AS region_total,
    SUM(total) OVER (PARTITION BY region) / COUNT(total) OVER (PARTITION BY region) AS region_avg
FROM 
    sales;

Ici, SUM(total) et COUNT(total) sont recalculés plusieurs fois pour chaque ligne.

Correction : raccourcis la requête avec des sous-requêtes ou un CTE :

WITH cte_region_totals AS (
    SELECT 
        region,
        SUM(total) AS region_total,
        COUNT(total) AS region_count
    FROM 
        sales
    GROUP BY 
        region
)
SELECT 
    s.id,
    s.total,
    t.region_total,
    t.region_total / t.region_count AS region_avg
FROM 
    sales s
JOIN 
    cte_region_totals t ON s.region = t.region;

Tips pour éviter les erreurs

Vérifie PARTITION BY et ORDER BY : checke toujours si ta fenêtre est bien définie.

Indexe tes données : surtout si tu fais des tris (ORDER BY) ou des filtres.

Utilise les CTE pour les calculs multiples : ça t’aide à éviter les doublons dans les calculs.

Mate le plan d’exécution : utilise EXPLAIN et EXPLAIN ANALYZE pour voir comment PostgreSQL gère ta requête.

Teste sur des vraies données : assure-toi que les résultats collent à ce que tu veux et que tes besoins sont bien couverts.