Comment utiliser correctement les fonctions de fenêtre avec GROUP BY dans Postgres pour calculer les statistiques cumulées des utilisateurs ?

Fonction de la fenêtre Postgres et regroupement par exception

Problème

Une requête cherche à récupérer statistiques cumulées des utilisateurs au fil du temps, mais il rencontre des inexactitudes. Lorsque plusieurs jeux existent au sein d'un événement, la requête produit plusieurs lignes pour différents paiements. La solution évidente de regroupement par ID d'événement échoue, provoquant l'erreur : "la colonne "sp.payout" doit apparaître dans la clause GROUP BY ou être utilisée dans une fonction d'agrégation."

Solution

L'erreur survient car la requête utilise des fonctions de fenêtre, qui agrègent les valeurs par partition tout en préservant toutes les lignes, plutôt que des fonctions d'agrégation. Les fonctions de fenêtre nécessitent que leurs arguments soient inclus dans la clause GROUP BY.

Pour résoudre le problème, on peut combiner les fonctions de fenêtre et d'agrégation comme suit :

SELECT p.name
     , e.event_id
     , e.date
     , sum(sum(sp.payout)) OVER w
     - sum(sum(s.buyin  )) OVER w AS "Profit/Loss" 
FROM   player            p
JOIN   result            r ON r.player_id     = p.player_id  
JOIN   game              g ON g.game_id       = r.game_id 
JOIN   event             e ON e.event_id      = g.event_id 
JOIN   structure         s ON s.structure_id  = g.structure_id 
JOIN   structure_payout sp ON sp.structure_id = g.structure_id
                          AND sp.position     = r.position
WHERE  p.player_id = 17 
GROUP  BY e.event_id
WINDOW w AS (ORDER BY e.date, e.event_id)
ORDER  BY e.date, e.event_id;

Explication

Dans cette requête, la fonction external sum() est une fonction de fenêtre qui calcule la somme de la fonction interne sum(). La fonction interne regroupe les paiements et les buy-ins pour chaque joueur au sein de chaque événement. Le résultat est une ligne par joueur et par événement, affichant le profit ou la perte cumulé.

Considérations supplémentaires

• Pour inclure plusieurs joueurs, remplacez la clause WHERE par : OÙ p.player_id < 17.
• Si p.name n'est pas unique, regroupez et classez également par player_id pour obtenir des résultats corrects.
• Le regroupement par e.date et p.name peut générer des avantages en termes de performances.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!