T6substring

AC代码:

 

 

- 授信表仅用于提取时间(不参与状态过滤) WITH credit_times AS ( SELECT apply_no, apply_time AS credit_apply_time FROM hw_jhy_iceberg.ods.ods_uds_apply_main_df WHERE dt = '20250825' ), -- 系数表提取审批通过数据(分母来源) approved_coeff AS ( SELECT no_appl, CAST( substring_index(substring_index(rsn_fnlres, 'raise_coeff_random:', -1), '$', 1) AS DOUBLE ) AS raise_coeff_random_value, substring_index(substring_index(inner_var, '"is_first_loan":"', -1), '"', 1) AS is_first_loan FROM hw_jhy_iceberg.lods_dp.dp_stdproc_credit_dca_audit_result_hive WHERE res_audit = 'accept' -- 核心修改:分母来源 AND rsn_fnlres LIKE '%raise_coeff_random:%' AND no_product = '6002' ), -- 复贷用户随机系数分区 reloan_bands AS ( SELECT no_appl, is_first_loan, CASE WHEN raise_coeff_random_value > 0.5 AND raise_coeff_random_value <= 0.7 THEN '(0.5,0.7]' WHEN raise_coeff_random_value > 0.7 AND raise_coeff_random_value <= 1 THEN '(0.7,1]' WHEN raise_coeff_random_value > 1 AND raise_coeff_random_value <= 1.3 THEN '(1,1.3]' WHEN raise_coeff_random_value > 1.3 AND raise_coeff_random_value <= 1.5 THEN '(1.3,1.5]' WHEN raise_coeff_random_value > 1.5 AND raise_coeff_random_value <= 2 THEN '(1.5,2]' END AS coeff_band FROM approved_coeff WHERE is_first_loan = '1' -- 复贷用户 ), -- 提现申请统计 cash_applications AS ( SELECT apply_no, apply_dt AS cash_apply_dt, COUNT(1) AS cash_count FROM hw_jhy_iceberg.dwd.dwd_ap_wdraw_aprv_det_df WHERE dt = '20250824' GROUP BY apply_no, apply_dt ), -- 三表关联计算时间差 time_intervals AS ( SELECT rb.no_appl AS apply_no, rb.coeff_band, ca.cash_apply_dt, ca.cash_count, FLOOR((UNIX_TIMESTAMP(ca.cash_apply_dt) - UNIX_TIMESTAMP(ct.credit_apply_time)) / 86400) AS days_diff FROM reloan_bands rb JOIN credit_times ct ON rb.no_appl = ct.apply_no -- 仅用于时间计算 LEFT JOIN cash_applications ca ON rb.no_appl = ca.apply_no ), -- 基础指标计算 base_metrics AS ( SELECT coeff_band, CASE WHEN days_diff = 0 THEN 't0' WHEN days_diff = 1 THEN 't1' WHEN days_diff = 2 THEN 't2' WHEN days_diff = 3 THEN 't3' WHEN days_diff = 4 THEN 't4' WHEN days_diff = 5 THEN 't5' WHEN days_diff = 6 THEN 't6' WHEN days_diff = 7 THEN 't7' ELSE 't7+' END AS time_band, -- 分母:系数表审批通过量(复贷用户) COUNT(DISTINCT ti.apply_no) AS credit_approved_count, -- 分子:提现申请次数 SUM(cash_count) AS cash_application_count FROM time_intervals ti GROUP BY coeff_band, time_band ) -- 最终结果:复贷提现通过率 SELECT coeff_band AS `随机额度系数区间`, MAX(CASE WHEN time_band = 't0' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t0_提现通过率`, MAX(CASE WHEN time_band = 't1' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t1_提现通过率`, MAX(CASE WHEN time_band = 't2' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t2_提现通过率`, MAX(CASE WHEN time_band = 't3' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t3_提现通过率`, MAX(CASE WHEN time_band = 't4' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t4_提现通过率`, MAX(CASE WHEN time_band = 't5' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t5_提现通过率`, MAX(CASE WHEN time_band = 't6' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t6_提现通过率`, MAX(CASE WHEN time_band = 't7' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t7_提现通过率`, MAX(CASE WHEN time_band = 't7+' THEN ROUND(cash_application_count / credit_approved_count, 4) END) AS `t7+_提现通过率` FROM base_metrics GROUP BY coeff_band ORDER BY CASE coeff_band WHEN '(0.5,0.7]' THEN 1 WHEN '(0.7,1]' THEN 2 WHEN '(1,1.3]' THEN 3 WHEN '(1.3,1.5]' THEN 4 WHEN '(1.5,2]' THEN 5 END; 显示报错:Error: org.apache.spark.sql.catalyst.parser.ParseException:
08-27
WITH credit_times AS ( SELECT apply_no, apply_time AS credit_apply_time FROM hw_jhy_iceberg.ods.ods_uds_apply_main_df WHERE dt = '20250825' and AND time_inst > '2025-08-20 00:00:00' ), -- 系数表提取审批通过数据(分母) approved_coeff AS ( SELECT no_appl, CAST(substring_index(substring_index(rsn_fnlres, 'raise_coeff_random:', -1), '$', 1) AS DOUBLE) AS raise_coeff_random_value, substring_index(substring_index(inner_var, '"is_first_loan":"', -1), '"', 1) AS is_first_loan FROM hw_jhy_iceberg.lods_dp.dp_stdproc_credit_dca_audit_result_hive WHERE res_audit = 'accept' AND rsn_fnlres LIKE '%raise_coeff_random:%' AND no_product = '6002' AND time_inst > '2025-08-20 00:00:00' ), -- 复贷用户随机系数分区 reloan_bands AS ( SELECT no_appl, CASE WHEN raise_coeff_random_value > 0.5 AND raise_coeff_random_value <= 0.7 THEN '(0.5,0.7]' WHEN raise_coeff_random_value > 0.7 AND raise_coeff_random_value <= 1 THEN '(0.7,1]' WHEN raise_coeff_random_value > 1 AND raise_coeff_random_value <= 1.3 THEN '(1,1.3]' WHEN raise_coeff_random_value > 1.3 AND raise_coeff_random_value <= 1.5 THEN '(1.3,1.5]' WHEN raise_coeff_random_value > 1.5 AND raise_coeff_random_value <= 2 THEN '(1.5,2]' END AS coeff_band FROM approved_coeff WHERE is_first_loan = '1' -- 仅复贷用户 ), -- 分母计算(按系数区间) denominator AS ( SELECT coeff_band, COUNT(DISTINCT no_appl) AS total_users FROM reloan_bands GROUP BY coeff_band ), -- 提现申请统计 cash_applications AS ( SELECT apply_no, apply_dt AS cash_apply_dt FROM hw_jhy_iceberg.dwd.dwd_ap_wdraw_aprv_det_df WHERE dt = '20250824' ), -- 三表关联计算时间差 time_intervals AS ( SELECT rb.no_appl, rb.coeff_band, ca.cash_apply_dt, FLOOR((UNIX_TIMESTAMP(ca.cash_apply_dt) - UNIX_TIMESTAMP(ct.credit_apply_time)) / 86400) AS days_diff FROM reloan_bands rb JOIN credit_times ct ON rb.no_appl = ct.apply_no LEFT JOIN cash_applications ca ON rb.no_appl = ca.apply_no ), -- 分子计算(按时间区间) numerator AS ( SELECT coeff_band, CASE WHEN days_diff = 0 THEN 't0' WHEN days_diff = 1 THEN 't1' WHEN days_diff = 2 THEN 't2' WHEN days_diff = 3 THEN 't3' WHEN days_diff = 4 THEN 't4' WHEN days_diff = 5 THEN 't5' WHEN days_diff = 6 THEN 't6' WHEN days_diff = 7 THEN 't7' ELSE 't7+' END AS time_band, COUNT(DISTINCT no_appl) AS cash_users -- 分子:提现用户数 FROM time_intervals WHERE cash_apply_dt IS NOT NULL -- 仅统计有提现记录 GROUP BY coeff_band, time_band ) -- 最终结果:复贷提现通过率 SELECT d.coeff_band AS `随机额度系数区间`, d.total_users AS `授信通过单量`, SUM(COALESCE(n.cash_users, 0)) AS `发起提现单量`, ROUND(SUM(CASE WHEN n.time_band = 't0' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t0_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't1' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t1_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't2' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t2_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't3' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t3_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't4' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t4_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't5' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t5_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't6' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t6_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't7' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t7_提现通过率`, ROUND(SUM(CASE WHEN n.time_band = 't7+' THEN COALESCE(n.cash_users, 0) ELSE 0 END) / d.total_users, 4) AS `t7+_提现通过率` FROM denominator d LEFT JOIN numerator n ON d.coeff_band = n.coeff_band GROUP BY d.coeff_band, d.total_users ORDER BY CASE d.coeff_band WHEN '(0.5,0.7]' THEN 1 WHEN '(0.7,1]' THEN 2 WHEN '(1,1.3]' THEN 3 WHEN '(1.3,1.5]' THEN 4 WHEN '(1.5,2]' THEN 5 END; 显示代码有语法错误
最新发布
08-29
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值