Tối Ưu Hóa Truy Vấn Thông Báo Bằng CTE Trong SQL: Hướng Dẫn Chi Tiết

Giới Thiệu

Trong bài viết này, chúng ta sẽ cùng nhau khám phá cách tối ưu hóa truy vấn SQL để lấy danh sách thông báo từ hai bảng notification.en_laand_user_notification và notification.en_laand_notification thông qua việc sử dụng CTE (Common Table Expression). CTE giúp làm cho mã SQL dễ đọc, bảo trì và quản lý hơn khi làm việc với các truy vấn phức tạp.

Nội Dung Truy Vấn SQL

Dưới đây là đoạn truy vấn SQL mà chúng ta sẽ phân tích:

sql Copy

WITH user_notification AS (
    SELECT un.notification_id,
           CASE WHEN un.is_read = 't' THEN 2 ELSE 1 END AS status,
           un.created_date AS date,
           un.group_id
    FROM notification.en_laand_user_notification un
    WHERE un.user_id = 'caobh'
),

notification AS (
    SELECT n.id AS notification_id,
           CASE WHEN n.is_global = 't' THEN 1 ELSE 0 END AS status,
           n.created_date AS date
    FROM notification.en_laand_notification n
    WHERE (n.is_deleted = 'f')
      AND (n.notification_type = 3)
      AND (n.expired_date IS NULL OR (NOW() BETWEEN n.effective_date AND n.expired_date))
      AND ((n.is_global = 't' OR n.user_id = 'caobh')
           OR EXISTS (SELECT 1 FROM user_notification un WHERE n.group_id = un.group_id))
      AND (n.content IS NULL OR n.content LIKE '%page1%')
    UNION ALL
    SELECT un.notification_id,
           un.status,
           un.date AS date
    FROM user_notification un
)

SELECT notification_id, SUM(status) AS total_status, MIN(date) AS earliest_date
FROM notification
GROUP BY notification_id
ORDER BY CASE WHEN SUM(status) > 1 THEN 1 ELSE 0 END, MIN(date);

Phân Tích Truy Vấn

1. CTE Đầu Tiên: user_notification

CTE này thu thập thông tin thông báo từ bảng en_laand_user_notification dựa trên user_id. Mỗi thông báo được phân loại trạng thái (status) tùy thuộc vào việc người dùng đã đọc hay chưa.

Kết quả của CTE user_notification như sau:

notification_id	status	date	group_id
101	2	2025-02-01	1
102	1	2025-02-02	2

2. CTE Thứ Hai: notification

CTE này lấy dữ liệu từ bảng en_laand_notification với các điều kiện cụ thể:

Chưa bị xóa (is_deleted = 'f').
Loại thông báo là 3 (notification_type = 3).
Ngày hiệu lực của thông báo có thể vĩnh viễn hoặc nằm trong khoảng thời gian đã xác định.
Kiểm tra xem thông báo có phải là toàn cục (is_global = 't') hoặc được gửi riêng cho người dùng (user_id = 'caobh').
Nếu không, điều kiện EXISTS sẽ kiểm tra xem có thông báo nào thuộc cùng group_id với thông báo của người dùng không.

3. Tổng Hợp Dữ Liệu

Cuối cùng, dữ liệu được tổng hợp và sắp xếp:

Sử dụng SUM(status) để xác định độ ưu tiên của thông báo.
MIN(date) để lấy ngày thông báo sớm nhất.
Sắp xếp theo độ ưu tiên và thời gian của thông báo.