converted_data AS (
SELECT
contra_no,
no_product,
fpd,
latest_pry_tm,
COALESCE(
DATEDIFF(TO_DATE('20250805', 'yyyyMMdd'), TO_DATE(latest_pry_tm, 'yyyy-MM-dd')),
CAST(latest_pry_tm AS DOUBLE)
) AS numeric_value
FROM risk_proxy
WHERE DATEDIFF(TO_DATE('20250805', 'yyyyMMdd'), TO_DATE(fst_loan_pmt_due_dt, 'yyyy-MM-dd')) >= 1
),
-- 分箱处理
binned_data AS (
SELECT
contra_no,
no_product,
fpd,
numeric_value,
CASE
WHEN numeric_value IS NULL THEN '其他区间'
WHEN numeric_value > 0 AND numeric_value <= 1 THEN '(0,1]'
WHEN numeric_value > 1 AND numeric_value <= 2 THEN '(1,2]'
WHEN numeric_value > 2 AND numeric_value <= 3 THEN '(2,3]'
WHEN numeric_value > 3 AND numeric_value <= 4 THEN '(3,4]'
WHEN numeric_value > 4 AND numeric_value <= 5 THEN '(4,5]'
WHEN numeric_value > 5 AND numeric_value <= 6 THEN '(5,6]'
WHEN numeric_value > 6 AND numeric_value <= 7 THEN '(6,7]'
WHEN numeric_value > 7 AND numeric_value <= 8 THEN '(7,8]'
WHEN numeric_value > 8 AND numeric_value <= 9 THEN '(8,9]'
WHEN numeric_value > 9 AND numeric_value <= 10 THEN '(9,10]'
WHEN numeric_value > 10 AND numeric_value <= 15 THEN '(10,15]'
WHEN numeric_value > 15 AND numeric_value <= 20 THEN '(15,20]'
WHEN numeric_value > 20 AND numeric_value <= 200 THEN '(20,200]'
ELSE '其他区间'
END AS time_bin
FROM converted_data
)
SELECT
time_bin AS `放款天数分箱`,
no_product AS `产品类型`,
COUNT(DISTINCT contra_no) AS `总样本数`,
SUM(CASE WHEN fpd >= 1 THEN 1 ELSE 0 END) AS fpd1_amt,
ROUND(SUM(CASE WHEN fpd >= 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT contra_no), 4) AS fpd1,
ROUND(
COALESCE(
(SUM(CASE WHEN fpd >= 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT contra_no))
/ NULLIF((SELECT global_fpd1 FROM overall), 0),
0), 4) AS fpd1_lift
FROM binned_data
GROUP BY time_bin, no_product
ORDER BY no_product,
CASE time_bin
WHEN '(0,1]' THEN 1
WHEN '(1,2]' THEN 2
WHEN '(2,3]' THEN 3
WHEN '(3,4]' THEN 4
WHEN '(4,5]' THEN 5
WHEN '(5,6]' THEN 6
WHEN '(6,7]' THEN 7
WHEN '(7,8]' THEN 8
WHEN '(8,9]' THEN 9
WHEN '(9,10]' THEN 10
WHEN '(10,15]' THEN 11
WHEN '(15,20]' THEN 12
WHEN '(20,200]' THEN 13
ELSE 14
END; 我修改了代码但是报语法错误
最新发布