SQL过程语句存档

最新推荐文章于 2022-09-23 17:10:32 发布

szc_1985

最新推荐文章于 2022-09-23 17:10:32 发布

阅读量161

点赞数 1

分类专栏：数据库办公记录语句文章标签： sql

本文链接：https://blog.csdn.net/szc_1985/article/details/109488485

版权

数据库办公记录语句专栏收录该内容

40 篇文章 1 订阅

订阅专栏

–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;