oracle-出租屋区、派出所、社区、楼宇、房屋级联各项指标统计包

本文详细介绍了出租屋数据的导入、清理、统计及分析流程,包括楼宇、房屋、人员数据的导入与索引创建,以及数据的定时分析与统计结果的存储。通过此过程,实现对出租屋人员动态的有效监控。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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;

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值