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 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
673
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
