求助 sum 语句在over之中的问题

Question

SELECT CUST_NBR,REGION_ID,YEAR, SUM(TOT_SALES) AS CUST_TOT,
SUM(SUM(TOT_SALES)) OVER(PARTITION BY REGION_ID,YEAR ORDER BY YEAR,REGION_ID) AS REGION_TOT
FROM ORDERS_TMP
GROUP BY REGION_ID,YEAR,CUST_NBR
ORDER BY YEAR,REGION_ID,CUST_NBR
这上面代码中有个问题，为什么要 SUM(SUM(TOT_SALES))，我认为over语句已经重新定义分组了，那么SUM(TOT_SALES) 就可以把region_id, year各个分组的情况计算出来了，如果不加，提示ORA_00979的错误，为什么啊？

Answer 1

ORA-00979 not a GROUP BY expressionCause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
如果不加SUM()的话

Answer 2

因为你前面已经出现了SUM(TOT_SALES)

Answer 3

SELECT CUST_NBR,REGION_ID,YEAR, SUM(TOT_SALES) AS CUST_TOT,
SUM(TOT_SALES) OVER(PARTITION BY REGION_ID,YEAR ORDER BY YEAR,REGION_ID) AS REGION_TOT
FROM ORDERS_TMP
GROUP BY REGION_ID,YEAR,CUST_NBR
ORDER BY YEAR,REGION_ID,CUST_NBR
上面语句报错，但是这里不是已经有一个sum了嘛...

Answer 4

内层SUM是针对分析函数的，外层SUM是GROUP BY的

Answer 5

感觉是语法冲突的问题。group by 要求除了聚组函数外的其他字段或表达式都要在group by后面出现。
但是分析函数是在结果集上再处理得到的，将其视为一个字段表达式（如果不是的话那么分析函数的查询都需要group by了），相当于是把一个含子循环的查询压缩到了一层。既然视为表达式，那么就要在group by后出现。但是放到group by 后面又会报错。。因为从本质上说分析函数那个是在结果集上再处理得到的，当然不能放到产生结果集的语句的group by中。所以需要再加个sum来躲过group by的形式检查。SQL> 
SQL> SELECT t.sws_dm,t.sws_mc,SUM(ry_dm) ,SUM(ry_dm)over(PARTITION BY sws_dm)
  2   FROM test  t
  3  GROUP BY t.sws_dm,t.sws_mc ;
 
SELECT t.sws_dm,t.sws_mc,SUM(ry_dm) ,SUM(ry_dm)over(PARTITION BY sws_dm)
 FROM test  t
GROUP BY t.sws_dm,t.sws_mc
 
ORA-00979: not a GROUP BY expression
 
SQL> 
SQL> SELECT t.sws_dm,t.sws_mc,SUM(ry_dm) ,sum(SUM(ry_dm))over(PARTITION BY sws_dm)
  2   FROM test  t
  3  GROUP BY t.sws_dm,t.sws_mc ;
 
SWS_DM     SWS_MC     SUM(RY_DM) SUM(SUM(RY_DM))OVER(PARTITIONB
---------- ---------- ---------- ------------------------------
1          第一税务所        510                            510
2          第二税务所       1010                           1010
3          第三税务所       1510                           1510
4          第四税务所       1610                           1610
5          第五税务所        500                            500
 
SQL> 
SQL> SELECT t.sws_dm,t.sws_mc,SUM(ry_dm)over(PARTITION BY sws_dm)
  2   FROM test  t
  3  ;
 
SWS_DM     SWS_MC     SUM(RY_DM)OVER(PARTITIONBYSWS_
---------- ---------- ------------------------------
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
4          第四税务所                           1610
4          第四税务所                           1610
4          第四税务所                           1610
4          第四税务所                           1610
5          第五税务所                            500
 
20 rows selected
 
SQL> 

Answer 6

感觉是语法冲突的问题。group by 要求除了聚组函数外的其他字段或表达式都要在group by后面出现。
但是分析函数是在结果集上再处理得到的，将其视为一个字段表达式（如果不是的话那么分析函数的查询都需要group by了），相当于是把一个含子循环的查询压缩到了一层。既然视为表达式，那么就要在group by后出现。但是放到group by 后面又会报错。。因为从本质上说分析函数那个是在结果集上再处理得到的，当然不能放到产生结果集的语句的group by中。所以需要再加个sum来躲过group by的形式检查。
写错了，应该是子查询

Answer 7

那问一下上面这个sql的执行顺序问题，
先计算外层的sum还是里层的sum？
外层的sum按照region_id, year 分组吗？
里层的sum按照region_id,year,cust_nbr分组吗？

Answer 8

我对这句话有怀疑，我觉得内层sum是针对group by, 外层sum是针对 分析函数的。下面是我测试的代码
create table orders_tmp
(CUST_NBR NUMBER(5) NOT NULL,
 REGION_ID NUMBER(5) NOT NULL,
 SALESPERSON_ID NUMBER(5) NOT NULL,
 YEAR NUMBER(4) NOT NULL,
 MONTH NUMBER(2) NOT NULL,
 TOT_ORDERS NUMBER(7) NOT NULL,
 TOT_SALES NUMBER(11,2) NOT NULL);INSERT INTO orders_tmp VALUES('11','7',' 11 ','2001 ','7','2','12204 ') ;
INSERT INTO orders_tmp VALUES(' 4','5','4 ','2001 ',' 10','2','37802 ') ;
INSERT INTO orders_tmp VALUES(' 7','6','7 ','2001 ','2','3',' 3750 '  ) ;
INSERT INTO orders_tmp VALUES('10','6','8 ','2001 ','1','2','21691 '  ) ;
INSERT INTO orders_tmp VALUES('10','6','7 ','2001 ','2','3','42624 '  ) ;
INSERT INTO orders_tmp VALUES('15','7',' 12 ','2000 ','5','6',' 24 '  ) ;
INSERT INTO orders_tmp VALUES('12','7','9 ','2000 ','6','2','50658 '  ) ;
INSERT INTO orders_tmp VALUES(' 1','5','2 ','2000 ','3','2','44494 '  ) ;
INSERT INTO orders_tmp VALUES(' 1','5','1 ','2000 ','9','2','74864 '  ) ;
INSERT INTO orders_tmp VALUES(' 2','5','4 ','2000 ','3','2','35060 '  ) ;
INSERT INTO orders_tmp VALUES(' 2','5','4 ','2000 ','4','4',' 6454 '  ) ;
INSERT INTO orders_tmp VALUES(' 2','5','1 ','2000 ',' 10','4','35580' ) ;
INSERT INTO orders_tmp VALUES(' 4','5','4 ','2000 ',' 12','2','39190' ) ;SELECT CUST_NBR,REGION_ID,YEAR, SUM(TOT_SALES) AS CUST_TOT,
avg((sum(TOT_SALES)) OVER(PARTITION BY REGION_ID,YEAR ORDER BY YEAR,REGION_ID) AS REGION_TOT,
ROUND(RATIO_TO_REPORT(SUM(TOT_SALES)) OVER (PARTITION BY REGION_ID,YEAR),3) * 100 ||'%'  AS CUST_SALES_RATIO
FROM ORDERS_TMP
GROUP BY REGION_ID,YEAR,CUST_NBR
ORDER BY YEAR,REGION_ID,CUST_NBR

Answer 9

如果是针对group by的，那这个句子就不会有结果了，因为语法错误。
SQL> SELECT t.sws_dm,t.sws_mc,SUM(ry_dm)over(PARTITION BY sws_dm)
  2   FROM test  t
  3  ;
 
SWS_DM     SWS_MC     SUM(RY_DM)OVER(PARTITIONBYSWS_
---------- ---------- ------------------------------
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
1          第一税务所                            510
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
2          第二税务所                           1010
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
3          第三税务所                           1510
4          第四税务所                           1610
4          第四税务所                           1610
4          第四税务所                           1610
4          第四税务所                           1610
5          第五税务所                            500
 
20 rows selected
 
SQL> 

Answer 10

经过测试，这位仁兄说的对，分析函数是正对外层的
不过有个语法错误 ：avg((sum(TOT_SALES)) 改为avg((sum(TOT_SALES))）

Answer 11

tangren说反了，这里内层是对group by的，外层是分析函数，不是分组函数。因此不能去掉其一
这是将分析函数套在分组查询里，稍微改一下，应该也正确，但不那么容易造成误解SELECT CUST_NBR,max(REGION_ID),YEAR, SUM(TOT_SALES) AS CUST_TOT,
SUM(SUM(TOT_SALES)) OVER(PARTITION BY max(REGION_ID),YEAR ORDER BY YEAR,max(REGION_ID)) AS REGION_TOT
FROM ORDERS_TMP
GROUP BY YEAR,CUST_NBR
ORDER BY YEAR,CUST_NBR

Answer 12

恩，的确，里面的sum是group by的，外面的sum是分析函数的

Answer 13

顺序应该是这样的。先执行
SELECT CUST_NBR,REGION_ID,YEAR, SUM(TOT_SALES) AS CUST_TOT,
SUM(TOT_SALES) AS REGION_TOT
FROM ORDERS_TMP
GROUP BY REGION_ID,YEAR,CUST_NBR
ORDER BY YEAR,REGION_ID,CUST_NBR
;
然后对上面selelct出的结果，再按REGION_ID,YEAR分组同sum（REGION_TOT），然后得到结果。
两点：1.分析函数是在group by的基础上再次统计。分析函数分组时partition by后面的字段只能在CUST_NBR,REGION_ID,YEAR中选择。
      2.外层的sum是分析函数使用的，内层的sum是group by使用的。这跟sql处理的顺序是一致的。

Answer 14

更正下，SUM(TOT_SALES)也是可以出现在分析函数partition by，order by等地方。