SQL Nâng cao | MinAI Learning

Thực hành

2/15•1.5 giờ

Đang tải...

SQL Nâng cao

Window Functions, CTEs, Subqueries và Query Optimization

SQL Nâng cao

🎯 Mục tiêu bài học

TB5 min

Advanced SQL Database

Sau bài này, bạn sẽ:

Hiểu khái niệm Window Frame và cách SQL tính toán trên từng dòng
Áp dụng thành thạo ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER
Viết được CTE và CTE đệ quy cho truy vấn phức tạp
Phân biệt khi nào dùng Subquery, CTE, hay JOIN
Tối ưu truy vấn bằng EXPLAIN ANALYZE và đánh chỉ mục

Task 0

1. Hàm cửa sổ (Window Functions)

1.1 Giới thiệu hàm cửa sổ

Window Functions cho phép tính toán trên một "cửa sổ" (window) của rows liên quan đến row hiện tại.

SQL

1-- Syntax cơ bản
2SELECT 
3    column1,
4    AGG_FUNCTION(column2) OVER (
5        PARTITION BY column3
6        ORDER BY column4
7        ROWS/RANGE BETWEEN ... AND ...
8    ) as window_result
9FROM table;

1.2 Các loại hàm cửa sổ

Loại	Hàm	Mục đích
Tổng hợp	SUM, AVG, COUNT, MIN, MAX	Tính toán trên cửa sổ
Xếp hạng	ROW_NUMBER, RANK, DENSE_RANK

Tiếp: Pandas Nâng cao

Ví dụ

1Dữ liệu gốc:      Window Frame cho dòng 3 (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING):
2┌─────────────┐    ┌─────────────┐
3│ Row 1: 100  │    │             │
4│ Row 2: 200  │    │ → Row 2: 200│  ← 1 dòng trước
5│ Row 3: 150  │ →  │ → Row 3: 150│  ← dòng hiện tại
6│ Row 4: 300  │    │ → Row 4: 300│  ← 1 dòng sau
7│ Row 5: 250  │    │             │
8└─────────────┘    └─────────────┘
9                   AVG = (200+150+300)/3 = 216.67

Frame	Ý nghĩa	Dùng khi
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Từ đầu đến dòng hiện tại	Tổng tích lũy (running total)
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	1 dòng trước + hiện tại + 1 dòng sau	Trung bình trượt (moving average)
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	2 dòng trước + hiện tại	Moving average 3 kỳ
`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Mặc định khi có ORDER BY	Tương tự ROWS nhưng gộp giá trị bằng nhau

Tháng	Doanh số
T1	100
T2	200
T3	150
T4	300

Ví dụ

1T1: Frame = [100]              → SUM = 100
2T2: Frame = [100, 200]         → SUM = 100 + 200 = 300  
3T3: Frame = [100, 200, 150]    → SUM = 100 + 200 + 150 = 450
4T4: Frame = [100, 200, 150, 300] → SUM = 100 + 200 + 150 + 300 = 750

Tháng	Doanh số	Tổng tích lũy	Cách tính
T1	100	100	100
T2	200	300	100 + 200
T3	150	450	100 + 200 + 150
T4	300	750	100 + 200 + 150 + 300

SQL

1-- Dataset: Sales
2CREATE TABLE sales (
3    employee VARCHAR(50),
4    region VARCHAR(50),
5    revenue DECIMAL(10,2)
6);
7
8INSERT INTO sales VALUES
9('Alice', 'North', 5000),
10('Bob', 'North', 7000),
11('Charlie', 'South', 7000),
12('David', 'South', 6000);
13
14-- ROW_NUMBER: Số thứ tự liên tục
15SELECT 
16    employee,
17    region,
18    revenue,
19    ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) as row_num
20FROM sales;
21
22/*
23employee | region | revenue | row_num
24---------|--------|---------|--------
25Bob      | North  | 7000    | 1
26Alice    | North  | 5000    | 2
27Charlie  | South  | 7000    | 1
28David    | South  | 6000    | 2
29*/
30
31-- RANK: Giống nhau thì cùng hạng, nhảy hạng
32SELECT 
33    employee,
34    revenue,
35    RANK() OVER (ORDER BY revenue DESC) as rank
36FROM sales;
37
38/*
39employee | revenue | rank
40---------|---------|-----
41Bob      | 7000    | 1
42Charlie  | 7000    | 1  -- Cùng rank
43David    | 6000    | 3  -- Nhảy qua rank 2
44Alice    | 5000    | 4
45*/
46
47-- DENSE_RANK: Không nhảy hạng
48SELECT 
49    employee,
50    revenue,
51    DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank
52FROM sales;
53
54/*
55employee | revenue | dense_rank
56---------|---------|------------
57Bob      | 7000    | 1
58Charlie  | 7000    | 1
59David    | 6000    | 2  -- Không nhảy
60Alice    | 5000    | 3
61*/

SQL

1-- LAG: Lấy giá trị row trước đó
2-- LEAD: Lấy giá trị row sau đó
3
4CREATE TABLE stock_prices (
5    date DATE,
6    price DECIMAL(10,2)
7);
8
9SELECT 
10    date,
11    price,
12    LAG(price, 1) OVER (ORDER BY date) as prev_price,
13    LEAD(price, 1) OVER (ORDER BY date) as next_price,
14    price - LAG(price, 1) OVER (ORDER BY date) as daily_change
15FROM stock_prices;
16
17/*
18date       | price | prev_price | next_price | daily_change
19-----------|-------|------------|------------|-------------
202024-01-01 | 100   | NULL       | 102        | NULL
212024-01-02 | 102   | 100        | 98         | 2
222024-01-03 | 98    | 102        | 105        | -4
232024-01-04 | 105   | 98         | NULL       | 7
24*/

SQL

1-- Bước 1: Tạo dữ liệu mẫu
2CREATE TABLE monthly_revenue (
3    month DATE,
4    revenue DECIMAL(12,2)
5);
6
7INSERT INTO monthly_revenue VALUES
8('2024-01-01', 150000),
9('2024-02-01', 180000),
10('2024-03-01', 165000),
11('2024-04-01', 210000),
12('2024-05-01', 195000),
13('2024-06-01', 240000);
14
15-- Bước 2: Tính tăng trưởng so với tháng trước
16SELECT 
17    month,
18    revenue,
19    LAG(revenue) OVER (ORDER BY month) as prev_revenue,
20    revenue - LAG(revenue) OVER (ORDER BY month) as change,
21    ROUND(
22        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
23        LAG(revenue) OVER (ORDER BY month), 1
24    ) as growth_pct,
25    SUM(revenue) OVER (ORDER BY month) as cumulative_revenue
26FROM monthly_revenue;

Tháng	Revenue	Prev	Change	Growth%	Tích lũy	Cách tính growth
T1	150K	NULL	NULL	NULL	150K	—
T2	180K	150K	+30K	+20.0%	330K	(180-150)/150×100
T3	165K	180K	-15K	-8.3%	495K	(165-180)/180×100
T4	210K	165K	+45K	+27.3%	705K	(210-165)/165×100
T5	195K	210K	-15K	-7.1%	900K	(195-210)/210×100
T6	240K	195K	+45K	+23.1%	1.14M	(240-195)/195×100

SQL

1-- CTE đơn giản
2WITH high_earners AS (
3    SELECT 
4        employee_id,
5        name,
6        salary
7    FROM employees
8    WHERE salary > 100000
9)
10SELECT * FROM high_earners
11WHERE department = 'Engineering';

SQL

1WITH 
2-- CTE 1: Aggregate sales
3monthly_sales AS (
4    SELECT 
5        DATE_TRUNC('month', order_date) as month,
6        SUM(amount) as total_sales
7    FROM orders
8    GROUP BY DATE_TRUNC('month', order_date)
9),
10-- CTE 2: Calculate growth
11sales_growth AS (
12    SELECT 
13        month,
14        total_sales,
15        LAG(total_sales) OVER (ORDER BY month) as prev_month_sales,
16        (total_sales - LAG(total_sales) OVER (ORDER BY month)) / 
17        LAG(total_sales) OVER (ORDER BY month) * 100 as growth_pct
18    FROM monthly_sales
19)
20SELECT * FROM sales_growth
21WHERE growth_pct > 10;

SQL

1-- Ví dụ: Employee hierarchy
2WITH RECURSIVE emp_hierarchy AS (
3    -- Base case: Top manager
4    SELECT 
5        employee_id,
6        name,
7        manager_id,
8        1 as level
9    FROM employees
10    WHERE manager_id IS NULL
11    
12    UNION ALL
13    
14    -- Recursive case
15    SELECT 
16        e.employee_id,
17        e.name,
18        e.manager_id,
19        h.level + 1
20    FROM employees e
21    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
22)
23SELECT * FROM emp_hierarchy
24ORDER BY level, name;

SQL

1-- Tìm employees có salary > average của department họ
2SELECT 
3    e.name,
4    e.department,
5    e.salary
6FROM employees e
7WHERE e.salary > (
8    SELECT AVG(salary)
9    FROM employees e2
10    WHERE e2.department = e.department
11);

SQL

1-- EXISTS: Kiểm tra tồn tại (nhanh hơn với large datasets)
2SELECT name
3FROM customers c
4WHERE EXISTS (
5    SELECT 1 
6    FROM orders o 
7    WHERE o.customer_id = c.id 
8    AND o.order_date > '2024-01-01'
9);
10
11-- IN: Kiểm tra trong danh sách
12SELECT name
13FROM customers
14WHERE id IN (
15    SELECT DISTINCT customer_id 
16    FROM orders 
17    WHERE order_date > '2024-01-01'
18);

SQL

1-- Tạo index
2CREATE INDEX idx_orders_date ON orders(order_date);
3CREATE INDEX idx_orders_customer ON orders(customer_id);
4
5-- Composite index
6CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);

SQL

1-- ❌ Bad: SELECT *
2SELECT * FROM large_table;
3
4-- ✅ Good: Chỉ lấy columns cần thiết
5SELECT id, name, created_at FROM large_table;
6
7-- ❌ Bad: Function trong WHERE
8SELECT * FROM orders WHERE YEAR(order_date) = 2024;
9
10-- ✅ Good: Sử dụng range
11SELECT * FROM orders 
12WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
13
14-- ❌ Bad: OR với nhiều conditions
15SELECT * FROM products 
16WHERE category = 'A' OR category = 'B' OR category = 'C';
17
18-- ✅ Good: IN
19SELECT * FROM products 
20WHERE category IN ('A', 'B', 'C');

Sai lầm	Vấn đề	Cách sửa
Dùng `RANK()` khi cần số thứ tự liên tục	Nhảy hạng khi có giá trị bằng nhau	Dùng `DENSE_RANK()` hoặc `ROW_NUMBER()`
Quên `PARTITION BY`	Window function tính trên toàn bảng thay vì từng nhóm	Luôn kiểm tra: cần tính theo nhóm hay toàn bộ?
`LAG()` trả về NULL ở dòng đầu	Phép tính sau đó bị NULL	Dùng `LAG(col, 1, 0)` — tham số thứ 3 là giá trị mặc định
CTE quá phức tạp, lồng 5+ CTE	Khó đọc, khó debug	Dùng temp table cho CTE trung gian
Dùng `SELECT *` trong subquery	Chậm, trả về cột không cần thiết	Chỉ SELECT đúng cột cần dùng
Thiếu index cho cột JOIN/WHERE	Full table scan, query chậm	Luôn kiểm tra EXPLAIN trước khi chạy trên production

SQL

1-- Dữ liệu mẫu
2CREATE TABLE category_sales (
3    month DATE,
4    category VARCHAR(50),
5    revenue DECIMAL(10,2)
6);
7
8INSERT INTO category_sales VALUES
9('2024-01-01', 'Electronics', 50000),
10('2024-02-01', 'Electronics', 65000),
11('2024-03-01', 'Electronics', 45000),
12('2024-01-01', 'Clothing', 30000),
13('2024-02-01', 'Clothing', 35000),
14('2024-03-01', 'Clothing', 40000);

month	category	revenue	cumulative_rev
2024-01	Clothing	30,000	30,000
2024-02	Clothing	35,000	65,000
2024-03	Clothing	40,000	105,000
2024-01	Electronics	50,000	50,000
2024-02	Electronics	65,000	115,000
2024-03	Electronics	45,000	160,000

SQL

1-- Dữ liệu mẫu
2CREATE TABLE orders (
3    order_id INT,
4    customer_name VARCHAR(50),
5    order_date DATE,
6    amount DECIMAL(10,2)
7);
8
9INSERT INTO orders VALUES
10(1, 'Alice', '2024-01-15', 500),
11(2, 'Bob', '2024-01-20', 800),
12(3, 'Alice', '2024-02-10', 600),
13(4, 'Charlie', '2024-02-15', 1200),
14(5, 'Bob', '2024-03-01', 400),
15(6, 'David', '2024-04-10', 900),
16(7, 'Alice', '2024-04-20', 700);

id	name	level	path
1	CEO Minh	1	CEO Minh
2	VP Lan	2	CEO Minh → VP Lan
3	VP Hùng	2	CEO Minh → VP Hùng
4	Manager Tú	3	CEO Minh → VP Lan → Manager Tú
5	Manager Mai	3	CEO Minh → VP Lan → Manager Mai
6	Dev Hoàng	4	CEO Minh → VP Lan → Manager Tú → Dev Hoàng

Nhu cầu	Công cụ	Ví dụ
Xếp hạng trong nhóm	`RANK() / DENSE_RANK()`	Top sản phẩm bán chạy mỗi danh mục
So sánh với kỳ trước	`LAG() / LEAD()`	Tăng trưởng doanh thu MoM, YoY
Tổng/trung bình lũy kế	`SUM() / AVG() OVER`	Doanh thu lũy kế theo quý
Phân chia nhóm đều	`NTILE(n)`	Chia khách hàng thành 4 nhóm chi tiêu
Truy cập dòng đầu/cuối	`FIRST_VALUE() / LAST_VALUE()`	Giá mở cửa / đóng cửa cổ phiếu

Nguồn	Nội dung	Link
PostgreSQL Docs	Hướng dẫn chính thức Window Functions	Window Functions
Mode Analytics	SQL Tutorial: Window Functions	Mode SQL Tutorial
LearnSQL.com	Bài tập Window Functions tương tác	Window Functions Course
SQLBolt	Bài tập SQL tương tác cho người mới	SQLBolt Exercises
Use The Index, Luke	Hướng dẫn tối ưu SQL & indexing	Use The Index, Luke
W3Schools	Tham khảo cú pháp SQL nhanh	SQL Reference

Khóa học

Mentor & Hỗ trợ

Blog

Giới thiệu

SQL Nâng cao

SQL Nâng cao

🎯 Mục tiêu bài học

1. Hàm cửa sổ (Window Functions)

1.1 Giới thiệu hàm cửa sổ

1.2 Các loại hàm cửa sổ

1.3 Window Frame là gì?

Tính toán thủ công: Running Total (Tổng tích lũy)

1.4 Ví dụ ROW_NUMBER so với RANK

1.4 LAG và LEAD

1.6 Ví dụ thực tế: Phân tích tăng trưởng doanh số

2. Biểu thức bảng chung (CTEs)

2.1 Cú pháp WITH

2.2 Nhiều CTE

2.3 CTE đệ quy

3. Truy vấn con nâng cao

3.1 Truy vấn con tương quan

3.2 EXISTS so với IN

4. Tối ưu truy vấn

4.1 EXPLAIN ANALYZE

4.2 Đánh chỉ mục

4.3 Thực tiễn tốt nhất

5. Sai lầm thường gặp

6. Bài tập thực hành

Bài 1: Running Total theo danh mục

Bài 2: Top khách hàng mỗi quý (CTE)

Bài 3: Tổ chức phân cấp (CTE đệ quy)

🧠 Góc Nhìn Chuyên Gia: Window Functions trong thực tế

Khi nào dùng công cụ nào?

Hiệu năng

📚 Tài liệu tham khảo