Data Engineering cho Phân tích Rủi ro 🏠
🏠 Trang chủ / Data Engineering cho Phân tích Rủi ro / Phần 0 · Nền tảng: rủi ro & dữ liệu / Module 3
🧰 Module 3 / 18 · 2 tuần Cơ bản

Bộ công cụ: SQL & Python cho dữ liệu rủi ro

Cài đặt môi trường, đọc từ điển dữ liệu An Phát, rồi viết truy vấn thật: doanh thu, bảng tuổi nợ, top đại lý với hàm cửa sổ, phát hiện đại lý xấu dần bằng LAG, và ma trận dự phòng bằng pandas. Mọi kết quả trong bài là kết quả chạy thật.

DuckDBSQL windowpandasBảng tuổi nợ
📌 Vì sao module này quan trọng

SQL là ngôn ngữ chung của dữ liệu rủi ro: kiểm toán viên, chuyên viên rủi ro và kỹ sư đều đọc được. Python bổ sung phần mô hình và tự động hoá. Thành thạo hai công cụ này, đặc biệt là hàm cửa sổ và tư duy “tại ngày chốt”, là đủ để làm 80% công việc phân tích rủi ro hằng ngày.

🎯 Mục tiêu học tập

  • Cài DuckDB + pandas và truy vấn thẳng file CSV
  • Hiểu từ điển dữ liệu và khoá của bộ dữ liệu An Phát
  • Viết truy vấn bảng tuổi nợ đúng tại một ngày chốt
  • Dùng hàm cửa sổ: RANK, SUM OVER, LAG, trung bình trượt, QUALIFY
  • Tính ma trận dự phòng bằng pandas và biết khi nào dùng SQL, khi nào dùng Python

🟢 Cài đặt trong 5 phút

# Cài Python 3.10+ rồi:
python -m venv .venv
# Windows: .venv\Scripts\activate     macOS/Linux: source .venv/bin/activate
pip install duckdb pandas numpy scikit-learn
# Tải các file CSV ở trang tổng quan khoá học vào thư mục data/ rồi kiểm tra:
cd data
python -c "import duckdb; print(duckdb.read_csv('invoices.csv').shape)"
# (23453, 6)  -> 23.453 hoá đơn, 6 cột

Vì sao DuckDB? Đây là cơ sở dữ liệu phân tích chạy ngay trong tiến trình Python, không cần cài server, đọc trực tiếp CSV/Parquet và đủ nhanh cho hàng trăm triệu dòng trên laptop. Cú pháp SQL gần với PostgreSQL, nên kiến thức mang sang hệ thống lớn (Postgres, Snowflake, BigQuery, Databricks) gần như nguyên vẹn.

Không có máy cài đặt?

Dán truy vấn vào DuckDB Web Shell (shell.duckdb.org) và tải file CSV lên trình duyệt. Mọi truy vấn trong khoá đều chạy được ở đó.

🟢 Từ điển dữ liệu: hiểu bảng trước khi truy vấn

BảngKhoáCột chínhÝ nghĩa nghiệp vụ
dealers.csvdealer_idregion, segment, credit_limit_mvnd, term_days, onboard_dateMột dòng = một đại lý. Hạn mức tính bằng triệu đồng.
invoices.csvinvoice_iddealer_id, invoice_date, due_date, amount_vnd, paid_dateMột dòng = một hoá đơn bán chịu. paid_date trống = chưa trả tại 31/12/2025.
fx_usdvnd.csvdateusd_vndTỷ giá ngày làm việc (không có thứ Bảy, Chủ nhật).
vendor_payments.csvpayment_idvendor_id, payment_date, hour, amount_vnd, invoice_no, created_byThanh toán cho nhà cung cấp năm 2025.
vendors.csv / employees.csvvendor_id / employee_idbank_accountDanh mục, dùng để đối chiếu tài khoản ngân hàng.
  • Grain (độ chi tiết) của invoices là hoá đơn; mỗi hoá đơn trả một lần (đơn giản hoá). Thực tế thường có bảng thanh toán riêng, một hoá đơn trả nhiều lần.
  • dpd (days past due) = ngày chốt − ngày đến hạn. Âm nghĩa là chưa đến hạn.
  • Mọi số tiền là đồng (VND), số nguyên; khi báo cáo chia 1e9 ra tỷ.

🟢 Truy vấn đầu tiên

-- Doanh thu bán chịu theo năm (DuckDB đọc thẳng file CSV, tự nhận kiểu dữ liệu)
SELECT year(invoice_date)            AS nam,
       COUNT(*)                      AS so_hoa_don,
       COUNT(DISTINCT dealer_id)     AS so_dai_ly,
       ROUND(SUM(amount_vnd) / 1e9)  AS doanh_thu_ty
FROM 'invoices.csv'
WHERE invoice_date >= DATE '2024-01-01'
GROUP BY nam
ORDER BY nam;
+------+------------+-----------+--------------+
| nam  | so_hoa_don | so_dai_ly | doanh_thu_ty |
+------+------------+-----------+--------------+
| 2024 |     10,127 |       547 |        3,033 |
| 2025 |     10,988 |       599 |        3,234 |
+------+------------+-----------+--------------+
(2 dòng)

DuckDB tự nhận invoice_date là kiểu DATE nên dùng được year(). Số đại lý năm 2025 nhiều hơn năm 2024 vì có đại lý mới; doanh thu tăng khoảng 6–7%.

🟡 Bảng tuổi nợ (aging) tại một ngày chốt

Đây là truy vấn quan trọng nhất của rủi ro tín dụng thương mại. Điểm mấu chốt nằm ở điều kiện “còn nợ tại ngày chốt”: hoá đơn đã phát hành trước ngày chốt và (chưa trả hoặc trả sau ngày chốt). Thiếu vế “trả sau ngày chốt” thì khi tính lại tháng cũ, bạn sẽ bỏ sót các hoá đơn lúc đó còn nợ.

-- Bảng tuổi nợ (aging) tại ngày chốt 31/12/2025
WITH open_ar AS (                                   -- hoá đơn còn nợ tại ngày chốt
  SELECT *, DATE '2025-12-31' - due_date AS dpd     -- dpd = số ngày quá hạn
  FROM 'invoices.csv'
  WHERE invoice_date <= DATE '2025-12-31'
    AND (paid_date IS NULL OR paid_date > DATE '2025-12-31')
)
SELECT CASE WHEN dpd <= 0  THEN '0. Trong hạn'
            WHEN dpd <= 30 THEN '1. 1–30'
            WHEN dpd <= 60 THEN '2. 31–60'
            WHEN dpd <= 90 THEN '3. 61–90'
            ELSE                '4. >90' END        AS nhom_qua_han,
       COUNT(*)                                     AS so_hd,
       ROUND(SUM(amount_vnd) / 1e9, 1)              AS du_no_ty,
       ROUND(100 * SUM(amount_vnd) / SUM(SUM(amount_vnd)) OVER (), 1) AS ty_trong_pct
FROM open_ar
GROUP BY 1
ORDER BY 1;
+--------------+-------+----------+--------------+
| nhom_qua_han | so_hd | du_no_ty | ty_trong_pct |
+--------------+-------+----------+--------------+
| 0. Trong hạn |  1336 |    531.3 |           86 |
| 1. 1–30      |    95 |     32.4 |          5.2 |
| 2. 31–60     |     3 |      2.4 |          0.4 |
| 3. 61–90     |    33 |      9.3 |          1.5 |
| 4. >90       |   180 |     42.1 |          6.8 |
+--------------+-------+----------+--------------+
(5 dòng)
Bảng tuổi nợ An Phát ngày 31/12/2025 (tỷ đồng)0153305458611531Trong hạn32.41–302.431–609.361–9042.1>90
6,8% phải thu đã quá hạn hơn 90 ngày: vượt ngưỡng khẩu vị 5% ở module 2. Gần như toàn bộ nhóm này thuộc 65 đại lý đã ngừng thanh toán.
Mẹo đọc

Nhóm 31–60 ngày rất nhỏ nhưng nhóm >90 lớn: đây là dấu hiệu nợ “nhảy cóc”: khách hoặc trả gần đúng hạn, hoặc ngừng hẳn. Với dữ liệu kiểu này, theo dõi riêng nhóm 1–30 ngày (chỉ báo sớm) quan trọng hơn chờ nợ lên 90 ngày.

🟡 Hàm cửa sổ: xếp hạng và tỷ trọng luỹ kế

Hàm cửa sổ (OVER (...)) tính trên một “cửa sổ” các dòng mà không gộp mất chi tiết. QUALIFY lọc trên kết quả hàm cửa sổ (giống HAVING cho GROUP BY); có trong DuckDB, Snowflake, BigQuery, Databricks. Với PostgreSQL, bọc trong một CTE rồi lọc WHERE hang <= 10.

-- Top 10 đại lý theo dư nợ: hạn mức đã dùng, quá hạn lâu nhất, tỷ trọng luỹ kế
WITH open_ar AS (
  SELECT * FROM 'invoices.csv'
  WHERE invoice_date <= DATE '2025-12-31'
    AND (paid_date IS NULL OR paid_date > DATE '2025-12-31')
), by_dealer AS (
  SELECT d.dealer_id, d.segment, d.credit_limit_mvnd,
         SUM(o.amount_vnd) / 1e6                  AS du_no_trieu,
         MAX(DATE '2025-12-31' - o.due_date)      AS qua_han_max
  FROM open_ar o
  JOIN 'dealers.csv' d USING (dealer_id)
  GROUP BY ALL
)
SELECT dealer_id, segment,
       ROUND(du_no_trieu)                                   AS du_no_trieu,
       ROUND(100 * du_no_trieu / credit_limit_mvnd)         AS dung_han_muc_pct,
       qua_han_max,
       RANK() OVER (ORDER BY du_no_trieu DESC)              AS hang,
       ROUND(100 * SUM(du_no_trieu) OVER (ORDER BY du_no_trieu DESC)
                 / SUM(du_no_trieu) OVER (), 1)             AS luy_ke_pct
FROM by_dealer
QUALIFY hang <= 10
ORDER BY hang;
+-----------+---------+-------------+------------------+-------------+------+------------+
| dealer_id | segment | du_no_trieu | dung_han_muc_pct | qua_han_max | hang | luy_ke_pct |
+-----------+---------+-------------+------------------+-------------+------+------------+
| DL0516    | Cấp 1   |       5,983 |              101 |          12 |    1 |          1 |
| DL0309    | Cấp 1   |       5,703 |              107 |           6 |    2 |        1.9 |
| DL0481    | Cấp 1   |       5,344 |               98 |           3 |    3 |        2.8 |
| DL0150    | Cấp 1   |       5,205 |               88 |           9 |    4 |        3.6 |
| DL0353    | Cấp 1   |       4,858 |              100 |          11 |    5 |        4.4 |
| DL0356    | Cấp 1   |       4,834 |              115 |           3 |    6 |        5.2 |
| DL0038    | Cấp 1   |       4,690 |               90 |           5 |    7 |        5.9 |
| DL0549    | Cấp 1   |       4,665 |               93 |          -2 |    8 |        6.7 |
| DL0076    | Cấp 1   |       4,354 |               96 |         -21 |    9 |        7.4 |
| DL0408    | Cấp 1   |       4,227 |               74 |           0 |   10 |        8.1 |
+-----------+---------+-------------+------------------+-------------+------+------------+
(10 dòng)

Đọc kết quả: 10 đại lý lớn nhất đều là Cấp 1, chiếm 8,1% tổng phải thu (dưới ngưỡng tập trung 25%), nhưng nhiều đại lý đang dùng xấp xỉ hoặc vượt 100% hạn mức vào mùa cao điểm Tết. Đây là thông tin phòng kinh doanh cần mỗi sáng, không phải cuối tháng.

🟡 LAG và trung bình trượt: bắt tín hiệu xấu đi

Đại lý DL0004 vỡ nợ tháng 7/2025. Liệu dữ liệu có báo trước? LAG() lấy giá trị dòng trước để so sánh, AVG() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) tính trung bình 3 tháng gần nhất.

-- Hành vi trả tiền theo tháng của đại lý DL0004: có xấu đi dần không?
WITH m AS (
  SELECT strftime(paid_date, '%Y-%m')          AS thang,
         COUNT(*)                              AS so_hd_tra,
         AVG(paid_date - due_date)             AS tre_tb
  FROM 'invoices.csv'
  WHERE paid_date IS NOT NULL AND dealer_id = 'DL0004'
  GROUP BY 1
)
SELECT thang, so_hd_tra,
       ROUND(tre_tb, 1)                                         AS tre_tb_ngay,
       ROUND(tre_tb - LAG(tre_tb) OVER (ORDER BY thang), 1)     AS so_voi_thang_truoc,
       ROUND(AVG(tre_tb) OVER (ORDER BY thang
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1)      AS tb_truot_3_thang
FROM m
WHERE thang >= '2024-10'
ORDER BY thang;
+---------+-----------+-------------+--------------------+------------------+
| thang   | so_hd_tra | tre_tb_ngay | so_voi_thang_truoc | tb_truot_3_thang |
+---------+-----------+-------------+--------------------+------------------+
| 2024-10 |         1 |           4 | NULL               |                4 |
| 2024-12 |         2 |         7.5 |                3.5 |              5.8 |
| 2025-02 |         2 |         6.5 |                 -1 |                6 |
| 2025-03 |         1 |           0 |               -6.5 |              4.7 |
| 2025-04 |         3 |           5 |                  5 |              3.8 |
| 2025-05 |         2 |         6.5 |                1.5 |              3.8 |
| 2025-06 |         1 |          38 |               31.5 |             16.5 |
| 2025-07 |         1 |          42 |                  4 |             28.8 |
| 2025-08 |         2 |          33 |                 -9 |             37.7 |
+---------+-----------+-------------+--------------------+------------------+
(9 dòng)
🎬 Tín hiệu có trước

Từ cuối 2024 đến tháng 5/2025, DL0004 trả trễ 0–7 ngày: hoàn toàn bình thường. Tháng 6/2025 độ trễ nhảy lên 38 ngày (+31,5 ngày so với tháng trước), rồi 42 ngày. Một quy tắc đơn giản “trễ trung bình tháng tăng > 15 ngày so với tháng trước” chạy hằng ngày đã có thể cảnh báo trước khi đại lý ngừng hẳn, và kịp khoá hạn mức trước khi họ lấy thêm hàng tháng 6–7.

Lưu ý thống kê

Một tháng chỉ có 1–3 hoá đơn nên độ trễ trung bình dao động mạnh. Quy tắc thực tế nên kết hợp: số tiền quá hạn, số hoá đơn quá hạn, và trung bình trượt.

🔴 Cùng bài toán bằng pandas: ma trận dự phòng

Ma trận dự phòng (provision matrix) áp một tỷ lệ tổn thất cho từng nhóm tuổi nợ. Đây là cách đơn giản hoá được IFRS 9 cho phép với phải thu thương mại.

import pandas as pd

inv = pd.read_csv("invoices.csv", parse_dates=["invoice_date", "due_date", "paid_date"])
snap = pd.Timestamp("2025-12-31")

# 1) Lọc hoá đơn còn nợ tại ngày chốt
open_ar = inv[(inv.invoice_date <= snap) & (inv.paid_date.isna() | (inv.paid_date > snap))].copy()
open_ar["dpd"] = (snap - open_ar.due_date).dt.days

# 2) Chia nhóm quá hạn và áp tỷ lệ tổn thất (ma trận dự phòng)
labels = ["Trong hạn", "1–30", "31–60", "61–90", ">90"]
open_ar["nhom"] = pd.cut(open_ar.dpd, [-10_000, 0, 30, 60, 90, 10_000], labels=labels)
loss_rate = {"Trong hạn": 0.005, "1–30": 0.02, "31–60": 0.08, "61–90": 0.25, ">90": 0.60}

t = open_ar.groupby("nhom", observed=True)["amount_vnd"].sum().div(1e9).to_frame("du_no_ty")
t["ty_le_ton_that"] = t.index.map(loss_rate).astype(float)
t["du_phong_ty"] = t["du_no_ty"] * t["ty_le_ton_that"]
print(t.round(2))
print("Tổng dư nợ:", round(t.du_no_ty.sum(), 1), "tỷ | Tổng dự phòng:", round(t.du_phong_ty.sum(), 1), "tỷ")
           du_no_ty  ty_le_ton_that  du_phong_ty
nhom                                            
Trong hạn    531.30            0.00         2.66
1–30          32.38            0.02         0.65
31–60          2.40            0.08         0.19
61–90          9.26            0.25         2.32
>90           42.10            0.60        25.26
Tổng dư nợ: 617.5 tỷ | Tổng dự phòng: 31.1 tỷ
Dùng SQL khiDùng Python/ pandas khi
Lọc, nối, gộp, cửa sổ trên dữ liệu lớnThống kê, mô hình, mô phỏng (module 11–14)
Logic cần chạy trong kho dữ liệu, dbt, dashboardGọi API, đọc file định dạng lạ, tự động hoá
Người kiểm toán cần đọc đượcTrực quan hoá nhanh, notebook khám phá

⚠️ Sai lầm thường mắc

❌ Lọc “chưa trả” bằng paid_date IS NULL khi tính ngày chốt trong quá khứ

Vì sao nguy hiểm: Bỏ sót hoá đơn trả sau ngày chốt → aging quá khứ thấp hơn thực tế.

Cách làm đúng: Dùng điều kiện (paid_date IS NULL OR paid_date > ngày chốt).

❌ Tính dpd từ ngày hoá đơn

Vì sao nguy hiểm: Phóng đại quá hạn với khách được kỳ hạn 60 ngày.

Cách làm đúng: dpd = ngày chốt − ngày đến hạn.

❌ SUM trên bảng đã JOIN nhân dòng

Vì sao nguy hiểm: Join hoá đơn với bảng nhiều-nhiều làm tổng tiền bị nhân đôi.

Cách làm đúng: Kiểm tra số dòng trước/sau join; gộp trước khi join.

❌ Dùng ngày hôm nay (CURRENT_DATE) trong truy vấn báo cáo

Vì sao nguy hiểm: Chạy lại hôm khác ra số khác, không tái lập được.

Cách làm đúng: Tham số hoá ngày chốt.

❌ So sánh tiền bằng float

Vì sao nguy hiểm: Sai số làm tròn khi đối soát.

Cách làm đúng: Lưu tiền dạng số nguyên (đồng) hoặc DECIMAL.

❌ Chỉ nhìn số tổng

Vì sao nguy hiểm: Không thấy đại lý đang xấu đi.

Cách làm đúng: Kết hợp cửa sổ theo từng đại lý (LAG, trung bình trượt).

📌 Cần nhớ
  • DuckDB truy vấn thẳng CSV/Parquet, SQL gần PostgreSQL.
  • Còn nợ tại ngày chốt: invoice_date ≤ chốt AND (paid_date IS NULL OR paid_date > chốt).
  • dpd = ngày chốt − ngày đến hạn.
  • Hàm cửa sổ giữ chi tiết: RANK, SUM OVER, LAG, trung bình trượt; QUALIFY để lọc.
  • An Phát 31/12/2025: phải thu 617,5 tỷ, quá hạn > 90 ngày 6,8%, dự phòng ≈ 31,1 tỷ.

🧩 Tự kiểm tra (bấm để xem đáp án)

1. Hoá đơn phát hành 10/11/2025, trả 05/01/2026. Tại ngày chốt 31/12/2025 hoá đơn này:
A. Đã trả, không tính
B. Còn nợ, phải tính vào aging
C. Bị loại vì paid_date không rỗng
Đáp án: B. Trả sau ngày chốt nên tại ngày chốt vẫn còn nợ.
2. Hoá đơn đến hạn 15/12/2025, ngày chốt 31/12/2025. dpd bằng:
A. 16
B. −16
C. 46
Đáp án: A. 31/12 − 15/12 = 16 ngày quá hạn.
3. Hàm nào lấy giá trị của dòng ngay trước trong cửa sổ?
A. LEAD()
B. LAG()
C. RANK()
Đáp án: B. LAG nhìn về trước, LEAD nhìn về sau.
4. QUALIFY dùng để:
A. Lọc theo kết quả hàm cửa sổ
B. Thay JOIN
C. Tạo bảng
Đáp án: A. Tương tự HAVING nhưng cho hàm cửa sổ.
5. Dư nợ >90 ngày là 42,1 tỷ, tỷ lệ tổn thất 60%. Dự phòng nhóm này:
A. 25,3 tỷ
B. 42,1 tỷ
C. 16,8 tỷ
Đáp án: A. Dự phòng = dư nợ × tỷ lệ tổn thất.

🏋️ Bài tập thực hành

Bảng tuổi nợ theo vùng và phân khúc, ở 3 ngày chốt

Viết một truy vấn tham số hoá ngày chốt, trả về bảng tuổi nợ theo vùng × phân khúc. Chạy cho 30/06/2025, 30/09/2025 và 31/12/2025. Vùng/phân khúc nào xấu đi nhanh nhất?

💡 Gợi ý lời giải
Thay ngày cố định bằng CTE WITH p AS (SELECT DATE '2025-09-30' AS d) và join chéo; hoặc dùng con.execute(sql, [ngay]) trong Python. Join dealers.csv để lấy region, segment; dùng SUM(...) FILTER (WHERE dpd > 90) để có cột tỷ trọng >90. Kiểm tra: tổng cả bảng ngày 31/12/2025 phải bằng 617,5 tỷ.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Cài DuckDB, pandas và chạy truy vấn đầu tiên
  • Viết lại truy vấn aging không nhìn bài
  • Dùng RANK, SUM OVER, LAG thành thạo
  • Tính ma trận dự phòng bằng pandas
  • Làm bài tập 3 ngày chốt
Lưu ý: đây là tài liệu giáo dục. Công ty An Phát Distribution, đại lý, nhà cung cấp, nhân viên và mọi số liệu đều là giả định do code sinh ra, không đại diện cho doanh nghiệp thật. Phương pháp (scorecard, ECL, VaR, Benford...) được đơn giản hoá cho mục đích học; khi áp dụng thật, hãy đối chiếu chuẩn mực kế toán (VAS/ IFRS 9), quy định pháp luật hiện hành (ví dụ về bảo vệ dữ liệu cá nhân) và chính sách nội bộ của tổ chức bạn.