-
场景为：查询某一个项目目前的成本耗费进度情况
-
问题：执行物化视图创建执行语句可以查到具体的数据信息，但是直接查询物化视图没有数据
- 操作1 ： 执行创建语句中的计算逻辑
- 操作2： 直接查询物化视图
创建完成物化视图后，直接查询物化视图数值显示为0
- 操作3：将整体逻辑t1 拆成一张物化视图，tcost拆为另一张物化视图，
将项目的维度数据与成本等事实数据拆分开后，分别创建对应的物化视图后，再次关联后创建物化视图就可以显示具体的数据。
- 操作1 ： 执行创建语句中的计算逻辑
-
物化视图创建的sql语句为：
其中包含了一个java udf函数get_Child（str , map）获取当前code下面的所有层级的子code信息，用于递归汇总
CREATE MATERIALIZED VIEW ywdb.dws_crm_proj_ccontrol_sta
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DISTRIBUTED BY HASH(prj_code) BUCKETS 3
as
select t1.prj_code as prj_code,
t1.prj_short_name as prj_short_name,
t1.prj_name as prj_name,
t1.dept_code as dept_code,
t1.dept_name as dept_name,
t1.client_code as client_code,
t1.client_short_name as client_short_name,
t1.client_name as client_name,
t1.prj_manager_code as prj_manager_code,
t1.prj_manager as prj_manager,
t1.min_node as min_node,
t1.prj_stage as prj_stage,
t1.prj_state as prj_state,
t1.proj_state as proj_state,
t1.sign_min_date as sign_min_date,
t1.prj_start_date as prj_start_date,
t1.prj_end_date as prj_end_date,
t1.prj_money as prj_money,
t1.prj_money_yuan as prj_money_yuan,
t1.contract_money as contract_money,
t1.contract_money_yuan as contract_money_yuan,
ifnull(tcost.bx_money_yuan, 0.0) as bx_money_yuan,
ifnull(tcost.ry_nb_money_yuan, 0.0) as ry_nb_money_yuan,
ifnull(tcost.bx_money_yuan, 0.0) +
ifnull(tcost.ry_nb_money_yuan, 0.0) as internal_cost_yuan,
ifnull(tcost.external_cost_yuan, 0.0) as external_cost_yuan,
ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0) +
ifnull(tcost.external_cost_yuan, 0.0) as total_cost_yuan,
round((ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0)) / 10000, 2) internal_cost,
round(ifnull(tcost.external_cost_yuan, 0.0) / 10000, 2) as external_cost,
round(
(ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0) +
ifnull(tcost.external_cost_yuan, 0.0)) / 10000, 2
) as total_cost,
round((ifnull(t1.prj_money_yuan, 0.0) -
(ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0) +
ifnull(tcost.external_cost_yuan, 0.0))) / 10000, 2) as gross_profit,
ifnull(t1.prj_money_yuan, 0.0) -
(ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0) +
ifnull(tcost.external_cost_yuan, 0.0)) as gross_profit_yuan,
round((ifnull(t1.prj_money_yuan, 0.0) -
(ifnull(tcost.bx_money_yuan, 0.0) + ifnull(tcost.ry_nb_money_yuan, 0.0) +
ifnull(tcost.external_cost_yuan, 0.0)))
/ ifnull(t1.prj_money_yuan, 0.0), 2) as gross_margin,
t1.if_prje as if_prje,
t1.manage_caliber as manage_caliber,
t1.create_company as create_company,
t1.is_osp as is_osp
from (select tpd.prj_code as prj_code,
tpd.prj_short_name as prj_short_name,
tpd.prj_name as prj_name,
tpd.dept_code as dept_code,
tpd.dept_name as dept_name,
tpd.client_code as client_code,
tcd.client_short_name as client_short_name,
tcd.client_name as client_name,
tpd.prj_manager_code as prj_manager_code,
tpd.prj_manager as prj_manager,
t_node.node_stage as min_node,
tpd.prj_stage as prj_stage,
tpd.prj_state as prj_state,
case
when tpd.prj_stage = '项目锁定' and tpd.close_type in ('放弃', '丢弃') then '无效项目'
when tpd.prj_stage = '外部验收' and tpd.close_type = '验收关闭' then '竣工验收'
when tpd.prj_stage in ('运维', '立项', '预立项', '内部验收') or
(tpd.prj_stage = '项目锁定' and tpd.close_type = '暂停') then '尚在交付'
end as proj_state,
tpd.sign_min_date as sign_min_date,
tpd.prj_start_date as prj_start_date,
tpd.prj_end_date as prj_end_date,
round(tpd.prj_money / 10000, 2) as prj_money,
tpd.prj_money as prj_money_yuan,
round(tpd.contract_money / 10000, 2) as contract_money,
tpd.contract_money as contract_money_yuan,
if(ifnull(tpd.prj_money, 0.0) <> ifnull(tpd.contract_money, 0.0), 1, 0) as if_prje,
if(tpd.prj_apply_type = '运维', 1, 0) as is_osp,
tpd.manage_caliber as manage_caliber,
tpd.create_company as create_company
from ywdb.t_project_data tpd
join ywdb.t_matterlist_data tmd
on tpd.prj_code = tmd.matter_code and tmd.valid_data = 1 and tmd.is_matter = '项目' and
tmd.purchase_type is null
left join ywdb.t_client_data tcd on tpd.client_code = tcd.client_code and tcd.valid_data = 1
left join (select prj_code,
node_stage
from (select prj_code,
node_stage,
row_number() over ( partition by prj_code order by node_date) as rn
from ywdb.t_project_node_data
where valid_data = 1
and node_valid is null) t1
where rn = 1) t_node on tpd.prj_code = t_node.prj_code
where tpd.valid_data = 1) t1
left join (select tcode.matter_code,
sum(ifnull(treim.all_cost_money, 0.0)) as bx_money_yuan,
sum(ifnull(tuser.cost_money, 0.0)) as ry_nb_money_yuan,
sum(ifnull(tpur.conp_money, 0.0)) as external_cost_yuan
from (select distinct matter_code, c_code
from (select matter_code, map_agg(matter_code, p_matter_code) over () as str_map
from (select tmd.matter_code, tmd.p_matter_code
from ywdb.t_matterlist_data tmd
join ywdb.t_project_data tpd
on tmd.matter_code = tpd.prj_code and tpd.valid_data = 1
where tmd.valid_data = 1
and purchase_type is null) t1) t2 LATERAL VIEW EXPLODE( split_by_string( if(replace(get_Child(matter_code, str_map), ' ', '') = '', matter_code,
concat(matter_code, ',', replace(get_Child(matter_code, str_map), ' ', ''))) ,',')) tmp as c_code) tcode
left join (
-- 报销
select ifnull(allo_matter_code, t_reim.matter_code) as matter_code,
sum(ifnull(t_reim.cost_money, 0.0)) as all_cost_money
from (select rsd_id as d_id,
rd_type,
rd_code,
cost_money,
matter_code
from (select concat('S', t1.rsd_id) as rsd_id,
t2.rd_type,
t1.rd_code,
t1.cost_money,
ifnull(t2.delivery_type, t1.delivery_type) as delivery_type,
ifnull(t2.matter_code, t1.matter_code) as matter_code
from ywdb.t_reimburse_subject_data t1
join ywdb.t_reimburse_data t2
on t1.rd_code = t2.rd_code and t2.valid_data = 1
where t1.valid_data = 1
union all
select concat('T', t1.rtd_id) as rtd_id,
t2.rd_type,
t1.rd_code,
t1.cost_money,
ifnull(t2.delivery_type, t1.delivery_type) as delivery_type,
ifnull(t2.matter_code, t1.matter_code) as matter_code
from ywdb.t_reimburse_travel_data t1
join ywdb.t_reimburse_data t2
on t1.rd_code = t2.rd_code and t2.valid_data = 1
where t1.valid_data = 1) tt
where delivery_type = '项目费用') t_reim
left join ywdb.t_contractsale_settle_cost_allocation_reimburse tcscar
on tcscar.valid_data = 1
and t_reim.d_id = tcscar.d_id
and t_reim.rd_type = tcscar.rd_type
and t_reim.rd_code = tcscar.rd_code
and t_reim.matter_code = tcscar.matter_code
group by ifnull(allo_matter_code, t_reim.matter_code)) treim on tcode.c_code = treim.matter_code
left join (
-- 人工
select ifnull(tcscau.allo_matter_code, t1.matter_code) as matter_code,
sum(ifnull(t1.paylist_money, 0)) as cost_money
from ywdb.ods_ywdb_matterworktimess t1
left join ywdb.t_contractsale_settle_cost_allocation_user tcscau
on t1.ymwm_id = tcscau.ymwm_id
and tcscau.valid_data = 1
where t1.valid_data = 1
group by ifnull(tcscau.allo_matter_code, t1.matter_code)) tuser
on tcode.c_code = tuser.matter_code
left join (
-- 采购
select prj_code, sum(ifnull(conp_money, 0.0)) as conp_money
from (
-- 采购合同 - 分配剩余
select tcond.conp_code,
tcond.prj_code,
tcond.conp_money - ifnull(tcam.all_money, 0.0) as conp_money
from (select tcpm.conp_code, tcpm.prj_code, tcpd.conp_money
from ywdb.t_contractpurchase_project_map tcpm
join ywdb.t_contractpurchase_data tcpd
on tcpm.conp_code = tcpd.conp_code and tcpd.valid_data = 1 and
tcpd.sign_type = 'CDT01'
where tcpm.valid_data = 1
and tcpm.prj_code <> ''
and cssd_code is null) tcond
left join (select conp_code, sum(ifnull(allo_money, 0.0)) as all_money
from ywdb.t_contractpurchase_allocation_map
where valid_data = 1
and sign_type = '合同'
group by conp_code) tcam on tcond.conp_code = tcam.conp_code
union all
-- 采购合同 - 分配出的
select conp_code, allo_matter_code, allo_money
from ywdb.t_contractpurchase_allocation_map
where valid_data = 1
and sign_type = '合同'
union all
-- 采购协议 - 分配剩余
select tcssd.cssd_code,
tcssd.matter_code,
tcssd.fin_settle_money - ifnull(tcam.allo_money, 0.0) as settle_money
from (select t1.cssd_code,
t1.matter_code,
sum(ifnull(t1.fin_settle_money, 0.0)) as fin_settle_money
from (select tcmsd.cssd_code,
if(tcmsd.service_cycle <= '202505',
ifnull(tcmsd.prj_code, tcmsd.matter_code),
tcmsd.matter_code) as matter_code,
tcmsd.fin_settle_money
from ywdb.t_contract_matter_settlement_detailed tcmsd
join ywdb.t_contract_settlement_doc_data tcsdd
on tcmsd.cssd_code = tcsdd.cssd_code and tcsdd.valid_data = 1
where tcmsd.valid_data = 1) t1
group by t1.cssd_code, t1.matter_code) tcssd
left join (select conp_code,
matter_code,
sum(ifnull(allo_money, 0)) as allo_money
from ywdb.t_contractpurchase_allocation_map
where valid_data = 1
and sign_type = '结算单'
group by conp_code, matter_code) tcam
on tcssd.cssd_code = tcam.conp_code and
tcssd.matter_code = tcam.matter_code
union all
-- 采购协议 - 分配
select conp_code, allo_matter_code, sum(ifnull(allo_money, 0)) as allo_money
from ywdb.t_contractpurchase_allocation_map
where valid_data = 1
and sign_type = '结算单'
group by conp_code, allo_matter_code) tpur
group by prj_code) tpur on tcode.c_code = tpur.prj_code
group by tcode.matter_code) tcost on t1.prj_code = tcost.matter_code;