[解決済み] PostgreSQL での累積和の計算

質問

私は、フィールドの累積または実行量を検索し、ステージングからテーブルに挿入したい。私のステージング構造は次のようなものです。

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          

対象のテーブルを以下のようにしたい。

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

私はこの結果を達成するために行く方法について本当に非常に混乱しています。私はPostgreSQLを使用してこの結果を達成したいです。

どなたか、この結果セットを達成するためにどうすればよいかを提案していただけませんか？

どのように解決するのですか？

基本的には ウィンドウ機能 . というのが今の標準的な機能です。純正のウィンドウ関数に加え 任意の 集約関数をPostgresのウィンドウ関数として使用することもできます。 OVER 節を追加することで、Postresのウィンドウ関数として使用できます。

ここでの特別な困難は、パーティションとソート順序を正しくすることです。

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;

そして いいえ GROUP BY .

各行の合計は、パーティションの最初の行から現在の行まで計算されます - あるいは、次のように引用します。 マニュアル を引用することで正確です。

デフォルトのフレーム化オプションは RANGE UNBOUNDED PRECEDING で、これは と同じです。 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . で ORDER BY で、これはフレームを パーティションから から現在の行の最後の ORDER BY ピア .

...これは、あなたが求めている累積または実行中の合計です。太字強調は私です。

を持つ行は、同じ (circle_id, ea_year, ea_month) は "peers"です。 で、このクエリでは これらはすべて、すべてのピアが合計に追加された、同じ実行中の合計を表示します。しかし、私はあなたのテーブルが UNIQUE で (circle_id, ea_year, ea_month) にある場合、ソート順は決定論的であり、どの行もピアを持ちません。

Postgres 11では、ピアをインクルード/エクスクルードするためのツールが追加され、新しい frame_exclusion オプション . 参照してください。

• 同じグループに属さないすべての値を集計する

では ORDER BY ... ea_month は、月名の文字列では動作しません。 . Postgresはロケールの設定に従ってアルファベット順にソートします。

もし、実際の date の値がテーブルに格納されている場合は、正しくソートできます。そうでない場合、私は ea_year と ea_month を1つのカラムとする mon タイプの date という型をテーブルの中に入れてください。

• で持っているものを変換して to_date() :

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

• 表示用には、オリジナルの文字列を to_char() :

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

残念なデザインに引っかかりつつも、これなら大丈夫でしょう。

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;