计算 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 实现这个结果。

有人能提出如何实现这一结果集的建议吗？

133475 次浏览
小开
最佳答案

基本上，你需要一个 窗口功能。这是现在的标准功能。除了真正的窗口函数外，你还可以在 Postgres 使用 任何聚合函数作为窗口函数，方法是附加一个 OVER子句。

这里的特殊困难在于正确分区和排序顺序:

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, ea_year, ea_month;

还有 没有 GROUP BY。

每一行的和是从分区的第一行到当前行计算出来的——或者引用 手册来确定:

默认的帧选项是 RANGE UNBOUNDED PRECEDING，即 与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同 ORDER BY，这将框架设置为 < strong > 分区中的所有行 通过当前行的最后一个 ORDER BY对等点启动

强调我的。 这是您所追求的累积(或“运行”)总和。

在默认的 RANGE模式下，排序顺序相同的行在此查询中是 「同侪」-相同的 (circle_id, ea_year, ea_month)。所有这些都显示相同的运算和，所有对等点都加到这个和中。但是我假设您的表是 (circle_id, ea_year, ea_month)上的 UNIQUE，那么排序顺序是确定的，没有行具有对等点。(你也可以使用更便宜的 ROWS模式。)

Postgres 11增加了新的 frame_exclusion选项包含/排除对等点的工具:

现在，ORDER BY ... ea_month< strong > 不能使用月份名称的字符串 .Postgres 将根据地区设置按字母顺序排序。

如果在表中存储了实际的 date值，则可以正确排序。如果没有，我建议在表中用 date类型的单列 the_date替换 ea_year和 ea_month。

  • 用 to_date()改变你所拥有的:

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

  • 为了便于显示，可以使用 to_char()获取原始字符串:

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

虽然被这个不幸的设计困住了，这个方法还是可行的:

SELECT ea_month, id, amount, ea_year, circle_id
, sum(amount) OVER (PARTITION BY circle_id ORDER BY the_date) AS cum_amt
FROM   (SELECT *, to_date(ea_year || ea_month, 'YYYYMonth') AS the_date FROM tbl) sub
ORDER  BY circle_id, mon;

提交答案