昆山建设网站seo广告
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