如何在oracle中进行累计求和

qhhrdooz 于 2023-02-18 发布在 Oracle

关注(0)|答案(2)|浏览(200)

我是新的SQL，我想做一个报告，显示每天的票数每班，也是迄今为止的总数。
下面是我的查询，显示了下面的前5列：

SELECT 
    TO_CHAR(DTTM,'YYYY-MM-DD') as "DATE"
    ,COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '14:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "DAYS"
    ,COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '06:00' AND '14:00' THEN TKTNUM ELSE NULL END) AS "MIDS"
    ,COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') NOT BETWEEN '06:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "SWINGS"
    ,COUNT(TKTNUM) AS "TOTAL"
    FROM TKTHISTORY
    GROUP BY TO_CHAR(DTTM,'YYYY-MM-DD')
    ORDER BY TO_CHAR(DTTM,'YYYY-MM-DD')

DATE        DAYS    MIDS    SWINGS  TOTAL   
2019-08-01  8       13      1       22      22
2019-08-02  19      5       3       27      49
2019-08-03  23      6       6       35      84
2019-08-04  7       9       13      29      113
2019-08-05  4       17      2       23      136
2019-08-06  10      5       16      31      167
2019-08-07  3       12      11      26      193

第6列应该是日期的累计和，我试着上网看过“over”和“partition by”，但还是不知道怎么用：（

oracle

来源：https://stackoverflow.com/questions/58776933/how-to-do-cumulative-sums-in-oracle

2条答案

按热度按时间

e5nszbig1#

下面是一个基于Scott的EMP表的示例，该表按部门计算工作。最后一列是“运行总数”值。
样本数据显示DEPTNO = 10中有3个员工，其中5个在部门20中，6个在部门30中：

SQL> select deptno, empno, ename from emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

查询如下所示：

SQL> select
  2    deptno,
  3    count(empno) emps_per_dept,
  4    sum(count(*)) over (order by deptno) total
  5  from emp
  6  group by deptno;

    DEPTNO EMPS_PER_DEPT      TOTAL
---------- ------------- ----------
        10             3          3
        20             5          8
        30             6         14

SQL>

在你的例子中，可能是这样的：

SELECT 
   ...
  ,sum(COUNT(TKTNUM)) over (order by TO_CHAR(DTTM,'YYYY-MM-DD')) AS "TOTAL"
FROM TKTHISTORY
...

赞(0）回复(0）举报 2023-02-18

azpvetkf2#

SELECT t.user_id,
       t.transactions_,
       SUM(t.transactions_) over(ORDER BY t.user_id) cum_sum
FROM FEBRUARY_2023_USER_ACTIVITIES t

赞(0）回复(0）举报 2023-02-18

我来回答

如何在oracle中进行累计求和

2条答案

相关问题

热门标签

最新问答