SQL 13 — Mẫu phân tích dữ liệu thực tế

13 thg 7, 2026 3 lượt xem
#sql
#analytics
#window-function
#thuc-chien

SQL 13 — Mẫu phân tích dữ liệu thực tế

Đây là bài "kết tinh": những gì bạn học về GROUP BY, CTE và window function ở các bài trước giờ được ráp lại thành các mẫu phân tích mà bạn sẽ gặp đi gặp lại trong công việc thật — đặc biệt là trong ngân hàng, fintech và BI.

Mỗi mẫu dưới đây trả lời một câu hỏi nghiệp vụ cụ thể. Đừng học thuộc cú pháp; hãy học cách nhận ra "à, bài toán này là running total" rồi áp khuôn vào. Khi bạn nhìn ra được mẫu, 80% công việc viết query đã xong.

Tất cả ví dụ là khối SQL ▶ Chạy được trong SQL Builder, chỉ đọc, mỗi khối một câu lệnh. Schema sandbox dùng xuyên suốt:

  • customers(id, full_name, city, created_at)
  • accounts(id, customer_id, account_no, balance, currency)
  • transactions(id, account_id, amount, kind ['credit'|'debit'], created_at)

Lưu ý cột: accounts dùng khoá chính là id (KHÔNG phải account_id); transactions có cả id (của chính nó) và account_id (khoá ngoại trỏ về accounts.id).

1. Bức tranh chung: window function là "siêu năng lực" phân tích

Trước khi vào từng mẫu, hãy nhớ lại điểm khác biệt cốt lõi:

  • GROUP BY gộp nhiều dòng thành một dòng — bạn mất chi tiết từng giao dịch.
  • Window function (... OVER (...)) tính toán trên một "cửa sổ" các dòng nhưng giữ nguyên từng dòng — bạn vừa thấy chi tiết, vừa thấy con số tổng hợp bên cạnh.

Phần lớn "mẫu phân tích" thực chiến chỉ là chọn đúng nhánh trong sơ đồ này.

2. Running total — số dư luỹ kế theo thời gian

Nghiệp vụ: Với một tài khoản, kế toán muốn thấy tổng dòng tiền cộng dồn sau mỗi giao dịch — giống cột "số dư sau giao dịch" trên sao kê ngân hàng. Đây là câu hỏi "tính đến thời điểm này, tổng là bao nhiêu?".

Mấu chốt là SUM(...) OVER (ORDER BY ...): khi có ORDER BY bên trong OVER, SUM không gộp toàn bộ mà cộng dồn từ dòng đầu đến dòng hiện tại.

-- ▶ Chạy được trong SQL Builder
SELECT
  t.id,
  t.created_at,
  t.kind,
  t.amount,
  SUM(CASE WHEN t.kind = 'credit' THEN t.amount ELSE -t.amount END)
    OVER (ORDER BY t.created_at, t.id) AS running_balance
FROM transactions t
WHERE t.account_id = 1
ORDER BY t.created_at, t.id;

Hai điểm tinh tế đáng nhớ:

  • Ta quy ước credit cộng tiền, debit trừ tiền nên dùng CASE để đổi dấu trước khi cộng dồn — running total mới phản ánh đúng dòng tiền thực.
  • ORDER BY t.created_at, t.id thêm t.id để phá hoà (tie-break): nếu hai giao dịch trùng thời điểm, thứ tự cộng dồn vẫn xác định, kết quả không đổi giữa các lần chạy.

Nếu muốn running total theo từng tài khoản trên cùng một query (không lọc một account), thêm PARTITION BY t.account_id — mỗi tài khoản có chuỗi luỹ kế riêng:

-- ▶ Chạy được trong SQL Builder
SELECT
  t.account_id,
  t.created_at,
  t.amount,
  SUM(t.amount) OVER (
    PARTITION BY t.account_id
    ORDER BY t.created_at, t.id
  ) AS acct_running_total
FROM transactions t
ORDER BY t.account_id, t.created_at, t.id;

3. Moving average — trung bình trượt làm mượt nhiễu

Nghiệp vụ: Doanh số giao dịch hàng ngày dao động mạnh (cuối tuần thấp, ngày lương cao). Để thấy xu hướng thật, ta làm mượt bằng trung bình trượt 7 ngày — mỗi điểm là trung bình của chính nó và 6 ngày trước đó. Đây là kỹ thuật chuẩn trong dashboard giám sát.

Trước hết gộp giao dịch theo ngày, rồi tính AVG(...) OVER (ORDER BY ... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Dùng CTE để tách bước "gộp theo ngày" cho rõ ràng:

-- ▶ Chạy được trong SQL Builder
WITH daily AS (
  SELECT
    DATE_TRUNC('day', t.created_at) AS day,
    SUM(t.amount) AS total_amount
  FROM transactions t
  GROUP BY DATE_TRUNC('day', t.created_at)
)
SELECT
  day,
  total_amount,
  AVG(total_amount) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily
ORDER BY day;

Hiểu cho đúng mệnh đề khung (frame):

  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW nghĩa là cửa sổ gồm 7 dòng: dòng hiện tại và 6 dòng liền trước (theo thứ tự day).
  • Ở những ngày đầu chuỗi (chưa đủ 6 ngày trước), khung tự co lại — ví dụ ngày thứ 2 chỉ trung bình 2 dòng. Đó là hành vi đúng, không phải lỗi.

Phân biệt nhanh: ROWS đếm theo số dòng vật lý; RANGE đếm theo giá trị của cột ORDER BY. Với chuỗi ngày liên tục đã gộp, ROWS là lựa chọn trực giác và an toàn nhất.

4. Phần trăm thay đổi so với kỳ trước — LAG

Nghiệp vụ: Báo cáo tháng luôn có câu "tăng/giảm bao nhiêu % so với tháng trước". Để so dòng hiện tại với dòng liền trước, ta dùng LAG() — hàm "ngó lại" giá trị của một dòng phía trên.

-- ▶ Chạy được trong SQL Builder
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', t.created_at) AS month,
    SUM(t.amount) AS total_amount
  FROM transactions t
  GROUP BY DATE_TRUNC('month', t.created_at)
)
SELECT
  month,
  total_amount,
  LAG(total_amount) OVER (ORDER BY month) AS prev_month,
  ROUND(
    100.0 * (total_amount - LAG(total_amount) OVER (ORDER BY month))
      / NULLIF(LAG(total_amount) OVER (ORDER BY month), 0),
    2
  ) AS pct_change
FROM monthly
ORDER BY month;

Những điểm dễ vấp:

  • Tháng đầu tiên không có "tháng trước" nên LAG(...) trả NULL → pct_change cũng NULL. Đúng nghiệp vụ: không có gốc để so thì không có %.
  • NULLIF(prev, 0) chống lỗi chia cho 0 khi tháng trước bằng 0. Nếu mẫu số là 0, phép chia trả NULL thay vì văng lỗi.
  • 100.0 * ... (chứ không phải 100 *) ép kiểu sang số thực để không bị chia nguyên làm tròn về 0.

Họ hàng với LAG là LEAD() (ngó xuống dòng sau) — hữu ích khi tính "khoảng cách đến giao dịch kế tiếp".

5. Xếp hạng trong nhóm — RANK / ROW_NUMBER + PARTITION BY

Nghiệp vụ: "Trong mỗi thành phố, xếp hạng khách hàng theo tổng số dư." PARTITION BY chia dữ liệu thành các nhóm (mỗi thành phố một nhóm), rồi đánh số trong từng nhóm.

-- ▶ Chạy được trong SQL Builder
WITH cust_balance AS (
  SELECT
    c.id,
    c.full_name,
    c.city,
    COALESCE(SUM(a.balance), 0) AS total_balance
  FROM customers c
  LEFT JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, c.full_name, c.city
)
SELECT
  city,
  full_name,
  total_balance,
  RANK()       OVER (PARTITION BY city ORDER BY total_balance DESC) AS rnk,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_balance DESC) AS rn
FROM cust_balance
ORDER BY city, rnk;

Phân biệt ba hàm xếp hạng — chọn sai là sai nghiệp vụ:

  • ROW_NUMBER(): luôn đánh số duy nhất 1, 2, 3… kể cả khi có giá trị bằng nhau. Dùng khi cần "chỉ chọn đúng một dòng đại diện".
  • RANK(): giá trị bằng nhau thì cùng hạng, sau đó nhảy số (1, 1, 3…). Đúng kiểu xếp hạng thi đấu.
  • DENSE_RANK(): bằng nhau cùng hạng nhưng không nhảy (1, 1, 2…). Dùng khi muốn "có bao nhiêu mức giá trị khác nhau".

COALESCE(SUM(...), 0) đảm bảo khách chưa có tài khoản vẫn xuất hiện với số dư 0 (nhờ LEFT JOIN), thay vì biến mất.

6. Top-N mỗi nhóm — CTE + ROW_NUMBER rồi lọc

Nghiệp vụ: "Mỗi thành phố lấy 3 khách hàng có số dư cao nhất" — bài toán top-N per group kinh điển. Không thể giải bằng LIMIT đơn thuần (vì LIMIT cắt toàn bảng, không cắt theo nhóm). Mẫu chuẩn: đánh số bằng ROW_NUMBER() trong CTE, rồi lọc rn <= N ở câu ngoài.

-- ▶ Chạy được trong SQL Builder
WITH cust_balance AS (
  SELECT
    c.id,
    c.full_name,
    c.city,
    COALESCE(SUM(a.balance), 0) AS total_balance
  FROM customers c
  LEFT JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, c.full_name, c.city
),
ranked AS (
  SELECT
    city,
    full_name,
    total_balance,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_balance DESC, id) AS rn
  FROM cust_balance
)
SELECT city, full_name, total_balance, rn
FROM ranked
WHERE rn <= 3
ORDER BY city, rn;

Vì sao phải tách hai bước? Bạn không thể viết WHERE rn <= 3 cùng chỗ với ROW_NUMBER() — window function được tính sau WHERE, nên alias rn chưa tồn tại ở tầng WHERE của câu lệnh tạo ra nó. CTE ranked "vật chất hoá" cột rn, rồi câu ngoài mới lọc được.

Dùng ROW_NUMBER (không phải RANK) ở đây để đảm bảo mỗi nhóm lấy đúng 3 dòng; nếu dùng RANK mà có số dư bằng nhau, có thể ra nhiều hơn 3. Thêm , id vào ORDER BY để tie-break ổn định.

7. Phân nhóm khách theo giá trị — CTE + CASE

Nghiệp vụ: Marketing muốn chia khách thành các phân khúc VIP / Khá / Phổ thông theo tổng số dư, rồi đếm mỗi phân khúc có bao nhiêu khách. Đây là bài toán segmentation nền tảng của mọi chiến dịch.

Cái bẫy lớn: bạn không được phân nhóm trực tiếp bằng CASE đặt trên SUM(balance) trong cùng một GROUP BY đơn giản, vì như vậy phải vừa SUM vừa nhóm theo kết quả của chính SUM — gây lỗi aggregate trong GROUP BY. Cách sạch là tách hai tầng bằng CTE: tầng trong tính tổng số dư mỗi khách, tầng ngoài mới gán phân khúc rồi đếm.

-- ▶ Chạy được trong SQL Builder
WITH cb AS (
  SELECT
    customer_id,
    SUM(balance) AS total
  FROM accounts
  GROUP BY customer_id
)
SELECT
  CASE
    WHEN total >= 1000000000 THEN 'VIP'
    WHEN total >= 100000000  THEN 'Khá'
    ELSE 'Phổ thông'
  END AS segment,
  COUNT(*) AS num_customers,
  SUM(total) AS segment_balance
FROM cb
GROUP BY 1
ORDER BY MIN(total) DESC;

Vài lưu ý thực chiến:

  • GROUP BY 1 nhóm theo biểu thức cột thứ nhất trong SELECT (chính là cột segment). Tiện vì không phải lặp lại nguyên cụm CASE dài.
  • Tầng cb chạy SUM(balance) trước, cho ra một dòng/khách. Tầng ngoài coi total như dữ liệu thường nên CASE chạy mượt — đó là lý do CTE tránh được lỗi aggregate lồng nhau.
  • Ngưỡng (1 tỷ / 100 triệu) tuỳ chính sách; tách thành tham số trong code ứng dụng để dễ chỉnh.

8. Cohort — đếm theo tháng tạo (date_trunc)

Nghiệp vụ: "Mỗi tháng chúng ta thu hút được bao nhiêu khách mới?" — phân tích cohort theo tháng đăng ký. Đây là chỉ số tăng trưởng cốt lõi; ghép thêm hành vi sau này sẽ ra phân tích retention.

-- ▶ Chạy được trong SQL Builder
SELECT
  DATE_TRUNC('month', created_at) AS signup_month,
  COUNT(*) AS new_customers
FROM customers
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY signup_month;

DATE_TRUNC('month', created_at) cắt ngày về đầu tháng (ví dụ 2026-06-30 → 2026-06-01), nên mọi khách trong cùng tháng gom về một mốc — đó chính là "cohort tháng".

Nâng một bậc: ghép cohort khách với tổng số dư hiện tại của cohort đó, để biết tháng nào mang về khách giá trị cao. Ta nối customers với accounts rồi gộp theo tháng tạo khách:

-- ▶ Chạy được trong SQL Builder
WITH cohort AS (
  SELECT
    c.id,
    DATE_TRUNC('month', c.created_at) AS signup_month,
    COALESCE(SUM(a.balance), 0) AS customer_balance
  FROM customers c
  LEFT JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, DATE_TRUNC('month', c.created_at)
)
SELECT
  signup_month,
  COUNT(*) AS new_customers,
  SUM(customer_balance) AS cohort_balance,
  ROUND(AVG(customer_balance), 0) AS avg_balance_per_customer
FROM cohort
GROUP BY signup_month
ORDER BY signup_month;

Mẫu hai tầng lặp lại: tầng cohort quy mỗi khách về một dòng (tổng số dư của họ), tầng ngoài mới gộp theo tháng. Nếu gộp trực tiếp một tầng mà có nhiều tài khoản/khách, bạn dễ đếm trùng khách hoặc cộng nhầm — luôn nhớ "một dòng một thực thể" trước khi gộp lên cấp cao hơn.

Tóm tắt

  • Window function giữ từng dòng mà vẫn tính tổng hợp; chọn nó khi cần chi tiết bên cạnh con số gộp, còn GROUP BY khi chỉ cần con số gộp.
  • Running total = SUM(...) OVER (ORDER BY ...); thêm PARTITION BY để luỹ kế riêng từng nhóm. Luôn tie-break bằng cột id.
  • Moving average = AVG(...) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW) để làm mượt xu hướng.
  • % thay đổi kỳ trước dùng LAG(); bọc mẫu số bằng NULLIF(..., 0) và nhân 100.0 để tránh chia 0 và chia nguyên.
  • Xếp hạng trong nhóm dùng RANK / ROW_NUMBER / DENSE_RANK với PARTITION BY; phân biệt rõ ba hàm trước khi chọn.
  • Top-N mỗi nhóm = ROW_NUMBER() trong CTE rồi lọc rn <= N ở câu ngoài (vì không lọc được window function trong WHERE cùng tầng).
  • Phân nhóm theo giá trị và cohort đều dùng mẫu hai tầng CTE: tầng trong gộp về một dòng/thực thể, tầng ngoài mới phân loại và đếm — tránh lỗi aggregate lồng nhau và đếm trùng.

Tự kiểm tra

  1. Vì sao SUM(amount) OVER (ORDER BY created_at) cho ra running total, còn SUM(amount) đứng một mình lại gộp toàn bộ thành một số?
  2. Trong mẫu top-3 mỗi thành phố, vì sao phải dùng CTE rồi mới WHERE rn <= 3 thay vì lọc trực tiếp trong cùng câu lệnh có ROW_NUMBER()?
  3. Hai khách cùng số dư cao nhất một thành phố. Kết quả RANK(), DENSE_RANK() và ROW_NUMBER() của họ khác nhau thế nào?
  4. Tại sao trong mẫu % thay đổi ta dùng NULLIF(prev_month, 0) và viết 100.0 * thay vì 100 *?
  5. Trong mẫu phân nhóm khách, điều gì xảy ra nếu bỏ CTE cb và đặt CASE trực tiếp trên SUM(balance) trong một GROUP BY?
  6. DATE_TRUNC('month', '2026-06-30') trả về giá trị gì, và vì sao điều đó giúp gom khách thành cohort tháng?

Đọc tiếp

  • Quay lại nền tảng: SQL 1 — Giới thiệu RDBMS để ôn lại tư duy bảng, khoá và quan hệ làm chỗ dựa cho mọi mẫu phân tích ở trên.
  • Bước tiếp theo: tìm hiểu mục BI / Trực quan hoá — đưa các query cohort, segmentation, moving average này lên dashboard (Metabase, Superset, Power BI) để biến số liệu thành biểu đồ ra quyết định.
  • Đào sâu hơn: mục Data Engineering — cách materialize các bảng tổng hợp (daily/monthly rollup), lập lịch ETL và tối ưu hiệu năng khi window function chạy trên hàng triệu dòng giao dịch.

Bài viết liên quan

SQL
SQL 5 — Thiết kế CSDL & Chuẩn hoá

Khoá, ràng buộc, quan hệ và chuẩn hoá 1NF/2NF/3NF — thiết kế lược đồ đúng từ đầu.

13 thg 7, 2026 4
SQL
SQL 11 — Hàm ngày tháng, chuỗi & số

date_trunc/extract/age, cộng trừ khoảng thời gian, hàm chuỗi, ép kiểu CAST, COALESCE/NULLIF và làm tròn.

13 thg 7, 2026 3
SQL
Nổi bật
SQL 6 — Index & Tối ưu truy vấn

Cách index hoạt động (B-Tree), đọc EXPLAIN, seq scan vs index scan và mẫu tối ưu truy vấn.

13 thg 7, 2026 3
SQL
PostgreSQL 5 — PL/pgSQL: hàm, thủ tục & trigger

Đưa logic vào trong CSDL: viết function và procedure bằng PL/pgSQL (biến, điều khiển luồng, vòng lặp, xử lý ngoại lệ), sự khác nhau function vs procedure, trigger & trigger function, và khi nào NÊN/KHÔNG NÊN đặt logic ở tầng DB.

13 thg 7, 2026 3