👉 點擊關注不迷路
👉 點擊關注不迷路
👉 點擊關注不迷路

---

PostgreSQL金融風控分析之風險指標可視化監控實戰

一、引言

在金融領域，風險控制是核心環節之一。

• 準確、及時地監控風險指標，對于金融機構做出明智決策、降低潛在損失至關重要。
• PostgreSQL作為一款強大的開源關系型數據庫，具備高效的數據存儲、處理和分析能力，能夠為金融風控分析提供堅實的數據基礎。
• 本文將結合實際案例，詳細闡述如何利用PostgreSQL進行金融風控分析中的風險指標可視化監控，從數據清洗到可視化展示的全流程。

二、案例背景

某商業銀行面臨著日益復雜的金融風險環境，需要對貸款業務的風險進行全面監控。

• 該銀行擁有大量的客戶數據、貸款數據、還款記錄等，數據存儲在PostgreSQL數據庫中。
• 我們的目標是通過對這些數據的分析，提取關鍵的風險指標，并進行可視化展示，以便管理層和風控人員能夠直觀地了解風險狀況，及時采取應對措施。

三、數據準備

（一）數據來源與字段說明

數據主要來源于銀行的貸款業務系統，包括以下幾個關鍵表：

• 1. 客戶信息表（customer_info）：包含客戶ID（customer_id）、姓名（customer_name）、年齡（age）、性別（gender）、職業（occupation）、信用評級（credit_rating）等字段。
• 2. 貸款申請表（loan_application）：包含貸款申請ID（application_id）、客戶ID（customer_id）、貸款金額（loan_amount）、貸款期限（loan_term）、申請日期（application_date）、貸款類型（loan_type）等字段。
• 3. 還款記錄表（repayment_record）：包含還款記錄ID（record_id）、貸款申請ID（application_id）、還款日期（repayment_date）、應還金額（due_amount）、實際還款金額（actual_repayment_amount）、是否逾期（is_overdue）等字段。
  • 建表語句及測試數據構造

    -- 創建客戶信息表
    CREATE TABLE IF NOT EXISTS customer_info (customer_id SERIAL PRIMARY KEY,customer_name VARCHAR(50) NOT NULL,age SMALLINT CHECK (age BETWEEN 18 AND 70),gender VARCHAR(1) CHECK (gender IN ('M', 'F')),occupation VARCHAR(30),credit_rating VARCHAR(10) CHECK (credit_rating IN ('優秀', '良好', '中等', '較差'))
    );-- 創建貸款申請表
    CREATE TABLE IF NOT EXISTS loan_application (application_id SERIAL PRIMARY KEY,customer_id INT NOT NULL REFERENCES customer_info(customer_id),loan_amount DECIMAL(12,2) CHECK (loan_amount > 0),loan_term SMALLINT CHECK (loan_term BETWEEN 6 AND 120),  -- 貸款期限（月）application_date DATE,loan_type VARCHAR(10) CHECK (loan_type IN ('信用貸款', '抵押貸款', '擔保貸款'))
    );-- 創建還款記錄表（修正：添加due_date應還日期字段）
    CREATE TABLE IF NOT EXISTS repayment_record (record_id SERIAL PRIMARY KEY,application_id INT NOT NULL REFERENCES loan_application(application_id),due_date DATE NOT NULL,  -- 新增：應還日期（關鍵字段）repayment_date DATE,  -- 實際還款日期due_amount DECIMAL(12,2) CHECK (due_amount > 0),  -- 應還金額actual_repayment_amount DECIMAL(12,2) CHECK (actual_repayment_amount >= 0),  -- 實際還款金額is_overdue BOOLEAN  -- 是否逾期（實際還款日期 > 應還日期）
    );-- 插入100條客戶信息（使用隨機函數生成）
    INSERT INTO customer_info (customer_name, age, gender, occupation, credit_rating)
    SELECT '客戶' || g.id,floor(random() * 53 + 18)::SMALLINT,  -- 18-70歲隨機CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END,(ARRAY['教師','醫生','程序員','公務員','自由職業','企業主','設計師','銷售','工程師','學生'])[floor(random()*10)+1],(ARRAY['優秀','良好','中等','較差'])[floor(random()*4)+1]
    FROM generate_series(1,100) AS g(id);-- 插入300條貸款申請記錄（每個客戶1-3條）
    INSERT INTO loan_application (customer_id, loan_amount, loan_term, application_date, loan_type)
    SELECT (random() * 100 + 1)::INT,  -- 隨機關聯客戶ID（1-100）floor(random() * 495000 + 5000)::DECIMAL(12,2),  -- 5000-500000元隨機金額floor(random() * 115 + 6)::SMALLINT,  -- 6-120期隨機期限(current_date - (random() * 1825)::INT)::DATE,  -- 最近5年隨機申請日期（1825天≈5年）(ARRAY['信用貸款','抵押貸款','擔保貸款'])[floor(random()*3)+1]
    FROM generate_series(1,300);-- 還款記錄表數據（修正：通過子查詢解決別名引用）
    INSERT INTO repayment_record (application_id, due_date, repayment_date, due_amount, actual_repayment_amount, is_overdue)
    WITH temp_data AS (SELECT la.application_id,la.application_date + (g.id * 30)::INT AS due_date,CASE WHEN random() > 0.3 THEN la.application_date + (g.id * 30)::INT + (random() * 15)::INT  -- 30%逾期（+0-15天）ELSE la.application_date + (g.id * 30)::INT - (random() * 5)::INT  -- 70%正常（-0-5天）END AS repayment_date,la.monthly_due AS due_amount,CASE WHEN random() > 0.05 THEN la.monthly_due  -- 95%正常還款ELSE floor(random() * la.monthly_due)::DECIMAL(12,2)  -- 5%部分還款END AS actual_repayment_amountFROM (SELECT application_id,application_date,loan_term,loan_amount / loan_term AS monthly_dueFROM loan_application) laCROSS JOIN generate_series(1,4) AS g(id)
    )
    SELECT application_id,due_date,repayment_date,due_amount,actual_repayment_amount,(repayment_date > due_date) AS is_overdue  -- 現在可以安全引用repayment_date別名
    FROM temp_data
    LIMIT 1200;
    

（二）數據清洗

• 1. 缺失值處理：通過SQL查詢檢查各表中的缺失值情況。
  • 對于客戶信息表中的信用評級字段，若存在缺失值，根據客戶的其他信息（如職業、收入等）進行合理填充，或者將其標記為未知類別。
  • 對于貸款申請表中的貸款期限字段，若存在缺失值，由于該字段是關鍵信息，無法通過其他字段推斷，因此刪除相應的缺失記錄。

-- 檢查客戶信息表信用評級缺失值
SELECT COUNT(*) FROM customer_info WHERE credit_rating IS NULL;-- 填充信用評級缺失值（示例，實際需根據業務規則）
UPDATE customer_info SET credit_rating = 'medium' WHERE credit_rating IS NULL AND occupation = 'employee';-- 刪除貸款申請表中貸款期限缺失的記錄
DELETE FROM loan_application WHERE loan_term IS NULL;

• 2. 異常值處理：對于貸款金額字段，通過統計分析確定合理的范圍，將明顯超出正常范圍的異常值視為錯誤數據并進行修正或刪除。
  • 例如，貸款金額不能為負數，若存在負數記錄，檢查是否為輸入錯誤，若是則進行修正，否則刪除。

-- 檢查貸款金額異常值（負數）
SELECT * FROM loan_application WHERE loan_amount < 0;-- 刪除貸款金額為負數的異常記錄
DELETE FROM loan_application WHERE loan_amount < 0;

• 3. 數據一致性檢查：確保各表之間的數據關聯正確，例如貸款申請表中的客戶ID必須存在于客戶信息表中，還款記錄表中的貸款申請ID必須存在于貸款申請表中。
  • 通過外鍵約束或SQL查詢進行檢查，對于不匹配的數據進行修正或刪除。

-- 檢查貸款申請表中客戶ID不存在于客戶信息表的記錄
SELECT la.* FROM loan_application la LEFT JOIN customer_info ci ON la.customer_id = ci.customer_id WHERE ci.customer_id IS NULL;-- 刪除不匹配的貸款申請記錄
DELETE FROM loan_application WHERE customer_id NOT IN (SELECT customer_id FROM customer_info);

四、風險指標計算

（一）逾期率計算

逾期率是衡量貸款風險的重要指標之一，它表示逾期還款的貸款占總貸款的比例。

• 計算公式為：逾期率 = 逾期貸款筆數 / 總貸款筆數 × 100%。

在PostgreSQL中，通過以下SQL語句計算各貸款類型的逾期率：

SELECT loan_type, COUNT(CASE WHEN is_overdue = 'yes' THEN 1 END) AS overdue_loans,COUNT(*) AS total_loans,ROUND(COUNT(CASE WHEN is_overdue = 'yes' THEN 1 END) * 100.0 / COUNT(*), 2) AS overdue_rate
FROM loan_application la
JOIN repayment_record rr ON la.application_id = rr.application_id
GROUP BY loan_type;

計算結果如下表所示：

（二）不良貸款率計算

不良貸款率是指不良貸款（通常指逾期90天以上的貸款）占總貸款的比例。

• 計算公式為：不良貸款率 = 不良貸款筆數 / 總貸款筆數 × 100%。

首先，需要確定逾期90天以上的貸款記錄。

• 通過計算還款日期與應還日期的時間差，判斷是否逾期90天以上。
• 在PostgreSQL中，使用AGE函數計算時間差：

SELECT rr.record_id, la.application_id, la.loan_type, AGE(rr.repayment_date, due_date) AS days_overdue
FROM repayment_record rr
JOIN loan_application la ON rr.application_id = la.application_id;

• 然后，計算不良貸款率：

SELECT loan_type, COUNT(CASE WHEN days_overdue > '90 days' THEN 1 END) AS bad_loans,COUNT(*) AS total_loans,ROUND(COUNT(CASE WHEN days_overdue > '90 days' THEN 1 END) * 100.0 / COUNT(*), 2) AS bad_loan_rate
FROM (SELECT rr.record_id, la.application_id, la.loan_type, AGE(rr.repayment_date, due_date) AS days_overdueFROM repayment_record rrJOIN loan_application la ON rr.application_id = la.application_id
) AS subquery
GROUP BY loan_type;

計算結果如下表所示：

（三）客戶信用評分分布

客戶信用評級是評估客戶信用風險的重要依據。

• 通過統計不同信用評級的客戶數量及其占比，了解客戶的信用狀況分布。

SELECT credit_rating, COUNT(*) AS customer_count,ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customer_info), 2) AS percentage
FROM customer_info
GROUP BY credit_rating
ORDER BY credit_rating;

結果如下表：

五、風險指標可視化

（一）可視化工具選擇

為了實現風險指標的可視化監控，我們選擇使用Tableau作為可視化工具。

• Tableau具有強大的數據可視化能力，能夠快速將PostgreSQL中的數據轉化為直觀、交互式的圖表和儀表盤，方便用戶進行數據分析和監控。

（二）可視化圖表設計

• 1. 逾期率對比柱狀圖：以貸款類型為橫軸，逾期率為縱軸，繪制柱狀圖，直觀展示不同貸款類型的逾期率差異。
  • 通過顏色區分不同的貸款類型，使圖表更加清晰易讀。
• 2. 不良貸款率趨勢線圖：按時間維度（如月份）統計不良貸款率，繪制趨勢線圖，觀察不良貸款率的變化趨勢。
  • 可以設置預警線，當不良貸款率超過預警線時，發出警示信號。
• 3. 客戶信用評分餅圖：以信用評級為分類，繪制餅圖，展示不同信用評級客戶的占比情況。
  • 通過標簽顯示具體的占比數據，方便用戶快速了解客戶信用分布。

（三）儀表盤搭建

將上述可視化圖表整合到一個儀表盤上，形成風險指標可視化監控界面。

• 儀表盤可以包含篩選器，允許用戶根據不同的條件（如時間范圍、貸款類型等）進行數據篩選，查看相應的風險指標。
• 同時，設置交互功能，當用戶點擊某個圖表中的數據時，其他相關圖表會進行聯動更新，以便更深入地分析數據。

六、結論與建議

（一）結論

通過利用PostgreSQL進行金融風控分析中的風險指標可視化監控，我們能夠有效地對貸款業務的風險進行評估和監控。

• 從數據清洗到風險指標計算，再到可視化展示，整個流程實現了數據的高效處理和分析，為金融機構提供了直觀、準確的風險信息。
• 通過對不同貸款類型的逾期率、不良貸款率以及客戶信用評分分布的分析，我們可以發現信用貸款的風險相對較高，需要重點關注和加強風控措施。

（二）建議

• 1. 針對信用貸款的高風險特點，進一步優化信用評估模型，加強對信用評級較低客戶的審核和監控，提高信用貸款的準入門檻。
• 2. 定期更新和維護風險指標可視化儀表盤，確保數據的及時性和準確性。同時，根據業務需求和市場變化，不斷優化可視化圖表和指標體系，提高監控的有效性。
• 3. 結合其他數據分析方法和技術，如機器學習算法，對客戶的信用風險進行更精準的預測和評估，為風險控制提供更科學的依據。

通過以上實戰案例，我們展示了如何利用PostgreSQL和Tableau實現金融風控分析中的風險指標可視化監控。

• 這一流程不僅適用于商業銀行的貸款業務風險監控，也可以為其他金融領域的風險分析提供參考和借鑒。
• 在實際應用中，需要根據具體的業務需求和數據特點，靈活調整分析方法和可視化方案，以實現更好的風險控制效果。

以上是完整的金融風控分析案例中風險指標可視化監控內容。

• 你可以和我說說對文章內容、結構等方面的看法，若有其他修改或補充需求，也請告知我。