Oracle每月滚动或运行总计 [英] Oracle Rolling or Running totals by month

问题描述

目标:每个月末所有报表的滚动/运行总计.

Goal: Rolling/Running total for all statements at the end of each month.

代码:

select 
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM') AS MNTH, 
    COUNT(ACTHX.INVOICE) as STMTS
from ACTHX
group by
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM')
ORDER BY
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM')

当前输出:

MNTH        STMTS
7/1/2009    1
10/1/2010   4
6/1/2011    26
9/1/2011    211
10/1/2011   163
11/1/2011   119

所需的输出:

MNTH        STMTS
7/1/2009    1
10/1/2010   5
6/1/2011    31
9/1/2011    242
10/1/2011   405
11/1/2011   524

推荐答案

这听起来像是您需要总运行量.您可以为此使用解析SUM函数.

It sounds like you want the running total. You can use the analytic SUM function for that.

select mnth,
       sum(stmts) over (order by mnth) stmts
  from (select TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM') AS MNTH, 
               COUNT(ACTHX.INVOICE) as STMTS
          from ACTHX
         group by TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM'))
 ORDER BY mnth

这篇关于Oracle每月滚动或运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！