sql - Oracle 窗口函数求和

Question

谁能告诉我在 oracle 分区中 sum (SUM(SUM(VAL))) 中 sum 的意义是什么，在下面的代码片段中

SELECT NMS,
       SUM(SUM(VAL)) OVER(PARTITION BY NMS),  ---- SIGNIFICANCE
       EMPNO,
       SUM(SUM(VAL)) OVER(PARTITION BY EMPNO)   ---- SIGNIFICANCE
  FROM PART_TEST_PART
 GROUP BY NMS, EMPNO;

Answer 1

它计算每个 NMS(对于第一个)和 EMPNO(对于第二个)的 VAL 的总和。

首先评估 group by + SUM(VAL)，然后根据该结果进行分析计算。

例如如果你在 group by 之后得到结果

EMPNO  NMS SUM(VAL)
1      a   15
1      b   24
2      a   50

SUM(SUM(VAL)) OVER(PARTITION BY NMS)

EMPNO  NMS SUM(VAL) SUM(SUM(VAL)) OVER(PARTITION BY NMS)
1      a   15       65  (15+50 for NMS = a)
1      b   24       24  (24 for NMS = b)
2      a   50       65  (15+50 for NMS = a)

SUM(SUM(VAL)) OVER(PARTITION BY EMPNO)

EMPNO  NMS SUM(VAL) SUM(SUM(VAL)) OVER(PARTITION BY EMPNO)
1      a   15       39  (15+24 for EMPNO = 1)
1      b   24       39  (15+24 for EMPNO = 1)
2      a   50       50  (50 for EMPNO = 2)