Question Fonction de fenêtre Postgres et groupe par exception


J'essaie de rassembler une requête qui récupérera les statistiques d'un utilisateur (bénéfice / perte) en tant que résultat cumulatif sur une période donnée.

Voici la requête que j'ai à ce jour:

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

La requête sera exécutée. Cependant, le résultat est légèrement incorrect. La raison est qu'un event peut avoir plusieurs jeux (avec différents sp.payouts). Par conséquent, ce qui précède apparaît avec plusieurs lignes si un utilisateur a 2 résultats dans un événement avec des gains différents (c.-à-d. Il y a 4 jeux par événement, et un utilisateur obtient 20 £ et 40 £).

La solution évidente serait de modifier le GROUP BY à:

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

Cependant, Postgres se plaint à ce sujet car il ne semble pas reconnaître que sp.payout et s.buyin sont à l'intérieur d'une fonction d'agrégat. Je reçois l'erreur:

la colonne "sp.payout" doit apparaître dans la clause GROUP BY ou être utilisée dans une   fonction d'agrégat

Je cours 9.1 sur le serveur Linux Ubuntu.
Est-ce que je manque quelque chose ou est-ce que cela pourrait être un véritable défaut chez Postgres?


11
2018-01-13 01:44


origine


Réponses:


Tu es ne pas, en fait, en utilisant des fonctions d'agrégat. Vous utilisez fonctions de fenêtre. C'est pourquoi les exigences de PostgreSQL sp.payout et s.buyin être inclus dans le GROUP BY clause.

En ajoutant un OVER clause, la fonction d'agrégat sum() est transformé en une fonction de fenêtre, qui agrège les valeurs par partition tout en en gardant toutes les lignes

Vous pouvez combiner des fonctions de fenêtre et des fonctions d'agrégat. Les agrégations sont appliquées en premier. D'après votre description, je ne comprenais pas comment vous souhaitez gérer plusieurs paiements / buyins par événement. En supposant que je calcule une somme par événement. À présent Je peux enlever sp.payout et s.buyin du GROUP BY clause et obtenir une ligne par player et event:

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 cette expression: sum(sum(sp.payout)) OVER w, l'extérieur sum() est une fonction de fenêtre, la fonction interne sum() est une fonction d'agrégat.

En supposant p.player_id et e.event_id sont PRIMARY KEY dans leurs tables respectives.

J'ai ajouté e.event_id au ORDER BY du WINDOW clause pour arriver à un ordre de tri déterministe. (Il pourrait y avoir plusieurs événements à la même date.) event_id dans le résultat pour distinguer plusieurs événements par jour.

Alors que la requête se limite à un unique joueur (WHERE p.player_id = 17), nous n'avons pas besoin d'ajouter p.name ou p.player_id à GROUP BY et ORDER BY. Si l'une des jointures multipliait indûment les lignes, la somme résultante serait incorrecte (en partie ou en totalité). Regroupement par p.name n'a pas pu réparer la requête alors.

J'ai aussi enlevé e.date du GROUP BY clause. La clé primaire e.event_id couvre toutes les colonnes de la ligne d'entrée depuis PostgreSQL 9.1.

Si vous modifiez la requête pour renvoyer plusieurs lecteurs à la fois, adaptez:

...
WHERE  p.player_id < 17  -- example - multiple players
GROUP  BY p.name, p.player_id, e.date, e.event_id  -- e.date and p.name redundant
WINDOW w AS (ORDER BY p.name, p.player_id, e.date, e.event_id)
ORDER  BY p.name, p.player_id, e.date, e.event_id;

Sauf si p.name est défini unique (?), groupe et ordre par player_id en outre pour obtenir des résultats corrects dans un ordre de tri déterministe.

Je n'ai gardé que e.date et p.name dans GROUP BY d'avoir un ordre de tri identique dans toutes les clauses, en espérant un avantage en termes de performances. Sinon, vous pouvez supprimer les colonnes ici. (Similaire pour juste e.datedans la première requête.)


26
2018-01-13 02:10