【经验积累】SQL语句

 表中有字段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;