福鼎手机网站建设国际十大市场营销公司

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

政务大数据治理数仓模型

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

示例

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 rnfrom(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})selectjgbs       ,...lyxt       ,concat_ws(',', lyb, lyb02, lyb03)from (selecta.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) aleft 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 rnfrom(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})selectjgbs       ,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 (selecta.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          ,  --予通过决定书idc.recom_fileid	                       as fhsid              ,  --复核书idc.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) aleft 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