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

qykong1986 2010-07-29 10:04:17 


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的错误，为什么啊？
...全文
234 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
minitoy 2010-07-30
  • 打赏
  • 举报
 回复
更正下，SUM(TOT_SALES)也是可以出现在分析函数partition by，order by等地方。
qykong1986 2010-07-30
  • 打赏
  • 举报
 回复
谢谢各位的解释了
qykong1986 2010-07-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 tangren 的回复:]
内层SUM是针对分析函数的，外层SUM是GROUP BY的
[/Quote]

我对这句话有怀疑，我觉得内层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

qykong1986 2010-07-29
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 minitoy 的回复:]
感觉是语法冲突的问题。group by 要求除了聚组函数外的其他字段或表达式都要在group by后面出现。
但是分析函数是在结果集上再处理得到的，将其视为一个字段表达式（如果不是的话那么分析函数的查询都需要group by了），相当于是把一个含子循环的查询压缩到了一层。既然视为表达式，那么就要在group by后出现。但是放到group by 后面又会报错。。。。。因为从本质上说分析函数那个是……
[/Quote]

那问一下上面这个sql的执行顺序问题，
先计算外层的sum还是里层的sum？
外层的sum按照region_id, year 分组吗？
里层的sum按照region_id,year,cust_nbr分组吗？
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
感觉是语法冲突的问题。group by 要求除了聚组函数外的其他字段或表达式都要在group by后面出现。
但是分析函数是在结果集上再处理得到的，将其视为一个字段表达式（如果不是的话那么分析函数的查询都需要group by了），相当于是把一个含子循环的查询压缩到了一层。既然视为表达式，那么就要在group by后出现。但是放到group by 后面又会报错。。。。。因为从本质上说分析函数那个是在结果集上再处理得到的，当然不能放到产生结果集的语句的group by中。所以需要再加个sum来躲过group by的形式检查。
写错了，应该是子查询
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
感觉是语法冲突的问题。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>
ks_reny 2010-07-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 tangren 的回复:]
内层SUM是针对分析函数的，外层SUM是GROUP BY的
[/Quote]
正解
tangren 2010-07-29
  • 打赏
  • 举报
 回复
内层SUM是针对分析函数的，外层SUM是GROUP BY的
qykong1986 2010-07-29
  • 打赏
  • 举报
 回复 


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了嘛...
真的不知道取什么名 2010-07-29
  • 打赏
  • 举报
 回复
因为你前面已经出现了SUM(TOT_SALES)
cosio 2010-07-29
  • 打赏
  • 举报
 回复
ORA-00979 not a GROUP BY expression

Cause: 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()的话
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
顺序应该是这样的。先执行
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处理的顺序是一致的。
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
我也有点晕了，测试下去
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
恩，的确，里面的sum是group by的，外面的sum是分析函数的
小灰狼W 2010-07-29
  • 打赏
  • 举报
 回复
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
zjhiphop2006 2010-07-29
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 qykong1986 的回复:]

引用 4 楼 tangren 的回复:
内层SUM是针对分析函数的，外层SUM是GROUP BY的


我对这句话有怀疑，我觉得内层sum是针对group by, 外层sum是针对 分析函数的。下面是我测试的代码SQL code

create table orders_tmp
(CUST_NBR NUMBER(5) NOT NULL,
REGION_ID NUMBER(5) NOT……
[/Quote]
经过测试，这位仁兄说的对，分析函数是正对外层的
不过有个语法错误 ：avg((sum(TOT_SALES)) 改为avg((sum(TOT_SALES))）
minitoy 2010-07-29
  • 打赏
  • 举报
 回复
如果是针对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>
Oracle row_number()分析函数，over（）开窗函数
在做系统选厂时，需要按比价单（com_no）、件号（pn）分组进行比价，选出厂家报价折算金额（convert_usd）最低，折算金额相同的情况下，选择交货期（delivery_day）最短的记录 。之前虽然想到了group by order by 之类的，但都不好分组进行内部排序，求助了同事后，同事帮写的SQL实现语句如下：  select *   from (   select row_nu
简单英译汉SQL脚本
create table t_dictionary(word varchar(200), trans varchar(1000)); insert into t_dictionary values('abandon','v.抛弃，放弃'); insert into t_dictionary values('abandonment','n.放弃'); insert into t_dictionary
CREATE VIEW SQL：在SQL Server中使用索引视图
This is the fourth article in a series of learning the CREATE VIEW SQL statement. So far, we have done a great deal of creating and altering views using T-SQL. In this last part, I want to take ...
oracle日常运维及应急故障处理手册
常见问题及处理方案 CPU使用率高的问题 通过操作系统命令toptopas glance等查看top进程号，确认是系统进程还是oracle应用进程，查询当前top进程执行的操作和sql语句进行分析。   根据进程号获取正在执行的sql   SELECT a.osuser, a.username,b.address,b.hash_value, b.sql_text fromv$sessi
java较全的面试题
一、Java基础部分 1. JAVA的基本数据类型有哪些 ? String 是不是基本数据类型 ? 2. 一个".java"源文件中是否可以包括多个类（不是内部类）？有什么限制？ 3. Java有没有goto? 4. 说说&和&&的区别. 5. 在JAVA中如何跳出当前的多重嵌套循环？ 6. switch语句能否作用在byte上，能否作用在long上，能否作用在Strin...
Oracle

17,082

社区成员

55,236

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章