oracle中带表达式的累积和

我只想:

SELECT to_char(INVC_DT, 'MON-YYYY') AS MONTH,
       SUM(INVC_AMT),
       SUM(SUM(INVC_AMT)) OVER (ORDER BY MIN(INVC_DT))
FROM T_INVC_INFO I INNER JOIN T_TASK_INFO T ON I.TASK_ID = T.TASK_ID
WHERE T.CNTRCT_ID = #session.user.cntrct_id#
GROUP BY to_char(INVC_DT, 'MON-YYYY')
ORDER BY MONTH DESC;

注意 ORDER BY 条款是按日期而不是按字母顺序排列的。

你可能也想要 ORDER BY MIN(INVC_DT) 最后的订单也是。

我将用另一个查询包装此查询,并使用sum的窗口变量:

SELECT   month, sum_month, SUM(sum_month) OVER (ORDER BY month DESC)
FROM     (SELECT     TO_CHAR(invc_dt, 'MON-YYYY') AS month,
                     SUM(invc_amt) AS sum_month
          FROM       t_invc_info i
          INNER JOIN t_task_info t ON i.task_id = t.task_id
          WHERE      t.cntrct_id = #session.user.cntrct_id#
          GROUP BY   TO_CHAR(invc_dt, 'MON-YYYY'))
ORDER BY month DESC