vi
new
0
Avatar
Xuân Chinh Trịnh @trinhxuanchinh.dev
34 3 192
Đã đăng vào about 12 hours ago 1 phút đọc
2

[Java Backend Zero to Hello] BÀI 4.3: SQL NÂNG CAO

Java Backend Zero to Hello

📚 Bài viết thuộc series Java Backend Zero to Hello 📌 Phần: Phase 4: Database & SQL | Bài 38/86

BÀI 4.3: SQL NÂNG CAO

Mục tiêu

  • Sử dụng Subquery
  • Áp dụng Window Functions
  • Sử dụng CTE (Common Table Expressions)
  • Tối ưu hóa truy vấn

1. SUBQUERY (TRUY VẤN CON)

1.1 Subquery trong WHERE

-- Tìm user có đơn hàng lớn nhất
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE total = (SELECT MAX(total) FROM orders)
);

-- Tìm user có đơn hàng > trung bình
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE total > (SELECT AVG(total) FROM orders)
);

1.2 Subquery trong SELECT

SELECT
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count,
    (SELECT MAX(total) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;

1.3 Subquery trong FROM

SELECT avg_order.user_id, avg_order.avg_total
FROM (
    SELECT user_id, AVG(total) AS avg_total
    FROM orders
    GROUP BY user_id
) AS avg_order
WHERE avg_order.avg_total > 100;

1.4 EXISTS / NOT EXISTS

-- User có ít nhất 1 đơn hàng
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- User không có đơn hàng
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

2. CTE (COMMON TABLE EXPRESSIONS)

CTE giúp truy vấn phức tạp dễ đọc hơn.

2.1 Cú pháp

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

2.2 Ví dụ

WITH high_value_customers AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING total_spent > 1000
)
SELECT u.name, hvc.total_spent
FROM users u
JOIN high_value_customers hvc ON u.id = hvc.user_id
ORDER BY hvc.total_spent DESC;

2.3 Multiple CTE

WITH
    monthly_sales AS (
        SELECT
            DATE_FORMAT(created_at, '%Y-%m') AS month,
            SUM(total) AS revenue
        FROM orders
        GROUP BY month
    ),
    top_month AS (
        SELECT month, revenue
        FROM monthly_sales
        ORDER BY revenue DESC
        LIMIT 1
    )
SELECT * FROM top_month;

2.4 Recursive CTE

-- Tìm tất cả cấp dưới của nhân viên
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: cấp cao nhất
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: cấp dưới
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

3. WINDOW FUNCTIONS

Tính toán trên một "cửa sổ" các dòng liên quan, không gộp thành 1 dòng.

3.1 ROW_NUMBER

Đánh số thứ tự.

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

3.2 RANK / DENSE_RANK

Xếp hạng (có/không có gap).

SELECT
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

3.3 PARTITION BY

Chia nhóm trước khi áp dụng window function.

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

3.4 Aggregate Window Functions

SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

3.5 LAG / LEAD

Giá trị dòng trước/sau.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    LEAD(revenue) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;

3.6 FIRST_VALUE / LAST_VALUE

SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

4. CASE WHEN

SELECT
    name,
    salary,
    CASE
        WHEN salary > 1000 THEN 'High'
        WHEN salary > 500 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

5. STRING FUNCTIONS

SELECT
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    SUBSTRING(name, 1, 3) AS first_3,
    CONCAT(first_name, ' ', last_name) AS full_name,
    TRIM(name) AS trimmed,
    REPLACE(name, 'a', '@') AS replaced
FROM users;

6. DATE FUNCTIONS

SELECT
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    DATE(created_at) AS date_only,
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    DAY(created_at) AS day,
    DATEDIFF(NOW(), created_at) AS days_ago,
    DATE_ADD(created_at, INTERVAL 7 DAY) AS next_week,
    DATE_FORMAT(created_at, '%d/%m/%Y') AS formatted
FROM orders;

7. INDEX VÀ PERFORMANCE

7.1 Tạo Index

-- Single column
CREATE INDEX idx_users_email ON users(email);

-- Composite
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique
CREATE UNIQUE INDEX idx_users_username ON users(username);

7.2 Xem và xóa Index

SHOW INDEX FROM users;
DROP INDEX idx_users_email ON users;

7.3 EXPLAIN - Phân tích truy vấn

EXPLAIN SELECT * FROM users WHERE email = 'an@example.com';

Các cột quan trọng:

  • type: ALL (full scan), index, range, ref, const
  • key: Index được sử dụng
  • rows: Số dòng ước tính
  • Extra: Thông tin thêm

8. TỐI ƯU TRUY VẤN

8.1 ✅ NÊN

-- Dùng Index
SELECT * FROM users WHERE email = 'an@example.com';

-- Chỉ SELECT cột cần thiết
SELECT id, name FROM users;

-- Dùng EXISTS thay IN cho subquery lớn
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- LIMIT khi không cần tất cả
SELECT * FROM users LIMIT 10;

8.2 ❌ TRÁNH

-- SELECT *
SELECT * FROM users;

-- Function trên cột có index
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- LIKE với wildcard đầu
SELECT * FROM users WHERE name LIKE '%an%';

-- OR không hiệu quả
SELECT * FROM users WHERE name = 'An' OR email = 'an@example.com';

9. TRANSACTION

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Nếu OK
COMMIT;

-- Nếu lỗi
ROLLBACK;

Isolation Levels

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

10. BÀI TẬP THỰC HÀNH

Bài 1: Phân tích doanh thu

-- Doanh thu theo tháng với growth rate
WITH monthly AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY month
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
          LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly;

Bài 2: Top sản phẩm theo danh mục

WITH ranked_products AS (
    SELECT
        category_id,
        name,
        price,
        RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
    FROM products
)
SELECT * FROM ranked_products WHERE rank <= 3;

Bài 3: Cohort Analysis

Phân tích retention của user theo tháng đăng ký.

11. TÓM TẮT

Khái niệm Mô tả
Subquery Truy vấn lồng
CTE Biến tạm cho truy vấn phức tạp
Window Function Tính toán trên cửa sổ
ROW_NUMBER Đánh số
RANK Xếp hạng có gap
PARTITION BY Chia nhóm
LAG/LEAD Giá trị trước/sau
EXPLAIN Phân tích truy vấn

Bài tiếp theo: 4.4 MySQL/PostgreSQL thực hành

🧭 Điều Hướng Series

⬅️ Bài trước: BÀI 4.2: SQL CƠ BẢN

📋 Lộ trình tổng quan: Xem Toàn Bộ Series

➡️ Bài tiếp theo: BÀI 4.4: MYSQL/POSTGRESQL THỰC HÀNH

Backend Java jpa Spring Boot SQL

All rights reserved

Mục lục


Viblo
Hãy đăng ký một tài khoản Viblo để nhận được nhiều bài viết thú vị hơn.
Đăng kí