基于房价指数与抵押数据表，用SQL实现各年12月账户索引LTV计算

核心计算口径 #

索引化LTV的计算核心是把初始房产估值，通过房价指数调整到对应统计时点的房产市值，再匹配同时间点的贷款余额计算价值占比，具体计算规则：

• 索引化房产价值 = 估值时点评估价值 *（统计时点房价指数值 / 估值日期所在月的房价指数值）
• 区域匹配规则：抵押房产所在区域（PropertyArea）与指数表的覆盖区域（IndexArea）做一一对应，跨区域不做指数匹配
• 索引化LTV = 对应统计时点的贷款未偿余额 / 该时点计算得到的索引化房产价值

前置准备校验 #

跑数前需先完成两项基础校验，避免结果偏差：

• 校验数据表中所有抵押房产的区域值域，与指数表的区域值域完全对齐，无缺失匹配项
• 校验所有抵押记录的估值日期，都能在对应区域的指数表中找到匹配月份的指数值；201612、201712、201812三个统计时点的对应区域指数值无缺失

可运行SQL实现 #

WITH index_stat_points AS (
    -- 提取三个固定统计时点的指数值，减少扫描数据量
    SELECT
        IndexArea,
        IndexMonth,
        Movement
    FROM index_table
    WHERE IndexMonth IN ('201612', '201712', '201812')
),
mortgage_base_info AS (
    SELECT
        Mortgageno,
        PropertyArea,
        ValuationDate,
        PropertyValuation,
        BalanceDecember2016,
        BalanceDecember2017,
        BalanceDecember2018,
        -- 估值日期转换为与IndexMonth一致的yyyymm格式作为关联键
        DATE_FORMAT(ValuationDate, '%Y%m') AS ValuationMonthKey
    FROM data_table
),
-- 关联估值当月的基准指数
base_with_valuation_index AS (
    SELECT
        mbi.*,
        idx_base.Movement AS ValuationMonthIndex
    FROM mortgage_base_info mbi
    LEFT JOIN index_table idx_base
        ON mbi.PropertyArea = idx_base.IndexArea
        AND mbi.ValuationMonthKey = idx_base.IndexMonth
)
SELECT
    bwi.Mortgageno,
    bwi.PropertyArea,
    bwi.ValuationDate,
    bwi.PropertyValuation,
    -- 2016年12月时点指标
    idx_2016.Movement AS IndexValue201612,
    bwi.PropertyValuation * idx_2016.Movement / bwi.ValuationMonthIndex AS IndexedPropertyValue201612,
    bwi.BalanceDecember2016,
    bwi.BalanceDecember2016 / (bwi.PropertyValuation * idx_2016.Movement / bwi.ValuationMonthIndex) AS IndexedLTV201612,
    -- 2017年12月时点指标
    idx_2017.Movement AS IndexValue201712,
    bwi.PropertyValuation * idx_2017.Movement / bwi.ValuationMonthIndex AS IndexedPropertyValue201712,
    bwi.BalanceDecember2017,
    bwi.BalanceDecember2017 / (bwi.PropertyValuation * idx_2017.Movement / bwi.ValuationMonthIndex) AS IndexedLTV201712,
    -- 2018年12月时点指标
    idx_2018.Movement AS IndexValue201812,
    bwi.PropertyValuation * idx_2018.Movement / bwi.ValuationMonthIndex AS IndexedPropertyValue201812,
    bwi.BalanceDecember2018,
    bwi.BalanceDecember2018 / (bwi.PropertyValuation * idx_2018.Movement / bwi.ValuationMonthIndex) AS IndexedLTV201812
FROM base_with_valuation_index bwi
LEFT JOIN index_stat_points idx_2016
    ON bwi.PropertyArea = idx_2016.IndexArea
    AND idx_2016.IndexMonth = '201612'
LEFT JOIN index_stat_points idx_2017
    ON bwi.PropertyArea = idx_2017.IndexArea
    AND idx_2017.IndexMonth = '201712'
LEFT JOIN index_stat_points idx_2018
    ON bwi.PropertyArea = idx_2018.IndexArea
    AND idx_2018.IndexMonth = '201812'

适配调整说明 #

• 若使用Oracle数据库，日期格式化函数DATE_FORMAT(ValuationDate, '%Y%m')需替换为TO_CHAR(ValuationDate, 'YYYYMM')
• 若使用Hive数仓，日期格式化函数需调整为DATE_FORMAT(ValuationDate, 'yyyyMM')
• 若出现指数值缺失的记录，对应计算结果会返回空值，可单独筛出缺失数据补全后重跑，避免错误计算
• 若关联后出现数据量翻倍的情况，需检查指数表中是否存在同一区域、同一月份的重复指数值，提前做去重清洗
• 计算过程中需避免除零错误，若某条记录的估值月指数值为0或空值，需过滤后再做比值计算

内容的提问来源于stack exchange，提问作者Georgina Stanley