如何在oraclesql中显示表中的合计、运行中的合计和季度合计

Question

数据：

NAME AGE  SAL  MONTH
A    21   100  JAN
B    22   150  FEB
C    21   200  SEP
D    22   100  OCT
EMP  21   150  DEC

所需输出：：

1>正常和

NAME AGE  SAL  SUM
A    21   100  700
B    22   150  700
C    21   200  700
D    22   100  700
EMP  21   150  700

2>运行总和

NAME AGE  SAL  RUNNING_SUM
A    21   100  100
B    22   150  250
C    21   200  450
D    22   100  550
EMP  21   150  700

3>季度总

NAME AGE  SAL  MONTH  SUM
A    21   100  JAN    250
B    22   150  FEB    250
C    21   200  SEP    200
D    22   100  OCT    250
EMP  21   150  DEC    250

有人能帮我编写代码以实现上述结果吗。我也会很高兴的。

Answer 1

所有结果都可以通过使用SUM聚合函数和窗口函数来实现。

--Normal Sum
SELECT s.*, SUM (sal) OVER () AS SUM
FROM sum_data s;
--Running Sum
SELECT s.*, SUM (sal) OVER (ORDER BY name) AS SUM
FROM sum_data s;
--Quarterly Sum
SELECT s.*,
SUM (sal)
OVER (
PARTITION BY TO_CHAR (TO_DATE ('2022-' || s.month || '-1', 'YYYY-MON-DD'), 'Q'))    AS quarterly_SUM
FROM sum_data s;