SQL cho Data Science

Trước khi mô hình hoá hay vẽ biểu đồ, bạn phải lấy đúng dữ liệu. Trong thực tế, phần lớn dữ liệu doanh nghiệp sống trong CSDL quan hệ (PostgreSQL, MySQL) hoặc data warehouse (BigQuery, Snowflake, Redshift) — và SQL là cách truy cập chuẩn.

• Đẩy tính toán về gần dữ liệu: lọc, join, tổng hợp hàng tỷ dòng ngay trong DB nhanh và rẻ hơn nhiều so với kéo hết về pandas.
• Trích xuất & feature engineering: aggregate, window function tạo đặc trưng (rolling average, rank, cohort) trực tiếp bằng SQL.
• Phỏng vấn: vòng SQL gần như bắt buộc cho vị trí Data Analyst/Scientist — top-N, dedup, retention là câu kinh điển.
• Nền của công cụ khác: dbt, Spark SQL, Grafana, Metabase... đều xoay quanh SQL.

Ta viết câu lệnh theo thứ tự SELECT → FROM → WHERE..., nhưng DB thực thi logic theo thứ tự khác. Hiểu điều này giải thích nhiều lỗi thường gặp:

SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = true            -- lọc dòng trước
GROUP BY department
HAVING AVG(salary) > 50000  -- lọc nhóm sau
ORDER BY avg_sal DESC       -- alias OK ở đây
LIMIT 10;

JOIN kết hợp dòng từ nhiều bảng theo điều kiện khớp (thường trên khoá). Hình dung hai bảng customers và orders:

-- LEFT JOIN: mọi khách hàng, kèm số đơn (0 nếu chưa mua)
SELECT c.id, c.name, COUNT(o.id) AS n_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- SELF JOIN: gắn tên quản lý cho từng nhân viên
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Hàm tổng hợp (aggregate) gộp nhiều dòng thành một giá trị. Kết hợp GROUP BY để tính theo từng nhóm.

SELECT category,
       COUNT(*)                 AS n_rows,
       COUNT(DISTINCT customer_id) AS n_customers,
       SUM(amount)             AS revenue,
       AVG(amount)             AS avg_order
FROM orders
GROUP BY category
HAVING SUM(amount) > 10000   -- lọc nhóm theo tổng doanh thu
ORDER BY revenue DESC;

Một subquery là truy vấn lồng bên trong truy vấn khác (ở FROM, WHERE hoặc SELECT). Một CTE (Common Table Expression) với WITH đặt tên cho một truy vấn tạm để tái sử dụng, giúp câu lệnh phức tạp đọc dễ như nhiều bước.

-- CTE: dễ đọc, có thể chuỗi nhiều bước, tự tham chiếu (recursive)
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;   -- ai lương trên trung bình phòng

Correlated subquery là subquery tham chiếu tới bảng bên ngoài, nên phải chạy lại cho từng dòng ngoài — mạnh nhưng có thể chậm:

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
    WHERE department = e.department   -- 'e' đến từ ngoài → correlated
);

Khác aggregate (gộp nhiều dòng thành một), window function tính trên một tập dòng liên quan nhưng giữ nguyên từng dòng. Cú pháp lõi:

func() OVER (
  PARTITION BY col      -- chia thành nhóm (như GROUP BY nhưng không gộp)
  ORDER BY col          -- thứ tự trong mỗi nhóm
  -- [frame: ROWS BETWEEN ... ] cho running/moving
)

Hàm xếp hạng:

-- Xếp hạng lương trong từng phòng
SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

LAG / LEAD lấy giá trị của dòng trước/sau — lý tưởng để tính chênh lệch theo thời gian:

-- Doanh thu tháng này so với tháng trước & % tăng trưởng
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_rev,
  revenue - LAG(revenue) OVER (ORDER BY month) AS diff
FROM monthly_sales;

Running total & moving average dùng frame:

-- Tổng luỹ kế (running total)
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
-- Trung bình trượt 7 dòng gần nhất (moving average)
  AVG(amount) OVER (ORDER BY date
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;

CASE WHEN là if/else trong SQL — tạo cột phân loại, pivot thủ công, gán nhãn:

SELECT name, score,
  CASE WHEN score >= 90 THEN 'A'
       WHEN score >= 70 THEN 'B'
       ELSE 'C' END AS grade
FROM students;

-- Pivot bằng CASE + SUM: đếm đơn theo trạng thái trên mỗi cột
SELECT region,
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status='refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders GROUP BY region;

NULL nghĩa "không rõ/thiếu", KHÔNG phải 0 hay chuỗi rỗng. Mọi so sánh với NULL cho kết quả UNKNOWN:

a) Top-N mỗi nhóm (vd 3 nhân viên lương cao nhất mỗi phòng) — dùng ROW_NUMBER trong CTE rồi lọc:

WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked WHERE rn <= 3;

b) Lương cao thứ nhì (second highest salary) — dùng DENSE_RANK để xử lý ties đúng:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Cách tổng quát cho hạng thứ N:
WITH r AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT salary FROM r WHERE dr = 2;

c) Phát hiện bản ghi trùng (duplicates) theo email:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Giữ 1 bản, đánh dấu phần thừa để xoá:
WITH d AS (
  SELECT id, ROW_NUMBER() OVER
    (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
SELECT id FROM d WHERE rn > 1;  -- các dòng dư

d) Retention / cohort cơ bản — % người dùng đăng ký tháng đầu quay lại ở các tháng sau:

WITH cohort AS (   -- tháng đầu tiên của mỗi user = cohort
  SELECT user_id, MIN(DATE_TRUNC('month', event_date)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_date) AS active_month
  FROM events
)
SELECT c.cohort_month,
       a.active_month,
       COUNT(DISTINCT a.user_id) AS active_users
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, a.active_month
ORDER BY c.cohort_month, a.active_month;