使用.. over (partition by..)遇到的问题

Question

大家好！在使用over时候遇到一个记录重复的问题。
a表如下：                      
well_id zone_name                
a a001
a a002
a a003b表如下：
well_Id zone_id zone_name  val
a 1 a001    10
a 2 a001    20
a 3 a001     5
a 4 a003     3
a 5 a003     8
a 6 a003    11
a 7 a002    12
运行下面的sql，出现重复记录的问题。
select a.well_id,a.zone_name,
count(b.zone_id) over (partition by a.well_id,a.zone_name),
sum(b.val) over (partition by a.well_id,a.zone_name)
 from count_test1 a left join count_test2 b on a.well_id = b.well_id 
and a.zone_name = b.zone_name查询结果如下：
well_id zone_name amount summation
a a001           3 35
a a001           3 35
a a001           3 35
a a002           1 12
a a003           3 22
a a003           3 22
a a003           3 22请问为什么会出现重复的记录呢？

Answer 1

肯定会重复的呀，因为B表1，2，3记录数分别是3，1，3,所以对于A表中的1，匹配3次，2，匹配1次，3匹配3次。总共是7次，而没有使用group by 分组，使用的是分析函数，分析函数的效果是加在每一条记录上面的，它不会减少记录数。这里应该使用group by 
select  a.well_id,a.zone_name,count(b.zone_id),sum(b.val)
from count_test1 a left join count_test2 b on a.well_id = b.well_id 
and a.zone_name = b.zone_name 
group by a.well_id,a.zone_name;

Answer 2

...分析函数不是聚合函数
要去重复select 后可以加个distinct
如果不要a.well_id,a.zone_name重复的话
group by+聚合就好了，不要用分析函数

Answer 3

with count_test1 as(
select 'a' well_id,'a001' zone_name from dual
union all                
select'a', 'a002' from dual
union all  
select'a' ,'a003' from dual),
count_test2 as 
(
select 'a' well_Id ,1 zone_id ,'a001' zone_name ,10  val from dual
union all     
select 'a', 2 ,'a001' ,  20 from dual
union all  
select 'a' ,3 ,'a001'  ,   5 from dual
union all  
select 'a', 4, 'a003'  ,   3 from dual
union all  
select 'a', 5 ,'a003'   ,  8 from dual
union all  
select 'a' ,6, 'a003'  , 11 from dual
union all  
select 'a', 7, 'a002'  , 12 from dual
)
select a.well_id,a.zone_name, 
count(b.zone_id) , 
sum(b.val) 
/*from count_test1 a left join count_test2 b on a.well_id = b.well_id */
from count_test1 a ,count_test2 b 
where a.well_id = b.well_id 
and a.zone_name = b.zone_name 
group by a.well_id,a.zone_name
结果
a a001 3 35
a a002 1 12
a a003 3 22

Answer 4

select a.well_id,a.zone_name, B.val
count(b.zone_id) over (partition by a.well_id,a.zone_name), 
sum(b.val) over (partition by a.well_id,a.zone_name) 
from count_test1 a left join count_test2 b on a.well_id = b.well_id 
and a.zone_name = b.zone_name 加个B.val估计你就容易理解了

Answer 5

少了个,
select a.well_id,a.zone_name, B.val ,
count(b.zone_id) over (partition by a.well_id,a.zone_name), 
sum(b.val) over (partition by a.well_id,a.zone_name) 
from count_test1 a left join count_test2 b on a.well_id = b.well_id 
and a.zone_name = b.zone_name 

Answer 6

原来是分析函数不会达到聚合的效果。多谢。
1、如果在某种情况下只能用分析函数，而不能使用group by。
2、得出的结果没有重复记录。是否必须在select后面加上distinct ，这样似乎对查询的性能有影响了

Answer 7

是的，可以用distinct，也可以使用row_number() 函数在算聚合结果的时候排下序，然后只取第一条记录，就可以避免使用distinct

Answer 8

select * from ().. where row_num = 1 请问是不是这种方式来返回结果的第一行记录呢？

Answer 9

select *
from 
(select a.well_id,
       a.zone_name, 
       count(b.zone_id) over (partition by a.well_id,a.zone_name) cn, 
       sum(b.val) over (partition by a.well_id,a.zone_name) su,
       row_number() over(partition by a.well_id,a.zone_name) rn
from   count_test1 a left join count_test2 b 
on     a.well_id     = b.well_id 
and    a.zone_name   = b.zone_name
)
where rn = 1;
在这里row_number不能当条件用的