SQL Nang Cao cho Phong Van Data Analyst: Subquery, Pivot va Toi Uu Hoa Truy Van 2026

Trong các buổi phỏng vấn Data Analyst, SQL nâng cao luôn là một trong những chủ đề then chốt để phân biệt ứng viên trung bình với ứng viên xuất sắc. Việc nắm vững các kỹ thuật như subquery tương quan, pivot query, và tối ưu hóa truy vấn không chỉ giúp giải quyết các bài test kỹ thuật mà còn phản ánh khả năng xử lý dữ liệu thực tế trong môi trường doanh nghiệp. Bài viết này tổng hợp những kiến thức SQL nâng cao quan trọng nhất mà mọi ứng viên Data Analyst cần chuẩn bị kỹ lưỡng trước khi bước vào phỏng vấn.

Subquery tương quan và subquery thông thường

Một trong những câu hỏi phỏng vấn phổ biến nhất là phân biệt giữa subquery thông thường (regular subquery) và subquery tương quan (correlated subquery). Sự khác biệt cốt lõi nằm ở cách thức thực thi: subquery thông thường chỉ chạy một lần duy nhất, trong khi subquery tương quan được đánh giá lại cho mỗi hàng của truy vấn bên ngoài.

Ví dụ dưới đây minh họa một subquery thông thường — truy vấn con chỉ tính giá trị trung bình một lần và sử dụng kết quả đó cho toàn bộ phép so sánh:

-- regular_subquery_threshold.sql
-- Find all orders above the average order value
SELECT
  order_id,
  customer_id,
  total_amount
FROM orders
WHERE total_amount > (
  -- Executes once, returns a single scalar value
  SELECT AVG(total_amount)
  FROM orders
);

Ngược lại, subquery tương quan tham chiếu đến cột của truy vấn bên ngoài, buộc hệ quản trị cơ sở dữ liệu phải thực thi lại truy vấn con cho mỗi hàng. Đây là trường hợp tìm nhân viên có mức lương cao hơn trung bình phòng ban của họ:

-- correlated_subquery_department.sql
-- Employees earning above their department average
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
WHERE e.salary > (
  -- Re-evaluated for each employee's department
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

Với bảng dữ liệu lớn, subquery tương quan có thể gây ra vấn đề hiệu năng nghiêm trọng. Giải pháp tối ưu là chuyển đổi sang CTE (Common Table Expression) kết hợp với JOIN, chỉ quét dữ liệu một lần duy nhất:

-- optimized_with_cte.sql
-- Same result, single pass over the data
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Trong phỏng vấn, việc chủ động đề xuất phương án CTE thay vì subquery tương quan sẽ cho thấy ứng viên có tư duy về hiệu năng truy vấn — một phẩm chất mà nhà tuyển dụng luôn đánh giá cao.

Các mẫu subquery thường gặp trong phỏng vấn

EXISTS so với IN

Khi cần kiểm tra sự tồn tại của bản ghi liên quan, hai phương pháp phổ biến là EXISTS và IN. Trên các tập dữ liệu lớn, EXISTS thường vượt trội hơn vì nó dừng tìm kiếm ngay khi tìm thấy kết quả đầu tiên, trong khi IN phải xây dựng toàn bộ danh sách kết quả trước khi so sánh:

-- exists_vs_in.sql
-- Customers who placed at least one order in 2026 (EXISTS — preferred)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-01-01'
);

-- Equivalent with IN (slower on large datasets)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= '2026-01-01'
);

Scalar subquery trong mệnh đề SELECT

Một dạng subquery khác thường xuất hiện trong phỏng vấn là scalar subquery đặt trong mệnh đề SELECT. Kỹ thuật này cho phép tính toán giá trị tổng hợp từ bảng liên quan cho mỗi hàng kết quả:

-- scalar_subquery_select.sql
-- Each product with its category's total revenue
SELECT
  p.product_id,
  p.product_name,
  p.category,
  (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    JOIN products p2 ON oi.product_id = p2.product_id
    WHERE p2.category = p.category
  ) AS category_total_revenue
FROM products p;

Cần lưu ý rằng scalar subquery trong SELECT cũng là subquery tương quan — nó được thực thi cho mỗi hàng kết quả. Với tập dữ liệu lớn, việc sử dụng CTE hoặc window function sẽ cho hiệu năng tốt hơn đáng kể.

Pivot query với phép tổng hợp có điều kiện

Pivot query là kỹ thuật chuyển đổi dữ liệu từ dạng hàng sang dạng cột — một yêu cầu cực kỳ phổ biến trong phân tích dữ liệu và báo cáo. Trong phỏng vấn, đây là dạng bài tập thể hiện rõ khả năng thao tác dữ liệu của ứng viên.

Phương pháp chuẩn sử dụng CASE WHEN kết hợp với hàm tổng hợp để tạo các cột pivot. Ví dụ dưới đây tạo báo cáo doanh thu theo tháng cho từng sản phẩm:

-- pivot_monthly_revenue.sql
-- Monthly revenue pivot: one row per product, one column per month
SELECT
  product_id,
  product_name,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1
    THEN quantity * unit_price ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2
    THEN quantity * unit_price ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3
    THEN quantity * unit_price ELSE 0 END) AS mar_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4
    THEN quantity * unit_price ELSE 0 END) AS apr_revenue,
  -- Repeat for remaining months
  SUM(quantity * unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
GROUP BY product_id, product_name
ORDER BY total_revenue DESC;

Kỹ thuật tương tự áp dụng cho việc phân tích hành vi người dùng. Ví dụ sau phân tích phiên truy cập theo loại thiết bị, tính cả tỷ lệ phần trăm — dạng bài phỏng vấn rất phổ biến tại các công ty công nghệ:

-- pivot_user_activity.sql
-- User activity: sessions by day of week and device type
SELECT
  user_id,
  COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) AS mobile_sessions,
  COUNT(CASE WHEN device_type = 'desktop' THEN 1 END) AS desktop_sessions,
  COUNT(CASE WHEN device_type = 'tablet' THEN 1 END) AS tablet_sessions,
  ROUND(
    COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) * 100.0 / COUNT(*),
    1
  ) AS mobile_pct
FROM user_sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5  -- Only users with meaningful activity
ORDER BY mobile_pct DESC;

Dynamic pivot với CROSSTAB trong PostgreSQL

Khi số lượng cột pivot không cố định hoặc khi cần một cú pháp gọn gàng hơn, PostgreSQL cung cấp hàm CROSSTAB thông qua extension tablefunc. Đây là giải pháp mạnh mẽ cho các báo cáo có cấu trúc pivot phức tạp:

-- crosstab_dynamic_pivot.sql
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Revenue by product and quarter using CROSSTAB
SELECT *
FROM crosstab(
  $$
    SELECT
      product_name,
      'Q' || EXTRACT(QUARTER FROM order_date)::TEXT AS quarter,
      SUM(quantity * unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2026
    GROUP BY product_name, quarter
    ORDER BY product_name, quarter
  $$,
  $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS pivot_table(
  product_name TEXT,
  q1_revenue NUMERIC,
  q2_revenue NUMERIC,
  q3_revenue NUMERIC,
  q4_revenue NUMERIC
);

Điểm cần nhớ khi sử dụng CROSSTAB: truy vấn nguồn phải trả về đúng ba cột theo thứ tự (row identifier, category, value), và danh sách giá trị category phải khớp với định nghĩa cột đầu ra. Đây là chi tiết nhỏ nhưng thường là nguyên nhân gây lỗi khi triển khai thực tế.

EXPLAIN plan và phân tích chi phí truy vấn

Hiểu cách đọc execution plan là kỹ năng phân biệt rõ nét giữa một người viết SQL và một người thực sự hiểu SQL. Trong phỏng vấn Data Analyst cấp trung và cấp cao, câu hỏi về tối ưu hóa truy vấn gần như luôn xuất hiện.

Lệnh EXPLAIN ANALYZE cung cấp thông tin chi tiết về cách PostgreSQL thực thi truy vấn, bao gồm thời gian thực tế, số lượng buffer sử dụng, và phương pháp quét bảng:

-- explain_analyze_example.sql
-- Analyze a slow query to identify bottlenecks
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) > 1000
ORDER BY lifetime_value DESC;

Khi phân tích kết quả EXPLAIN, cần chú ý đến các yếu tố chính sau:

• Seq Scan vs Index Scan: Sequential Scan quét toàn bộ bảng, trong khi Index Scan sử dụng chỉ mục. Seq Scan trên bảng lớn thường là dấu hiệu cần tạo index.
• Nested Loop vs Hash Join: Nested Loop hiệu quả khi bảng bên trong nhỏ hoặc có index tốt. Hash Join phù hợp hơn với các phép JOIN trên tập dữ liệu lớn.
• Actual Time vs Estimated Rows: Sự chênh lệch lớn giữa số hàng ước tính và thực tế cho thấy thống kê bảng cần được cập nhật bằng ANALYZE.
• Buffers: Số lượng shared hit (đọc từ cache) so với shared read (đọc từ đĩa) phản ánh hiệu quả sử dụng bộ nhớ đệm.

Chiến lược tạo index hiệu quả

Tạo index đúng cách có thể cải thiện hiệu năng truy vấn từ vài giây xuống vài mili-giây. Dưới đây là ba loại index quan trọng mà ứng viên Data Analyst cần nắm vững:

-- indexing_strategies.sql
-- Index on order_date: filters a small percentage of rows
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index for queries filtering on both columns
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);

-- Covering index: includes columns needed in SELECT
-- Enables Index Only Scan (no table access)
CREATE INDEX idx_orders_covering
  ON orders (customer_id, order_date)
  INCLUDE (total_amount, status);

Composite index (chỉ mục tổng hợp) cần tuân thủ nguyên tắc thứ tự cột: đặt cột có tính chọn lọc cao nhất (selectivity) ở đầu, hoặc cột thường xuất hiện trong mệnh đề WHERE với phép so sánh bằng trước cột dùng phép so sánh phạm vi.

Covering index với mệnh đề INCLUDE cho phép PostgreSQL trả về kết quả hoàn toàn từ index mà không cần truy cập bảng gốc (Index Only Scan) — đây là mức tối ưu cao nhất có thể đạt được.

Ngoài ra, partial index (chỉ mục bộ phận) là kỹ thuật tối ưu khi truy vấn chỉ tập trung vào một tập con dữ liệu cụ thể:

-- partial_index.sql
-- Only index active orders (smaller, faster index)
CREATE INDEX idx_orders_active
  ON orders (customer_id, order_date)
  WHERE status = 'active';

-- Only index recent data for dashboard queries
CREATE INDEX idx_orders_recent
  ON orders (order_date)
  WHERE order_date >= '2026-01-01';

Partial index nhỏ hơn đáng kể so với index toàn bộ bảng, giúp tiết kiệm dung lượng lưu trữ và tăng tốc độ cập nhật. Đây là kỹ thuật nâng cao mà nếu ứng viên đề cập trong phỏng vấn sẽ tạo ấn tượng rất tốt.

Các anti-pattern trong truy vấn SQL

Nhận biết và tránh các anti-pattern là dấu hiệu của một Data Analyst có kinh nghiệm. Dưới đây là hai anti-pattern nghiêm trọng nhất thường xuất hiện trong phỏng vấn.

Sử dụng hàm trên cột index

Khi áp dụng hàm lên cột đã được đánh index, query optimizer không thể sử dụng index và buộc phải quét toàn bộ bảng:

-- anti_patterns.sql
-- BAD: function on indexed column disables the index
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- GOOD: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

Phiên bản tối ưu sử dụng phép so sánh phạm vi trực tiếp trên cột, cho phép PostgreSQL tận dụng index một cách hiệu quả. Nguyên tắc chung là: giữ cột index "sạch" — không bọc trong bất kỳ hàm nào.

Bẫy NULL với NOT IN

Đây là anti-pattern nguy hiểm nhất vì nó không gây lỗi cú pháp mà trả về kết quả sai hoàn toàn. Khi subquery trong NOT IN chứa giá trị NULL, toàn bộ truy vấn sẽ trả về tập rỗng:

-- not_in_null_trap.sql
-- BAD: returns empty if any customer_id is NULL in subquery
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Nguyên nhân kỹ thuật là do phép so sánh với NULL trong SQL luôn trả về UNKNOWN theo logic ba giá trị (three-valued logic). Khi NOT IN gặp NULL, toàn bộ biểu thức trở thành UNKNOWN và không hàng nào thỏa mãn điều kiện. NOT EXISTS xử lý đúng trường hợp này vì nó chỉ kiểm tra sự tồn tại của hàng khớp, không thực hiện phép so sánh trực tiếp với giá trị NULL.

Kết luận

Tổng kết lại, những kiến thức SQL nâng cao quan trọng nhất cho phỏng vấn Data Analyst bao gồm:

• Subquery tương quan vs subquery thông thường: Hiểu rõ sự khác biệt về cơ chế thực thi và biết khi nào nên chuyển sang CTE để tối ưu hiệu năng.
• EXISTS thay vì IN: Ưu tiên sử dụng EXISTS cho các truy vấn kiểm tra sự tồn tại, đặc biệt trên tập dữ liệu lớn. Tuyệt đối tránh NOT IN khi dữ liệu có thể chứa NULL.
• Pivot query với CASE WHEN: Thành thạo kỹ thuật chuyển đổi dữ liệu từ hàng sang cột — kỹ năng báo cáo thiết yếu mà hầu hết phỏng vấn đều kiểm tra.
• EXPLAIN ANALYZE: Khả năng đọc và phân tích execution plan là yếu tố phân biệt ứng viên cấp trung và cấp cao.
• Chiến lược index: Nắm vững composite index, covering index, và partial index. Hiểu nguyên tắc thứ tự cột và khi nào nên dùng loại index nào.
• Tránh anti-pattern: Không áp dụng hàm lên cột index, không sử dụng NOT IN với dữ liệu có thể chứa NULL, và luôn kiểm tra execution plan trước khi triển khai truy vấn trên production.

Việc nắm vững những chủ đề trên không chỉ giúp vượt qua phỏng vấn mà còn xây dựng nền tảng vững chắc cho công việc phân tích dữ liệu hàng ngày. SQL là công cụ mạnh mẽ nhất trong bộ kỹ năng của Data Analyst — đầu tư thời gian để thành thạo nó luôn mang lại giá trị xứng đáng.