SQL nâng cao 3 — Gaps & Islands, Sessionization

Có một lớp bài toán tưởng riêng lẻ nhưng thực ra cùng một khuôn: "những dòng này có liền mạch nhau không, và nếu đứt thì đứt ở đâu?". Khách hàng đăng nhập bao nhiêu ngày liên tiếp? Streak giao dịch dài nhất của một tài khoản là mấy ngày? Có khoảng nào tài khoản im ắng suốt vài tuần rồi bỗng hoạt động lại? Một chuỗi thao tác trên app có phải cùng một "phiên" hay là hai lần khác nhau?

Trong cộng đồng SQL, họ bài toán này có một cái tên rất hình tượng: gaps and islands — khoảng trống và hòn đảo. "Island" (hòn đảo) là một chuỗi các giá trị liên tục theo một thứ tự nào đó (ngày kề ngày, số kề số). "Gap" (khoảng trống) là chỗ đứt gãy giữa hai đảo. Điều đẹp đẽ là: một khi bạn nắm được kỹ thuật lõi để nhận diện đảo, thì streak, sessionization, phát hiện gián đoạn, gom cụm theo thời gian... đều chỉ là biến thể của cùng một ý tưởng.

Bài này giả định bạn đã quen OVER (PARTITION BY ... ORDER BY ...) từ SQL nâng cao 1 — Window functions. Ta sẽ đi từ trực giác của row_number trick, mở rộng sang phân nhóm và dùng LAG, rồi tới sessionization — một ứng dụng cực kỳ thực tế cho log giao dịch và hành vi người dùng.

1. Bài toán: đảo và khoảng trống

Hãy tưởng tượng một cột giá trị đã sắp xếp. Ví dụ các số nguyên một tài khoản có giao dịch trong tháng, tính theo ngày trong tháng:

Ngày có giao dịch:  2, 3, 4,   7,   9, 10, 11, 12,   20

Mắt người nhìn ra ngay ba "đảo": {2,3,4}, {7}, {9,10,11,12}, {20} — bốn đảo, ngăn cách bởi các khoảng trống (thiếu ngày 5-6, thiếu ngày 8, thiếu ngày 13-19). Câu hỏi: làm sao dạy cho SQL nhìn ra điều đó?

SQL không có khái niệm "liền kề" sẵn. Nó chỉ có các dòng và một thứ tự. Nên ta cần một mẹo biến "tính liên tục" thành một thứ có thể GROUP BY.

2. Row_number trick — trái tim của gaps & islands

Kỹ thuật lõi kinh điển gói gọn trong một dòng:

grp = value - ROW_NUMBER() OVER (ORDER BY value)

Với mỗi dòng, lấy giá trị trừ đi số thứ tự của nó. Điều thần kỳ: mọi dòng thuộc cùng một đảo sẽ có grp giống hệt nhau, còn khi sang đảo mới thì grp nhảy sang một hằng số khác. Sau đó chỉ việc GROUP BY grp là gom đúng từng đảo.

Vì sao? Trực giác như sau. Trong một đảo, giá trị tăng đều 1 đơn vị mỗi bước (2, 3, 4...). Số thứ tự ROW_NUMBER cũng tăng đều 1 mỗi bước (1, 2, 3...). Hai đại lượng cùng tăng 1 thì hiệu của chúng đứng yên — một hằng số. Khi gặp gap, giá trị nhảy vọt (từ 4 lên 7, tăng 3) nhưng số thứ tự chỉ tăng 1, nên hiệu tăng lên — đánh dấu đảo mới.

Xem bảng cho ví dụ trên:

Cột grp gom đúng bốn đảo: 1, 1, 1 → đảo A; 3 → đảo B; 4, 4, 4, 4 → đảo C; 11 → đảo D. Bản thân giá trị của grp (1, 3, 4, 11) vô nghĩa — ta chỉ quan tâm các dòng nào chung nhau một grp.

Ba điều kiện để mẹo này đúng:

1. Bước liên tục là một hằng số (thường là 1: ngày kề ngày, số nguyên kề nhau). Nếu "liên tục" của bạn không đều bước (ví dụ chỉ tính ngày làm việc, bỏ cuối tuần) thì phải chuẩn hoá trước.
2. ROW_NUMBER chạy cùng thứ tự với value (ORDER BY value). Dùng ROW_NUMBER chứ không phải RANK/DENSE_RANK, vì ta cần một dãy 1,2,3 không trùng, không nhảy.
3. Giá trị không trùng lặp trong phạm vi xét. Nếu có trùng (hai giao dịch cùng ngày), phải DISTINCT hoặc gom về mức ngày trước, nếu không ROW_NUMBER sẽ đếm dư và grp lệch.

3. Biến thể theo ngày và theo phân nhóm

Trong thực tế, dữ liệu hiếm khi là số nguyên đẹp. Thường ta làm việc với ngày và cần xử lý từng khách/từng tài khoản riêng.

Theo ngày. PostgreSQL cho phép trừ số nguyên khỏi một date và nhận lại date. Nên grp = d - ROW_NUMBER() với d kiểu date cho ra một date đóng vai trò khoá nhóm. Mỗi chuỗi ngày liên tiếp (mỗi ngày cách nhau đúng 1) sẽ cùng một grp. Lưu ý phải ép ROW_NUMBER() (kiểu bigint) về int vì date - bigint không có toán tử trực tiếp: viết d - (ROW_NUMBER() OVER (...))::int.

Theo phân nhóm. Chỉ cần thêm PARTITION BY account_id vào ROW_NUMBER. Khi đó số thứ tự đếm lại từ 1 cho mỗi tài khoản, và các đảo được nhận diện độc lập trong từng tài khoản. Khoá nhóm cuối cùng phải gồm cả account_id lẫn grp — vì hai tài khoản khác nhau hoàn toàn có thể tình cờ ra cùng một grp.

Đây là ví dụ chạy được: tìm chuỗi ngày liên tiếp có giao dịch (islands) của từng tài khoản, chỉ giữ những streak dài từ 2 ngày trở lên, xếp theo độ dài giảm dần.

-- ▶ Chạy được
WITH daily AS (
  SELECT DISTINCT account_id, created_at::date AS d
  FROM transactions
),
grp AS (
  SELECT account_id, d,
         d - (ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY d))::int AS island
  FROM daily
)
SELECT account_id,
       MIN(d)   AS streak_start,
       MAX(d)   AS streak_end,
       COUNT(*) AS streak_len
FROM grp
GROUP BY account_id, island
HAVING COUNT(*) >= 2
ORDER BY streak_len DESC, account_id
LIMIT 50;

Bước daily với DISTINCT là bắt buộc: một tài khoản có thể có nhiều giao dịch trong cùng ngày, ta phải rút về một dòng mỗi ngày trước khi đánh số, nếu không ROW_NUMBER sẽ đếm theo số giao dịch chứ không theo số ngày và mẹo sẽ sai hoàn toàn. MIN(d)/MAX(d) cho ngày đầu và cuối của mỗi đảo; COUNT(*) cho độ dài streak (số ngày).

4. Phát hiện gap bằng LAG

Mặt còn lại của đồng xu là khoảng trống. Row_number trick giỏi gom đảo, nhưng nếu câu hỏi chỉ là "chỗ nào bị đứt và đứt bao lâu?" thì LAG trực tiếp hơn nhiều.

LAG(x) OVER (PARTITION BY ... ORDER BY ...) trả về giá trị x của dòng liền trước trong cùng phân vùng. So sánh dòng hiện tại với dòng trước, nếu khoảng cách vượt ngưỡng thì đó là một gap. Với thời gian, phép trừ hai timestamp cho ra kiểu interval, so sánh trực tiếp với INTERVAL '7 days' được.

Ví dụ chạy được: tìm các khoảng im lặng dài hơn 7 ngày giữa hai giao dịch liên tiếp của cùng một tài khoản — dấu hiệu tài khoản "ngủ đông" rồi hoạt động trở lại.

-- ▶ Chạy được
WITH ordered AS (
  SELECT account_id, created_at,
         LAG(created_at) OVER (PARTITION BY account_id ORDER BY created_at) AS prev_at
  FROM transactions
)
SELECT account_id,
       prev_at            AS gap_start,
       created_at         AS gap_end,
       created_at - prev_at AS gap_len
FROM ordered
WHERE prev_at IS NOT NULL
  AND created_at - prev_at > INTERVAL '7 days'
ORDER BY gap_len DESC
LIMIT 50;

prev_at IS NULL xảy ra ở giao dịch đầu tiên của mỗi tài khoản (không có dòng trước) — ta loại bỏ vì nó không phải một gap thật. LAG và row_number trick bổ sung cho nhau: một cái đo hòn đảo, một cái đo cây cầu bị sập giữa hai đảo.

5. Sessionization — gom sự kiện thành phiên

Đây là ứng dụng "đắt giá" nhất của tư duy gaps & islands. Sessionization (phân phiên) là gom một dòng sự kiện thô — mỗi lần bấm, mỗi giao dịch, mỗi request — thành các phiên (session): những cụm hoạt động gần nhau về thời gian, xen kẽ bởi các quãng nghỉ. Quy ước phổ biến: nếu khoảng cách giữa hai sự kiện liên tiếp vượt một ngưỡng (ví dụ 30 phút) thì coi như người dùng đã rời đi và quay lại — mở một phiên mới.

Đây chính xác là gaps & islands trên trục thời gian: mỗi phiên là một "đảo" các sự kiện sát nhau, mỗi quãng nghỉ dài là một "gap". Thuật toán ba nhịp:

1. LAG timestamp — với mỗi sự kiện, lấy thời điểm sự kiện ngay trước đó của cùng người dùng.
2. Cờ new-session — đặt cờ 1 nếu đây là sự kiện đầu tiên (prev IS NULL) hoặc nếu khoảng cách tới sự kiện trước vượt ngưỡng; ngược lại cờ 0.
3. Cộng dồn cờ — SUM(cờ) OVER (PARTITION BY user ORDER BY time) là tổng luỹ kế của cờ. Mỗi lần cờ bật 1, tổng luỹ kế tăng thêm 1 → tạo ra một số phiên tăng dần: 1, 1, 1, 2, 2, 3, 3, 3...

Trong sơ đồ, ngưỡng 30 phút: khoảng nghỉ 148 phút giữa 09:12 và 11:40 vượt ngưỡng → bật cờ → SUM nhảy từ 1 lên 2 → tách phiên 2. Cả năm sự kiện gom thành hai phiên rõ ràng.

Ví dụ chạy được: đánh số phiên giao dịch cho mỗi tài khoản với ngưỡng nghỉ 30 phút, rồi tóm tắt mỗi phiên (số sự kiện, thời điểm bắt đầu/kết thúc).

-- ▶ Chạy được
WITH tx AS (
  SELECT account_id, created_at,
         LAG(created_at) OVER (PARTITION BY account_id ORDER BY created_at) AS prev_at
  FROM transactions
),
flagged AS (
  SELECT account_id, created_at,
         CASE WHEN prev_at IS NULL
                OR created_at - prev_at > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new
  FROM tx
),
sessioned AS (
  SELECT account_id, created_at,
         SUM(is_new) OVER (PARTITION BY account_id ORDER BY created_at) AS session_id
  FROM flagged
)
SELECT account_id, session_id,
       COUNT(*)       AS n_events,
       MIN(created_at) AS session_start,
       MAX(created_at) AS session_end
FROM sessioned
GROUP BY account_id, session_id
ORDER BY account_id, session_id
LIMIT 50;

Toàn bộ logic nằm gọn trong một câu WITH nhiều bước — hợp lệ với sandbox read-only vì đó vẫn là một câu SELECT duy nhất. Ba CTE tương ứng ba nhịp thuật toán ở trên: tx lấy LAG, flagged đặt cờ, sessioned cộng dồn cờ thành session_id. Câu cuối gom nhóm theo (account_id, session_id) để mô tả từng phiên.

Vì sao dùng SUM cộng dồn mà không dùng thẳng ROW_NUMBER? Vì ROW_NUMBER đánh số từng sự kiện, còn ta muốn đánh số từng cụm. Cộng dồn một cờ 0/1 chính là cách biến "ranh giới" (những chỗ cờ bật) thành "nhãn nhóm" ổn định cho mọi dòng trong cụm — cùng một tinh thần với row_number trick, chỉ khác là ở đây bước nhảy không đều nên ta tự dựng cờ thay vì trừ số thứ tự.

6. Chọn ngưỡng và những cái bẫy

Ngưỡng phiên là quyết định nghiệp vụ, không phải kỹ thuật. Web analytics kinh điển dùng 30 phút. Với log giao dịch ngân hàng, một phiên "thao tác" trên app có thể chỉ vài phút; còn nếu định nghĩa "đợt hoạt động" theo ngày thì ngưỡng có thể là 24 giờ. Nên tách ngưỡng thành tham số rõ ràng và ghi rõ giả định.

Vài bẫy hay gặp:

• Trùng timestamp. Hai giao dịch cùng mốc thời gian khiến created_at - prev_at = 0, không bật cờ — thường vô hại. Nhưng nếu ORDER BY không đủ để định thứ tự ổn định giữa các dòng trùng, kết quả có thể dao động giữa các lần chạy. Thêm khoá phụ (ví dụ ORDER BY created_at, id) để ổn định.
• RANK thay vì ROW_NUMBER. Trong row_number trick, dùng nhầm RANK/DENSE_RANK khi có giá trị trùng sẽ phá vỡ tính "tăng đều 1" và làm grp sai. Luôn dùng ROW_NUMBER (và khử trùng trước).
• Quên PARTITION BY. Không phân vùng theo tài khoản thì đảo/phiên của các tài khoản khác nhau bị trộn vào nhau — sai kín đáo mà query vẫn chạy.
• grp là date chứ không phải số. Với biến thể theo ngày, grp mang kiểu date — hoàn toàn dùng làm khoá GROUP BY được, chỉ đừng nhầm tưởng nó "có nghĩa".

Use case thực tế

Bối cảnh NCB. Đội phân tích rủi ro và tăng trưởng muốn hai thứ từ cùng một luồng dữ liệu giao dịch: (1) đo mức độ gắn kết — mỗi tài khoản thực chất có bao nhiêu "đợt" tương tác chứ không phải bao nhiêu giao dịch rời rạc; (2) phát hiện bất thường — những phiên "bùng nổ" bất thường (rất nhiều giao dịch dồn trong một khoảng ngắn) là ứng viên cho rà soát gian lận hoặc lỗi hệ thống (ví dụ retry lặp, bot rút tiền hàng loạt).

Cách làm. Ta phân phiên giao dịch mỗi tài khoản với ngưỡng nghỉ 30 phút (như mục 5), rồi tổng hợp lên mức tài khoản: số phiên (n_sessions — proxy cho tần suất quay lại), số sự kiện trung bình mỗi phiên (avg_events_per_session — độ sâu tương tác), và số sự kiện lớn nhất trong một phiên (max_events_in_a_session — cờ báo phiên bùng nổ).

-- ▶ Chạy được
WITH tx AS (
  SELECT account_id, created_at, amount,
         LAG(created_at) OVER (PARTITION BY account_id ORDER BY created_at) AS prev_at
  FROM transactions
),
flagged AS (
  SELECT account_id, created_at, amount,
         CASE WHEN prev_at IS NULL
                OR created_at - prev_at > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new
  FROM tx
),
sessioned AS (
  SELECT account_id, created_at, amount,
         SUM(is_new) OVER (PARTITION BY account_id ORDER BY created_at) AS session_id
  FROM flagged
),
per_session AS (
  SELECT account_id, session_id,
         COUNT(*)   AS n_events,
         SUM(amount) AS total_amount
  FROM sessioned
  GROUP BY account_id, session_id
)
SELECT account_id,
       COUNT(*)                       AS n_sessions,
       ROUND(AVG(n_events)::numeric, 2) AS avg_events_per_session,
       MAX(n_events)                  AS max_events_in_a_session
FROM per_session
GROUP BY account_id
ORDER BY max_events_in_a_session DESC
LIMIT 50;

Lưu ý AVG(n_events) trả về double precision, nên phải ép ::numeric trước khi ROUND(..., 2) — nếu không Postgres báo lỗi không tìm thấy hàm round(double precision, integer).

Đọc kết quả. Tài khoản đứng đầu bảng theo max_events_in_a_session là nơi soi trước: một phiên gói vài chục giao dịch trong nửa giờ, kèm total_amount bất thường, thường là retry lỗi hoặc hành vi tự động — chuyển sang phân tích nâng cao và đối chiếu ngưỡng cảnh báo trong chỉ số & KPI. Ngược lại, n_sessions cao đều đặn với avg_events_per_session vừa phải là chân dung khách hàng gắn kết lành mạnh — đầu vào tự nhiên cho phân tích cohort & retention, nơi ta theo dõi các nhóm khách theo thời gian tham gia.

Vì sao hiệu quả. Cùng một câu SQL, đổi ngưỡng 30 phút thành 24 giờ là chuyển từ "phiên thao tác" sang "đợt hoạt động theo ngày"; thêm total_amount vào bộ lọc là chuyển từ đo gắn kết sang sàng lọc gian lận. Toàn bộ đứng trên một ý tưởng duy nhất: đánh cờ ranh giới rồi cộng dồn.

Ghi nhớ

• Gaps & islands là một khuôn chung: "island" = chuỗi giá trị liên tục, "gap" = chỗ đứt. Streak, sessionization, phát hiện gián đoạn đều là biến thể của nó.
• Row_number trick: grp = value - ROW_NUMBER() OVER (ORDER BY value). Trong một đảo, giá trị và số thứ tự cùng tăng 1 nên hiệu đứng yên → cùng grp. Sau đó GROUP BY grp gom đảo.
• Điều kiện đúng: bước liên tục là hằng số, dùng ROW_NUMBER (không RANK), khử trùng giá trị trước (DISTINCT/gom về mức ngày).
• Theo ngày: d - (ROW_NUMBER() OVER (...))::int (ép bigint về int). Theo nhóm: thêm PARTITION BY và gom theo cả khoá nhóm lẫn grp.
• LAG đo trực tiếp khoảng cách giữa hai dòng liền kề — tiện để phát hiện gap vượt ngưỡng; loại prev IS NULL (dòng đầu phân vùng).
• Sessionization ba nhịp: LAG timestamp → cờ new-session khi prev IS NULL hoặc khoảng cách > ngưỡng → SUM(cờ) OVER (...) cộng dồn thành session_id.
• Ngưỡng phiên là quyết định nghiệp vụ (30 phút cho thao tác, 24 giờ cho đợt hoạt động) — tách thành tham số, ghi rõ giả định.
• Nhớ ép ::numeric khi ROUND(AVG(...), 2) trên Postgres, và luôn ORDER BY đủ khoá để thứ tự ổn định giữa các lần chạy.

Nguồn tham khảo

• PostgreSQL Documentation — "Window Functions" (Tutorial, mục 3.5): https://www.postgresql.org/docs/current/tutorial-window.html
• PostgreSQL Documentation — "Window Function Calls" (mục 4.2.8) và "Window Function Processing": https://www.postgresql.org/docs/current/functions-window.html
• Itzik Ben-Gan — T-SQL Window Functions: For Data Analysis and Beyond, 2nd Edition (Microsoft Press) — chương về gaps and islands.
• Itzik Ben-Gan et al. — SQL Server MVP Deep Dives (Manning) — chương "Gaps and islands" của Itzik Ben-Gan.
• Itzik Ben-Gan — Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Microsoft Press).