Comment utiliser correctement les fonctions de fenêtre PostgreSQL avec les clauses GROUP BY ?

Fonction de fenêtre PostgreSQL et exception Group By

Lorsque vous essayez d'utiliser une fonction de fenêtre en conjonction avec une clause GROUP BY dans PostgreSQL, il est crucial de comprendre la distinction entre deux. Les fonctions de fenêtre fonctionnent sur une partition de données de table, effectuant des calculs sur plusieurs lignes tout en préservant toutes les lignes, contrairement aux fonctions d'agrégation qui résument les lignes en une seule valeur. Par conséquent, lors de l'utilisation des fonctions de fenêtre, il est essentiel d'inclure les colonnes partitionnées dans la clause GROUP BY.

Description du problème

Dans la requête fournie, l'utilisateur rencontre une erreur lorsqu'il tente d'exécuter ce qui suit code :

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

L'erreur survient car la fenêtre fonctionne, sum(sp.payout) OVER (ORDER BY e.date) et sum(s.buyin) OVER (ORDER BY e.date), ne sont pas accompagnés d'une fonction d'agrégation. Par conséquent, PostgreSQL exige que les colonnes partitionnées, sp.payout et s.buyin, soient incluses dans la clause GROUP BY.

Solution

Pour résoudre le problème, l'utilisateur peut soit :

1. Inclure sp.payout et s.buyin dans le GROUP BY clause :

GROUP BY p.name, e.date, e.event_id

Cependant, cette approche peut conduire à plusieurs lignes pour chaque joueur et événement s'il existe plusieurs valeurs sp.payout ou s.buyin.

2. Utiliser la combinaison de fonctions de fenêtre et d'agrégat fonctions :

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;

Dans cet exemple, les expressions sum(sum(sp.payout)) OVER w et sum(sum(s.buyin)) OVER w combinent une fenêtre externe fonction avec une fonction d'agrégation interne pour calculer le total des paiements et des buyins par événement.

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!