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;
显示代码有语法错误
最新发布