【详述】同样的建视图语句，在 3.1.3 中有数据，在 3.2.8 中没有数据
【背景】StarRocks 从版本 3.1.3 升级到 3.2.8 。其中基表为视图和普通表
【是否存算分离】否
【StarRocks版本】3.8.2
【建表语句】CREATE MATERIALIZED VIEW test_v_abs_cont_loan_pool
DISTRIBUTED BY HASH(loan_serial)
REFRESH ASYNC START(‘2024-06-20 02:00:00’) EVERY (interval 1 day)
AS SELECT
etl_time ,-- ETL时间
policy_no ,-- 保单号码
pol_no ,-- 险种号码
loan_serial ,-- 贷款批单号
actu_get_no ,-- 实付号码
intrst_tp_cd ,-- 贷款利息方式编码
intrst_mode_cd ,-- 利率类型编码
order_no ,-- 顺序号码
loan_effective_date ,-- 贷款日期
loan_maturity_date ,-- 贷款到期日
datediff(loan_maturity_date, loan_effective_date) as loan_term,-- 贷款期限
case when DATE_FORMAT(loan_maturity_date,’%Y%m%d’) <= now() then 0
else datediff(loan_maturity_date, now())
end as loan_remain_term ,-- 贷款剩余期限（天）
pay_off_date ,-- 还清日期
pay_off_flg ,-- 还清标志
loan_rate ,-- 贷款利率
round(lnbj_money,4) as loan_money ,-- 贷款本金
lnlx_money ,-- 贷款利息
rfbj_money ,-- 已还本金金额
rflx_money ,-- 已还利息金额
round(lnbj_money-rfbj_money,4) as base_loan_money,-- 截至基准日贷款未偿本金
round(lnlx_money-rflx_money,4) as base_loan_interest,-- 截至基准日未偿利息余额
sign_date ,-- 保单签单日期
c_vali_date ,-- 险种生效日期
policy_risk_code ,-- 险种编码
policy_risk_name ,-- 险种名称
borrower_id ,
appnt_name ,
borrower_birthday,
TIMESTAMPDIFF(YEAR, borrower_birthday, CURDATE()) AS borrower_age,
applicant_id_number,-- 投保人身份证号
policy_status,-- 保单状态
app_flag_name,
round(cash_value,4) as base_policy_cash_value,-- 截至基准日的现金价值
round(case when cash_value=0 then 0 else (lnbj_money+lnlx_money-rfbj_money-rflx_money)/cash_value end ,4)100 as base_pledge_ratio,-- 截至基准日的质押率（截至基准日的贷款本息和/截至基准日的现金价值）（计算）
case when pay_off_flg=1 then ‘0’
when pay_off_flg=0
and DATE_FORMAT(loan_maturity_date,’%Y%m%d’) < DATE_FORMAT(now(), ‘%Y%m%d’)
then ‘1’
else ‘2’ end as base_loan_status,-- 截至基准日贷款状态
case when pay_off_flg=1 then ‘0’
when pay_off_flg=0 and (lnbj_money+lnlx_money-rfbj_money-rflx_money)>cash_value then ‘1’
else ‘0’ end as is_loan_exceed_policy, – 是否超停（是否本息超现金价值）
lnbj_money/cash_value as loan_money_ratio, – 现金价值比例
(lnbj_money+lnlx_money-rflx_money)/cash_value as loan_ratio – 未达到或超过现金价值
,edorreasoncode, codename
from (
select
now() as etl_time ,-- ETL时间
t1.contno as policy_no ,-- 保单号码
t1.polno as pol_no ,-- 险种号码
t1.edorno as loan_serial ,-- 贷款批单号
t1.actugetno as actu_get_no ,-- 实付号码
t1.interesttype as intrst_tp_cd ,-- 贷款利息方式编码
t1.interestmode as intrst_mode_cd ,-- 利率类型编码
t1.orderno as order_no ,-- 顺序号码
t1.loandate as loan_effective_date ,-- 贷款日期
t1.payoffdate as pay_off_date ,-- 还清日期
case when DATE_FORMAT(t1.payoffdate,’%Y%m%d’) >= now() then 0 else t1.payoffflag end as pay_off_flg ,-- 还清标志
t1.interestrate100 as loan_rate ,-- 贷款利率
coalesce(t2.LNBJ_money,0) as LNBJ_money ,-- 贷款金额
coalesce(t2.lnlx_money,0) as lnlx_money ,-- 贷款利息
coalesce(t3.returnmoney,0) as rfbj_money ,-- 已还本金金额
coalesce(t3.returninterest,0) as rflx_money ,-- 已还利息金额
coalesce(cast(date_add(t1.loandate,t6.loandefferdays) as datetime),
cast(date_add(t1.loandate,180) as datetime)) as loan_maturity_date ,-- 贷款到期日 优先取贷款配置表里的展期天数 其他默认180天
t7.signdate as sign_date ,-- 保单签单日期
t7.cvalidate as c_vali_date ,-- 险种生效日期
t7.riskcode as policy_risk_code ,-- 险种编码
t8.riskname as policy_risk_name ,-- 险种名称
t9.customerno as borrower_id ,-- 借款人ID
t9.name as appnt_name,
t9.birthday as borrower_birthday,-- 借款人出生日期
CONCAT(SUBSTRING(t9.idno, 1, LENGTH(t9.idno) - 4), ‘****’) as applicant_id_number,-- 投保人身份证号
t7.appflag as policy_status,
case when t7.appflag=‘1’ then ‘有效’ when ‘4’ then ‘终止’ when ‘0’ then ‘未承保’ else t7.appflag end as app_flag_name,
t10.cash_value, t3.edorreasoncode, t3.codename,
row_number() over(partition by t1.contno,t1.polno,t1.edorno order by t1.modifydate desc) as rn
from data_warehouse_test.t_ods_core_loloan_prod t1 – 贷款表
left join v_abs_loan_money t2
on t1.edorno = t2.loanno
and t1.contno = t2.contno
and t1.polno = t2.polno
left join v_abs_return_money t3 on t1.edorno=t3.loanno and t1.contno=t3.contno and t1.polno=t3.polno
left join ( select loanconfigno,
cast(loandefferdays as int) loandefferdays
from data_warehouse_test.t_ods_core_ldpubrate_prod
where loanconfigno is not null
group by loanconfigno,
loandefferdays
)t6
on t1.loanrulecode = t6.loanconfigno
left join data_warehouse_test.t_ods_core_lcpol_prod t7
on t1.polno = t7.polno
left join data_warehouse_test.t_ods_core_lmriskapp_prod t8 on t7.riskcode = t8.riskcode
left join data_warehouse_test.t_ods_core_ldperson_prod t9 on t7.appntno = t9.customerno
left join v_abs_cash_value t10 on t1.contno=t10.contno and t1.polno=t10.polno and DATE_FORMAT(t10.etl_time, ‘%Y%m%d’)=DATE_FORMAT(now(), ‘%Y%m%d’)
where t8.risktype3=‘3’ and not exists(select t11.contno from v_abs_cont_trans_lifelong as t11 where t1.contno = t11.contno)
)t
where rn =1
;
【建表后执行刷新】
【查看视图任务】
