用SQL语句实现如下格式的统计


usr  (坐席表)
usrid    varchar2(10)   话务员ID
name     varchar2(20)   话务员姓名
password varchar2(10)   密码
acdgroup varchar2(10)   所在的分组  如7500  7501grp 工作组 表
grpid     varchar2(10)  工作组ID
grpname   varchar2(20)  工作名  如7500  7501....
report_call_hist （进线表）
  DATETIME        VARCHAR2(30) not null,   进线时间
  CALLID          VARCHAR2(30) not null, 
  TYPE            VARCHAR2(10),     ACD组
  CALLTYPE        VARCHAR2(10),     呼叫类型 如：out(呼出) in （呼入）
  CALLRESULT      VARCHAR2(10),     呼叫结果 如：ABDN 放弃 ACTV接入
  GROUPID         VARCHAR2(20),   
  AGENTID         VARCHAR2(20),
  AGENTDN         VARCHAR2(20),
  AGENTTN         VARCHAR2(20),
  WAITSPAN        NUMBER(10,2),
  TALKSPAN        NUMBER(10,2),
  ACWSPAN         NUMBER(10,2),
  ANI             VARCHAR2(30),
  DNIS            VARCHAR2(30),
  DNCOUNT         VARCHAR2(10),
  ISIVR           VARCHAR2(1),
  ISQUEUE         VARCHAR2(1),
  ISAGENT         VARCHAR2(1),
  AGENTANSWERSPAN NUMBER(10,2)
orderhist  订单表
orderid    订单ID
crusr      登录坐席名称
mailtype   快递方式 9 (宅急送)  2 （邮购）
crdt       date   订购日期  
status     订单状态 现在要根据日期和坐席ID 统计 该坐席这段时间内每一天的接听数，未接听数，等等。。如：选择从 2010-01-01 到  2010-02-01 ，坐席ID为164 的坐席 每一天的接听数，未接听数，订单数量。
格式：
日期     坐席编号             姓名                接听数     未接听数         订单数量。。
01-01    164                  张五                12          2               2     
01-02    164                  张五                67          3               10。01-31    164                  张五                67          3               10
如果所选时间不足1个月，如：2010-01-01 到  2010-01-21  那么就只显示1-21号 每一天的详细信息 
原SQL如下：select t.usrid,t.name,t1.actv,t1.abdn,round(decode(t1.actv,0,0,t1.ztalk/t1.actv),1),
t1.ztalk,t1.stalk,t1.sltalk,t2.em,t2.post,t2.sm,t2.ps,t2.dds,t2.cgdds,
round(decode(t2.dds,0,0.00,t2.cgdds/t2.dds),4) ddcgl,
round((decode((nvl(t1.actv,0)+nvl(t1.abdn,0)),0,0.00,t2.dds/(nvl(t1.actv,0)+nvl(t1.abdn,0)))),4) jxcgl,
round((decode((nvl(t1.actv,0)+nvl(t1.abdn,0)),0,0.00,t2.cgdds/(nvl(t1.actv,0)+nvl(t1.abdn,0)))),4) sxcgl
from
(select usr.usrid usrid,usr.name name
   from usr,grp
   where usr.defgrp=grp.grpid and grp.grpname = (case when '^4'='%' or '^4' is null then grp.grpname else '^4' end)
   and usr.acdgroup = (case when '^3'='%' or '^3' is null then usr.acdgroup else '^3' end)
) t,
(select a.agentid usrid,count(*) num,
   sum(case when upper(callresult)='ACTV' then 1 else 0 end) actv,
   sum(case when upper(callresult)='ABDN' then 1 else 0 end) abdn,
   sum(case when upper(callresult)='ACTV' then nvl(a.talkspan,0) else 0 end) ztalk,
   sum(case when upper(callresult)='ACTV' and (nvl(a.talkspan,0))<6 then 1 else 0 end) stalk,
   sum(case when upper(callresult)='ACTV' and (nvl(a.talkspan,0))<30 then 1 else 0 end) sltalk
   from report_call_hist a
   where to_date(a.datetime,'yyyy-mm-dd hh24:mi:ss') between to_date('^1','yyyy-mm-dd hh24:mi:ss')
   and to_date('^2','yyyy-mm-dd hh24:mi:ss')
   and calltype='IN' 
   and a.agentid is not null
   and a.isivr='N'
   and groupid is not null
   and groupid = (case when '^3'='%' or '^3' is null then groupid else '^3' end)
   group by a.agentid
) t1,
(select a.crusr usrid,count(a.orderid) dds,sum(decode(a.status,5,1,0)) cgdds,
   sum(case when mailtype = '3' then 1 else 0 end) em,--欢乐送
   sum(case when mailtype = '2' then 1 else 0 end) post,--邮购
   sum(case when mailtype = '9' then 1 else 0 end) sm,--宅急送
   sum(case when mailtype not in ('2','3','9') then 1 else 0 end) ps--其他送货
   from orderhist a,usr b
   where a.crusr=b.usrid
   and a.crdt between to_date('^1','yyyy-mm-dd HH24:mi:ss') 
   and to_date('^2','yyyy-mm-dd HH24:mi:ss')
   and a.status <> 0
   and b.acdgroup = (case when '^3'='%' or '^3' is null then b.acdgroup else '^3' end)
   group by a.crusr
) t2
where t.usrid = t1.usrid(+)
and t.usrid = t2.usrid(+)
and (t1.actv+t1.abdn <> 0 or t2.dds <> 0)

解决方案 »

  1.   

    原SQL语句得出的结果不对吗？
      

  2.   

    顶一下 原SQL语句统计的格式如下：坐席编号 姓名 接听数 未接听数 平均时长(秒) 工作时长(秒) 短呼叫(6秒) 短呼叫(30秒) 欢乐送 邮购 宅急送 其他送货 订单数量 成功订单数 订单成功率 进线成功率 实现成功率
    123 张五 0 0 0 1 1 0 0.00% 0.00% 0.00%
    124 小李 22 7 65.5 1440.55 0 8
    125 李四 0 0 0 1 1 0 0.00% 0.00% 0.00%
    之前统计的是某段时间内 所有坐席的接听数，未接听数。。，想在要统计某段时间内某个坐席每一天的接听数，未接听数。。
      

  3.   

    主要是查询的条件有变 ，原SQL语句中 像
    (select a.agentid usrid,count(*) num,
       sum(case when upper(callresult)='ACTV' then 1 else 0 end) actv,
       sum(case when upper(callresult)='ABDN' then 1 else 0 end) abdn,
       sum(case when upper(callresult)='ACTV' then nvl(a.talkspan,0) else 0 end) ztalk,
       sum(case when upper(callresult)='ACTV' and (nvl(a.talkspan,0))<6 then 1 else 0 end) stalk,
       sum(case when upper(callresult)='ACTV' and (nvl(a.talkspan,0))<30 then 1 else 0 end) sltalk
       from report_call_hist a
       where to_date(a.datetime,'yyyy-mm-dd hh24:mi:ss') between to_date('^1','yyyy-mm-dd hh24:mi:ss')
       and to_date('^2','yyyy-mm-dd hh24:mi:ss')
       and calltype='IN' 
       and a.agentid is not null
       and a.isivr='N'
       and groupid is not null
       and groupid = (case when '^3'='%' or '^3' is null then groupid else '^3' end)
       group by a.agentid
    ) t1,
    (select a.crusr usrid,count(a.orderid) dds,sum(decode(a.status,5,1,0)) cgdds,
       sum(case when mailtype = '3' then 1 else 0 end) em,--欢乐送
       sum(case when mailtype = '2' then 1 else 0 end) post,--邮购
       sum(case when mailtype = '9' then 1 else 0 end) sm,--宅急送
       sum(case when mailtype not in ('2','3','9') then 1 else 0 end) ps--其他送货
       from orderhist a,usr b
       where a.crusr=b.usrid
       and a.crdt between to_date('^1','yyyy-mm-dd HH24:mi:ss') 
       and to_date('^2','yyyy-mm-dd HH24:mi:ss')
       and a.status <> 0
       and b.acdgroup = (case when '^3'='%' or '^3' is null then b.acdgroup else '^3' end)
       group by a.crusr
    ) t2等应该不需要改动
      

  4.   

    to_date(a.datetime,'yyyy-mm-dd hh24:mi:ss') between to_date('^1','yyyy-mm-dd hh24:mi:ss')
       and to_date('^2','yyyy-mm-dd hh24:mi:ss')
    这种是什么条件？to_date('^1','yyyy-mm-dd hh24:mi:ss')中的'^1'是什么意思？不是很明白
      

  5.   


    select crdt,usrid,name,sum(actv),sum(abdn),sum(dss)
    from
    (
    select t1.crdt,t.usrid,t.name,t1.actv,t1.abdn,0 as dds
    from
    (select usr.usrid usrid,usr.name name
       from usr,grp
       where usr.defgrp=grp.grpid and grp.grpname = (case when '^4'='%' or '^4' is null then grp.grpname else '^4' end)
       and usr.acdgroup = (case when '^3'='%' or '^3' is null then usr.acdgroup else '^3' end)
    ) t,
    (select a.agentid usrid,a.datetime,
       sum(case when upper(callresult)='ACTV' then 1 else 0 end) actv,
       sum(case when upper(callresult)='ABDN' then 1 else 0 end) abdn
       from report_call_hist a
       where to_date(a.datetime,'yyyy-mm-dd hh24:mi:ss') between to_date('^1','yyyy-mm-dd hh24:mi:ss')
       and to_date('^2','yyyy-mm-dd hh24:mi:ss')
       and calltype='IN' 
       and a.agentid is not null
       and a.isivr='N'
       and groupid is not null
       and groupid = (case when '^3'='%' or '^3' is null then groupid else '^3' end)
       group by a.agentid,a.datetime
    ) t1
    where t.usrid = t1.usrid and t1.actv+t1.abdn <> 0
    union all
    select a.crdt,a.crusr usrid,b.name,a.crdt,0 as actv,0 as abdn,count(a.orderid) dds
    from orderhist a,usr b,grp c
    where a.crusr=b.usrid
       and a.crdt between to_date('^1','yyyy-mm-dd HH24:mi:ss') 
       and to_date('^2','yyyy-mm-dd HH24:mi:ss')
       and a.status <> 0
       and b.acdgroup = (case when '^3'='%' or '^3' is null then b.acdgroup else '^3' end)
       and b.defgrp=c.grpid and c.grpname = (case when '^4'='%' or '^4' is null then c.grpname else '^4' end)
       and b.acdgroup = (case when '^3'='%' or '^3' is null then b.acdgroup else '^3' end)
       group by a.crusr,a.crdt
    having count(a.orderid) > 0
    )
    group by crdt,usrid,name
    你参考下吧。。
      

  6.   

    --楼主要构造一个每一天某个时间段的数据集,如
    start_time        end_time
    2010-01-01 08:00  2010-01-01 17:00
    2010-01-02 08:00  2010-01-02 17:00
    2010-01-03 08:00  2010-01-03 17:00
    --可用如下语句
    SELECT LEVEL + start_date - 1 + to_number(start_hour) / 24 + to_number(start_minute) / 1440 AS starttime,
           LEVEL + start_date - 1 + to_number(end_hour) / 24 + to_number(end_minute) / 1440
      FROM dual
    CONNECT BY LEVEL <= (trunc(end_date, 'dd') - trunc(start_date, 'dd'));
    --然后关联report_call_hist的表datetime字段就行，亦即datetime between starttime and endtime就可以了
      

  7.   


    --上面sql语句中使用的变量
    --start_date和end_date是要统计的时间范围 如2010-1-1~2010-1-31
    --start_hour和start_minute为时段的开始时间 如8:00
    --end_hour和end_minute为时段的结束时间 如17:00
      

  8.   

    或者使用report_call_hist表上增加条件to_char(datetime,'hh24:mi') between start_time and end_time
      

  9.   

    报什么错我这边没有环境，无法测试另外，你是否是想要构造一个某个时间段每一天的数据集，还是只取你这个表report_call_hist中存在的时间就可以了？
      

  10.   

    前者，
    格式如下：
    如：选择从 2010-01-01 到  2010-02-01 ，坐席ID为164 的坐席 每一天（从00：00-23：59）的接听数，未接听数，订单数量。 
    格式： 
    日期    坐席编号            姓名                接听数    未接听数        订单数量。。 
    01-01    164                  张五                12          2              2    
    01-02    164                  张五                67          3              10 。 01-31    164                  张五                67          3              10 
    select t1.crdt,t.usrid,t.name,t1.actv,t1.abdn,0 as dds
    提示 dds为无效字段