SQL nâng cao 5 — Time-series & Funnel bằng SQL

Hai câu hỏi xuất hiện trong gần như mọi cuộc họp kinh doanh ngân hàng. Thứ nhất: "Giao dịch tháng này so với tháng trước tăng hay giảm bao nhiêu?" — đó là bài toán chuỗi thời gian (time-series). Thứ hai: "Trong 1.000 khách mở tài khoản, bao nhiêu người thực sự giao dịch, rồi bao nhiêu người thành khách thường xuyên?" — đó là bài toán phễu (funnel). Cả hai đều dựng được gọn gàng bằng SQL thuần, không cần công cụ BI, và bài này chỉ cho bạn từng mảnh ghép.

Time-series nối tiếp mảng khung cửa sổ và trung bình trượt ở SQL nâng cao 2 — Frames & running totals; funnel là họ hàng gần của phân tích giữ chân ở SQL nâng cao 4 — Cohort & Retention. Điểm chung: cả hai đều dựa trên việc gom sự kiện về mốc thời gian và đếm đối tượng phân biệt qua các trạng thái.

1. Gom kỳ với date_trunc

Nền của mọi báo cáo chuỗi thời gian là cắt cụt (truncate) một timestamp về đầu kỳ. date_trunc('month', ts) biến mọi thời điểm trong tháng 3/2025 thành cùng một giá trị 2025-03-01 00:00:00. Đổi tham số đầu là đổi độ mịn của trục thời gian:

Bắt đầu bằng câu hỏi cơ bản nhất — số giao dịch và tổng tiền theo từng tháng:

-- ▶ Chạy được
SELECT
  date_trunc('month', created_at)::date AS month,
  COUNT(*)                                AS txn_count,
  SUM(amount)                             AS total_amount
FROM transactions
GROUP BY 1
ORDER BY 1;

date_trunc trả về timestamp; ép ::date cho gọn khi hiển thị và khi so khớp với trục ngày dựng ở phần sau. Mỗi dòng kết quả là một kỳ; đây là "vật liệu thô" để tính tăng trưởng và trung bình trượt.

2. Tăng trưởng kỳ-trên-kỳ bằng LAG

Có bảng theo tháng rồi, muốn biết tăng/giảm so với kỳ liền trước (Month-over-Month, MoM) ta cần nhìn được giá trị của dòng ngay trên. Đó chính là việc của hàm cửa sổ LAG: LAG(x) OVER (ORDER BY month) lấy giá trị x của dòng đứng trước theo thứ tự thời gian.

-- ▶ Chạy được
WITH monthly AS (
  SELECT
    date_trunc('month', created_at)::date AS month,
    COUNT(*)    AS txn_count,
    SUM(amount) AS total_amount
  FROM transactions
  GROUP BY 1
)
SELECT
  month,
  txn_count,
  total_amount,
  LAG(total_amount) OVER (ORDER BY month) AS prev_amount,
  ROUND(
    (total_amount - LAG(total_amount) OVER (ORDER BY month))::numeric
    / NULLIF(LAG(total_amount) OVER (ORDER BY month), 0) * 100,
    2
  ) AS mom_growth_pct
FROM monthly
ORDER BY month;

Ba điểm phải khắc cốt:

• Ép ::numeric trước khi chia. Nếu total_amount là kiểu nguyên, phép (a-b)/b cho ra số nguyên (làm tròn xuống 0). Ép một vế sang numeric để có phần thập phân, rồi ROUND(...::numeric, 2) mới dùng được dạng hai tham số.
• NULLIF(prev, 0) chặn lỗi chia cho 0 ở kỳ đầu tiên hoặc kỳ mà giá trị trước bằng 0 — nó biến mẫu số 0 thành NULL, khiến kết quả ra NULL thay vì lỗi.
• Dòng đầu tiên mom_growth_pct là NULL vì không có kỳ trước để so — đúng về mặt logic, không phải bug.

LAG(x, 12) (bước nhảy 12) cho ta YoY (Year-over-Year, so cùng kỳ năm trước) nếu dữ liệu là chuỗi tháng liên tục — nhưng chỉ đúng khi không thiếu kỳ nào. Đó chính là lý do phần tiếp theo cực kỳ quan trọng.

3. Vấn đề "thiếu kỳ" và cách điền bằng generate_series

Câu GROUP BY month chỉ sinh ra dòng cho những tháng có ít nhất một giao dịch. Tháng nào không có giao dịch nào sẽ biến mất khỏi kết quả — không phải ra 0, mà là không có dòng. Hậu quả rất tệ:

• Biểu đồ đường bị "nhảy cóc", nối thẳng qua tháng trống làm sai xu hướng.
• LAG hiểu sai "kỳ trước": nếu tháng 4 trống, LAG ở tháng 5 sẽ lấy tháng 3, khiến MoM và YoY lệch pha.
• Báo cáo quản trị thiếu hàng, người đọc tưởng hệ thống lỗi.

Giải pháp chuẩn là gap filling (điền khoảng trống): dựng trước một trục thời gian đầy đủ rồi LEFT JOIN dữ liệu vào, kỳ nào không khớp thì điền 0. Công cụ tạo trục là generate_series, sinh ra một chuỗi mốc thời gian đều nhau:

Ý tưởng: generate_series là "khung xương" đủ mọi kỳ, dữ liệu thật là "thịt" gắn vào bằng LEFT JOIN, và COALESCE lấp chỗ trống thành 0. Sau bước này, LAG(x, 1) và LAG(x, 12) mới đáng tin vì mọi kỳ đều hiện diện đúng vị trí.

4. Time-series đủ kỳ — dựng trục rồi LEFT JOIN

Ghép trọn ý trên vào một câu. Ta lấy min/max tháng có giao dịch làm biên, generate_series sinh mọi tháng ở giữa, rồi LEFT JOIN bảng tổng hợp theo tháng:

-- ▶ Chạy được
WITH bounds AS (
  SELECT
    date_trunc('month', MIN(created_at)) AS lo,
    date_trunc('month', MAX(created_at)) AS hi
  FROM transactions
),
calendar AS (
  SELECT generate_series(lo, hi, interval '1 month')::date AS month
  FROM bounds
),
monthly AS (
  SELECT
    date_trunc('month', created_at)::date AS month,
    COUNT(*)    AS txn_count,
    SUM(amount) AS total_amount
  FROM transactions
  GROUP BY 1
)
SELECT
  c.month,
  COALESCE(m.txn_count, 0)          AS txn_count,
  COALESCE(m.total_amount, 0)       AS total_amount
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

Bây giờ mọi tháng giữa mốc đầu và mốc cuối đều có mặt; tháng không giao dịch ra txn_count = 0 thay vì biến mất. Đây là dạng chuẩn để vẽ đường liên tục và để LAG chạy đúng.

Gộp cả gap filling và MoM vào một truy vấn — đây là "báo cáo tăng trưởng theo tháng" hoàn chỉnh:

-- ▶ Chạy được
WITH bounds AS (
  SELECT date_trunc('month', MIN(created_at)) AS lo,
         date_trunc('month', MAX(created_at)) AS hi
  FROM transactions
),
calendar AS (
  SELECT generate_series(lo, hi, interval '1 month')::date AS month
  FROM bounds
),
monthly AS (
  SELECT date_trunc('month', created_at)::date AS month,
         SUM(amount) AS total_amount
  FROM transactions
  GROUP BY 1
),
filled AS (
  SELECT c.month,
         COALESCE(m.total_amount, 0) AS total_amount
  FROM calendar c
  LEFT JOIN monthly m ON m.month = c.month
)
SELECT
  month,
  total_amount,
  LAG(total_amount) OVER (ORDER BY month) AS prev_amount,
  ROUND(
    (total_amount - LAG(total_amount) OVER (ORDER BY month))::numeric
    / NULLIF(LAG(total_amount) OVER (ORDER BY month), 0) * 100, 2
  ) AS mom_growth_pct
FROM filled
ORDER BY month;

Vì filled đã đủ kỳ, cột prev_amount luôn là đúng tháng liền kề. Không có bước gap filling, một tháng trống sẽ làm mọi con số MoM phía sau lệch một nhịp.

5. Trung bình trượt trên trục đủ kỳ

Khi chuỗi có nhiễu theo mùa (cuối tuần, lễ tết), trung bình trượt (moving average) làm mượt để lộ xu hướng. Kỹ thuật khung cửa sổ ROWS BETWEEN đã bàn kỹ ở SQL nâng cao 2 — Frames & running totals; ở đây điểm mới là nó phải chạy trên trục đã điền đủ kỳ, nếu không "3 kỳ gần nhất" sẽ nhảy qua các tháng trống và sai.

-- ▶ Chạy được
WITH bounds AS (
  SELECT date_trunc('month', MIN(created_at)) AS lo,
         date_trunc('month', MAX(created_at)) AS hi
  FROM transactions
),
calendar AS (
  SELECT generate_series(lo, hi, interval '1 month')::date AS month
  FROM bounds
),
monthly AS (
  SELECT date_trunc('month', created_at)::date AS month,
         COUNT(*) AS txn_count
  FROM transactions
  GROUP BY 1
),
filled AS (
  SELECT c.month, COALESCE(m.txn_count, 0) AS txn_count
  FROM calendar c
  LEFT JOIN monthly m ON m.month = c.month
)
SELECT
  month,
  txn_count,
  ROUND(AVG(txn_count) OVER (
    ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  )::numeric, 1) AS ma_3m
FROM filled
ORDER BY month;

Khung ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gom kỳ hiện tại cùng 2 kỳ trước thành trung bình trượt 3 tháng. AVG trả về double precision nên phải ép ::numeric trước khi ROUND hai tham số — quy tắc bắt buộc trên sandbox này.

6. Phễu (funnel) — đếm đối tượng qua các bước tuần tự

Chuyển sang bài toán thứ hai. Phễu đo số đối tượng sống sót qua một chuỗi bước tuần tự, mỗi bước là một điều kiện khắt khe hơn. Ví dụ kinh điển của ngân hàng bán lẻ:

Đặc trưng của phễu: các bước lồng nhau — ai qua được bước 3 thì tất yếu đã qua bước 1 và 2. Vì thế số ở mỗi bước chỉ có thể giảm dần hoặc bằng, và tỉ lệ chuyển đổi (conversion rate) của một bước = số đối tượng bước đó chia cho số ở bước liền trước.

Nguyên tắc SQL cốt lõi:

• COUNT(DISTINCT customer_id) ở mỗi bước — ta đếm số khách phân biệt, không đếm giao dịch. Một khách giao dịch 20 lần vẫn là 1 người ở bước "có ≥1 giao dịch".
• FILTER (WHERE điều kiện) cho phép đếm nhiều bước trong một lần quét bảng, thay vì viết nhiều truy vấn con.
• Ép ::numeric khi tính tỉ lệ chuyển đổi, vì chia hai số nguyên ra số nguyên.

7. Dựng phễu kích hoạt khách bằng SQL

Trước hết, với mỗi khách ta cần biết họ có bao nhiêu giao dịch. Đó là một bảng trung gian per_customer, sau đó dùng COUNT ... FILTER để đếm khách thoả từng ngưỡng:

-- ▶ Chạy được
WITH per_customer AS (
  SELECT
    c.id AS customer_id,
    COUNT(t.id) AS txn_count
  FROM customers c
  LEFT JOIN accounts     a ON a.customer_id = c.id
  LEFT JOIN transactions t ON t.account_id  = a.id
  GROUP BY c.id
)
SELECT
  COUNT(*)                                        AS step1_opened,
  COUNT(*) FILTER (WHERE txn_count >= 1)          AS step2_first_txn,
  COUNT(*) FILTER (WHERE txn_count >= 5)          AS step3_repeat
FROM per_customer;

LEFT JOIN từ customers giữ lại cả khách chưa giao dịch lần nào (họ có txn_count = 0), nếu không phễu sẽ mất mẫu số ở bước 1. Ba con số ra theo dạng hàng ngang, giảm dần qua các bước.

Thêm tỉ lệ chuyển đổi — mỗi bước chia cho bước trước, và cả tỉ lệ tổng so với bước 1:

-- ▶ Chạy được
WITH per_customer AS (
  SELECT
    c.id AS customer_id,
    COUNT(t.id) AS txn_count
  FROM customers c
  LEFT JOIN accounts     a ON a.customer_id = c.id
  LEFT JOIN transactions t ON t.account_id  = a.id
  GROUP BY c.id
),
funnel AS (
  SELECT
    COUNT(*)                               AS step1_opened,
    COUNT(*) FILTER (WHERE txn_count >= 1) AS step2_first_txn,
    COUNT(*) FILTER (WHERE txn_count >= 5) AS step3_repeat
  FROM per_customer
)
SELECT
  step1_opened,
  step2_first_txn,
  step3_repeat,
  ROUND(step2_first_txn::numeric / NULLIF(step1_opened, 0)   * 100, 1) AS conv_1_to_2_pct,
  ROUND(step3_repeat::numeric    / NULLIF(step2_first_txn, 0) * 100, 1) AS conv_2_to_3_pct,
  ROUND(step3_repeat::numeric    / NULLIF(step1_opened, 0)   * 100, 1) AS overall_pct
FROM funnel;

conv_1_to_2_pct là tỉ lệ hoạt hoá (khách mở tài khoản rồi có dùng), conv_2_to_3_pct là tỉ lệ giữ đà (đã dùng rồi có dùng lặp), overall_pct là tỉ lệ khách mở tài khoản trở thành khách thường xuyên. NULLIF(..., 0) bảo vệ khỏi chia 0. Bước rơi rụng mạnh nhất chỉ ra chỗ cần can thiệp trải nghiệm.

Muốn phễu ở dạng dọc (mỗi bước một dòng, dễ vẽ biểu đồ phễu), dùng UNION ALL gộp ba bước lại:

-- ▶ Chạy được
WITH per_customer AS (
  SELECT c.id AS customer_id, COUNT(t.id) AS txn_count
  FROM customers c
  LEFT JOIN accounts     a ON a.customer_id = c.id
  LEFT JOIN transactions t ON t.account_id  = a.id
  GROUP BY c.id
)
SELECT 1 AS step_no, 'Mở tài khoản'   AS step_name,
       COUNT(*) AS customers FROM per_customer
UNION ALL
SELECT 2, 'Có >= 1 giao dịch',
       COUNT(*) FILTER (WHERE txn_count >= 1) FROM per_customer
UNION ALL
SELECT 3, 'Có >= 5 giao dịch',
       COUNT(*) FILTER (WHERE txn_count >= 5) FROM per_customer
ORDER BY step_no;

Dạng dọc này dán thẳng vào công cụ vẽ biểu đồ phễu, mỗi thanh một bước.

Use case thực tế

Bối cảnh NCB. Khối Ngân hàng số cần hai báo cáo định kỳ cho ban điều hành. (1) Tăng trưởng giao dịch theo tháng — không được thiếu kỳ, vì tháng Tết thường ít giao dịch và nếu tháng đó "rơi" khỏi bảng thì đường xu hướng trên slide sẽ sai và câu chuyện MoM lệch nhịp. (2) Phễu kích hoạt khách mới — đo xem chiến dịch mở tài khoản online có biến khách thành người dùng thực sự không.

Báo cáo 1 — tăng trưởng đủ kỳ. Đội dữ liệu dùng đúng mẫu ở mục 4: generate_series dựng trục tháng liên tục từ giao dịch đầu tiên tới hiện tại, LEFT JOIN dữ liệu, COALESCE(..., 0) cho tháng trống, rồi LAG tính MoM. Nhờ trục đủ kỳ, tháng Tết hiện rõ là "0 hoặc rất thấp" thay vì biến mất, và MoM tháng sau Tết phản ánh đúng cú bật lại. Trước đây khi dùng thẳng GROUP BY month, tháng thiếu dữ liệu bị nuốt, khiến báo cáo tăng trưởng "đẹp giả tạo" — sếp hỏi lại mới lộ.

Báo cáo 2 — phễu kích hoạt. Dùng mẫu ở mục 7. Giả sử kết quả (minh hoạ trên dữ liệu thật) như sau:

Diễn giải. Tỉ lệ hoạt hoá 62% nghĩa là gần 4/10 khách mở tài khoản rồi không giao dịch lần nào — điểm rơi rụng lớn nhất nằm ngay bước đầu. Đây là tín hiệu để đội sản phẩm rà lại luồng onboarding: có phải khách mở xong không biết làm gì tiếp, hay chưa nạp được tiền? Bước 2→3 giữ được 38,7% cho thấy ai đã giao dịch một lần thì tiềm năng thành khách thường xuyên khá cao — nên ưu tiên "đẩy" khách qua giao dịch đầu tiên (tặng phí chuyển tiền lần đầu, gợi ý nạp tiền ngay sau khi mở). Kết hợp hai báo cáo: đường MoM cho biết khi nào tăng trưởng, phễu cho biết tại sao — khách vào nhiều nhưng kẹt ở bước hoạt hoá thì tăng trưởng tài khoản không chuyển thành tăng trưởng giao dịch. Cách chuẩn hoá các con số này thành chỉ số theo dõi định kỳ được bàn ở BI — Metrics & KPI.

Ghi nhớ

• date_trunc('month'|'week'|'day', ts) là công cụ gom kỳ nền tảng của mọi báo cáo chuỗi thời gian; đổi đơn vị là đổi độ mịn trục thời gian.
• LAG(x) OVER (ORDER BY month) cho giá trị kỳ trước → tính MoM; LAG(x, 12) cho YoY, nhưng chỉ đúng khi chuỗi đủ kỳ. Dòng đầu ra NULL là đúng logic.
• Thiếu kỳ là cạm bẫy lớn nhất: GROUP BY month bỏ hẳn tháng không có dữ liệu, làm LAG lệch nhịp và biểu đồ nhảy cóc.
• Gap filling: generate_series(lo, hi, interval '1 month') dựng trục đủ kỳ → LEFT JOIN dữ liệu → COALESCE(value, 0) lấp chỗ trống. Sau đó LAG và trung bình trượt mới đáng tin.
• Trung bình trượt (AVG ... OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) phải chạy trên trục đã điền đủ kỳ; nhớ ép ::numeric trước ROUND.
• Phễu: đếm COUNT(DISTINCT khách) (không đếm giao dịch) qua các bước lồng nhau; bước sau luôn ≤ bước trước.
• Dùng COUNT(*) FILTER (WHERE điều kiện) để đếm nhiều bước trong một lần quét; LEFT JOIN từ customers để giữ khách chưa giao dịch (mẫu số bước 1 không bị mất).
• Tỉ lệ chuyển đổi = bước sau / bước trước, luôn ép ::numeric và bọc mẫu số bằng NULLIF(..., 0) chống chia 0.

Xem thêm: SQL nâng cao 2 — Frames & running totals, SQL nâng cao 4 — Cohort & Retention, BI — Metrics & KPI.

Nguồn tham khảo

• PostgreSQL Documentation — Date/Time Functions and Operators (date_trunc)
• PostgreSQL Documentation — Set Returning Functions (generate_series)
• PostgreSQL Documentation — Window Functions (LAG, OVER, frame clause)
• PostgreSQL Documentation — Window Function Calls (cú pháp OVER, ROWS BETWEEN)
• PostgreSQL Documentation — Aggregate Expressions (FILTER, COUNT(DISTINCT ...))
• PostgreSQL Documentation — Mathematical Functions (round, ép kiểu numeric)