FACT_HARASS_NUMBER表中也有ORIGIN_字段,表示数据来源。识别只是针对这张表。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.类型;
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.类型;
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.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_) z
order by z.数据来源, z.类型;
select CALLER_NUMBER as a ,count(1) as c from FACT_HARASS_NUMBER
group by CALLER_NUMBER order by CALLER_NUMBER查出来记录是5300条。而那个SQL 查出来的识别数相加却只有几百条。
不同的数据来源如果算一条 那么这一条算到哪个数据来源上去?
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 (select m.origin_, m.operate_type, m.number_
from HARASS_OPERATE_LOG m
where m.operate_status = 0
group by m.origin_,
m.operate_type,
m.number_,
m.operate_time) a
left join FACT_HARASS_NUMBER b on a.number_ =
b.caller_number
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.origin_ as origin_,
b.caller_origin caller_origin,
count(distinct b.caller_number) 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_) z
order by z.数据来源, z.类型;
拦截 1 6 0 0 6
识别 1 1 0 0 1
解锁 9 30 0 30 0
拦截 9 18 0 18 0
识别 9 1 0 1 0识别怎么可能只有一次呢,识别是根据数据来源单独统计FACT_HARASS_NUMBER这张表所出现的号码个数跟。
FACT_HARASS_NUMBER这张表有ORIGIN_字段是表示数据来源,应该两个数据来源下识别量有几千才对啊啊
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 (select m.origin_, m.operate_type, m.number_
from HARASS_OPERATE_LOG m
where m.operate_status = 0
group by m.origin_,
m.operate_type,
m.number_,
m.operate_time) a
left join FACT_HARASS_NUMBER b on a.number_ =
b.caller_number
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 b.origin_ as origin_,
b.caller_origin caller_origin,
count(distinct b.caller_number) cn
from FACT_HARASS_NUMBER b
group by b.origin_, b.caller_origin) x
group by x.origin_) z
order by z.数据来源, z.类型;
nvl(sum(decode(x.caller_origin, '互联互通', x.cn)), 0) 互联互通号码数,
nvl(sum(decode(x.caller_origin, '省内移动号码', x.cn)), 0) 省内移动号码数
关注下这几行
拦截 1 2911070 0 1044630 0
识别 1 13778 0 4089 0
解锁 9 620 0 296 0
拦截 9 129 0 21 0外省数和省内数都是0,哎,真不会调这个,起码要总数 = 外省 + 互联互通+省内吧
什么意思
caller_origin 这个字段就是有三种可能性 外省移动,互联互通,本省移动
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 (select m.origin_, m.operate_type, m.number_
from HARASS_OPERATE_LOG m
where m.operate_status = 0
group by m.origin_,
m.operate_type,
m.number_,
m.operate_time) a
left join FACT_HARASS_NUMBER b on a.number_ =
b.caller_number
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 b.origin_ as origin_,
b.caller_origin caller_origin,
count(distinct b.caller_number) cn
from FACT_HARASS_NUMBER b
group by b.origin_, b.caller_origin) x
group by x.origin_) z
order by z.数据来源, z.类型;
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) 省内移动号码数原来是里面的值没有匹配上。哎 我都搞急了