拜求一个统计的SQL（二）

Question

昨天的问题可能没描述清楚。有两张表HARASS_OPERATE_LOG，FACT_HARASS_NUMBERHARASS_OPERATE_LOG表有字段：ORIGIN_（数据来源） NUMBER_（手机号码）OPERATE_TYPE（操作类型）OPERATE_STATUS（操作状态） OPERATE_TIME（时间）
   短信呼            13811112222                 1                      0          2010-04-29 12:26:44
   小区短信          13800001111                 1                      0          2010-04-29 22:26:44
    BOSS             13866669999                 2                      1          2010-04-29 13:26:44
    手工             13911112222                 2                      0          2010-04-29 15:26:44操作类型：1表示封锁，2表示解锁
操作状态：0 成功，1 失败FACT_HARASS_NUMBER表有字段：CALLER_ORIGIN     CALLER_NUMBER
   互联互通          13811112222
   省内移动号码      13800001111
   省外移动号码      13866669999
   互联互通          13911112222注：HARASS_OPERATE_LOG中的手机号码，可以去FACT_HARASS_NUMBER中查找对应的是属于互联互通还是省内或省外号码现有查询条件 数据来源（下拉框形势，包括短信呼，小区短信，BOSS，手工，不选择就是全选），时间
列表依次列出每一个数据来源的拦截，解锁，识别的数据量拦截表示：时段内，封锁成功的号码数，包括：号码总数、省内移动号码数、省外移动号码数、互联互通号码数。
解锁表示：时段内，解锁成功的号码数，包括：号码总数、省内移动号码数、省外移动号码数、互联互通号码数。识别表示：时段内，所有唯一性号码数，包括：号码总数、省内移动号码数、省外移动号码数、互联互通号码数。 
现在需要统计出如下格式：
类型       数据来源    总数   外省移动号码数  互联互通号码数 省内移动号码数
拦截       短信呼
解锁       短信呼
识别       短信呼拦截       小区短信
解锁       小区短信
识别       小区短信拦截       BOSS
解锁       BOSS
识别       BOSS拦截       手工
解锁       手工
识别       手工
   

Answer 1

一个SQL 查询吗？还是用过程？

Answer 2

用一个SQL查询！唯一性就是号码不重复的数

Answer 3

用case when语句可实现
给出表结构语句和测试数据，可以考虑写

Answer 4

CREATE TABLE EASTCOM.HARASS_OPERATE_LOG
(
    ID_            VARCHAR2(32)   NOT NULL,
    ORIGIN_        VARCHAR2(2)        NULL,
    NUMBER_        VARCHAR2(24)       NULL,
    NE_ID          VARCHAR2(32)       NULL,
    OPERATE_TYPE   VARCHAR2(1)        NULL,
    OPERATOR_      VARCHAR2(32)       NULL,
    OPERATE_TIME   VARCHAR2(24)       NULL,
    OPERATE_STATUS VARCHAR2(1)        NULL,
    BLOCK_TIME     VARCHAR2(24)       NULL,
    UNBLOCK_TIME   VARCHAR2(24)       NULL,
    DETAIL_        VARCHAR2(2000)     NULL
)CREATE TABLE EASTCOM.FACT_HARASS_NUMBER
(
    TIMEID        NUMBER(*,0)       NULL,
    ORIGIN_       VARCHAR2(2)       NULL,
    CALLER_NUMBER VARCHAR2(24)      NULL,
    CALL_DATE     VARCHAR2(24)      NULL,
    CALL_TIMES    NUMBER(*,0)       NULL,
    CALLER_ORIGIN VARCHAR2(32)      NULL,
    CALLED_STYLE  VARCHAR2(255)     NULL,
    SYNC_TIME     VARCHAR2(24)  DEFAULT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')     NULL,
    IS_HARASS     VARCHAR2(1)       NULL,
    UUID          VARCHAR2(32)      NULL
)oracle数据库

Answer 5

select *
  from (select decode(x.operate_type, 1, '拦截', 2, '解锁') 类型,
               x.origin_ 数据来源,
               nvl(sum(x.cn), 0) 总数,
               nvl(sum(decode(x.caller_origin, '省外移动号码', x.cn)), 0) 外省移动号码数,
               nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
               nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
          from (select a.operate_type operate_type,
                       a.origin_ as origin_,
                       b.caller_origin caller_origin,
                       count(b.caller_origin) cn
                
                  from HARASS_OPERATE_LOG a
                  left join FACT_HARASS_NUMBER b on a.number_ =
                                                    b.caller_number
                
                 where a.operate_status = 0
                 group by a.origin_, a.operate_type, b.caller_origin) x
         group by x.operate_type, x.origin_
        union all
        select '识别' 类型,
               x.origin_ 数据来源,
               nvl(sum(x.cn), 0) 总数,
               nvl(sum(decode(x.caller_origin, '省外移动号码', x.cn)), 0) 外省移动号码数,
               nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
               nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
          from (select a.operate_type operate_type,
                       a.origin_ as origin_,
                       b.caller_origin caller_origin,
                       count(b.caller_origin) cn
                
                  from HARASS_OPERATE_LOG a
                  left join FACT_HARASS_NUMBER b on a.number_ =
                                                    b.caller_number
                 where a.number_ in (select y.number_
                                       from (select a.number_,
                                                    count(a.number_) over(partition by a.number_) as cn
                                               from HARASS_OPERATE_LOG a) y
                                      where y.cn = 1)
                 group by a.origin_, a.operate_type, b.caller_origin) x
         group by x.operate_type, x.origin_) z
 order by z.数据来源, z.类型;因测试数据太少 只能给出个初步结构 逻辑或性能不适用处 请自行测试修改

Answer 6

这一部分就是识别的类型：按你的说法 () 我理解为所有记录中(如果需要可以加条件限制范围)只出现一次的号码select '识别' 类型,
  x.origin_ 数据来源,
  nvl(sum(x.cn), 0) 总数,
  nvl(sum(decode(x.caller_origin, '省外移动号码', x.cn)), 0) 外省移动号码数,
  nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
  nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
  from (select a.operate_type operate_type,
  a.origin_ as origin_,
  b.caller_origin caller_origin,
  count(b.caller_origin) cn
    
  from HARASS_OPERATE_LOG a
  left join FACT_HARASS_NUMBER b on a.number_ =
  b.caller_number
  where a.number_ in (select y.number_
  from (select a.number_,
  count(a.number_) over(partition by a.number_) as cn
  from HARASS_OPERATE_LOG a) y
  where y.cn = 1)
  group by a.origin_, a.operate_type, b.caller_origin) x
  group by x.operate_type, x.origin_

Answer 7

非常谢谢 楼上
刚确认了下需求，识别量仅仅针对FACT_HARASS_NUMBER这张表所出现的号码。这该怎么改下

Answer 8

是指出现了多少个号码，在FACT_HARASS_NUMBER这张表中，而不是说只出现了一次的。

Answer 9

select '识别' 类型,
  x.origin_ 数据来源,
  nvl(sum(x.cn), 0) 总数,
  nvl(sum(decode(x.caller_origin, '省外移动号码', x.cn)), 0) 外省移动号码数,
  nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
  nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
  from (select a.operate_type operate_type,
  a.origin_ as origin_,
  b.caller_origin caller_origin,
  count(b.caller_origin) cn
    
  from HARASS_OPERATE_LOG a,
  FACT_HARASS_NUMBER b 
  where a.number_ = b.caller_number
  group by a.origin_, a.operate_type, b.caller_origin) x
  group by x.operate_type, x.origin_看意思是只要在FACT_HARASS_NUMBER表 能找到的就是能识别的 看来a与b是多对1的关系，不知a中一个号码多次出现时 是否要去重（还有去重的范围：是全集范围，同一数据来源下，还是同一来源下成功的或失败的，这个都要视业务的实际需要而定）你可以试着自己修改以符合自己真实的业务需求，我也只是提供一个思路而已，最终结果还是要靠自己完成，将来需求变更了 还要靠你修改呢。

Answer 10

是出现了两次 一次是封锁 一次是解锁 我没有测试数据 自己调调呗
select '识别' 类型,
       x.origin_ 数据来源,
       nvl(sum(x.cn), 0) 总数,
       nvl(sum(decode(x.caller_origin, '省外移动号码', x.cn)), 0) 外省移动号码数,
       nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
       nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
  from (select a.origin_ as origin_,
               b.caller_origin caller_origin,
               count(b.caller_origin) cn
        
          from HARASS_OPERATE_LOG a, FACT_HARASS_NUMBER b
         where a.number_ = b.caller_number
         group by a.origin_, b.caller_origin) x
 group by x.origin_