本文详细介绍了出租屋数据的导入、清理、统计及分析流程，包括楼宇、房屋、人员数据的导入与索引创建，以及数据的定时分析与统计结果的存储。通过此过程，实现对出租屋人员动态的有效监控。
1、包头
CREATE OR REPLACE PACKAGE PKG_DATA_CZWCOUNT as
procedure PRO_QZLD_CZW_DELETE; --清空统计需要的基础表数据，为导入新的数据做准备
procedure PRO_QZLD_CZW_IMPORT; --导入数据
procedure PRO_CZW_FW_TJ; --定时分析数据统计
procedure PRO_CZW_FW_CZWRY_TJFX; --将统计分析的数据分析一次
procedure PRO_CZW_ALL;
end;
2、包体
CREATE OR REPLACE PACKAGE BODY PKG_DATA_CZWCOUNT is
/*
* 清空统计需要的基础表数据，为导入新的数据做准备
*/
PROCEDURE PRO_QZLD_CZW_DELETE as
begin
--删除索引
execute immediate 'drop index index_ry_houseid';
execute immediate 'drop index index_ry_updatetime';
execute immediate 'drop index index_ry_cardno';
execute immediate 'drop index index_fw_id';
execute immediate 'drop index index_czw_fw_tj_tjsj';
execute immediate 'drop index index_czw_fw_tj_tjsj_tjsq';
execute immediate 'drop index index_czw_fw_czwry_tjfx_time';
--清空数据
execute immediate 'truncate table MV_CZW_LY';
execute immediate 'truncate table MV_CZW_FW';
execute immediate 'truncate table MV_CZW_RY';
end;
/*
* 导入数据
*/
PROCEDURE PRO_QZLD_CZW_IMPORT as
begin
--导入楼宇数据
insert into mv_czw_ly
(OBJECTID,
ID,
CODE,
COMM_ID,
SQNAME,
POLICE,
SSPCS,
SUBSTATION,
SUBSTATION_NAME,
POINT_X,
POINT_Y,
SHAPE,
ZY_RKSJ,
REDFLAG)
SELECT "QZYDJW_LY"."OBJECTID" "OBJECTID",
"QZYDJW_LY"."ID" "ID",
"QZYDJW_LY"."CODE" "CODE",
"QZYDJW_LY"."COMM_ID" "COMM_ID",
"QZYDJW_LY"."SQNAME" "SQNAME",
"QZYDJW_LY"."POLICE" "POLICE",
"QZYDJW_LY"."SSPCS" "SSPCS",
"QZYDJW_LY"."SUBSTATION" "SUBSTATION",
"QZYDJW_LY"."SUBSTATION_NAME" "SUBSTATION_NAME",
"QZYDJW_LY"."POINT_X" "POINT_X",
"QZYDJW_LY"."POINT_Y" "POINT_Y",
"QZYDJW_LY"."SHAPE" "SHAPE",
"QZYDJW_LY"."ZY_RKSJ" "ZY_RKSJ",
"QZYDJW_LY"."REDFLAG" "REDFLAG"
FROM "QZYDJW_LY"@CZW_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM "QZYDJW_LY"
WHERE "QZYDJW_LY"."POLICE" = '440306900000'
OR "QZYDJW_LY"."POLICE" = '440306780000'
OR "QZYDJW_LY"."POLICE" = '440306790000'
OR "QZYDJW_LY"."POLICE" = '440306800000'
OR "QZYDJW_LY"."POLICE" = '440306810000'
OR "QZYDJW_LY"."POLICE" = '440306830000'
OR "QZYDJW_LY"."POLICE" = '440306820000'
OR "QZYDJW_LY"."POLICE" = '440306840000'
OR "QZYDJW_LY"."POLICE" = '440306850000'
OR "QZYDJW_LY"."POLICE" = '440306860000'
OR "QZYDJW_LY"."POLICE" = '440306870000';
commit;
--导入房屋数据
insert into MV_CZW_FW
(ID,
HOUSEUSAGE,
BUILDINGID,
BUILDINGNAME,
HOUSEDESIGNUSEID,
HOUSEPHOTO,
CODE,
HOUSESTAIR,
NAME,
HOUSECERTIFICATIONID,
HOUSECERTIFICATION,
CERTIFICATIONADDRESS,
MANAGETYPE,
HIDDENDANGERTYPE,
BOOKERID,
REGISTRAR,
REGISTERTIME,
USERID,
USERNAME,
HOUSEADDRESS,
HOUSENAME,
HOUSETYPEID,
HOUSESTRUCTRUE,
REMARKS,
HOUSELINKMANID,
HOUSELINKMAN,
HOUSELINKMANADDRESS,
HOUSELINKMANTEL,
AREAID,
AGENTID,
AGENTNAME,
AGENTADDRESS,
AGENTTEL,
AGENTSTARTDATE,
AGENTENDDATE,
CREATOR,
CREATETIME,
UPDATETIME,
UPDATER,
ISVALID,
HOUSETENANCYAREA,
OWNERID,
OWNERNAME,
OWNERADDRESS,
BUILDINGADDRESS,
BUILDINGAREA,
BUILDINGTYPE,
CREATORGROUP,
DATABEGIN,
DATASOURCE,
HOUSEWORTH,
LASTUPDATEDATE,
LASTUPDATOR,
MEDIAS,
UPDATED,
UPDATERGROUP,
BUILDINGLAYERS,
RESPONSIBILITY,
FIRMER,
FIRMTIME,
HOUSEAREA,
LIVEAREA,
BUSINESSAREA,
OFFICERAREA,
FACTORYAREA,
STOREAREA,
OTHERAREA,
OWNAREA,
UNKNOWNAREA,
HOMEADDRESS2,
TEMP_ID,
TEMP_AREAID,
CONSIGNOR,
CERTIFICATIONMAN,
DELMARK,
UPDATE_KEY,
OWNERTEL,
PAPERTYPE,
CARDNO,
COMM_ID,
ZY_RKSJ,
REDFLAG,
OLDCODE,
QHAREAID,
CONTROLLER,
HOUSEDEUSE,
AGENTCARDTYPE,
AGENTCARDNO,
TEMP_BAID,
OLDBUILDINGID,
OLDBUILDINGCODE,
WLGYREMARKS,
OLDBUILDINGNAME)
SELECT *
FROM QZYDJW_FW@CZW_LINK f
where f.BUILDINGID in
(select b.CODE
from qzydjw_ly@CZW_LINK b
where b.POLICE in
('440306900000', '440306780000', '440306790000',
'440306800000', '440306810000', '440306830000',
'440306820000', '440306840000', '440306850000',
'440306860000', '440306870000'));
commit;
--创建房屋索引
execute immediate 'create index index_fw_id on mv_czw_fw(id)';
--导入人员数据
insert into mv_czw_ry
(id,
nationality,
name,
alias,
cardtypeid,
cardno,
effectivedate,
issuingorgan,
sex,
nationid,
nativeplace,
registertype,
marryid,
registeraddtypeid,
edulevelid,
politicsid,
registeraddress,
beliefid,
photo,
managetype,
tradeid,
jobid,
techtitleid,
bidefashion,
domiciletype,
company,
companyaddress,
companytel,
cpolicestation,
tel,
mtel,
email,
intime1,
addtime,
registrar,
remarks,
creator,
createtime,
updatetime,
updater,
isvalid,
leasereasonid,
degree,
bloodtypeid,
escuageid,
finishschool,
refisteraddresspre,
registerplace,
speciality,
leaserelationid,
health,
homeaddress,
areaid,
houseid,
diedate,
marrydate,
rapostalcode,
resideinaddress,
resideinpostalcode,
singlekid,
residenttype,
height,
salary,
birthday,
updatelivecode,
homeaddressnew,
housecode,
islogout,
istransact,
transacttime,
transactor,
isrewriteaddress,
rewritetime,
rewriteperson,
temp_areaid,
temp_id,
temp_mingzu,
letdutybook,
dutyname,
logoutor,
logouttime,
temp_photo,
isinhabitation,
isaddressnot,
islessee,
leavedate,
checkindate,
procreatedynamic,
contraceptive,
cardholders,
voucher,
vouchercodenum,
voucherphone,
outreason,
newareaid,
cardno2,
isstat,
update_key,
rksj,
readflag,
zy_rksj,
qhareaid,
temp_hosueid,
temp_fanghao,
tmep_fh,
temp_edit,
temp_delete,
rksbid)
SELECT *
from qzydjw_ry@CZW_LINK r
where r.HOUSEID in
(select f.ID
from qzydjw_fw@CZW_LINK f
where f.BUILDINGID in
(select b.CODE
from qzydjw_ly@CZW_LINK b
where b.POLICE in
('440306900000', '440306780000', '440306790000',
'440306800000', '440306810000', '440306830000',
'440306820000', '440306840000', '440306850000',
'440306860000', '440306870000')));
commit;
--创建人员索引
execute immediate 'create index index_ry_houseid on mv_czw_ry(houseid)';
execute immediate 'create index index_ry_updatetime on mv_czw_ry(updatetime)';
execute immediate 'create index index_ry_cardno on mv_czw_ry(cardno)';
end;
/*
* 定时分析出租屋人员数据，包括出租屋中的现住人数、三个月以上未更新人员
* 六个月以上未更新人员、一年以上未更新人员、疑似一房多人、疑似一人多房、
* 本月注销的过程语句
*
* 统计后的数据插入到czw_fw_tj结果表中。
*/
PROCEDURE PRO_CZW_FW_TJ as
begin
--现居住人数
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'1' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
f.tjsl tjsl,
l.comm_id comm_id
from (select r.houseid houseid,
r.tjsl tjsl,
f.buildingid buildingid,
f.houseaddress houseaddress
from (select houseid, count(1) tjsl
from mv_czw_ry
where islogout = 0
group by houseid) r,
mv_czw_fw f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--三个月以上没更新
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'2' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
f.tjsl tjsl,
l.comm_id comm_id
from (select r.houseid houseid,
r.tjsl tjsl,
f.buildingid buildingid,
f.houseaddress houseaddress
from (select houseid, count(1) tjsl
from mv_czw_ry
where islogout = 0
and updatetime between (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 180) and (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 90)
group by houseid) r,
mv_czw_fw f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--六个月以上没更新
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'3' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
f.tjsl tjsl,
l.comm_id comm_id
from (select r.houseid houseid,
r.tjsl tjsl,
f.buildingid buildingid,
f.houseaddress houseaddress
from (select houseid, count(1) tjsl
from mv_czw_ry
where islogout = 0
and updatetime between (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 365) and (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 180)
group by houseid) r,
mv_czw_fw f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--一年以上没更新
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'4' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
f.tjsl tjsl,
l.comm_id comm_id
from (select r.houseid houseid,
r.tjsl tjsl,
f.buildingid buildingid,
f.houseaddress houseaddress
from (select houseid, count(1) tjsl
from mv_czw_ry
where islogout = 0
and updatetime < (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 365)
group by houseid) r,
mv_czw_fw f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--疑似未注销(一人多房)
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select r.houseid fwid,
f.houseaddress fwdz,
r.tjsl tjsl,
'5' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select r.houseid, count(1) tjsl
from (select cardno
from (select cardno, houseid
from mv_czw_ry
where islogout = 0
group by cardno, houseid) r
where (length(cardno) = 15 or length(cardno) = 18)
group by cardno
having count(1) > 1) tj,
mv_czw_ry r
where tj.cardno = r.cardno
and islogout = 0
group by r.houseid) r,
mv_czw_fw f,
mv_czw_ly l,
czw_dm_sq s
where r.houseid = f.id
and f.buildingid = l.code
and l.comm_id = s.dm;
commit;
--疑似未注销（一房多人）
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'6' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
f.tjsl tjsl,
l.comm_id comm_id
from (select r.houseid,
r.tjsl tjsl,
f.buildingid buildingid,
f.houseaddress houseaddress
from (select houseid, count(1) tjsl
from MV_CZW_RY
where islogout = 0
group by houseid
having count(1) > 1) r,
MV_CZW_FW f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--本月注销
insert into CZW_FW_TJ
(TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ)
select l.houseid tjid,
l.houseaddress tjmc,
l.tjsl tjsl,
'7' tjlx,
to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj,
SEQ_CZW_ID.NEXTVAL id,
s.dm tjsq
from (select f.houseid houseid,
f.houseaddress houseaddress,
l.comm_id comm_id,
f.tjsl
from (select r.houseid houseid,
f.buildingid buildingid,
f.houseaddress houseaddress,
r.tjsl
from (select houseid, count(1) tjsl
from mv_czw_ry
where islogout = 1
and updatetime > (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 30)
group by houseid) r,
mv_czw_fw f
where r.houseid = f.id) f,
mv_czw_ly l
where f.buildingid = l.code) l,
czw_dm_sq s
where l.comm_id = s.dm;
commit;
--给CZW_FW_TJ表创建索引
execute immediate 'create index index_czw_fw_tj_tjsj on CZW_FW_TJ(tjsj)';
execute immediate 'create index index_czw_fw_tj_tjsj_tjsq on CZW_FW_TJ(tjsj,tjsq)';
end;
/*
* 定时将czw_fw_tj表，再让照 单位和统计类型分组，分析的结果插入CZW_FW_CZWRY_TJFX表
* 分统计类型tjlx 1：警局 2：社区
*/
PROCEDURE PRO_CZW_FW_CZWRY_TJFX as
begin
--以警局单为位统计数据
insert into czw_fw_czwry_tjfx
(dm, mc, xzrs, swgx, lwgx, ynwgx, yrdf, yfdr, byzx, tjsj, tjlx)
select s.dm,
s.mc,
tj.xzrs,
tj.swgx,
tj.lwgx,
tj.ynwgx,
tj.yrdf,
tj.yfdr,
tj.byzx,
to_char(trunc(sysdate), 'yyyy-mm-dd'),
'1'
from (select decode(pcs, null, '440306900000', pcs) pcs,
sum(decode(t.tjlx, 1, t.tjsl, 0)) xzrs,
sum(decode(t.tjlx, 2, t.tjsl, 0)) swgx,
sum(decode(t.tjlx, 3, t.tjsl, 0)) lwgx,
sum(decode(t.tjlx, 4, t.tjsl, 0)) ynwgx,
sum(decode(t.tjlx, 5, t.tjsl, 0)) yrdf,
sum(decode(t.tjlx, 6, t.tjsl, 0)) yfdr,
sum(decode(t.tjlx, 7, t.tjsl, 0)) byzx
from (select substr(tjsq, 0, 12) pcs,
nvl(sum(tjsl), 0) tjsl,
tjlx
from czw_fw_tj
where tjsj = to_char(trunc(sysdate), 'yyyy-mm-dd')
group by substr(tjsq, 0, 12), tjlx) t
group by pcs) tj,
czw_dm_pcs s
where s.dm = tj.pcs;
--以社区为单位统计数据
insert into czw_fw_czwry_tjfx
(dm, mc, xzrs, swgx, lwgx, ynwgx, yrdf, yfdr, byzx, tjsj, tjlx)
select s.dm dm,
s.mc mc,
tj.xzrs,
tj.swgx,
tj.lwgx,
tj.ynwgx,
tj.yrdf,
tj.yfdr,
tj.byzx,
to_char(trunc(sysdate), 'yyyy-mm-dd'),
'2'
from (select sq,
sum(decode(t.tjlx, 1, t.tjsl, 0)) xzrs,
sum(decode(t.tjlx, 2, t.tjsl, 0)) swgx,
sum(decode(t.tjlx, 3, t.tjsl, 0)) lwgx,
sum(decode(t.tjlx, 4, t.tjsl, 0)) ynwgx,
sum(decode(t.tjlx, 5, t.tjsl, 0)) yrdf,
sum(decode(t.tjlx, 6, t.tjsl, 0)) yfdr,
sum(decode(t.tjlx, 7, t.tjsl, 0)) byzx
from (select tjsq sq, nvl(sum(tjsl), 0) tjsl, tjlx
from czw_fw_tj
where tjsj = to_char(trunc(sysdate), 'yyyy-mm-dd')
group by tjsq, tjlx) t
group by sq) tj,
czw_dm_sq s
where s.dm = tj.sq;
commit;
--创建索引
execute immediate 'create index index_czw_fw_czwry_tjfx_time on czw_fw_czwry_tjfx(tjsj)';
end;
/*
* 出租屋数据统计分析全部存储过程
*/
PROCEDURE PRO_CZW_ALL as
begin
PRO_QZLD_CZW_DELETE(); --删除索引，清空数据
PRO_QZLD_CZW_IMPORT(); --导入数据
PRO_CZW_FW_TJ(); --统计分析
PRO_CZW_FW_CZWRY_TJFX(); --将统计分析的数据再统计一次
end;
end;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
