SQL cho Data Analyst: Window Functions, CTEs và Truy vấn Nâng cao

Trong lĩnh vực phân tích dữ liệu, SQL window functions, CTEs (Common Table Expressions) và các mẫu truy vấn nâng cao đóng vai trò nền tảng không thể thiếu. Dù đang chuẩn bị cho buổi phỏng vấn vị trí data analyst hay xử lý các báo cáo phức tạp hàng ngày, việc thành thạo các kỹ thuật này sẽ giúp chuyển đổi những subquery dài dòng, khó đọc thành những câu truy vấn SQL gọn gàng và hiệu suất cao.

Tìm hiểu SQL Window Functions và mệnh đề OVER

Window function áp dụng một phép tính trên một "cửa sổ" (window) các hàng được xác định trước. Mệnh đề OVER kiểm soát những hàng nào nằm trong cửa sổ đó và thứ tự sắp xếp của chúng. Khác với các hàm tổng hợp kết hợp GROUP BY, window functions giữ nguyên mọi hàng riêng lẻ trong tập kết quả.

Cú pháp chung tuân theo một khuôn mẫu nhất quán trên tất cả các hệ quản trị cơ sở dữ liệu phổ biến — PostgreSQL, MySQL 8+, BigQuery, SQL Server và Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY chia dữ liệu thành các nhóm (tương tự GROUP BY nhưng không gộp hàng). ORDER BY xác định thứ tự trong từng partition. Mệnh đề frame tùy chọn (ROWS BETWEEN ...) thu hẹp cửa sổ xuống một phạm vi hàng cụ thể.

So sánh ROW_NUMBER, RANK và DENSE_RANK

Ba hàm xếp hạng này trông khá giống nhau nhưng hoạt động khác biệt khi có giá trị trùng (ties). Việc chọn sai hàm là nguyên nhân thường gặp gây ra lỗi trong các truy vấn phân tích.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER luôn gán một số nguyên tuần tự duy nhất — ngay cả khi có giá trị trùng, một hàng sẽ được gán số thấp hơn một cách tùy ý. RANK gán cùng một số cho các giá trị trùng nhưng bỏ qua các số tiếp theo (1, 1, 3). DENSE_RANK cũng xử lý giá trị trùng nhưng không bao giờ bỏ qua số nào (1, 1, 2). Đối với các truy vấn top-N mà các giá trị trùng cần chia sẻ cùng vị trí, DENSE_RANK thường là lựa chọn chính xác nhất.

LAG và LEAD cho phân tích so sánh theo kỳ

LAG và LEAD truy cập dữ liệu từ các hàng trước hoặc sau mà không cần self-join. Hai hàm này không thể thiếu khi tính tăng trưởng theo kỳ, phát hiện xu hướng và nhận diện các thay đổi bất thường.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Tham số thứ hai của LAG/LEAD xác định độ lệch (offset), mặc định là 1. Tham số thứ ba (tùy chọn) cung cấp giá trị mặc định khi không có hàng nào tại vị trí offset đó — rất hữu ích để tránh NULL ở các hàng đầu tiên hoặc cuối cùng. Hàm NULLIF trong phép tính tăng trưởng giúp ngăn chặn lỗi chia cho số 0.

NTILE và phân nhóm theo phần trăm

NTILE phân phối các hàng vào một số lượng nhóm gần bằng nhau được chỉ định. Các data analyst sử dụng hàm này cho phân tích tứ phân vị, chấm điểm thập phân vị và phân khúc khách hàng.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Tứ phân vị 1 chứa những khách hàng chi tiêu cao nhất, tứ phân vị 4 chứa những khách hàng chi tiêu thấp nhất. Mẫu truy vấn này ánh xạ trực tiếp sang mô hình phân khúc RFM (Recency, Frequency, Monetary) được sử dụng phổ biến trong phân tích marketing.

Common Table Expressions: Thay thế các subquery lồng nhau

CTEs (mệnh đề WITH) chia các truy vấn phức tạp thành các bước được đặt tên, dễ đọc. Mỗi CTE hoạt động như một tập kết quả tạm thời có tên, chỉ tồn tại trong suốt thời gian thực thi truy vấn. Ngoài việc cải thiện tính dễ đọc, CTEs còn giúp việc debug trở nên dễ dàng hơn — mỗi bước có thể được kiểm tra độc lập.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Cách tiếp cận ba bước này thay thế cho một subquery lồng sâu nhiều tầng. Mỗi CTE có một trách nhiệm rõ ràng: tổng hợp dữ liệu, xếp hạng và lọc kết quả.

Recursive CTEs cho dữ liệu phân cấp

Recursive CTEs giải quyết các bài toán liên quan đến dữ liệu phân cấp hoặc dạng đồ thị — sơ đồ tổ chức, cây danh mục, danh sách vật liệu (bill-of-materials) và các truy vấn tìm đường.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Base case chọn các node gốc (nhân viên không có quản lý). Recursive case join ngược lại với chính CTE, xây dựng cây phân cấp từng cấp một. Cột management_chain nối các tên lại để hiển thị toàn bộ đường dẫn báo cáo. Hầu hết các hệ quản trị cơ sở dữ liệu giới hạn độ sâu đệ quy để ngăn vòng lặp vô hạn — PostgreSQL mặc định giới hạn ở 100 lần lặp.

Các mẫu phân tích nâng cao: Gaps, Islands và Running Totals

Mẫu gaps-and-islands nhận diện các chuỗi liên tiếp trong dữ liệu — các giai đoạn đăng ký hoạt động, số ngày đăng nhập liên tiếp, hoặc các đợt sản xuất không gián đoạn. Kỹ thuật này kết hợp ROW_NUMBER với phép tính ngày tháng.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Ý tưởng cốt lõi nằm ở chỗ: đối với các ngày liên tiếp, khi lấy ngày trừ đi một số hàng tăng dần (row number), kết quả luôn cho ra cùng một giá trị. Khi xuất hiện khoảng trống (gap), giá trị kết quả thay đổi, tạo ra một nhóm mới. Truy vấn này tìm tất cả các chuỗi đăng nhập liên tiếp từ 3 ngày trở lên cho mỗi người dùng.

Kết hợp Window Functions với CASE cho phân tích có điều kiện

Các truy vấn phân tích trong thực tế thường xuyên kết hợp window functions với biểu thức CASE để tính toán các chỉ số có điều kiện trong cùng một câu truy vấn.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Chỉ với một câu truy vấn duy nhất, có thể tính được trung bình trượt 7 ngày, tổng lũy kế theo quý (tự động reset mỗi quý mới), và gắn cờ outlier — tất cả mà không cần subquery hay self-join nào.

Tối ưu hiệu suất cho truy vấn phân tích

Window functions và CTEs rất mạnh mẽ nhưng có thể trở thành nút thắt cổ chai trên các bảng dữ liệu lớn. Một số kỹ thuật sau đây giúp duy trì hiệu suất ở quy mô lớn.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Mệnh đề WINDOW (được hỗ trợ trong PostgreSQL, MySQL 8+ và BigQuery) cho phép định nghĩa window một lần và tái sử dụng trên nhiều hàm. Điều này cải thiện cả tính dễ đọc lẫn khả năng tối ưu hóa query plan.

Ngoài named window, một số kỹ thuật tối ưu quan trọng khác bao gồm: lọc dữ liệu trong mệnh đề WHERE trước khi window function được đánh giá, đánh index cho các cột trong PARTITION BY và ORDER BY, cũng như sử dụng frame clause cụ thể thay vì để mặc định khi có thể. Đối với các bảng có hàng triệu bản ghi, việc thu hẹp tập dữ liệu đầu vào trước khi áp dụng window function có thể giảm đáng kể thời gian thực thi.

Kết luận

• Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) thực hiện phép tính trên tập hàng mà không gộp chúng lại — khác biệt cơ bản so với các hàm tổng hợp GROUP BY
• CTEs chia các truy vấn phức tạp thành các bước được đặt tên, dễ kiểm tra, thay thế hoàn toàn cho các subquery lồng nhau nhiều tầng
• Recursive CTEs xử lý dữ liệu phân cấp như sơ đồ tổ chức và cây danh mục, nhưng cần giới hạn độ sâu và đánh index cho cột join
• Kỹ thuật gaps-and-islands (ROW_NUMBER + phép tính ngày tháng) nhận diện các chuỗi liên tiếp trong dữ liệu chuỗi thời gian
• Named windows (mệnh đề WINDOW) giảm thiểu trùng lặp code và có thể cải thiện tối ưu hóa query plan
• Lọc dữ liệu trong mệnh đề WHERE trước khi window function được đánh giá giúp duy trì hiệu suất trên các bảng dữ liệu lớn
• Các mẫu truy vấn này xuất hiện thường xuyên trong phỏng vấn data analytics và áp dụng trực tiếp vào công việc báo cáo, phân khúc khách hàng và phân tích xu hướng hàng ngày