老姜-政务数据治理项目-数据治理实战

曾经终败给现在 2022-08-31 13:18 234阅读 0赞

# 1. 数据分层介绍以及数据源梳理 #

**政务大数据治理数仓模型**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]

# 2.数据建模、数据模型mapping #

示例  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]

# 3. 主题层sql编写 #

–来源系统：消防技术服务机构和注册消防工程师业务信息管理系统  
–来源表：std\_xfyw\_p\_org\_info，std\_xfyw\_p\_org\_qual\_info，std\_xfyw\_p\_org\_reg\_info，std\_xfyw\_p\_org\_register

–1.取所有来源系统表机构信息进行汇总，按照机构代码，注册申请标识去重  
–2.按照注册申请标识进行关联获取注册信息和登记信息  
–3.因为都是来源一个系统，机构标识或者机构代码可作为主键  
–注意事项：去重分区排序是否合理？

with temp as (
                select
                   *,
                   row_number()over(partition by A.jgdm,A.zcsq_id order by A.jg_id Desc) as rn
                from(
                         SELECT 
                            jg_id	                     ,      --机构标识
                             ...
            		        'std_xfyw_p_org_info' as lyb        --来源表
                          FROM std_xfyw_p_org_info 
    					  WHERE dt=(SELECT max(dt)FROM std_xfyw_p_org_info)
                union all	
            	         SELECT 
                            jg_id	                     ,       --机构标识
                            ...
            		        'std_xfyw_p_org_qual_info' as lyb     --来源表
                           FROM 
    					   std_xfyw_p_org_qual_info WHERE dt=(SELECT max(dt)FROM std_xfyw_p_org_qual_info)
                           
            	          ) A  where A.rn=1  
                  )

insert OVERWRITE table prod_space.dwd_lj_xfjsfwjg partition(dt=${yyyyMMdd;-1})
        select
         jgbs       ,
         ...
    	 lyxt       ,
    	 concat_ws(',', lyb, lyb02, lyb03)
        from (
          select
             a.jg_id	                           as jgbs               , --机构标识
             ...                    as wjbj               ,  --完结标记
    		 a.lyb                                 as lyb                ,
    		 'std_xfyw_p_org_reg_info'             as lyb02              ,
    		 'std_xfyw_p_org_register'             as lyb03              ,
    		 '消防技术服务机构和注册消防工程师业务信息管理系统' as lyxt     -- 来源系统
           from
           (select * from temp) a
                       left join 
    				     std_xfyw_p_org_reg_info b on a.zcsq_id = b.zcsq_id					 
    				   left join 
    					 std_xfyw_p_org_register c on a.zscq_id =c.zcsq_id
    					  
            )d  
    		 group by 
    		 jgbs       ,
    	     ...
             wjbj       ,
    		 lyxt

具体的sql:

--来源系统：消防技术服务机构和注册消防工程师业务信息管理系统
    --来源表：std_xfyw_p_org_info，std_xfyw_p_org_qual_info，std_xfyw_p_org_reg_info，std_xfyw_p_org_register
    
    --1.取所有来源系统表机构信息进行汇总，按照机构代码，注册申请标识去重
    --2.按照注册申请标识进行关联获取注册信息和登记信息
    --3.因为都是来源一个系统，机构标识或者机构代码可作为主键
    --注意事项：去重分区排序是否合理？
    
    
    with temp as (
                select
                   *,
                   row_number()over(partition by A.jgdm,A.zcsq_id order by A.jg_id Desc) as rn
                from(
                         SELECT 
                            jg_id	                     ,      --机构标识
                            jgdm	                     ,      --机构代码
                            jgmc	                     ,      --机构名称
                            jg_summary	                 ,      --机构简介
                            status	                     ,      --状态： 0 代表正常 1 审核中 2 吊销 3 注销
                            ssdq	                     ,      --所属地区代码
                            du	                         ,      --地区
                            province	                 ,      --省级代码
                            city	                     ,      --市级代码
                            fddbr	                     ,      --法定代表人
                            fddbr_phone                  ,      --法定代表人联系电话
                            lxr	                         ,      --联系人
                            lxdh	                     ,      --联系电话
                            dwdz	                     ,      --单位地址
                            gfwz	                     ,      --官方网站
            		        jg_email	                 ,      --机构邮箱
                            create_string                ,      --发证日期
                            book_id                      ,      --单位资质证书
                            zcsq_id                      ,      --注册申请标识 
                            null as dj                   ,      --资质等级
                            null as finish_time          ,      -- 资质过期时间
                            null as start_time           ,      -- 资质生效时间
                            null as sfdj                 ,      -- 冻结状态（默认0，冻结状态为1）
                            null as reason               ,      -- 冻结理由
            		        null as barcode              ,      --资质条形码
            		        'std_xfyw_p_org_info' as lyb        --来源表
                          FROM std_xfyw_p_org_info 
    					  WHERE dt=(SELECT max(dt)FROM std_xfyw_p_org_info)
                union all	
            	         SELECT 
                            jg_id	                     ,       --机构标识
                            jgdm	                     ,       --机构代码
                            null as jgmc	             ,       --机构名称
                            null as jg_summary	         ,       --机构简介
                            null as status               ,       --状态： 0 代表正常 1 审核中 2 吊销 3 注销
                            null as ssdq	             ,       --所属地区代码
                            null as du	                 ,       --地区
                            null as province             ,       --省级代码
                            null as city                 ,       --市级代码
                            null as fddbr                ,       --法定代表人
                            null as fddbr_phone          ,       --法定代表人联系电话
                            null as lxr                  ,       --联系人
                            null as lxdh                 ,       --联系电话
                            null as dwdz                 ,       --单位地址
                            null as gfwz                 ,       --官方网站
            		        null as jg_email             ,       --机构邮箱
                            null as create_string        ,       --发证日期
                            book_id                      ,       --单位资质证书
                            zcsq_id                      ,       --注册申请标识 
                            dj                           ,       --资质等级
                            finish_time                  ,       -- 资质过期时间
                            start_time                   ,       -- 资质生效时间
                            sfdj                         ,       -- 冻结状态（默认0，冻结状态为1）
                            reason                       ,       -- 冻结理由
            		        barcode                      ,       --资质条形码
            		        'std_xfyw_p_org_qual_info' as lyb     --来源表
                           FROM 
    					   std_xfyw_p_org_qual_info WHERE dt=(SELECT max(dt)FROM std_xfyw_p_org_qual_info)
                           
            	          ) A  where A.rn=1  
                  )

insert OVERWRITE table prod_space.dwd_lj_xfjsfwjg partition(dt=${yyyyMMdd;-1})
        select
         jgbs       ,
         jgdm       ,
         jgmc       ,
         jgjj       ,
         jgzt       ,
         ssdqdm     ,
         dqmc       ,
         shengjidm  ,
         shijidm    ,
         fddbr      ,
         fddbrlxdh  ,
         lxr 	    ,
         lxdh	    ,
         dwdz       ,
         gfwz       ,
         jgyx       ,
         fzrq       ,
         dwzzzs     ,
         zssqbs     ,
         zzdj       ,
         zzgqsj     ,
         zzsxsj     ,
         djzt       ,
         djly       ,
         zztxm      ,
         clsj       ,
         zjzxyy     ,
          zczb      ,
          zrs       ,
          yjrs      ,
          ybrs      ,
          tzrs      ,
          cfxx      ,
          fwfw      ,
          pjjg      ,
         zclx       ,
         sslc       ,
         sqdkz      ,
         thyy       ,
         lczt       ,
         sqjglx     ,
         khzt       ,
         jrztkssj   ,
         jsztlcsj   ,
         jxzjkh     ,
         sftysl     ,
         sfwcsl     ,
         thbz       ,
         sbdjsj     ,
         sfff       ,
         sfspzx     ,
         fhslh      ,
         fhjg       ,
         sptgdjfhsj ,
         bytgjdsid  ,
         fhsid      ,
         sffslqjdsdx,
         spbyjdsl   ,
         wjbj       ,
    	 lyxt       ,
    	 concat_ws(',', lyb, lyb02, lyb03)
        from (
          select
             a.jg_id	                           as jgbs               , --机构标识
             a.jgdm	                               as jgdm               , --机构代码
             coalesce(a.jgmc,b.dwmc)               as jgmc               , --机构名称
             a.jg_summary                          as jgjj               , --机构简介
             a.status                              as jgzt               , --机构状态
             coalesce(a.ssdq,b.qudm)               as ssdqdm             , --区域代码
             coalesce(a.du,b.qu)                   as dqmc               , --区域名称
             a.province                            as shengjidm          , --省级代码
             a.city                                as shijidm            , --市级代码
             coalesce(a.fddbr,b.fddbr) 	           as fddbr              , --法定代表人
             a.fddbr_phone                         as fddbrlxdh          , --法定代表人联系电话
             coalesce(a.lxr,b.lxr)                 as lxr 	             , --联系人
             coalesce(a.lxdh,b.lxdh)               as lxdh	             , --联系电话
             coalesce(a.dwdz,b.dz)	               as dwdz               , --单位地址
             a.gfwz	                               as gfwz               , --官方网站
    		 coalesce(a.jg_email,b.jgemail) 	   as jgyx               , --机构邮箱
             coalesce(a.create_string,c.fzrq)      as fzrq               , --发证日期
             a.book_id                             as dwzzzs             , --单位资质证书
             a.zcsq_id                             as zssqbs             , --注册申请标识
             a.dj                                  as zzdj               ,  --资质等级
             coalesce(a.finish_time,c.finish_time) as zzgqsj             ,  -- 资质过期时间
             coalesce(a.start_time,c.start_time)   as zzsxsj             ,  -- 资质生效时间
             a.sfdj                                as djzt               ,  -- 冻结状态（默认0，冻结状态为1）
             a.reason                              as djly               ,  -- 冻结理由
    		 a.barcode                             as zztxm              ,  --资质条形码
    		 b.clsj                                as clsj               ,  --成立时间
    		 c.zz_zx_reason                        as zjzxyy             ,  --资质注销原因
    		 b.zczb                                as  zczb              ,  --注册资本
             b.zrs                                 as  zrs               ,  --总人数
             b.yjrs                                as  yjrs              ,  --一级人数
             b.ybrs                                as  ybrs              ,  --一般人数
             b.tzrs                                as  tzrs              ,  -- 特种人数
             b.cfxx                                as  cfxx              ,  -- 处罚信息
             b.fwfw                                as  fwfw              ,  --服务范围 
             b.pj                                  as  pjjg              ,  --评价
             c.zclx	                               as zclx               ,  --注册类型
             c.sslc	                               as sslc               ,  --所属流程
             c.sqdkz	                           as sqdkz              ,  --申请单快照
             c.back_reason	                       as thyy               ,  --退回原因
             c.status	                           as lczt               ,  --流程状态： 01 退回 02 预审 07 受理 04 审批，05完成，03审核，06注销，08暂停，09待提交;10 复核
             c.service_dj	                       as sqjglx             ,  --申请消防技术服务机构类型
             c.isstop	                           as khzt               ,  --考核暂停 0未暂停 1已暂停
             c.stop_start_time                     as jrztkssj           ,  --进入暂停开始时间
             c.stop_end_time                       as jsztlcsj           ,  --结束暂停流程时间
             c.iszt	                               as jxzjkh             ,  --进行专家考核： 0未进入 1已进入 2已提交报告
             c.is_agree_accept                     as sftysl             ,  --是否同意受理(审核人) ： 1 同意受理 2 不同意受理
             c.is_com_accept                       as sfwcsl             ,  --是否完成受理(预审人) ： 1 已受理 2 未完成受理
             c.back_status	                       as thbz               ,  --退回标志 ： 1 审批退回预审
             c.create_time	                       as sbdjsj             ,  --申报提交时间
             c.is_grant	                           as sfff               ,  --是否发放 0 未发放 1 已发放 2 已签收
             c.is_sp	                           as sfspzx             ,  --是否审批注销 1 其他地方注销 2审批注销
             c.recom_slh	                       as fhslh              ,  --复核受理号
             c.recom_result	                       as fhjg               ,  --复核结果0不通过1 通过
             c.sp_time	                           as sptgdjfhsj         ,  --审批通过提交复核时间
             c.not_pass_fileid                     as bytgjdsid          ,  --予通过决定书id
             c.recom_fileid	                       as fhsid              ,  --复核书id
             c.is_send_msg	                       as sffslqjdsdx        ,  --是否发送过领取决定书短信;0未发送，1已发送
             c.not_pass_slh	                       as spbyjdsl           ,  --审批不予决定受理号
             c.end_remarks	                       as wjbj               ,  --完结标记
    		 a.lyb                                 as lyb                ,
    		 'std_xfyw_p_org_reg_info'             as lyb02              ,
    		 'std_xfyw_p_org_register'             as lyb03              ,
    		 '消防技术服务机构和注册消防工程师业务信息管理系统' as lyxt     -- 来源系统
           from
           (select * from temp) a
                       left join 
    				     std_xfyw_p_org_reg_info b on a.zcsq_id = b.zcsq_id					 
    				   left join 
    					 std_xfyw_p_org_register c on a.zscq_id =c.zcsq_id
    					  
            )d  
    		 group by 
    		 jgbs       ,
    	     jgdm       ,
             jgmc       ,
             jgjj       ,
             jgzt       ,
             ssdqdm     ,
             dqmc       ,
             shengjidm  ,
             shijidm    ,
             fddbr      ,
             fddbrlxdh  ,
             lxr 	    ,
             lxdh	    ,
             dwdz       ,
             gfwz       ,
             jgyx       ,
             fzrq       ,
             dwzzzs     ,
             zssqbs     ,
             zzdj       ,
             zzgqsj     ,
             zzsxsj     ,
             djzt       ,
             djly       ,
             zztxm      ,
             clsj       ,
             zjzxyy     ,
              zczb      ,
              zrs       ,
              yjrs      ,
              ybrs      ,
              tzrs      ,
              cfxx      ,
              fwfw      ,
              pjjg      ,
             zclx       ,
             sslc       ,
             sqdkz      ,
             thyy       ,
             lczt       ,
             sqjglx     ,
             khzt       ,
             jrztkssj   ,
             jsztlcsj   ,
             jxzjkh     ,
             sftysl     ,
             sfwcsl     ,
             thbz       ,
             sbdjsj     ,
             sfff       ,
             sfspzx     ,
             fhslh      ,
             fhjg       ,
             sptgdjfhsj ,
             bytgjdsid  ,
             fhsid      ,
             sffslqjdsdx,
             spbyjdsl   ,
             wjbj       ,
    		 lyxt

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]: /images/20220829/247327fbbc2143458477b796dba8b6bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]: /images/20220829/20928ba681554203a9f43c0d1405b4a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]: /images/20220829/5fc522fc351e4daba0b0731339b718c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]: /images/20220829/6e08ffadab6542afa1e0b159074e119b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]: /images/20220829/a8c01cdd3069442ebc77f69fff882931.png