报表统计的问题,请教一下:
需要根据CallType分别统计出 重复来电号码 以及 重复来电号码的来电数
数据如下:
ID CallType Mobile Date
1 1 13500000000 2009-10-21 15:31:00
2 1 13500000000 2009-10-21 15:32:00
3 1 13500000000 2009-10-21 15:32:00
4 2 13200000000 2009-10-21 15:32:00
5 2 13200000000 2009-10-21 15:32:00
6 3 13100000000 2009-10-21 15:32:00
7 2 13100000000 2009-10-21 15:33:00
8 5 13600000000 2009-10-21 15:35:00
9 5 13700000000 2009-10-21 15:35:00
10 2 13800000000 2009-10-21 15:50:00
11 2 13800000000 2009-10-21 15:50:00
12 2 13800000000 2009-10-21 15:50:00
13 2 13800000000 2009-10-21 15:50:00
14 2 13800000000 2009-10-21 15:50:00要求统计结果:
CallType 重复来电号码数 重复来电次数
1 1 3
2 2 7
需要根据CallType分别统计出 重复来电号码 以及 重复来电号码的来电数
数据如下:
ID CallType Mobile Date
1 1 13500000000 2009-10-21 15:31:00
2 1 13500000000 2009-10-21 15:32:00
3 1 13500000000 2009-10-21 15:32:00
4 2 13200000000 2009-10-21 15:32:00
5 2 13200000000 2009-10-21 15:32:00
6 3 13100000000 2009-10-21 15:32:00
7 2 13100000000 2009-10-21 15:33:00
8 5 13600000000 2009-10-21 15:35:00
9 5 13700000000 2009-10-21 15:35:00
10 2 13800000000 2009-10-21 15:50:00
11 2 13800000000 2009-10-21 15:50:00
12 2 13800000000 2009-10-21 15:50:00
13 2 13800000000 2009-10-21 15:50:00
14 2 13800000000 2009-10-21 15:50:00要求统计结果:
CallType 重复来电号码数 重复来电次数
1 1 3
2 2 7
count(1) 重复来电次数
from table1
group by calltype
有点问题,如果一个号码只有一天记录也会选出来。select calltype,count(distinct Mobile)重复来电号码数,
count(1) 重复来电次数
from (select calltype, Mobile, count(1) over(partition by Mobile, calltype) ctn from table1) where ctn > 1 group by calltype
也可以加个条件
select calltype,count(distinct Mobile)重复来电号码数,
count(1) 重复来电次数
from table1 a
where exists(
select 1 from table1
where id<>a.id and mobile=a.mobile and calltype=a.calltype)
group by calltype