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.
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ộtVì 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.
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ảng | Khoá | Cột chính | Ý nghĩa nghiệp vụ |
|---|---|---|---|
| dealers.csv | dealer_id | region, segment, credit_limit_mvnd, term_days, onboard_date | Một dòng = một đại lý. Hạn mức tính bằng triệu đồng. |
| invoices.csv | invoice_id | dealer_id, invoice_date, due_date, amount_vnd, paid_date | Mộ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.csv | date | usd_vnd | Tỷ giá ngày làm việc (không có thứ Bảy, Chủ nhật). |
| vendor_payments.csv | payment_id | vendor_id, payment_date, hour, amount_vnd, invoice_no, created_by | Thanh toán cho nhà cung cấp năm 2025. |
| vendors.csv / employees.csv | vendor_id / employee_id | bank_account | Danh 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)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ừ 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.
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 khi | Dùng Python/ pandas khi |
|---|---|
| Lọc, nối, gộp, cửa sổ trên dữ liệu lớn | Thống kê, mô hình, mô phỏng (module 11–14) |
| Logic cần chạy trong kho dữ liệu, dbt, dashboard | Gọi API, đọc file định dạng lạ, tự động hoá |
| Người kiểm toán cần đọc được | Trực quan hoá nhanh, notebook khám phá |
⚠️ Sai lầm thường mắc
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).
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.
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.
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.
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.
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).
- 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
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
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()
4. QUALIFY dùng để:
A. Lọc theo kết quả hàm cửa sổ
B. Thay JOIN
C. Tạo bảng
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ỷ
🏋️ Bài tập thực hành
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
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
- DuckDB DocumentationSQL, hàm cửa sổ, đọc CSV/ParquetTài liệu
- pandas User Guidegroupby, cut, merge, thời gianTài liệu
- PostgreSQL: Window FunctionsHướng dẫn hàm cửa sổ chuẩnTài liệu
✅ 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