分组统计
请各位高手提供一下思路,我想按DialDuration(电话时长)来分组统计每个用户拨打的电话量.分组的原则是按DialDuration 10秒做一个级别往上递增.(电话时间长10s的有多少个电话,为20s的有了多少个电话....).
Ext EplName DialDuration
------ -------------------------------------------------- ------------
652 用户1 00:01:34
865 用户2 00:00:12
865 用户2 00:02:09
877 用户3 00:00:11
865 用户2 00:02:27
877 用户3 00:00:19
887 用户4 00:01:02
652 用户1 00:01:28
871 用户5 00:00:15
877 用户3 00:00:39
877 用户3 00:00:13
877 用户3 00:00:14
871 用户5 00:00:32
877 用户3 00:00:34
877 用户3 00:00:19
871 用户5 00:00:57
877 用户3 00:00:35
想要得到结果就是
ext eplName DialDuration count
877 用户3 10 2
877 用户3 20 10
[解决办法]
create table A (Ext int,EplName varchar(10),DialDuration datetime)
insert into A values(652, '用户1 ', '00:01:34 ')
insert into A values(865, '用户2 ', '00:02:09 ')
insert into A values(865, '用户2 ', '00:00:12 ')
go
select * from
(
select Ext,EplName,b.m , sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) > =m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration) <n then 1 else 0 end) [count] from a,
(select 0 m,9 n
union all select 10 , 19
union all select 20 , 29
union all select 30 , 39
union all select 40 , 49
union all select 50 , 59
union all select 60 , 69
union all select 70 , 79
union all select 80 , 89
union all select 90 , 100
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 90 1
865 用户2 10 1
（所影响的行数为 2 行）
*/
[解决办法]
--由于你按固定时间统计，临时表可只用一列，每列加十为终点值。
create table A (Ext int,EplName varchar(10),DialDuration datetime)
insert into A values(652, '用户1 ', '00:01:34 ')
insert into A values(865, '用户2 ', '00:00:12 ')
insert into A values(865, '用户2 ', '00:02:09 ')
insert into A values(877, '用户3 ', '00:00:11 ')
insert into A values(865, '用户2 ', '00:02:27 ')
insert into A values(877, '用户3 ', '00:00:19 ')
insert into A values(887, '用户4 ', '00:01:02 ')
insert into A values(652, '用户1 ', '00:01:28 ')
insert into A values(871, '用户5 ', '00:00:15 ')
insert into A values(877, '用户3 ', '00:00:39 ')
insert into A values(877, '用户3 ', '00:00:13 ')
insert into A values(877, '用户3 ', '00:00:14 ')
insert into A values(871, '用户5 ', '00:00:32 ')
insert into A values(877, '用户3 ', '00:00:34 ')
insert into A values(877, '用户3 ', '00:00:19 ')
insert into A values(871, '用户5 ', '00:00:57 ')
insert into A values(877, '用户3 ', '00:00:35 ')
go
select * from
(
select Ext,EplName,b.m ,
sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) > =m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration) <(m+10) then 1 else 0 end) [count]
from a,
(select 0 m
union all select 10
union all select 20
union all select 30
union all select 40
union all select 50
union all select 60
union all select 70
union all select 80
union all select 90
union all select 100
union all select 110
union all select 120
union all select 130
union all select 140
union all select 150
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 80 1
652 用户1 90 1
865 用户2 10 1
865 用户2 120 1
865 用户2 140 1
871 用户5 10 1
871 用户5 30 1
871 用户5 50 1
877 用户3 10 5
877 用户3 30 3
887 用户4 60 1
（所影响的行数为 11 行）
*/
