Le calcul de la Somme Cumulée dans PostgreSQL

Je veux trouver le cumul ou l'exécution sur le terrain et l'insérer à partir de la mise en scène à la table. Ma structure de scène est quelque chose comme ceci:

ea_month    id       amount    ea_year    circle_id
April       92570    1000      2014        1
April       92571    3000      2014        2
April       92572    2000      2014        3
March       92573    3000      2014        1
March       92574    2500      2014        2
March       92575    3750      2014        3
February    92576    2000      2014        1
February    92577    2500      2014        2
February    92578    1450      2014        3

Je veux ma table cible à ressembler à quelque chose comme ceci:

ea_month    id       amount    ea_year    circle_id    cum_amt
February    92576    1000      2014        1           1000 
March       92573    3000      2014        1           4000
April       92570    2000      2014        1           6000
February    92577    3000      2014        2           3000
March       92574    2500      2014        2           5500
April       92571    3750      2014        2           9250
February    92578    2000      2014        3           2000
March       92575    2500      2014        3           4500
April       92572    1450      2014        3           5950

Je suis vraiment très souvent confondu avec comment parvenir à ce résultat. Je veux arriver à ce résultat en utilisant PostgreSQL.

Quelqu'un peut-il suggérer comment parvenir à ce résultat?

  • Comment obtenez-vous l'cum_amount de 1000 dans votre table cible? Pour circle_id, le montant semble être l'an 2000.
InformationsquelleAutor Yousuf Sultan | 2014-04-03
  1. 92

    Fondamentalement, vous avez besoin d'un fonction fenêtre ici. C'est une caractéristique standard de nos jours. En plus à de véritables fonctions de la fenêtre, vous pouvez utiliser tout fonction d'agrégation en tant que fonction de fenêtre dans Postgres en ajoutant un OVER clause.

    Spécial de la difficulté ici est d'obtenir les partitions et l'ordre de tri à droite:

    SELECT ea_month, id, amount, ea_year, circle_id
     , sum(amount) OVER (PARTITION BY circle_id ORDER BY ea_year, ea_month) AS cum_amt
FROM   tbl
ORDER  BY circle_id, month;

    Et pas GROUP BY ici.

    La somme de chaque ligne est calculée à partir de la première ligne de la partition de la ligne actuelle - ou de le citer le manuel pour être plus précis:

    La valeur par défaut option de cadrage est RANGE UNBOUNDED PRECEDING, qui est
    le même que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Avec
    ORDER BY, ce qui établit le cadre pour être toutes les lignes de la partition
    démarrage par le biais de la ligne actuelle du dernier ORDER BY par les pairs    .

    ... qui est le cumul ou la somme en cours d'exécution, vous êtes après. En gras c'est moi qui souligne.

    Des lignes avec le même (circle_id, ea_year, ea_month) sont "pairs" dans cette requête. Tous indiquent la même somme en cours d'exécution avec tous les pairs ajouté à la somme. Mais je suppose que votre table est UNIQUE sur (circle_id, ea_year, ea_month), puis l'ordre de tri est déterministe et aucune ligne n'a pairs.

    Maintenant, ORDER BY ... ea_month de ne pas travailler avec des chaînes de caractères pour les noms de mois. Postgres serait trier par ordre alphabétique en fonction des paramètres régionaux.

    Si vous avez réels date valeurs stockées dans la table, vous pouvez les trier correctement. Si pas, je suggère de remplacer ea_year et ea_month avec une seule colonne mon de type date dans votre table.

    • Transformer ce que vous avez avec to_date():

      to_date(ea_year || ea_month , 'YYYYMonth') AS mon

    • Pour l'affichage, vous pouvez obtenir un exemplaire original de chaînes avec to_char():

      to_char(mon, 'Month') AS ea_month
to_char(mon, 'YYYY') AS ea_year

    Tout coincé avec la malheureuse disposition, cela va fonctionner:

    SELECT ea_month, id, amount, ea_year, circle_id
     , sum(amount) OVER (PARTITION BY circle_id ORDER BY mon) AS cum_amt
FROM   (SELECT *, to_date(ea_year || ea_month, 'YYYYMonth') AS mon FROM tbl)
ORDER  BY circle_id, mon;
    • Merci pour la solution.. Pouvez-vous m'aider avec une chose de plus. Je veux mettre en œuvre la même chose à l'aide d'un curseur avec la logique de chaque cercle aurez juste un niveau record pour un mois d'une année. Et la fonction est censé fonctionner une fois tous les mois. Comment puis-je y parvenir?
    • La plupart du temps il y a une meilleure solution qu'un curseur. C'est certainement la substance pour une nouvelle question. Veuillez commencer une nouvelle question.
    • Je trouve cette réponse incomplète sans au moins une note qu'il y a du "framing" qui se passe ici, par défaut, à range unbounded preceding, qui est le même que range between unbounded preceding and current row. C'est pourquoi sum()lorsqu'il est utilisé comme une fenêtre de fonction produit un total -- tandis que les autres fonctions de la fenêtre ne disposent pas de ce cadre par défaut.
    • J'ai ajouté un peu plus de ci-dessus afin de préciser.
    • Voici un lien vers une question similaire, avec une simple requête (le PARTITION n'est pas toujours nécessaire de créer un total cumulé): stackoverflow.com/a/5700744/175830
    InformationsquelleAutor Erwin Brandstetter