Calcul de la somme Cumulative dans PostgreSQL
Je veux trouver la quantité cumulative ou courante de champ et l'insérer de la mise en scène à la table. Ma structure de mise en 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 que ma table cible ressemble à 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 confus avec la façon de parvenir à ce résultat. Je veux atteindre ce résultat en utilisant PostgreSQL.
Quelqu'un peut-il suggérer comment atteindre cet ensemble de résultats?
1 réponses
Fondamentalement, vous avez besoin d'une fonction de fenêtre ici. C'est une caractéristique standard de nos jours. En plus des fonctions de fenêtre authentiques, vous pouvez utiliser n'importe quelle fonction d'agrégation comme fonction de fenêtre dans Postgres en ajoutant une clause OVER.
La difficulté particulière ici est d'obtenir les partitions et l'ordre de tri correct:
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 non GROUP BY ici.
La somme pour chaque ligne est calculée à partir de la première ligne de la partition à la ligne courante - ou citant le manuel, pour être précis:
L'option de cadrage par défaut est
RANGE UNBOUNDED PRECEDING, qui est la même chose queRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. AvecORDER BY, définit le cadre comme toutes les lignes de la partition démarrer à travers le dernierORDER BYPair de la ligne en cours.
... Quelle est la somme cumulative ou courante que vous recherchez. Emphase audacieuse mine.
Lignes avec le même (circle_id, ea_year, ea_month) sont "pairs" dans cette requête. Tous ceux-ci montrent la même somme courante avec tous les pairs ajouté à la somme. Mais je suppose que votre table est UNIQUE sur (circle_id, ea_year, ea_month), alors l'ordre de tri est déterministe et aucune ligne n'a pairs.
Maintenant, ORDER BY ... ea_month ne fonctionne pas avec des chaînes de caractères pour les noms de mois. Postgres trie par ordre alphabétique en fonction du paramètre locale.
Si vous avez des valeurs date réelles stockées dans votre table, vous pouvez 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 des chaînes originales avec
to_char():to_char(mon, 'Month') AS ea_month to_char(mon, 'YYYY') AS ea_year
Bien que coincé avec la mise en page malheureuse, cela fonctionnera:
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;