表中有字段dfhm(对方号码),thsc(通话时长),ID
要求统计出所有记录中,和哪些人的通话中有60%以上的通话时间不足5秒的对方号码,总通话次数,不足5秒的次数 ,比率,平均通话时长
查询结果字段如下: 对方号码 总次数 超短次数 比率 平均时长
一下是SQL的设计,从分解完成到最终完成
select t.DFHM ,count(id) from T_ANALYS_PHONE_DETAIL t
group by dfhm;
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5;
select dfhm,count(id) 超短次数 from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm;
select t.DFHM ,count(t.id) 总次数 from T_ANALYS_PHONE_DETAIL t
where t.dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm;
select zctj.d 对方号码,zctj.cou 总次数,cdtj.cou 超短次数 from
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm
)zctj,
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)cdtj
where zctj.d=cdtj.d;
select zctj.d 对方号码,zctj.cou 总次数,cdtj.cou 超短次数 from
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm
)zctj,
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)cdtj
where zctj.d=cdtj.d and cdtj.cou/zctj.cou>=0.6;
select zctj.d 对方号码,zctj.cou 总次数,cdtj.cou 超短次数,cdtj.cou/zctj.cou 比率 from
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm
)zctj,
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)cdtj
where zctj.d=cdtj.d and cdtj.cou/zctj.cou>=0.6;
select zctj.d 对方号码,zctj.cou 总次数,cdtj.cou 超短次数,cdtj.cou/zctj.cou 比率,zctj.thsc/zctj.cou 平均时长 from
(
select dfhm d,count(id) cou,sum(thsc) thsc from T_ANALYS_PHONE_DETAIL
where dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm
)zctj,
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)cdtj
where zctj.d=cdtj.d and cdtj.cou/zctj.cou>=0.6;
select zctj.d 对方号码,zctj.cou 总次数,cdtj.cou 超短次数,round(cdtj.cou/zctj.cou,2) 比率,round(zctj.thsc/zctj.cou,1) 平均时长 from
(
select dfhm d,count(id) cou,sum(thsc) thsc from T_ANALYS_PHONE_DETAIL
where dfhm in(
select dfhm from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)
group by dfhm
)zctj,
(
select dfhm d,count(id) cou from T_ANALYS_PHONE_DETAIL
where thsc<5
group by dfhm
)cdtj
where zctj.d=cdtj.d and cdtj.cou/zctj.cou>=0.6;