本文主要是介绍SQL过程语句存档，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
–1、导入12321举报号码明细、核减数据明细、涉诈号码明细三个表的内容，后期可手工导入。
–2、生成指标进度报表，区县目标值为固定值，指标进度值为计算值，截止日期值为表内数据值。
–3、指标进度报表的截止日期值计算方式：核减数据明细数量+（当前日期-核减数据明细最大日期）12321举报号码明细数量=截止日期值
–4、涉诈号码直接提取涉诈号码明细即可。
–5、提供相应表项的清单下载，并可以选时间段进行查询。
–第一列类型，最多的22列，最后留两列 导入工号yu导入时间
—建立 举报号码_经分报表_基础数据 ，然后分类保存，并且形成报表
— XXXX.jbsj_jfbbdr_jcsj
---12321数据
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_12321’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_12321 as
select 字段1 区县,
字段2 处置意见,
字段3 举报时间,
字段4 ID,
字段5 举报来源,
字段6 核查类型,
字段7 举报号码,
字段8 举报号码运营商,
字段9 举报号码归属省,
字段10 举报号码归属市,
字段11 被举报号码,
字段12 被举报号码运营商,
字段13 被举报号码归属省,
字段14 被举报号码归属市,
字段15 来电时间,
字段16 通话时长秒,
字段17 不良类型,
字段18 骚扰形式,
字段19 号码类型,
字段20 举报内容,
字段21 核查企业,
字段22 核查情况
from
(select a.*,row_number() over (partition by a.字段4 order by a.in_time desc ) 排名
from XXXX.jbsj_jfbbdr_jcsj a where a.类型=’‘12321’’ ) a
where a.排名=1 ’ ;
execute immediate (SQL_STRING);
—核减数据
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_hjsj’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_hjsj as
select 字段1 ID,
字段2 举报来源,
字段3 核查类型,
字段4 举报号码,
字段5 举报号码运营商,
字段6 举报号码归属省,
字段7 举报号码归属市,
字段8 被举报号码,
字段9 号码类型,
字段10 被举报号码运营商,
字段11 被举报号码归属省,
字段12 被举报号码归属市,
字段13 举报时间,
字段14 举报内容,
字段15 区县
from
(select a.*,row_number() over (partition by a.字段1 order by a.in_time desc ) 排名
from XXXX.jbsj_jfbbdr_jcsj a where a.类型=’‘核减数据’’ ) a
where a.排名=1 ’ ;
execute immediate (SQL_STRING);
—涉诈号码
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_szhm’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_szhm as
select 字段1 区县,
字段2 公安函号,
字段3 邮件转派时间,
字段4 涉案号码,
字段5 案件类别,
字段6 通话地,
字段7 号码当前状态,
字段8 开户时间,
字段9 渠道属性,
字段10 渠道编码
from
(select a.*,row_number() over (partition by a.字段2,a.字段4 order by a.in_time desc ) 排名
from XXXX.jbsj_jfbbdr_jcsj a where a.类型=’‘涉诈号码’’ ) a
where a.排名=1 ’ ;
execute immediate (SQL_STRING);
—诈骗涉案号码量目标（件）
– XXXX.jbsj_jfbbdr_jcsj_zpsa_mb
–涉诈号码汇总
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_szhm_hz’),‘XXXX’);
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_szhm_hz as
select a.区县,count(distinct a.公安函号||a.涉案号码) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_szhm a
group by a.区县
union all
select ‘‘合计’’,count(distinct a.公安函号||a.涉案号码) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_szhm a ’ ;
execute immediate (SQL_STRING);
–涉诈号码进度
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_zpsa_mbjd’),‘XXXX’);
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_zpsa_mbjd as
select a.qx,a.目标量,nvl(b.涉案量,0) 涉案量,
round(nvl(b.涉案量,0)/decode(a.目标量,0,999999,a.目标量)*100,2) 指标进度
from XXXX.jbsj_jfbbdr_jcsj_zpsa_mb a,
XXXX.jbsj_jfbbdr_jcsj_szhm_hz b
where a.qx=b.区县(+) ’ ;
execute immediate (SQL_STRING);
—1、诈骗涉案号码 的指标为涉诈号码表格内的数据分解；
—2、骚扰电话被举报量 的指标为核减数据明细数量（号码类型的手机）+（当前日期-核减数据明细（号码类型的手机）最大日期）12321举报号码（号码类型为手机和移动手机）明细数量=截止日期值
—3、骚扰固话被举报量 的指标为核减数据明细数量（号码类型的IMS固话）+（当前日期-核减数据明细（号码类型的IMS固话）最大日期）12321举报号码（号码类型为IMS固话、固话、家客）明细数量=截止日期值
—2、骚扰电话 目标量 XXXX.jbsj_jfbbdr_jcsj_srdh_mb
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_hjsj_hz’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_hjsj_hz as
select a.区县,count(a.id) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 not in (’‘经开区’’,’‘南定’’,’‘空号’’,’‘地市’’)
and a.号码类型 =’‘手机’’
group by a.区县
union all
select ‘‘经开区’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 in (’‘经开区’’,’‘南定’’)
and a.号码类型 =’‘手机’’
union all
select ‘‘地市’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 in (’‘空号’’,’‘地市’’)
and a.号码类型 =’‘手机’’
union all
select ‘‘合计’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.号码类型 =’‘手机’’ ’ ;
execute immediate (SQL_STRING);
—（当前日期-核减数据明细（号码类型的手机）最大日期）12321举报号码
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_12321a’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_12321a as
select * from XXXX.jbsj_jfbbdr_jcsj_12321 a where to_date(a.举报时间,’‘yyyy-mm-dd hh24:mi:ss’’) >
(select to_date(max(a.举报时间),’‘yyyy-mm-dd hh24:mi:ss’’) from XXXX.jbsj_jfbbdr_jcsj_hjsj a where a.号码类型=’‘手机’’ )
and a.号码类型 in (’‘移动手机’’,’‘手机’’) ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_12321_hz’),‘XXXX’);
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_12321_hz as
select a.区县,count(distinct a.id) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_12321a a
group by a.区县
union all
select ‘‘合计’’,count(distinct a.id)
from XXXX.jbsj_jfbbdr_jcsj_12321a a ’ ;
execute immediate (SQL_STRING);
–骚扰电话进度
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_srdh_mbjd’),‘XXXX’);
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_srdh_mbjd as
select a.qx,a.目标量,nvl(b.涉案量,0) +nvl(c.涉案量,0) 涉案量,
round((nvl(b.涉案量,0) +nvl(c.涉案量,0))/decode(a.目标量,0,999999,a.目标量)*100,2) 指标进度
from XXXX.jbsj_jfbbdr_jcsj_srdh_mb a,
XXXX.jbsj_jfbbdr_jcsj_hjsj_hz b,
XXXX.jbsj_jfbbdr_jcsj_12321_hz c
where a.qx=b.区县(+)
and a.qx=c.区县(+) ’ ;
execute immediate (SQL_STRING);
----骚扰固话 目标量 XXXX.jbsj_jfbbdr_jcsj_srgh_mb
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_hjsj_ghz’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_hjsj_ghz as
select a.区县,count(a.id) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 not in (’‘经开区’’,’‘南定’’,’‘空号’’,’‘地市’’)
and a.号码类型 <>’‘手机’’
group by a.区县
union all
select ‘‘经开区’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 in (’‘经开区’’,’‘南定’’)
and a.号码类型 <>’‘手机’’
union all
select ‘‘地市’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.区县 in (’‘空号’’,’‘地市’’)
and a.号码类型 <>’‘手机’’
union all
select ‘‘合计’’,count(a.id)
from XXXX.jbsj_jfbbdr_jcsj_hjsj a
where a.号码类型 <>’‘手机’’ ’ ;
execute immediate (SQL_STRING);
—（当前日期-核减数据明细（号码类型的IMS固话）最大日期）12321举报号码
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_12321b’),‘XXXX’);
SQL_STRING:=‘create table XXXX.jbsj_jfbbdr_jcsj_12321b as
select * from XXXX.jbsj_jfbbdr_jcsj_12321 a where to_date(a.举报时间,’‘yyyy-mm-dd hh24:mi:ss’’) >
(select to_date(max(a.举报时间),’‘yyyy-mm-dd hh24:mi:ss’’) from XXXX.jbsj_jfbbdr_jcsj_hjsj a where a.号码类型 <>’‘手机’’ )
and a.号码类型 not in (’‘移动手机’’,’‘手机’’) ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jbsj_jfbbdr_jcsj_12321_ghz'),'XXXX');
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_12321_ghz as
select a.区县,count(distinct a.id) 涉案量
from XXXX.jbsj_jfbbdr_jcsj_12321b a
group by a.区县
union all
select ‘‘合计’’,count(distinct a.id)
from XXXX.jbsj_jfbbdr_jcsj_12321b a ’ ;
execute immediate (SQL_STRING);
–骚扰固话进度
fan_drop_retable(upper(‘jbsj_jfbbdr_jcsj_srgh_mbjd’),‘XXXX’);
SQL_STRING:='create table XXXX.jbsj_jfbbdr_jcsj_srgh_mbjd as
select a.qx,a.目标量,nvl(b.涉案量,0) +nvl(c.涉案量,0) 涉案量,
round((nvl(b.涉案量,0) +nvl(c.涉案量,0))/decode(a.目标量,0,999999,a.目标量)*100,2) 指标进度
from XXXX.jbsj_jfbbdr_jcsj_srgh_mb a,
XXXX.jbsj_jfbbdr_jcsj_hjsj_ghz b,
XXXX.jbsj_jfbbdr_jcsj_12321_ghz c
where a.qx=b.区县(+)
and a.qx=c.区县(+) ’ ;
execute immediate (SQL_STRING);
—合并
SQL_STRING:=‘delete from XXXX.jbsj_jfbbdr_jcsj_hz a where a.日期=’’’||P_DAY||’’’ ’ ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=‘insert into XXXX.jbsj_jfbbdr_jcsj_hz
select ‘’’||P_DAY||’’’ 日期,t.序列,t.qx,
a.目标量 诈骗涉案号码量目标,a.涉案量 诈骗涉案号码量,a.指标进度 诈骗涉案指标进度,
b.目标量 骚扰电话被举报量目标,b.涉案量 骚扰电话被举报量,b.指标进度 骚扰电话指标进度,
c.目标量 骚扰固话被举报量目标,c.涉案量 骚扰固话被举报量,c.指标进度 骚扰固话指标进度
from XXXX.jbsj_jfbbdr_jcsj_qxxl t,
XXXX.jbsj_jfbbdr_jcsj_zpsa_mbjd a,
XXXX.jbsj_jfbbdr_jcsj_srdh_mbjd b,
XXXX.jbsj_jfbbdr_jcsj_srgh_mbjd c
where t.qx=a.qx(+)
and t.qx=b.qx(+)
and t.qx=c.qx(+) ’ ;
execute immediate (SQL_STRING);
commit;
这篇关于SQL过程语句存档的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
