Mục lục
Định dạng/khả năng đọc
- Dùng dấu phẩy ở đầu khi phân tách các trường
- Dùng giá trị giả trong mệnh đề WHERE
- Thụt lề mã hợp lý
- Cân nhắc CTE khi viết truy vấn phức tạp
Tính năng hữu ích
- Dùng toán tử
:: để chuyển đổi kiểu dữ liệu
- Tận dụng anti join
- Dùng
QUALIFY để lọc hàm cửa sổ
- Có thể dùng
GROUP BY theo vị trí cột
Những cạm bẫy cần tránh
- Cẩn thận khi dùng
NOT IN với giá trị NULL
- Đổi tên trường tính toán để tránh mơ hồ
- Chỉ rõ mỗi cột thuộc về bảng nào
- Hiểu thứ tự thực thi
- Thêm chú thích vào mã
- Đọc toàn bộ tài liệu
Định dạng/khả năng đọc
Dùng dấu phẩy ở đầu khi phân tách các trường
- Khi phân tách các trường trong mệnh đề
SELECT, dùng dấu phẩy ở đầu có thể giúp phân biệt rõ các cột mới
- Dấu phẩy ở đầu cung cấp tín hiệu trực quan giúp dễ phát hiện việc thiếu dấu phẩy
SELECT
employee_id,
employee_name,
job,
salary
FROM employees;
Dùng giá trị giả trong mệnh đề WHERE
- Có thể dùng giá trị giả trong mệnh đề WHERE để thêm và bỏ điều kiện một cách linh hoạt
SELECT *
FROM employees
WHERE 1=1 -- giá trị giả
AND job IN ('Clerk', 'Manager')
AND dept_no != 5;
Thụt lề mã hợp lý
- Thụt lề mã để tăng khả năng đọc và giúp đồng nghiệp cũng như chính bạn trong tương lai dễ hiểu hơn
-- ví dụ tệ:
SELECT
timeslot_date,
timeslot_channel,
overnight_fta_share,
IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C7_fta_share,
IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C28_fta_share
FROM timeslot_data;
-- ví dụ tốt:
SELECT
timeslot_date,
timeslot_channel,
overnight_fta_share,
IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C7_fta_share,
IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C28_fta_share
FROM timeslot_data;
Cân nhắc CTE khi viết truy vấn phức tạp
- Thay vì lồng các inline view, có thể dùng common table expression (CTE) để tăng khả năng đọc và tổ chức mã tốt hơn
-- dùng inline view:
SELECT
vhs.movie,
vhs.vhs_revenue,
cs.cinema_revenue
FROM
(SELECT
movie_id,
SUM(ticket_sales) AS cinema_revenue
FROM tickets
GROUP BY movie_id) AS cs
INNER JOIN
(SELECT
movie,
movie_id,
SUM(revenue) AS vhs_revenue
FROM blockbuster
GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;
-- dùng CTE:
WITH cinema_sales AS (
SELECT
movie_id,
SUM(ticket_sales) AS cinema_revenue
FROM tickets
GROUP BY movie_id
),
vhs_sales AS (
SELECT
movie,
movie_id,
SUM(revenue) AS vhs_revenue
FROM blockbuster
GROUP BY movie, movie_id
)
SELECT
vhs.movie,
vhs.vhs_revenue,
cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;
Tính năng hữu ích
Dùng toán tử :: để chuyển đổi kiểu dữ liệu
- Trong một số RDBMS, có thể dùng toán tử
:: để chuyển giá trị sang kiểu dữ liệu khác
SELECT CAST('5' AS INTEGER); -- dùng hàm CAST
SELECT '5'::INTEGER; -- dùng cú pháp ::
Tận dụng anti join
- Anti join rất hữu ích khi cần trả về các hàng chỉ tồn tại ở một bảng
- Cũng có thể dùng subquery, nhưng nhìn chung anti join thường nhanh hơn
-- anti join:
SELECT
video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;
-- subquery:
SELECT
*
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);
-- correlated subquery:
SELECT
*
FROM video_content
WHERE NOT EXISTS (
SELECT 1
FROM archive a
WHERE a.series_id = vc.series_id
);
-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;
Dùng QUALIFY để lọc hàm cửa sổ
- Có thể dùng
QUALIFY để lọc kết quả của hàm cửa sổ
- Hữu ích để giảm số dòng mã
-- dùng QUALIFY:
SELECT
product,
market,
SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;
-- không dùng QUALIFY:
SELECT
product,
market,
market_revenue
FROM (
SELECT
product,
market,
SUM(revenue) AS market_revenue,
DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
FROM sales
GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;
Có thể dùng GROUP BY theo vị trí cột
- Có thể dùng vị trí cột thay cho tên cột để
GROUP BY hoặc ORDER BY
- Hữu ích cho truy vấn tạm thời, nhưng trong mã production thì luôn nên tham chiếu bằng tên cột
SELECT
dept_no,
SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no là cột đầu tiên trong mệnh đề SELECT
ORDER BY 2 DESC;
Những cạm bẫy cần tránh
Cẩn thận khi dùng NOT IN với giá trị NULL
NOT IN sẽ không hoạt động khi có giá trị NULL- Thay vào đó nên dùng
NOT EXISTS
INSERT INTO departments (id)
VALUES (1), (2), (NULL);
-- không hoạt động vì giá trị NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);
-- cách khắc phục
SELECT *
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.id = e.department_id
);
Đổi tên trường tính toán để tránh mơ hồ
- Nếu đổi tên một trường tính toán thành tên cột đã có sẵn, có thể phát sinh hành vi không mong muốn
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);
-- hàm cửa sổ xếp sản phẩm 'Robot' ở hạng 1
SELECT
product,
CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
RANK() OVER (ORDER BY revenue DESC)
FROM products;
Chỉ rõ mỗi cột thuộc về bảng nào
- Trong truy vấn phức tạp, việc chỉ rõ mỗi cột thuộc về bảng nào sẽ giúp dễ truy vết vấn đề hơn
SELECT
vc.video_id,
vc.series_name,
metadata.season,
metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;
Hiểu thứ tự thực thi
- Lời khuyên quan trọng nhất cho người học SQL là phải hiểu thứ tự thực thi
- Khi hiểu thứ tự thực thi, cách bạn viết truy vấn sẽ thay đổi hoàn toàn
Thêm chú thích vào mã
- Khi viết mã, nên thêm chú thích giải thích lý do
- Đồng nghiệp và chính bạn trong tương lai sẽ biết ơn điều đó
SELECT
video_content.*
FROM video_content
LEFT JOIN archive -- CMS mới không thể xử lý định dạng video lưu trữ
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;
Đọc toàn bộ tài liệu
- Đọc kỹ toàn bộ tài liệu sẽ giúp tránh những vấn đề ngoài dự kiến
- Chỉ mất vài phút để đọc tài liệu, và điều đó có thể giúp giải quyết các vấn đề bất ngờ
-- nếu đọc tài liệu kỹ hơn thì đã có thể xử lý vấn đề NULL
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);
-- có thể dùng hàm GREATEST_IGNORE_NULLS
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);
Tổng kết của GN⁺
- Bài viết này cung cấp nhiều mẹo và thủ thuật khác nhau để viết SQL hiệu quả hơn và dễ đọc hơn
- Có nhiều thông tin hữu ích cho cả người mới học SQL lẫn các nhà phân tích dữ liệu đã có kinh nghiệm
- Đặc biệt, việc dùng CTE khi viết truy vấn phức tạp, tận dụng anti join và dùng
QUALIFY sẽ rất hữu ích trong công việc thực tế
- Điều quan trọng là hình thành thói quen hiểu thứ tự thực thi của SQL, thêm chú thích vào mã và đọc tài liệu cẩn thận
- Các công cụ khác có tính năng tương tự gồm PostgreSQL, MySQL, Oracle
2 bình luận
Dấu phẩy đặt ở đầu trong bài viết này lại đều được viết thành dấu phẩy đặt ở cuối. Trong bản gốc, chúng được nhập theo kiểu đặt ở đầu.
Ý kiến trên Hacker News
Sự cần thiết phải hiểu rõ máy chủ DB và thường xuyên kiểm tra kế hoạch truy vấn
EXISTSnhanh hơnINNOT EXISTSvàEXCEPThoạt động khác nhau khi xử lý giá trịNULLUNION ALLcó thể nhanh hơnORJOINMẹo xử lý stored procedure phức tạp
Ý kiến về tính dễ đọc của mã
Đề xuất dùng cú pháp FROM-first và piping của SQL
Mẹo liên quan đến Anti Join
EXISTS, có lợi khi kiểm tra sự tồn tại của hàng trong subquery dựa trên điều kiệnƯu điểm của việc dùng dấu phẩy ở đầu dòng trong câu lệnh
SELECTTrong MSSQL, khi dùng chú thích nên dùng
/* */thay vì--Khuyến nghị sử dụng window function
Tranh luận về việc dùng
1=1trong mệnh đềWHEREGiới thiệu AI2sql