Job Seekers - Lesson 2
Xử lý, phân
tích dữ liệu bằng SQL và một số best practices
Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com
06/2023
Truy cập dữ liệu sử dụng để thực hành cho lesson này theo 1 trong các cách sau:
Download và cài đặt mysql (google hướng dẫn trên mạng, nên cài đặt thêm MySQL Workbench để viết lệnh và sử dụng MySQL). Bạn vẫn hoàn toàn có thể sử dụng 1 hệ quản trị cơ sở dữ liệu khác (VD: SQL Server, PostgreSQL, …) tuy nhiên có thể sẽ có 1 vài điểm khác biệt nho nhỏ (nhưng không ảnh hưởng trọng yếu tới kiến thức và thực hành của bài học này).
Sử dụng Google Colab notebook sau đây để truy vấn dữ liệu mà không cần cài đặt gì trên máy tính cá nhân: https://colab.research.google.com/drive/1nQY2YKfMAuzcfciKqi5VfjjtlB3jU30h?usp=sharing
Truy cập website http://sqlfiddle.com/ , paste code tạo bảng dữ liệu vào rồi bấm “Build Schema”, sau đó viết SQL query để thực hành theo bài tập mà không cần cài đặt gì
1 Nhóm dữ liệu và tính toán
Khi xử lý và phân tích dữ liệu thì ta rất thường xuyên phải nhóm dữ liệu và thực hiện các tính toán, sử dụng mệnh đề GROUP BY.
Để viết query sử dụng GROUP BY được chính xác thì ta cần nắm vững Order of execution (thứ tự thực hiện các lệnh) trong SQL query. Ta có thể ghi nhớ order of execution đơn giản như sau:
1.1 Order of Execution
FROM (và JOIN) truy cập dữ liệu từ các bảng được nêu trong query
WHERE lọc dữ liệu
GROUP BY nhóm dữ liệu lại theo 1 hoặc nhiều thuộc tính nhất định
Aggregate functions thực hiện các tính toán (VD: SUM, COUNT, …) sau khi dữ liệu đã được nhóm lại bởi GROUP BY
HAVING lọc dữ liệu sau khi đã được nhóm lại bởi GROUP BY và các Aggregate functions đã được thực hiện.
SELECT lấy ra các cột cần hiển thị
DISTINCT loại bỏ các bản ghi trùng lặp
UNION gộp dữ liệu lại thành 1 tập dữ liệu duy nhất
ORDER BY sắp xếp dữ liệu trả ra từ query
LIMIT/FETCH/TOP (tuỳ vào SQL dialect mà bạn sử dụng mà tên lệnh này sẽ khác nhau) hạn chế số dòng trả ra từ kết quả
Bên cạnh các vấn đề hay gặp về sai order of execution, sai syntax, thì có 1 số điểm ta cần lưu ý như sau:
WHERE và HAVING: Điều kiện lọc ở mệnh đề WHERE sẽ được thực hiện trước điều kiện lọc ở mệnh đề HAVING. Vì vậy tùy vào yêu cầu của đề bài mà ta sẽ để điều kiện lọc vào WHERE hay HAVING. Với những trường hợp điều kiện lọc để ở mệnh đề WHERE hay HAVING đều được thì hãy ưu tiên sử dụng WHERE do nó sẽ làm giảm số lượng bản ghi cần phải GROUP BY, từ đó làm tăng tốc độ xử lý của query
Ví dụ: 2 query dưới đây đều trả ra kết quả giống nhau, nhưng query sử dụng WHERE sẽ có tốc độ nhanh hơn, đặc biệt là khi bảng có lượng dữ liệu lớn
--để điều kiện lọc ở mệnh đề WHERE SELECT ContractDate, SUM(Amount) AS TotalAmount FROM contract WHERE ContractDate >= '2023-01-01' GROUP BY ContractDate --để điều kiện lọc ở mệnh đề HAVING SELECT ContractDate, SUM(Amount) AS TotalAmount FROM contract GROUP BY ContractDate HAVING ContractDate >= '2023-01-01'Nhóm dữ liệu theo cột (hoặc tập hợp các cột) mà không phải là duy nhất. Điều này sẽ khiến kết quả tính toán bị sai. Xem ví dụ dưới đây:
Ta có bảng dữ liệu inventory thể hiện số lượng hàng tồn kho của từng sản phẩm tại các cửa hàng của 1 doanh nghiệp như sau
StoreID InventoryID InventoryName Number 1 5 Máy xay sinh tố 421 1 6 Lò vi sóng Panasonic P244 177 1 7 Máy xay sinh tố 280 2 14 Máy đánh trứng HueWei H121 92 3 14 Máy đánh trứng HueWei H121 141 3 8 Máy xay sinh tố Samsung S43 322 Ta được yêu cầu xuất thông tin số lượng hàng tồn kho trên toàn hệ thống theo từng sản phẩm.
Nếu viết query như sau sẽ trả ra kết quả sai. Ta thấy số lượng của InventoryID 5 và 7 đã bị gộp vào nhau do ta GROUP BY InventoryName thay vì GROUP BY InventoryID. InventoryID 5 và 7 vì lí do nào đó mà có InventoryName giống nhau (có thể do nhập liệu thiếu) nên khi ta GROUP BY InventoryName sẽ cho kết quả sai.
InventoryName Number Máy xay sinh tố 701 Lò vi sóng Panasonic P244 177 Máy đánh trứng HueWei H121 233 Máy xay sinh tố Samsung S43 322 Query đúng phải như sau
SELECT InventoryID, InventoryName SUM(Number) AS TotalNumber FROM inventory GROUP BY InventoryID, InventoryNameInventoryID InventoryName Number 5 Máy xay sinh tố 421 6 Lò vi sóng Panasonic P244 177 7 Máy xay sinh tố 280 14 Máy đánh trứng HueWei H121 233 8 Máy xay sinh tố Samsung S43 322 Tính toán sai do có các giá trị NULL
Ví dụ 1:
Ta có bảng dữ liệu orders_tmp như sau:
DROP TABLE IF EXISTS orders_tmp; CREATE TEMPORARY TABLE orders_tmp ( OrderID int, ProductID int, TotalAmount int ); INSERT INTO orders_tmp VALUES (1, 22, 150), (1, 23, NULL), (2, 23, 50), (2, 35, 180), (3, 22, 400) ;OrderID
ProductID
TotalAmount
1
22
150
1
23
NULL
2
23
50
2
35
180
3
22
400
Ta được yêu cầu trích xuất ra dữ liệu tổng giá trị thanh toán trung bình (Average Total Amount) của từng sản phẩm trên mỗi đơn hàng
SELECT ProductID, AVG(TotalAmount) AS AvgWithoutNULL, (CAST(SUM(TotalAmount) AS FLOAT) / COUNT(DISTINCT OrderID)) AS AvgWithNULL FROM orders_tmp GROUP BY ProductID;ProductID
AvgWithoutNULL
AvgWithNULL
22
275.0000
275
23
50.0000
25
35
180.0000
180
Ta có thể thấy trong trường hợp này cách tính giá trị trung bình sử dụng hàm AVG() trả ra kết quả khác so với cách tính sử dụng hàm SUM() và COUNT(). Cụ thể khác biệt nằm ở ProductID = 23. Hàm AVG() trả ra kết quả giá trị thanh toán trung bình cho ProductID = 23 là 50 do hàm AVG() tự động loại bỏ các giá trị NULL ở cột TotalAmount trong quá trình tính toán. Còn cách tính sử dụng SUM() và COUNT() thì tuy SUM() cũng bỏ qua các giá trị NULL ở cột TotalAmount nhưng hàm COUNT() thì không, nên kết quả trả ra khác với kết quả từ hàm AVG().
Ví dụ 2:
Ta có 2 bảng customers_tmp và contract_tmp như sau
DROP TABLE IF EXISTS customers_tmp; DROP TABLE IF EXISTS contracts_tmp; CREATE TEMPORARY TABLE customers_tmp ( CustomerID int, CustomerName varchar(50) ); CREATE TEMPORARY TABLE contracts_tmp ( CustomerID int, ContractID int, Amount int ); INSERT INTO customers_tmp VALUES (1, 'Nguyen Van A'), (2, 'Nguyen Van B'), (3, 'Le Thi C'), (4, 'Hoang Thi D') ; INSERT INTO contracts_tmp VALUES (1, 488, 1230000), (2, 490, 2500000), (2, 491, 950000), (4, 520, 1100000) ;Bảng customer_tmp:
CustomerID
CustomerName
1
Nguyen Van A
2
Nguyen Van B
3
Le Thi C
4
Hoang Thi D
Bảng contracts_tmp:
CustomerID
ContractID
Amount
1
488
1230000
2
490
2500000
2
491
950000
4
520
1100000
Ta được yêu cầu xuất dữ liệu ID và tên của tất cả các khách hàng, kèm theo số lượng hợp đồng của họ.
Có thể nhiều bạn sẽ viết query như sau:
SELECT a.CustomerID, a.CustomerName, COUNT(*) AS NoContracts FROM customers_tmp a LEFT JOIN contracts_tmp b ON a.CustomerID = b.CustomerID GROUP BY a.CustomerID, a.CustomerName;Kết quả trả ra như sau:
CustomerID
CustomerName
NoContracts
1
Nguyen Van A
1
2
Nguyen Van B
2
3
Le Thi C
1
4
Hoang Thi D
1
Kết quả này sai vì Le Thi C (CustomerID = 3) không có hợp đồng nào cả, nhưng kết quả trả ra lại là có 1 hợp đồng. Điều này là do COUNT(*) đếm tất cả các dòng, và lệnh LEFT JOIN thì giữ lại tất cả các dòng ở bảng bên trái (bảng customers_tmp) kể cả những dòng không join được với bảng bên phải, nên kết quả trả ra từ COUNT(*) cho Le Thi C là 1.
Để thực hiện đúng được yêu cầu của đề bài, ta phải viết query như sau (bạn hãy thử tự viết query trước khi xem đáp án):
SELECT a.CustomerID, a.CustomerName, COUNT(DISTINCT b.ContractID) AS NoContracts --COUNT(DISTINCT ) chỉ đếm các giá trị khác NULL, nếu tất cả giá trị đều NULL thì nó sẽ trả về kết quả = 0 FROM customers_tmp a LEFT JOIN contracts_tmp b ON a.CustomerID = b.CustomerID GROUP BY a.CustomerID, a.CustomerName;CustomerID
CustomerName
NoContracts
1
Nguyen Van A
1
2
Nguyen Van B
2
3
Le Thi C
0
4
Hoang Thi D
1
2 Làm việc với dữ liệu thời gian (date, datetime, timestamp)
Dữ liệu thời gian là những kiểu dữ liệu rất quan trọng mà ta thường xuyên gặp trong cơ sở dữ liệu quan hệ, nhưng cũng đồng thời là những kiểu dữ liệu rất hay gây phát sinh lỗi trong khi truy vấn và xử lý dữ liệu.
Có 1 số lí do chính cho việc hay xảy ra lỗi khi truy vấn và xử lý những kiểu dữ liệu này:
Có nhiều kiểu định dạng ngày tháng khác nhau trên thế giới (VD: ngày 1 tháng 8 năm 2023 có thể được thể hiện dưới dạng 1/8/2023 hoặc 8/1/2023 hoặc 2023-08-01, …) dẫn đến việc dễ nhầm lẫn khi sử dụng
Dữ liệu từ các nguồn khác nhau được ghi nhận ở những múi giờ khác nhau, nên nếu không để ý sẽ dẫn đến nhầm lẫn khi sử dụng
Yêu cầu phân tích dữ liệu đòi hỏi phải biến đổi ngày tháng để thể hiện nhiều mốc thời gian khác nhau như ngày đầu tháng, cuối tháng, quý, tuần, năm tài chính, ngày nghỉ lễ, … nên có thể phát sinh lỗi nếu ta sử dụng các hàm biến đổi thời gian không chính xác
2.1 Định dạng ngày tháng:
Khi làm việc với các bộ dữ liệu có định dạng ngày tháng khác nhau, ta nên chuyển chúng về cùng 1 định dạng để đảm bảo tính chính xác khi sử dụng dữ liệu.
Best practice là nên lưu ngày tháng theo tiêu chuẩn ISO standard 8601 for date format. Theo tiêu chuẩn này thì ngày tháng sẽ được lưu dưới định dạng YYYY-MM-DD hoặc YYYYMMDD và nếu có cả thời gian sẽ được lưu dưới định dạng yyyy-mm-ddThh:mm:ss. Lí do vì sao ta nên lưu ngày tháng dưới định dạng YYYY-MM-DD là vì các định dạng khác như MM/DD/YYYY và DD/MM/YYYY sẽ khiến người dùng dữ liệu có thể nhầm lẫn không rõ đâu là ngày và đâu là tháng, nhất là với những trường hợp cả ngày và tháng đều < 12, ví dụ như: 02/03/2023
Ví dụ biến đổi dữ liệu ngày 02 tháng 03 năm 2023 ở các định dạng ngày tháng khác nhau về dạng ‘YYYY-MM-DD’:
SELECT
STR_TO_DATE('02/03/2023', '%d/%m/%Y') AS Convert1,
STR_TO_DATE('03/02/2023', '%m/%d/%Y') AS Convert2,
STR_TO_DATE('03/02/2023 12:28:30', '%m/%d/%Y') AS Convert3| Convert1 | Convert2 | Convert3 |
|---|---|---|
| 2023-03-02 | 2023-03-02 | 2023-03-02 |
2.2 Time zones:
Khi phải lấy dữ liệu từ những hệ thống có server đặt ở các quốc gia khác nhau, đôi khi các bạn sẽ gặp tình huống mỗi dataset lưu dữ liệu theo 1 time zone khác nhau do setting của Server (VD: bộ dữ liệu ở Server Việt Nam lưu theo time zone UTC+7, bộ dữ liệu ở Server Australia lưu theo time zone UTC+10).
Vì vậy khi sử dụng, lưu trữ, biến đổi, phân tích dữ liệu, ta nên lưu dữ liệu về cùng 1 time zone, và best practice là lưu về UTC.
Xem ví dụ dưới đây, lưu ý hàm convert_tz dùng để chuyển ngày giờ từ 1 time zone sang 1 time zone khác:
select
current_timestamp(), --show current date and time in server's time zone
now(), --show current date and time in server's time zone
utc_timestamp(), --show current date and time in UTC
convert_tz(now(), '+07:00', '+00:00'), --convert current date and time from UTC+7 to UTC
current_date(), --show current date in server's time zone
utc_date();--show current date in UTC| current_timestamp.. | now.. | utc_timestamp.. | convert_tz.now……07.00…..00.00.. | current_date.. | utc_date.. |
|---|---|---|---|---|---|
| 2023-08-03 23:34:10 | 2023-08-03 23:34:10 | 2023-08-03 16:34:10 | 2023-08-03 16:34:10 | 2023-08-03 | 2023-08-03 |
2.3 Xác định đúng kiểu dữ liệu giữa date, datetime, timestamp
Khi lọc dữ liệu thời gian, ta cần lưu ý kiểm tra loại dữ liệu của cột trong điều kiện lọc là date hay datetime, timestamp, nếu không sẽ có thể trả ra kết quả không chính xác.
Ta có bảng dữ liệu date_table như sau:
-- create a temporary date table
DROP TABLE IF EXISTS date_table;
CREATE TEMPORARY TABLE date_table (
Id INT,
Date DATETIME(3)
);
-- insert values into temporary date table
INSERT INTO date_table
VALUES
(1, '2023-03-11 15:28:40.224'),
(2, '2023-03-11 23:59:59.998'),
(3, '2023-03-12 00:00:00.000'),
(4, '2023-03-12 21:48:25.442'),
(5, '2023-03-13 00:00:00.000'),
(6, '2023-03-13 08:09:21.668');| Id | Date |
|---|---|
| 1 | 2023-03-11 15:28:40.224 |
| 2 | 2023-03-11 23:59:59.998 |
| 3 | 2023-03-12 00:00:00.000 |
| 4 | 2023-03-12 21:48:25.442 |
| 5 | 2023-03-13 00:00:00.000 |
| 6 | 2023-03-13 08:09:21.668 |
Yêu cầu: viết query trả ra dữ liệu ở bảng date_table của ngày 12 tháng 3 năm 2023
Nếu bạn viết query như dưới đây, kết quả trả ra sẽ không chính xác
| Id | Date |
|---|---|
| 3 | 2023-03-12 00:00:00.000 |
Lí do là vì trường Date ở bảng date_table là dạng datetime, nên khi ta viết điều kiện lọc WHERE Date = ‘2023-03-12’ thì cơ sở dữ liệu hiểu điều kiện này tương tự với điều kiện WHERE Date = ‘2023-03-12 00:00:00.000’ (lúc nửa đêm).
Query đúng để đáp ứng yêu cầu của đề bài là như sau (hãy thử tự viết trước khi xem đáp án):
SELECT *
FROM date_table
WHERE Date >= '2023-03-12 00:00:00.000'
AND Date < '2023-03-13 00:00:00.000'
;Ta cũng có thể viết query như sau, nhưng cách viết query này sẽ không tận dụng được index ở cột Date (nếu có). Xem thêm ở phần [Sử dụng Index chính xác] trong bài này
| Id | Date |
|---|---|
| 3 | 2023-03-12 00:00:00.000 |
| 4 | 2023-03-12 21:48:25.442 |
2.4 Hiểu rõ cơ chế làm tròn của các hàm tính toán về thời gian
Để tính toán khoảng cách về thời gian được chính xác ta nên sử dụng các hàm tính toán có sẵn của cơ sở dữ liệu. Ví dụ dưới đây tính khoảng cách thời gian theo giây, phút, giờ, ngày giữa 2 mốc thời gian trong MySQL
SET @a = '2015-12-31 23:59:02.000',
@b = '2016-01-01 00:00:01.000';
SELECT
TIMESTAMPDIFF(SECOND, @a, @b) AS DELTA_SECOND,
TIMESTAMPDIFF(MINUTE, @a, @b) AS DELTA_MINUTE,
TIMESTAMPDIFF(HOUR, @a, @b) AS DELTA_HOUR,
TIMESTAMPDIFF(DAY, @a, @b) AS DELTA_DAY;| DELTA_SECOND | DELTA_MINUTE | DELTA_HOUR | DELTA_DAY |
|---|---|---|---|
| 59 | 0 | 0 | 0 |
Ta thấy rằng khoảng cách về “second” (giây) được tính toán đúng, nhưng khoảng cách về phút, giờ không chính xác. Khoảng cách về ngày có thể chấp nhận được (với logic là cứ cách từ 24h thì mới tính là cách 1 ngày).
Hãy thử tự viết query để trả ra kết quả chính xác, sau đó xem đáp án dưới đây
SET @a = '2015-12-31 23:59:02.000',
@b = '2016-01-01 00:00:01.000';
SELECT
TIMESTAMPDIFF(SECOND, @a, @b) AS DELTA_SECOND,
TIMESTAMPDIFF(SECOND, @a, @b) / 60 AS DELTA_MINUTE,
TIMESTAMPDIFF(SECOND, @a, @b) / 3600 AS DELTA_HOUR,
TIMESTAMPDIFF(DAY, @a, @b) AS DELTA_DAY;Kết quả đúng cần trả ra sẽ như sau:
| DELTA_SECOND | DELTA_MINUTE | DELTA_HOUR | DELTA_DAY |
|---|---|---|---|
| 59 | 0.9833 | 0.0164 | 0 |
2.5 Tính ngày đầu và cuối tháng
Ngày đầu và cuối tháng là 2 mốc thời gian quan trọng nên ta sẽ thường xuyên gặp phải trong công việc thực tế. Thông thường, các Data Engineers hoặc Database Admins sẽ tạo 1 bảng calendar để lưu trữ ngày tháng giúp các Analysts thuận tiện hơn trong việc xử lý dữ liệu thời gian. Tuy nhiên, nếu không có 1 bảng như vậy thì ta cũng cần biết cách xử lý để tính ra ngày đầu và cuối tháng của 1 date bất kỳ.
Ví dụ ta có 1 bảng calendar đơn giản như sau. Hãy thử viết lệnh SQL để biến đổi từ 1 date bất kỳ ra được kết quả như cột MonthStartDate và MonthEndDate (không cần phải viết code để tạo ra cả bảng calendar mà chỉ cần tìm được ngày đầu và cuối tháng từ 1 date bất kỳ thôi). Sau đó, mở đoạn code dưới đây để xem cách các cột này được tạo ra.
-- create calendar table
DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (
Date DATE NOT NULL,
DateString VARCHAR(8) NOT NULL,
MonthStartDate DATE NOT NULL,
MonthEndDate DATE NOT NULL,
Year INTEGER NOT NULL,
Month INTEGER NOT NULL, -- 1 to 12
Day INTEGER NOT NULL, -- 1 to 31
Quarter INTEGER NOT NULL, -- 1 to 4
Week INTEGER NOT NULL, -- 1 to 52/53
DayName VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
MonthName VARCHAR(9) NOT NULL, -- 'January', 'February'...
WeekendFlag CHAR(1) DEFAULT 'F'
);
-- insert values into calendar table
DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
DECLARE currentdate DATE;
SET currentdate = startdate;
WHILE currentdate <= stopdate DO
INSERT INTO calendar VALUES (
currentdate,
DATE_FORMAT(currentdate, '%Y%m%d'),
DATE_ADD(currentdate, INTERVAL -DAY(currentdate)+1 DAY), -- calculate MonthStartDate
LAST_DAY(currentdate), -- calculate MonthEndDate
YEAR(currentdate),
MONTH(currentdate),
DAY(currentdate),
QUARTER(currentdate),
WEEKOFYEAR(currentdate),
DATE_FORMAT(currentdate,'%W'),
DATE_FORMAT(currentdate,'%M'),
CASE
WHEN DAYOFWEEK(currentdate) in (1, 7) THEN 'T'
ELSE 'F'
END);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;
CALL fill_date_dimension('2022-01-01','2023-12-31');
-- show calendar table
select * from calendar;3 Lệnh CASE
Lệnh CASE kiểm tra các điều kiện và trả ra giá trị tương ứng với điều kiện đầu tiên được thỏa mãn, tương tự như cấu trúc if-then-else trong các ngôn ngữ lập trình hoặc MS Excel
Lưu ý 1: Các điều kiện được kiểm tra lần lượt theo thứ tự, và nếu có nhiều hơn 1 điều kiện thỏa mãn thì lệnh sẽ trả ra kết quả của điều kiện thỏa mãn đầu tiên.
Ví dụ ta có bảng case_table như sau:
DROP TABLE IF EXISTS case_table;
CREATE TEMPORARY TABLE case_table (
ContractID INT,
EffectiveDate DATE
);
INSERT INTO case_table
VALUES
(1, '2023-01-15'),
(2, '2022-12-24'),
(3, '2023-02-26'),
(4, '2021-08-05');| ContractID | EffectiveDate |
|---|---|
| 1 | 2023-01-15 |
| 2 | 2022-12-24 |
| 3 | 2023-02-26 |
| 4 | 2021-08-05 |
Ta viết query để tạo thêm 1 cột đánh dấu các hợp đồng, chia thành các nhóm theo năm mà hợp đồng bắt đầu có hiệu lực
SELECT
ContractID,
EffectiveDate,
CASE
WHEN EffectiveDate >= CAST('2022-01-01' AS DATE) THEN 'Contract opened since 2022'
WHEN EffectiveDate >= CAST('2023-01-01' AS DATE) THEN 'Contract opened since 2023'
ELSE 'Contract opened before 2022'
END AS ContractGroup
FROM case_table| ContractID | EffectiveDate | ContractGroup |
|---|---|---|
| 1 | 2023-01-15 | Contract opened since 2022 |
| 2 | 2022-12-24 | Contract opened since 2022 |
| 3 | 2023-02-26 | Contract opened since 2022 |
| 4 | 2021-08-05 | Contract opened before 2022 |
Ta thấy rằng ContractID 1 và 3 đều thỏa mãn cả 2 điều kiện WHEN đầu tiên (kiểm tra EffectiveDate >= ‘2022-01-01’ và kiểm tra EffectiveDate >= ‘2023-01-01’) tuy nhiên chỉ có kết quả của điều kiện WHEN đầu tiên được trả ra, và cả 2 ContractID này đều được đánh dấu là thuộc nhóm ‘Contract opened since 2022’.
Trong thực tế, ta nên viết các điều kiện WHEN sao cho chúng ‘mutually exclusive’ và ‘collectively exhaustive’ (MECE).
Hai điều kiện được gọi là mutually exclusive khi chúng không thể xảy ra đồng thời (VD: x > 1 và x <= 1 là 2 điều kiện mutually exclusive)
Khi các điều kiện của ta bao gồm được hết tất cả các trường hợp có thể xảy ra, thì ta gọi chúng là collectively exhaustive
Trong những trường hợp mà nhiều điều kiện WHEN có thể xảy ra đồng thời, thì ta cần xác định rõ kết quả từ điều kiện WHEN nào sẽ được ưu tiên trả ra
Lưu ý 2: Tuy kết quả trả ra luôn là giá trị tương ứng với điều kiện đầu tiên được thỏa mãn, nhưng tùy vào cơ sở dữ liệu quan hệ mà ta đang làm việc với (MySQL hay PostgreSQL hay SQL Server, …) mà các điều kiện sau điều kiện đầu tiên được thỏa mãn có được kiểm tra hay không.
Ví dụ ta viết query như dưới đây:
SELECT
ContractID,
EffectiveDate,
CASE
WHEN EffectiveDate >= CAST('2021-01-01' AS DATE) THEN 'Contract opened since 2021'
WHEN EffectiveDate = 'this is a string' THEN 'this is a string'
ELSE 'Contract opened before 2021'
END AS ContractGroup
FROM case_table;Khi chạy query này trong MySQL sẽ bị báo lỗi Incorrect DATE value: ‘this is a string’ do MySQL kiểm tra điều kiện WHEN EffectiveDate = ‘this is a string’ THEN ‘this is a string’ và không thể so sánh kiểu dữ liệu DATE với kiểu dữ liệu chữ (STRING).
Tuy nhiên khi chạy query này trong Microsoft SQL Server thì lại không bị báo lỗi và kết quả vẫn trả ra như dưới đây. Lí do là vì khi Microsoft SQL Server kiểm tra điều kiện cho từng dòng dữ liệu thì tất cả các dòng dữ liệu ở bảng đều thỏa mãn điều kiện số 1, và SQL Server trả ra kết quả tương ứng, sau đó không kiểm tra đến điều kiện số 2 nữa, nên dù điều kiện số 2 lỗi nhưng query vẫn chạy bình thường. Điều này sẽ nguy hiểm nếu như ta không để ý và trong tương lai bảng này xuất hiện thêm dòng dữ liệu mà không thỏa mãn điều kiện số 1, lúc đó điều kiện số 2 sẽ bị kiểm tra và query sẽ lỗi.
| ContractID | EffectiveDate | ContractGroup |
|---|---|---|
| 1 | 2023-01-15 | Contract opened since 2021 |
| 2 | 2022-12-24 | Contract opened since 2021 |
| 3 | 2023-02-26 | Contract opened since 2021 |
| 4 | 2021-08-05 | Contract opened since 2021 |
4 JOIN các bảng
Trong thực tế công việc, ta thường xuyên phải liên kết (join) các bảng dữ liệu lại với nhau. Vì vậy, ta cần biết và thực hiện các best practice khi join các bảng dữ liệu. Ta lưu ý một số điểm quan trọng sau:
4.1 Dùng mệnh đề JOIN … ON …
Dùng mệnh đề JOIN để join các bảng dữ liệu và ON để nêu ra điều kiện join. Việc này giúp query của ta dễ hiểu hơn và đảm bảo ta kiểm soát được chính xác cách các bảng được join với nhau và điều kiện join như thế nào. Nếu dùng mệnh đề WHERE để join các bảng dữ liệu thì sẽ khó có thể phân biệt được điều kiện join và điều kiện lọc dữ liệu sau khi join.
Ví dụ: 2 query dưới đây trả ra kết quả giống hệt nhau, nhưng query 1 điều kiện join và điều kiện lọc sau khi join được phân tích rõ ràng hơn
-- query 1
SELECT
cust.CustomerID,
cont.ContractID,
cont.Amount
FROM customers_tmp cust
INNER JOIN contracts_tmp cont
ON cust.CustomerID = cont.CustomerID
WHERE cust.CustomerID > 1;-- query 2: bad practice
SELECT
cust.CustomerID,
cont.ContractID,
cont.Amount
FROM customers_tmp cust, contracts_tmp cont
WHERE
cust.CustomerID = cont.CustomerID
AND cust.CustomerID > 1| CustomerID | ContractID | Amount |
|---|---|---|
| 2 | 490 | 2500000 |
| 2 | 491 | 950000 |
| 4 | 520 | 1100000 |
Ngoài ra, join các bảng bằng câu lệnh WHERE thường chỉ dễ thực hiện được với inner join (lưu ý rằng cách join này vẫn là bad practice) và rất khó thực hiện được với outer join (left outer join, right outer join, full outer join).
4.2 Đảm bảo thứ thự join của các bảng
Ta tránh viết code join mà đưa tất cả mệnh đề ON về cuối query như dưới đây. Cách viết như vậy khiến ta dễ nhầm lẫn về thứ tự join của các bảng vì các bảng sẽ không được join với nhau theo thứ tự xuất hiện trong query (ta tạm không bàn kĩ hơn về bad practice này để tránh phức tạp hoá vấn đề)
-- bad practice:
SELECT
table1.col_name,
table2.col_name,
table3.col_name
FROM table1
LEFT JOIN table2
INNER JOIN table3
ON table3.Id = table2.Id
ON table2.Id = table1.IdKhi cần đảm bảo thứ tự join các bảng đúng như mình muốn, ta nên dùng ngoặc đơn hoặc CTE (xem thêm về dùng CTE ở phần Dùng CTE thay cho subquery để đơn giản hoá câu lệnh SQL)
Ví dụ: ta có bảng t1, t2, t3. Ta cần inner join bảng t2 và t3 với nhau trước, sau đó lấy bảng t1 left join với kết quả đó để kết quả cuối cùng của query thể hiện tất cả các bản ghi trong bảng t1.
Query 1 và 2 trả ra kết quả đúng vì đảm bảo thứ tự join các bảng đúng với yêu cầu đề bài.
Query 3 trả ra kết quả sai vì thứ tự join khác với yêu cầu đề bài.
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1(
Id INT
);
create table t2(
Id INT
);
create table t3(
Id INT
);
insert into t1
values
(1),
(2),
(3),
(4),
(5),
(6),
(7);
insert into t2
values
(3),
(4),
(5),
(6);
insert into t3
values
(1),
(2),
(3),
(4)
;
-- query 1: correct result
SELECT
t1.Id AS Id1,
t2.Id AS Id2,
t3.Id AS Id3
FROM t1
LEFT JOIN (
t2
INNER JOIN t3 ON t2.Id = t3.Id
) ON t1.Id = t2.Id
;
-- query 2: correct result
WITH cte AS (
SELECT
t2.Id AS Id2,
t3.Id AS Id3
FROM t2
INNER JOIN t3 on t2.Id = t3.Id
)
SELECT
t1.Id AS Id1,
cte.Id2,
cte.Id3
FROM t1
LEFT JOIN cte ON t1.Id = cte.Id2
;
-- query 3: wrong result
SELECT
t1.Id AS Id1,
t2.Id AS Id2,
t3.Id AS Id3
FROM t1
LEFT JOIN t2 ON t1.Id = t2.Id
INNER JOIN t3 ON t2.Id = t3.Id
;Kết quả đúng (trả ra từ query 1 và 2):
| Id1 | Id2 | Id3 |
|---|---|---|
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
Kết quả sai (trả ra từ query 3):
| Id1 | Id2 | Id3 |
|---|---|---|
| 3 | 3 | 3 |
| 4 | 4 | 4 |
Vì vậy, hãy luôn dùng mệnh đề JOIN … ON … đúng cách và kết hợp sử dụng ngoặc đơn hoặc CTE nếu cần để join các bảng trong cơ sở dữ liệu quan hệ.
4.3 Dùng alias cho tên bảng và gọi tên cột rõ ràng
Khi join các bảng dữ liệu, ta nên đặt alias cho từng bảng để giúp query trở nên dễ đọc hơn và gọn gàng hơn.
Khi cần tham chiếu tới 1 cột nào trong query thì ta cần nêu rõ cột đó đến từ bảng nào theo cấu trúc table_alias.column_name. Làm vậy để query rõ ràng hơn và tránh chỉ nêu tên cột vì có thể gây lỗi nếu có cột trùng tên giữa các bảng.
Ví dụ: ta nên viết query như dưới đây
5 Window functions
Để định nghĩa window functions một cách dễ hiểu và dễ nhớ, ta tư duy như sau:
Window trong tiếng Anh tức là cửa sổ, khi nhìn qua cửa sổ thì ta chỉ thấy 1 phần của không gian bên ngoài. Tương tự, đối với mỗi bảng dữ liệu, ta hiểu window là 1 tập hợp các dòng dữ liệu trong bảng.
Vậy window functions là những functions (hàm) trong SQL mà áp dụng lên các windows, mà mỗi window chính là 1 tập hợp các dòng dữ liệu trong bảng.
Có 3 loại window functions:
Aggregate window functions: SUM(), MIN(), MAX(), COUNT(), …
Value window functions: LAG(), LEAD(), FIRST_VALUE(), …
Ranking window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), …
Cú pháp của window function như sau:
function(expression | column) OVER(
[PARTITION BY expression | column optional]
[ORDER BY expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] optional]
)function(expression | column) là các hàm window function như SUM(), RANK(), …
Mệnh đề OVER() là từ khóa để giúp SQL engine hiểu rằng hàm đứng trước từ khóa này là 1 window function chứ không phải là 1 function bình thường.
Trong mệnh đề OVER() ta có thể đưa vào một số parameters (các parameter này là optional, tức là không có cũng được), bao gồm:
PARTITION BY: chia dữ liệu thành các partitions (hay chính là các window)
ORDER BY: sắp xếp dữ liệu trong mỗi window
Sau đây là một số trường hợp window functions thường xuyên sử dụng trong thực tế:
5.0.1 Ví dụ 1: đánh số dòng để tìm dòng mới nhất hoặc cũ nhất
Trong quá trình xử lý dữ liệu, đôi khi ta cần lấy ra từ bảng các dòng dữ liệu mới nhất của từng ID theo 1 tiêu chí nhất định, ta dùng hàm ROW_NUMBER()
Giả sử ta có bảng customer_phone như sau:
DROP TABLE IF EXISTS customer_phone;
CREATE TEMPORARY TABLE customer_phone(
CustomerID INT,
Phone VARCHAR(12),
Source VARCHAR(20),
UpdatedDate DATE
);
INSERT INTO customer_phone
VALUES
(1, '0913266654', 'CRM', '2021-07-12'),
(1, '0984331988', 'Mobile App Form', '2023-06-01'),
(2, '0943524647', 'CRM', '2019-12-24'),
(3, '0942221817', 'Mobile App Form', '2022-02-08'),
(3, '0942464764', 'Mobile App Form', '2023-06-29'),
(3, '0915298756', 'CRM', '2023-06-29');
SELECT * FROM customer_phone;| CustomerID | Phone | Source | UpdatedDate |
|---|---|---|---|
| 1 | 913266654 | CRM | 2021-07-12 |
| 1 | 984331988 | Mobile App Form | 2023-06-01 |
| 2 | 943524647 | CRM | 2019-12-24 |
| 3 | 942221817 | Mobile App Form | 2022-02-08 |
| 3 | 942464764 | Mobile App Form | 2023-06-29 |
| 3 | 915298756 | CRM | 2023-06-29 |
Yêu cầu: viết query lấy ra dữ liệu số điện thoại mới nhất của từng khách hàng dựa theo thời gian cập nhật trên hệ thống
Đáp án:
WITH phone_row_number AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY UpdatedDate DESC) AS Rn
FROM customer_phone
)
SELECT
*
FROM phone_row_number
WHERE Rn = 1;| CustomerID | Phone | Source | UpdatedDate | Rn |
|---|---|---|---|---|
| 1 | 984331988 | Mobile App Form | 2023-06-01 | 1 |
| 2 | 943524647 | CRM | 2019-12-24 | 1 |
| 3 | 942464764 | Mobile App Form | 2023-06-29 | 1 |
Tuy nhiên, ta có thể thấy với cách viết query như trên, với khách hàng có CustomerID = 3, kết quả trả ra số điện thoại của khách hàng đó từ hệ thống ‘Mobile App Form’, trong khi số điện thoại từ hệ thống ‘CRM’ cũng có cùng ngày cập nhật dữ liệu là 29 tháng 6 năm 2023.
Nếu ta muốn với những trường hợp có cùng UpdateDate thì thứ tự ưu tiên lấy số điện thoại ra sẽ là ‘CRM’ -> ‘Mobile App Form’, thì sẽ phải viết query như thế nào?
Đáp án:
WITH source_order AS (
SELECT
*,
CASE
WHEN Source = 'CRM' THEN 1
WHEN Source = 'Mobile App Form' THEN 2
ELSE 3
END AS SourceOrder
FROM customer_phone
),
phone_row_number AS(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY UpdatedDate DESC, SourceOrder ASC) AS Rn
FROM source_order
)
SELECT
*
FROM phone_row_number
WHERE Rn = 1;| CustomerID | Phone | Source | UpdatedDate | SourceOrder | Rn |
|---|---|---|---|---|---|
| 1 | 984331988 | Mobile App Form | 2023-06-01 | 2 | 1 |
| 2 | 943524647 | CRM | 2019-12-24 | 1 | 1 |
| 3 | 915298756 | CRM | 2023-06-29 | 1 | 1 |
5.0.2 Ví dụ 2: tìm giá trị của dòng liền sau hoặc liền trước
Trong một số trường hợp ta sẽ cần viết query để lấy ra giá trị liền sau (giá trị ở dòng bên dưới) hoặc giá trị liền trước (giá trị của dòng bên trên) của một bản ghi. Trong những trường hợp này, ta sử dụng các hàm LEAD() và LAG
Syntax đơn giản:
LAG(expression, N) over_clause
Trong đó:
expression: thường ta đưa tên cột vào đây, nhưng cũng có thể là 1 expression (như 1 cụm lệnh CASE…WHEN chẳng hạn)
N: số lượng dòng liền trước/liền sau
over_clause: mệnh đề OVER()
Ta có bảng google_analytics_pageview thể hiện các trang mà người dùng (user) đã xem trên một website:
DROP TABLE IF EXISTS google_analytics_pageviews;
CREATE TEMPORARY TABLE google_analytics_pageviews (
SessionID INT,
User VARCHAR(100),
Timestamp DATETIME,
PageURL VARCHAR(1000)
);
INSERT INTO google_analytics_pageviews
VALUES
(1, 'thang@mail.com', '2023-01-15 12:28:30', 'https://datatute.vn/'),
(1, 'thang@mail.com', '2023-01-15 12:28:40', 'https://datatute.vn/about/'),
(1, 'thang@mail.com', '2023-01-15 12:29:56', 'https://datatute.vn/portfolio/'),
(1, 'thang@mail.com', '2023-01-15 12:41:02', 'https://datatute.vn/portfolio/job-seekers-course/'),
(1, 'thang@mail.com', '2023-01-15 12:42:40', 'https://datatute.vn/about/'),
(1, 'thang@mail.com', '2023-01-15 12:44:04', 'https://datatute.vn/portfolio/job-seekers-course/'),
(2, 'huyen@gmail.com', '2023-01-15 12:33:08', 'https://datatute.vn/portfolio/'),
(2, 'huyen@gmail.com', '2023-01-15 12:33:15', 'https://datatute.vn/portfolio/interview-training/'),
(2, 'huyen@gmail.com', '2023-01-15 12:38:30', 'https://datatute.vn/portfolio/free-cv-review/'),
(3, 'thang@mail.com', '2023-01-16 09:05:31', 'https://datatute.vn/'),
(3, 'thang@mail.com', '2023-01-16 09:05:35', 'https://datatute.vn/blog/'),
(3, 'thang@mail.com', '2023-01-16 09:06:01', 'https://datatute.vn/sql-ve-viec-tim-kiem-thong-tin-chua-xay-ra/'),
(3, 'thang@mail.com', '2023-01-16 09:25:03', 'https://datatute.vn/contact/');
SELECT * FROM google_analytics_pageviews;| SessionID | User | Timestamp | PageURL |
|---|---|---|---|
| 1 | thang@mail.com | 2023-01-15 12:28:30 | https://datatute.vn/ |
| 1 | thang@mail.com | 2023-01-15 12:28:40 | https://datatute.vn/about/ |
| 1 | thang@mail.com | 2023-01-15 12:29:56 | https://datatute.vn/portfolio/ |
| 1 | thang@mail.com | 2023-01-15 12:41:02 | https://datatute.vn/portfolio/job-seekers-course/ |
| 1 | thang@mail.com | 2023-01-15 12:42:40 | https://datatute.vn/about/ |
| 1 | thang@mail.com | 2023-01-15 12:44:04 | https://datatute.vn/portfolio/job-seekers-course/ |
| 2 | huyen@gmail.com | 2023-01-15 12:33:08 | https://datatute.vn/portfolio/ |
| 2 | huyen@gmail.com | 2023-01-15 12:33:15 | https://datatute.vn/portfolio/interview-training/ |
| 2 | huyen@gmail.com | 2023-01-15 12:38:30 | https://datatute.vn/portfolio/free-cv-review/ |
| 3 | thang@mail.com | 2023-01-16 09:05:31 | https://datatute.vn/ |
| 3 | thang@mail.com | 2023-01-16 09:05:35 | https://datatute.vn/blog/ |
| 3 | thang@mail.com | 2023-01-16 09:06:01 | https://datatute.vn/sql-ve-viec-tim-kiem-thong-tin-chua-xay-ra/ |
| 3 | thang@mail.com | 2023-01-16 09:25:03 | https://datatute.vn/contact/ |
Ta có bảng google_analytics_sessions thể hiện các session mà người dùng đã thực hiện trên website. 1 session có thể hiểu là 1 lần người dùng truy cập vào website, từ lúc mở website cho đến khi tắt trình duyệt web đi. Trong 1 session thì người dùng có thể truy cập và xem nhiều trang của website.
DROP TABLE IF EXISTS google_analytics_sessions;
CREATE TABLE google_analytics_sessions(
SessionID INT,
SessionStart DATETIME,
SessionEND DATETIME
);
INSERT INTO google_analytics_sessions
VALUES
(1, '2023-01-15 12:28:27', '2023-01-15 12:45:23'),
(2, '2023-01-15 12:33:08', '2023-01-15 12:42:11'),
(3, '2023-01-16 09:05:29', '2023-01-16 09:28:51');
SELECT * FROM google_analytics_sessions;| SessionID | SessionStart | SessionEnd |
|---|---|---|
| 1 | 2023-01-15 12:28:27 | 2023-01-15 12:45:23 |
| 2 | 2023-01-15 12:33:08 | 2023-01-15 12:42:11 |
| 3 | 2023-01-16 09:05:29 | 2023-01-16 09:28:51 |
Yêu cầu: Tính thời gian (bằng số giây) của mỗi lần khách hàng xem 1 trang (page) trên website. Để cho bài toán đỡ phức tạp, ta giả sử khách hàng chỉ xem 1 trang tại 1 thời điểm, và xem xong trang này mới chuyển sang trang khác (tức là không mở nhiều trang cùng 1 lúc).
Hãy thử tự làm trước khi xem đáp án. Khi xem đáp án, lưu ý cách dùng hàm LEAD()
Đáp án:
-- overall logic:
-- step 1: left join google_analytics_pageviews and google_analytics_sessions to get SessionEnd column
-- step 2: Timestamp is the page start time. Use LEAD() function to get the next Timestamp
-- of each record in a Session, which is the page end time
-- step 3: last pageview in a session will have page end time = SessionEnd time
-- step 4: calculate time spent on each page by subtracting page start time and page end time
WITH pages AS (
SELECT
p.SessionID,
p.User,
p.PageURL,
p.Timestamp AS PageStartTime,
s.SessionEnd
FROM google_analytics_pageviews p
LEFT JOIN google_analytics_sessions s
ON p.SessionID = s.SessionID
),
pages_lead AS (
SELECT
*,
-- get the next Timestamp of each record in a Session, which is the page end time
LEAD(PageStartTime, 1) OVER(PARTITION BY SessionID ORDER BY PageStartTime ASC) AS PageEndTime
FROM pages
),
time_on_page AS (
SELECT
SessionID,
User,
PageURL,
PageStartTime,
CASE
WHEN PageEndTime IS NULL THEN SessionEnd -- last pageview in a session will have page end time = SessionEnd time
ELSE PageEndTime
END AS PageEndTime
FROM pages_lead
)
SELECT
*,
TIMESTAMPDIFF(SECOND, PageStartTime, PageEndTIme) AS TimeOnPage -- calculate time spent on each page
FROM time_on_page;| SessionID | User | PageURL | PageStartTime | PageEndTime | TimeOnPage |
|---|---|---|---|---|---|
| 1 | thang@mail.com | https://datatute.vn/ | 2023-01-15 12:28:30 | 2023-01-15 12:28:40 | 10 |
| 1 | thang@mail.com | https://datatute.vn/about/ | 2023-01-15 12:28:40 | 2023-01-15 12:29:56 | 76 |
| 1 | thang@mail.com | https://datatute.vn/portfolio/ | 2023-01-15 12:29:56 | 2023-01-15 12:41:02 | 666 |
| 1 | thang@mail.com | https://datatute.vn/portfolio/job-seekers-course/ | 2023-01-15 12:41:02 | 2023-01-15 12:42:40 | 98 |
| 1 | thang@mail.com | https://datatute.vn/about/ | 2023-01-15 12:42:40 | 2023-01-15 12:44:04 | 84 |
| 1 | thang@mail.com | https://datatute.vn/portfolio/job-seekers-course/ | 2023-01-15 12:44:04 | 2023-01-15 12:45:23 | 79 |
| 2 | huyen@gmail.com | https://datatute.vn/portfolio/ | 2023-01-15 12:33:08 | 2023-01-15 12:33:15 | 7 |
| 2 | huyen@gmail.com | https://datatute.vn/portfolio/interview-training/ | 2023-01-15 12:33:15 | 2023-01-15 12:38:30 | 315 |
| 2 | huyen@gmail.com | https://datatute.vn/portfolio/free-cv-review/ | 2023-01-15 12:38:30 | 2023-01-15 12:42:11 | 221 |
| 3 | thang@mail.com | https://datatute.vn/ | 2023-01-16 09:05:31 | 2023-01-16 09:05:35 | 4 |
| 3 | thang@mail.com | https://datatute.vn/blog/ | 2023-01-16 09:05:35 | 2023-01-16 09:06:01 | 26 |
| 3 | thang@mail.com | https://datatute.vn/sql-ve-viec-tim-kiem-thong-tin-chua-xay-ra/ | 2023-01-16 09:06:01 | 2023-01-16 09:25:03 | 1142 |
| 3 | thang@mail.com | https://datatute.vn/contact/ | 2023-01-16 09:25:03 | 2023-01-16 09:28:51 | 228 |
6 Wildcard characters
Wildcard là các ký tự đại diện được sử dụng khi tìm kiếm dữ liệu dạng chữ (text/string) và dùng kết hợp với toán tử LIKE
2 wildcard chính bao gồm ký tự % và ký tự _ (dấu gạch dưới)
Trong thực tiễn, sử dụng wildcard giúp ta đơn giản hóa 1 số điều kiện lọc mà có quá nhiều giá trị giống nhau, tuy nhiên chúng cũng có những nhược điểm như:
Nếu để wildcard ở đầu chuỗi ký tự (VD: ‘%abc’) thì sẽ không tận dụng được index, câu lệnh sẽ chạy chậm nếu bảng dữ liệu nặng (xem thêm ở phần “Sử dụng index chính xác”)
Rủi ro về bảo mật thông tin (thường các software engineers sẽ quan tâm và hay gặp phải vấn đề này khi phát triển phần mềm hơn là những người làm data)
Ví dụ:
Ta có bảng dữ liệu customers như sau
-- drop table
DROP TABLE IF EXISTS customers;
-- create customers table
CREATE TABLE customers(
CustomerID int,
FullName varchar(200)
);
-- insert data into table
INSERT INTO customers
VALUES
(1, 'Nguyen Van Toan'),
(2, 'Nguyen Thi Tu'),
(3, 'Le Thi Hoa'),
(4, 'Le Van Hoang'),
(5, 'Hoang Trung Hieu'),
(6, 'Nguyen Trong Hoang');
SELECT *
FROM customers;| CustomerID | FullName |
|---|---|
| 1 | Nguyen Van Toan |
| 2 | Nguyen Thi Tu |
| 3 | Le Thi Hoa |
| 4 | Le Van Hoang |
| 5 | Hoang Trung Hieu |
| 6 | Nguyen Trong Hoang |
Yêu cầu: viết câu lệnh SQL để
lấy ra dữ liệu của các khách hàng có họ là Nguyen
lấy ra dữ liệu của các khách hàng có tên là Hoang
Đáp án:
lấy ra dữ liệu của các khách hàng có họ là Nguyen
| CustomerID | FullName |
|---|---|
| 1 | Nguyen Van Toan |
| 2 | Nguyen Thi Tu |
| 6 | Nguyen Trong Hoang |
lấy ra dữ liệu của các khách hàng có tên là Hoang
| CustomerID | FullName |
|---|---|
| 4 | Le Van Hoang |
| 6 | Nguyen Trong Hoang |
7 Reshape data (PIVOT, UNPIVOT, UNION ALL)
Trong thực tế công việc ta thường gặp những tình huống (đặc biệt là để phục vụ phân tích hoặc vẽ biểu đồ) mà ta cần biến đổi dữ liệu từ dạng wide về dạng long và ngược lại. Việc biến đổi này gọi là “reshape data”.
Ví dụ 1: Ta thường gặp dữ liệu gốc ở định dạng sau (đặc biệt là nếu dữ liệu gốc được cung cấp từ 1 file excel hay google spreadsheet)
-- create table
DROP TABLE IF EXISTS revenue_and_cost;
CREATE TABLE revenue_and_cost (
Item VARCHAR(20),
FY2019 INT,
FY2020 INT,
FY2021 INT,
FY2022 INT
);
-- insert values to table
INSERT INTO revenue_and_cost
VALUES
('Revenue', 2850000, 2680000, 2775000, 3001000),
('Cost', 2450000, 2150000, 2285000, 2785000);
SELECT *
FROM revenue_and_cost;| Item | FY2019 | FY2020 | FY2021 | FY2022 |
|---|---|---|---|---|
| Revenue | 2850000 | 2680000 | 2775000 | 3001000 |
| Cost | 2450000 | 2150000 | 2285000 | 2785000 |
Dữ liệu ở dạng này gọi là dạng “wide”. Để thuận tiện cho việc phân tích cũng như vẽ đồ thị thì ta thường cần chuyển nó về dạng “long”. Tuy rằng thường ta thực hiện loại biến đổi này bằng các ngôn ngữ lập trình như Python, R sẽ tiện lợi hơn, trong nhiều trường hợp ta vẫn cần biết cách thực hiện nó bằng SQL.
Biến đổi về dạng “long” sử dụng UNION ALL:
-- transform to long format using UNION ALL
SELECT
Item,
'FY2019' AS Year,
FY2019 AS Value
FROM revenue_and_cost
UNION ALL
SELECT
Item,
'FY2020' AS Year,
FY2020 AS Value
FROM revenue_and_cost
UNION ALL
SELECT
Item,
'FY2021' AS Year,
FY2021 AS Value
FROM revenue_and_cost
UNION ALL
SELECT
Item,
'FY2022' AS Year,
FY2022 AS Value
FROM revenue_and_cost; | Item | Year | Value |
|---|---|---|
| Revenue | FY2019 | 2850000 |
| Cost | FY2019 | 2450000 |
| Revenue | FY2020 | 2680000 |
| Cost | FY2020 | 2150000 |
| Revenue | FY2021 | 2775000 |
| Cost | FY2021 | 2285000 |
| Revenue | FY2022 | 3001000 |
| Cost | FY2022 | 2785000 |
Từ bảng dữ liệu dạng “long” ta dễ dàng vẽ biểu đồ như dưới đây sử dụng các công cụ BI hoặc các ngôn ngữ lập trình như Python, R. Biểu đồ dưới đây được vẽ bằng R.
df <-
read.csv2(
"data/revenue_and_cost_melted.csv",
header = TRUE,
sep = ",",
quote = "\""
)
df %>% ggplot() +
geom_line(aes(
x = Year,
y = Value,
group = Item,
color = Item
#linetype = Item
)) +
theme_minimal()
Cách sử dụng UNION ALL tuy dễ hiểu và thực hiện được đối với mọi SQL dialect (MySQL, SQL Server, PostgreSQL, …) nhưng có nhược điểm là câu lệnh sẽ phải quét dữ liệu trong bảng nhiều lần, khiến lệnh chạy chậm nếu bảng nặng.
Các bạn hãy đọc thêm và thử ứng dụng các lệnh biến đổi dữ liệu từ dạng “wide” về dạng “long” như trên nhưng không sử dụng UNION ALL. Ví dụ như UNPIVOT trong SQL Server hay CROSS JOIN LATERAL trong MySQL.
Ví dụ ta dùng CROSS JOIN LATERAL trong MySQL để biến đổi dữ liệu về dạng “long” như UNION ALL bên trên nhưng với performance tốt hơn cho bảng nặng vì CROSS JOIN LATERAL chỉ quét dữ liệu ở bảng 1 lần
SELECT
a.Item,
b.*
FROM revenue_and_cost a
CROSS JOIN LATERAL (
SELECT 'FY2019', FY2019
UNION ALL
SELECT 'FY2020', FY2020
UNION ALL
SELECT 'FY2021', FY2021
UNION ALL
SELECT 'FY2022', FY2022
) AS b(Year, Value)
;Ví dụ 2: Có những trường hợp ta sẽ cần biến đổi dữ liệu dạng “long” về dạng “wide”, ví dụ như khi muốn trình bày kết quả phân tích dưới dạng bảng sao cho thuận mắt người xem hoặc để cho gần hơn với dạng dữ liệu hay thể hiện trong Excel, Google Spreadsheet giúp kết quả trở nên thân thiện với người xem hơn.
Ta có bảng dữ liệu sau:
DROP TABLE IF EXISTS store_comparison;
CREATE TABLE store_comparison (
StoreID INT,
Metric VARCHAR(50),
Product VARCHAR(50),
Value INT
);
-- insert data into table
INSERT INTO store_comparison
VALUES
(1, 'No. online orders', 'Sandwich', 25),
(1, 'No. online orders', 'Tea', 30),
(1, 'No. online orders', 'Soft drinks', 12),
(2, 'No. online orders', 'Sandwich', 11),
(2, 'No. online orders', 'Tea', 9),
(2, 'No. online orders', 'Soft drinks', 18),
(1, 'No. offline orders', 'Sandwich', 39),
(1, 'No. offline orders', 'Tea', 22),
(1, 'No. offline orders', 'Soft drinks', 27),
(2, 'No. offline orders', 'Sandwich', 31),
(2, 'No. offline orders', 'Tea', 18),
(2, 'No. offline orders', 'Soft drinks', 44);
SELECT *
FROM store_comparison;| StoreID | Metric | Product | Value |
|---|---|---|---|
| 1 | No. online orders | Sandwich | 25 |
| 1 | No. online orders | Tea | 30 |
| 1 | No. online orders | Soft drinks | 12 |
| 2 | No. online orders | Sandwich | 11 |
| 2 | No. online orders | Tea | 9 |
| 2 | No. online orders | Soft drinks | 18 |
| 1 | No. offline orders | Sandwich | 39 |
| 1 | No. offline orders | Tea | 22 |
| 1 | No. offline orders | Soft drinks | 27 |
| 2 | No. offline orders | Sandwich | 31 |
| 2 | No. offline orders | Tea | 18 |
| 2 | No. offline orders | Soft drinks | 44 |
Rõ ràng từ bảng dữ liệu này nếu không vẽ thành đồ thị hoặc không chuyển thành dạng “wide” thì rất khó cho người dùng dữ liệu để đọc tìm kiếm ra insight. Nếu được yêu cầu trình bày dưới dạng bảng, ta sẽ chuyển bảng này về dạng “wide” và trình bày như sau:
SELECT
StoreID,
Metric,
SUM(CASE WHEN Product = 'Sandwich' THEN Value END) AS Sandwich,
SUM(CASE WHEN Product = 'Tea' THEN Value END) AS Tea,
SUM(CASE WHEN Product = 'Soft drinks' THEN Value END) AS SoftDrinks
FROM store_comparison
GROUP BY
StoreID,
Metric
ORDER BY Metric, StoreID;| StoreID | Metric | Sandwich | Tea | SoftDrinks |
|---|---|---|---|---|
| 1 | No. offline orders | 39 | 22 | 27 |
| 2 | No. offline orders | 31 | 18 | 44 |
| 1 | No. online orders | 25 | 30 | 12 |
| 2 | No. online orders | 11 | 9 | 18 |
Trong một số cơ sở dữ liệu quan hệ khác (như SQL Server, Oracle database, …) ta có thể dùng hàm PIVOT để chuyển bảng từ dạng “long” về dạng “wide”.
8 Xử lý các giá trị NULL
Việc xử lý các giá trị NULL rất quan trọng bởi vì các giá trị này có thể khiến cho query để trích xuất, xử lý dữ liệu của ta trả ra kết quả sai hoặc bị lỗi khi chạy nếu ta không để ý kĩ. Có nhiều trường hợp mà ta cần xử lý các giá trị NULL, và trong thực tế với những người còn ít kinh nghiệm làm việc với data thì hay bỏ sót mất việc này. Dưới đây là các trường hợp thường gặp và hướng dẫn để xử lý các giá trị NULL:
8.1 Kiểm tra dữ liệu và tạo ràng buộc NOT NULL cho cột khi tạo bảng
Vì việc so sánh, xử lý, tính toán dữ liệu sử dụng các cột có giá trị NULL dễ trả ra kết quả không như mong muốn nếu ta không để ý kĩ, nên tốt nhất trước khi viết lệnh SQL ta nên:
Kiểm tra dữ liệu của các cột cần sử dụng để xem có giá trị NULL hay không (xem thêm lesson 1 của khóa học này về kiểm tra chất lượng dữ liệu)
Nếu có quyền tạo bảng (ví dụ như khi ta là Data Engineer hay Database admin chẳng hạn) thì hãy tạo ràng buộc NOT NULL (NOT NULL constraint) cho các cột mà ta biết chắc chắn rằng không thể được phép thiếu dữ liệu, để luôn luôn đảm bảo không có giá trị NULL ở các cột đó
Ví dụ: bảng dưới đây có 2 cột có NOT NULL constraint bao gồm: CustomerID (khóa chính thì mặc định luôn có ràng buộc NOT NULL) và NationalIdentity (căn cước công dân). Khi thêm dữ liệu vào bảng này mà thêm giá trị NULL vào 2 cột nêu trên thì sẽ không thêm được và bị báo lỗi
8.2 Luôn dùng IS NULL và IS NOT NULL
Khi viết điều kiện để lọc lấy ra các dòng dữ liệu bị NULL hoặc không bị NULL thì ta dùng lệnh IS NULL hoặc lệnh IS NOT NULL.
Tuyệt đối không bao giờ viết điều kiện lọc là WHERE ColumnA = NULL hay là WHERE ColumnA != NULL vì câu lệnh sẽ không bao giờ trả ra kết quả. Khi viết điều kiện so sánh 1 cột với giá trị NULL như thế này thì SQL engine sẽ không hiểu là ta đang muốn kiểm tra xem ColumnA có kiểu dữ liệu là NULL hay không, và tất nhiên không có kiểu dữ liệu nào là “kiểu NULL” cả, mà chỉ có giá trị NULL mà thôi, nên câu lệnh sẽ luôn không trả ra kết quả.
Ví dụ: Ta có bảng tbl_transactions và tbl_offices như dưới đây.
tbl_transactions lưu dữ liệu các giao dịch mua hàng của khách hàng của 1 công ty. Công ty này có hoạt động kinh doanh ở nhiều quốc gia.
tbl_offices lưu dữ liệu về các văn phòng đại diện tại các quốc gia khác nhau của công ty.
-- create transactions table
DROP TABLE IF EXISTS tbl_transactions;
CREATE TABLE tbl_transactions (
TransID BIGINT,
CustomerID INT,
TransDateTime DATETIME,
TransAmount FLOAT,
Country VARCHAR(2),
City VARCHAR(100)
);
INSERT INTO tbl_transactions (
TransID,
CustomerID,
TransDateTime,
TransAmount,
Country,
City
)
VALUES
(1, 45, '2023-08-22 14:22:56', 12999000, 'VN', 'Danang'),
(2, 45, '2023-08-22 14:23:01', 21000000, 'VN', 'Danang'),
(3, 47, '2023-08-22 12:11:41', 599, 'US', 'New York'),
(4, 51, '2023-08-23 21:22:12', NULL, 'VN', 'Hanoi'),
(5, 51, '2023-08-23 21:22:25', 13500000, 'VN', NULL),
(6, 66, '2023-08-24 16:00:02', 0, 'VN', 'Danang'),
(7, 29, '2023-08-25 19:10:48', 299, 'AU', 'Melbourne'),
(8, 89, '2023-08-25 11:12:35', 399, NULL, NULL),
(9, 91, '2023-08-26 12:22:02', 399, 'NZ', 'Auckland');
-- create office table
DROP TABLE IF EXISTS tbl_offices;
CREATE TABLE tbl_offices (
OfficeID INT,
City VARCHAR(100),
Country VARCHAR(2),
NoEmployees INT
);
INSERT INTO tbl_offices
VALUES
(1, 'New York', 'US', 52),
(2, 'Hanoi', 'VN', 11),
(3, 'Danang', 'VN', 43),
(4, 'Melbourne', 'AU', 26),
(5, NULL, 'NZ', 64);| TransID | CustomerID | TransDateTime | TransAmount | Country | City |
|---|---|---|---|---|---|
| 1 | 45 | 2023-08-22 14:22:56 | 12999000 | VN | Danang |
| 2 | 45 | 2023-08-22 14:23:01 | 21000000 | VN | Danang |
| 3 | 47 | 2023-08-22 12:11:41 | 599 | US | New York |
| 4 | 51 | 2023-08-23 21:22:12 | NULL | VN | Hanoi |
| 5 | 51 | 2023-08-23 21:22:25 | 13500000 | VN | NULL |
| 6 | 66 | 2023-08-24 16:00:02 | 0 | VN | Danang |
| 7 | 29 | 2023-08-25 19:10:48 | 299 | AU | Melbourne |
| 8 | 89 | 2023-08-25 11:12:35 | 399 | NULL | NULL |
| 9 | 91 | 2023-08-26 12:22:02 | 399 | NZ | Auckland |
| OfficeID | City | Country | NoEmployees |
|---|---|---|---|
| 1 | New York | US | 52 |
| 2 | Hanoi | VN | 11 |
| 3 | Danang | VN | 43 |
| 4 | Melbourne | AU | 26 |
| 5 | NULL | NZ | 64 |
Ta thấy trong bảng tbl_transactions có giao dịch với TransAmount bị NULL. Nếu muốn lọc bỏ giao dịch này trước khi phân tích dữ liệu, hoặc muốn lấy ra chỉ riêng giao dịch này, thì ta viết các SQL query như sau
-- remove records with NULL value in TransAmount
SELECT *
FROM tbl_transactions
WHERE TransAmount IS NOT NULL
;
-- get only records with NULL value in TransAmount
SELECT *
FROM tbl_transactions
WHERE TransAmount IS NULL
;Nếu viết SQL query như dưới đây sẽ không bao giờ trả ra kết quả
SELECT *
FROM tbl_transactions
WHERE TransAmount <> NULL -- DO NOT WRITE QUERIES LIKE THIS!
;
SELECT *
FROM tbl_transactions
WHERE TransAmount = NULL -- DO NOT WRITE QUERIES LIKE THIS!
;
SELECT *
FROM tbl_transactions
WHERE NULL = NULL -- Even this will not return any record. Always use IS NULL instead!
;8.3 Giá trị NULL ở điều kiện lọc dữ liệu
Khi lọc dữ liệu (dùng mệnh đề WHERE) ta cần để ý tới các giá trị NULL, đặc biệt là khi có sử dụng các toán tử so sánh như =, >, <, … nếu không có thể kết quả câu lệnh trả ra sẽ bị thiếu dữ liệu.
Ví dụ: Ta được Sales Manager yêu cầu viết SQL query để lấy ra dữ liệu các giao dịch có giá trị giao dịch = 0. Xem xét query dưới đây:
| TransID | CustomerID | TransDateTime | TransAmount | Country | City |
|---|---|---|---|---|---|
| 6 | 66 | 2023-08-24 16:00:02 | 0 | VN | Danang |
Kết quả trả ra tuy về mặt kỹ thuật thì đúng, nhưng từ góc độ kinh doanh, nghiệp vụ thì có thể chưa đầy đủ. Lí do là vì câu lệnh không trả ra các giao dịch có TransAmount với giá trị NULL. Trong thực tế, các giao dịch này có thể bị NULL do lỗi hệ thống, lỗi khi ETL dữ liệu, nhưng cũng có thể bị NULL do 1 setting hoặc quy tắc xử lý dữ liệu nào đó khi dữ liệu được trích xuất ra, biến đổi và đưa vào bảng. Ta cần điều tra nguyên nhân dữ liệu bị NULL để thực sự hiểu rõ bản chất dữ liệu và lọc dữ liệu cho việc phân tích, báo cáo được chính xác.
Lưu ý là điều kiện lọc dữ liệu với các toán tử so sánh như =, >, <, … sẽ tự động bỏ qua các giá trị NULL. Vì vậy ta viết lại query như sau:
| TransID | CustomerID | TransDateTime | TransAmount | Country | City |
|---|---|---|---|---|---|
| 4 | 51 | 2023-08-23 21:22:12 | NULL | VN | Hanoi |
| 6 | 66 | 2023-08-24 16:00:02 | 0 | VN | Danang |
8.4 Giá trị NULL ở lệnh NOT IN ()
Khi viết điều kiện lọc dữ liệu có sử dụng lệnh NOT IN () mà các giá trị bên trong dấu ngoặc ( ) có giá trị NULL thì câu lệnh sẽ trả ra kết quả sai.
Ví dụ: Ta được yêu cầu lấy ra dữ liệu giao dịch phát sinh tại các văn phòng của công ty, ngoại trừ văn phòng ở Mỹ và những văn phòng quá lớn (số lượng nhân viên > 60).
Xem xét query sau:
SELECT *
FROM tbl_transactions
WHERE City NOT IN
(SELECT City FROM tbl_offices WHERE Country = 'US' OR NoEmployees > 60);Câu lệnh này không trả ra dòng dữ liệu nào. Lí do là vì kết quả trả
ra từ lệnh ở trong ngoặc
( SELECT City FROM tbl_offices WHERE Country = 'US' OR NoEmployees > 60)
có giá trị NULL. Xem kết quả này ở bảng dưới đây:
| City |
|---|
| New York |
| NULL |
Để giải thích cho hiện tượng này, ta thấy rằng query ở trên có thể được diễn giải thành dưới dạng 1 query sau:
Kết quả của query này trả ra cho từng dòng dữ liệu của bảng tbl_transactions là:
| TransID | result.of.check.conditions |
|---|---|
| 1 | True and NULL |
| 2 | True and NULL |
| 3 | False and NULL |
| 4 | True and NULL |
| 5 | NULL and NULL |
| 6 | True and NULL |
| 7 | True and NULL |
| 8 | NULL and NULL |
| 9 | True and NULL |
Ta thấy kết quả kiểm tra điều kiện lọc cho từng dòng dữ liệu đều là NULL (VD: True and NULL sẽ trả ra là NULL, False and NULL cũng trả ra là NULL) nên query không trả ra dòng dữ liệu nào.
Bài tập tự nghiên cứu:
- Có cách nào viết SQL query để giải quyết bài toán bên trên mà không phải sử dụng NOT IN () hay không?
- Hãy tìm hiểu vì sao với lệnh IN () thì khi có giá trị NULL ở trong ngoặc, kết quả trả ra vẫn chính xác nhé!
8.5 Giá trị NULL khi ghép các chuỗi ký tự (concatenate strings)
Khi muốn ghép các chuỗi ký tự (string) lại với nhau, ta dùng hàm CONCAT(). Tuy nhiên, nếu 1 trong các string cần ghép lại với nhau là giá trị NULL thì kết quả trả ra sau khi ghép sẽ là giá trị NULL.
Ví dụ: Giả sử ta cần gộp giá trị ở 2 cột City và Country trong bảng tbl_transactions lại với nhau. Xem xét query sau:
| TransID | Country | City | Location |
|---|---|---|---|
| 1 | VN | Danang | Danang, VN |
| 2 | VN | Danang | Danang, VN |
| 3 | US | New York | New York, US |
| 4 | VN | Hanoi | Hanoi, VN |
| 5 | VN | NULL | NULL |
| 6 | VN | Danang | Danang, VN |
| 7 | AU | Melbourne | Melbourne, AU |
| 8 | NULL | NULL | NULL |
| 9 | NZ | Auckland | Auckland, NZ |
Ta thấy cột Location của TransID = 5 có giá trị NULL vì cột City của TransID này có giá trị NULL.
Để giải quyết vấn đề này, ta cần xử lý cột City và Country để thay thế giá trị NULL bằng 1 giá trị khác thuộc dạng chữ (string).
Ta có thể dùng lệnh CASE và hàm COALESCE() để xử lý như sau:
SELECT
TransID,
City,
Country,
CASE
WHEN City IS NULL THEN COALESCE(Country, '')
WHEN Country IS NULL THEN COALESCE(City, '')
ELSE CONCAT(City, ", ", Country)
END AS Location
FROM tbl_transactions
;| TransID | City | Country | Location |
|---|---|---|---|
| 1 | Danang | VN | Danang, VN |
| 2 | Danang | VN | Danang, VN |
| 3 | New York | US | New York, US |
| 4 | Hanoi | VN | Hanoi, VN |
| 5 | NULL | VN | VN |
| 6 | Danang | VN | Danang, VN |
| 7 | Melbourne | AU | Melbourne, AU |
| 8 | NULL | NULL | |
| 9 | Auckland | NZ | Auckland, NZ |
8.6 Giá trị NULL khi sử dụng các phép tính toán học
Trong các công thức tính toán, nếu 1 thành phần trong công thức có giá trị NULL thì kết quả trả ra sẽ là NULL. Vì vậy ta cần xử lý để biến đổi các giá trị NULL đó thành 1 giá trị số giúp công thức có thể chạy được.
Ví dụ: ta có bảng tbl_exchange_rates lưu dữ liệu về tỷ giá các đồng ngoại tệ với đồng Việt Nam như sau:
DROP TABLE IF EXISTS tbl_exchange_rates;
CREATE TABLE tbl_exchange_rates (
Currency VARCHAR(3),
Rate INT
);
INSERT INTO tbl_exchange_rates
VALUES
('USD', 24005),
('AUD', 15397),
('NZD', 14220);
SELECT *
FROM tbl_exchange_rates;| Currency | Rate |
|---|---|
| USD | 24005 |
| AUD | 15397 |
| NZD | 14220 |
Ta được yêu cầu viết SQL query để lấy ra giá trị của từng giao dịch sau khi đã quy đổi sang VND. Xem xét query sau:
WITH transactions AS (
SELECT
TransID,
TransAmount,
CASE
WHEN Country = 'VN' THEN 'VND'
WHEN Country = 'US' THEN 'USD'
WHEN Country = 'AU' THEN 'AUD'
WHEN Country = 'NZ' THEN 'NZD'
ELSE NULL
END AS Currency
FROM tbl_transactions
)
SELECT
tran.TransID,
tran.TransAmount,
tran.Currency,
ex.Rate,
(tran.TransAmount * ex.Rate) AS AmountVND
FROM transactions tran
LEFT JOIN tbl_exchange_rates ex
ON tran.Currency = ex.Currency
;| TransID | TransAmount | Currency | Rate | AmountVND |
|---|---|---|---|---|
| 1 | 12999000 | VND | NULL | NULL |
| 2 | 21000000 | VND | NULL | NULL |
| 3 | 599 | USD | 24005 | 14378995 |
| 4 | NULL | VND | NULL | NULL |
| 5 | 13500000 | VND | NULL | NULL |
| 6 | 0 | VND | NULL | NULL |
| 7 | 299 | AUD | 15397 | 4603703 |
| 8 | 399 | NULL | NULL | NULL |
| 9 | 399 | NZD | 14220 | 5673780 |
Ta thấy kết quả của cột AmountVND trả ra cho các giao dịch bằng đồng ngoại tệ thì chính xác, nhưng với các giao dịch bằng đồng VND thì lại sai vì cột Rate có giá trị NULL. Để công thức tính toán cho cột AmountVND được chính xác, ta dùng hàm COALESCE() để xử lý giá trị NULL ở cột Rate như sau:
WITH transactions AS (
SELECT
TransID,
TransAmount,
CASE
WHEN Country = 'VN' THEN 'VND'
WHEN Country = 'US' THEN 'USD'
WHEN Country = 'AU' THEN 'AUD'
WHEN Country = 'NZ' THEN 'NZD'
ELSE NULL
END AS Currency
FROM tbl_transactions
)
SELECT
tran.TransID,
tran.TransAmount,
tran.Currency,
ex.Rate,
CASE
WHEN tran.Currency IS NOT NULL THEN (tran.TransAmount * COALESCE(ex.Rate, 1))
ELSE NULL
END AS AmountVND
FROM transactions tran
LEFT JOIN tbl_exchange_rates ex
ON tran.Currency = ex.Currency
;| TransID | TransAmount | Currency | Rate | AmountVND |
|---|---|---|---|---|
| 1 | 12999000 | VND | NULL | 12999000 |
| 2 | 21000000 | VND | NULL | 21000000 |
| 3 | 599 | USD | 24005 | 14378995 |
| 4 | NULL | VND | NULL | NULL |
| 5 | 13500000 | VND | NULL | 13500000 |
| 6 | 0 | VND | NULL | 0 |
| 7 | 299 | AUD | 15397 | 4603703 |
| 8 | 399 | NULL | NULL | NULL |
| 9 | 399 | NZD | 14220 | 5673780 |
8.7 Giá trị NULL khi sử dụng các hàm aggregate
Các hàm aggregate (SUM(), AVG(), MIN(), MAX(), …) đều bỏ qua các giá trị NULL khi thực hiện tính toán, ngoại trừ hàm COUNT(*). Lưu ý là COUNT(column_name) và COUNT(DISTINCT column_name) vẫn sẽ bỏ qua các giá trị NULL.
Ta cần đặc biệt lưu ý điều này để thực hiện biến đổi, phân tích dữ liệu được chính xác. Xem ví dụ ở phần Nhóm dữ liệu và tính toán
8.8 Giá trị NULL khi INNER JOIN
Khi JOIN các bảng với nhau, ta cần lưu ý rằng 1 giá trị NULL ở bảng này sẽ không match được với bất kỳ 1 giá trị nào ở bảng khác, kể cả giá trị NULL ở bảng khác, vì thế nên khi ta thực hiện INNER JOIN thì các dòng mà có giá trị NULL ở cột dùng để JOIN của cả 2 bảng đều sẽ bị bỏ ra khỏi kết quả của câu lệnh.
Ví dụ: Ta được yêu cầu viết query lấy ra tất cả các giao dịch, bao gồm các dữ liệu về TransID, City, Country, NoEmployees. Xem xét query dưới đây:
SELECT
a.TransID,
a.City,
a.Country,
b.NoEmployees
FROM tbl_transactions a
INNER JOIN tbl_offices b
ON a.City = b.City;| TransID | City | Country | NoEmployees |
|---|---|---|---|
| 1 | Danang | VN | 43 |
| 2 | Danang | VN | 43 |
| 3 | New York | US | 52 |
| 4 | Hanoi | VN | 11 |
| 6 | Danang | VN | 43 |
| 7 | Melbourne | AU | 26 |
Ta thấy rằng kết quả trả ra không có TransID = 5 vì giao dịch này có giá trị NULL ở cột City, nên khi dùng INNER JOIN thì giao dịch này đã bị loại ra.
Trong thực tế công việc, nếu cần bảo đảm lấy ra tất cả các giao dịch (cho dù có thiếu hay đủ dữ liệu City hay không) thì ta dùng LEFT JOIN thay vì INNER JOIN.
8.9 Giá trị NULL khi dùng CASE WHEN
Giả sử ta được yêu cầu sửa lại các giá trị ở cột Country của bảng tbl_transactions từ ký tự viết tắt thành tên đầy đủ của các quốc gia. Ta viết SQL query như sau:
SELECT
TransID,
Country,
CASE
WHEN Country = 'VN' THEN 'Vietnam'
WHEN Country = 'US' THEN 'The USA'
WHEN Country = 'AU' THEN 'Australia'
WHEN Country = 'NZ' THEN 'New Zealand'
WHEN Country IS NULL THEN 'NA'
END AS CountryFullName
FROM tbl_transactions;| TransID | Country | CountryFullName |
|---|---|---|
| 1 | VN | Vietnam |
| 2 | VN | Vietnam |
| 3 | US | The USA |
| 4 | VN | Vietnam |
| 5 | VN | Vietnam |
| 6 | VN | Vietnam |
| 7 | AU | Australia |
| 8 | NULL | NA |
| 9 | NZ | New Zealand |
Có 1 cách khác để viết lệnh CASE được gọn hơn, tuy nhiên tiềm ẩn nguy cơ trả ra kết quả sai. Hãy xem query dưới đây:
SELECT
TransID,
Country,
CASE Country
WHEN 'VN' THEN 'Vietnam'
WHEN 'US' THEN 'The USA'
WHEN 'AU' THEN 'Australia'
WHEN 'NZ' THEN 'New Zealand'
WHEN NULL THEN 'NA'
END AS CountryFullName
FROM tbl_transactions;| TransID | Country | CountryFullName |
|---|---|---|
| 1 | VN | Vietnam |
| 2 | VN | Vietnam |
| 3 | US | The USA |
| 4 | VN | Vietnam |
| 5 | VN | Vietnam |
| 6 | VN | Vietnam |
| 7 | AU | Australia |
| 8 | NULL | NULL |
| 9 | NZ | New Zealand |
Ta thấy kết quả trả ra cho hầu hết các dòng đều vẫn đúng, nhưng với TransID = 8 thì bị sai, kết quả trả ra không phải là ‘NA’ mà lại là NULL. Đây là do câu lệnh WHEN NULL vì nó tương đồng với WHEN Country = NULL, mà như ở phần Luôn dùng IS NULL và IS NOT NULL đã đề cập, đây là cách kiểm tra điều kiện NULL sai.
Vì vậy hãy luôn cố gắng viết lệnh CASE đầy đủ và rõ ràng, đừng chỉ vì rút gọn một vài chữ mà khiến cả câu lệnh bị sai.
9 Một số Best practices khác
Bên cạnh những nội dung đã đề cập ở Lesson 1 và ở bài học này, ta cần lưu ý một số best practices khác trong quá trình sử dụng SQL:
9.1 Dùng CTE thay cho subquery để đơn giản hoá câu lệnh SQL
Như đã đề cập ở Lesson 01 - extra, CTE hoạt động giống như 1 bảng ảo, có tên bảng, có hàng và cột. Nó chỉ tồn tại tạm thời trong phạm vi của SQL query. Khi SQL query gồm nhiều bước biến đổi dữ liệu và cần dùng nhiều subquery thì ta nên sử dụng CTE dể query rõ ràng, dễ hiểu hơn.
Ví dụ:
Ta có query sau sử dụng subquery:
SELECT
orders.SalesOrderID
, orders.OrderDate
, orders.DueDate
, details.NoProducts
, cust.CustomerGroup
FROM sales_order_header AS orders
LEFT JOIN (
SELECT
SalesOrderID
, COUNT(1) AS NoProducts
FROM sales_order_detail
GROUP BY SalesOrderID
) details
ON orders.SalesOrderID = details.SalesOrderID
LEFT JOIN (
SELECT
CustomerID,
CASE
WHEN CustomerGroup = 1 THEN 'VIP'
WHEN CustomerGroup = 2 THEN 'Regular'
ELSE 'N/A'
END AS CustomerGroup
FROM sales_customers
) cust
ON orders.CustomerID = cust.CustomerID
WHERE
orders.OrderDate >= '2014-06-01'Ta viết lại query, sử dụng CTE thay cho subquery như sau, giúp query trở nên rõ ràng, dễ nhìn hơn:
WITH details AS (
SELECT
SalesOrderID
, COUNT(1) AS NoProducts
FROM sales_order_detail
GROUP BY SalesOrderID
),
cust AS (
SELECT
CustomerID,
CASE
WHEN CustomerGroup = 1 THEN 'VIP'
WHEN CustomerGroup = 2 THEN 'Regular'
ELSE 'N/A'
END AS CustomerGroup
FROM sales_customers
)
SELECT
orders.SalesOrderID
, orders.OrderDate
, orders.DueDate
, details.NoProducts
, cust.CustomerGroup
FROM sales_order_header AS orders
LEFT JOIN details
ON orders.SalesOrderID = details.SalesOrderID
LEFT JOIN cust
ON orders.CustomerID = cust.CustomerID
WHERE
orders.OrderDate >= '2014-06-01'9.2 Đặt alias cho bảng khi JOIN
Khi query nhiều bảng với nhau, hãy đặt alias cho các bảng để viết query được chính xác và dễ hiểu. Tốt nhất hãy có gắng đặt alias dựa theo tên của mỗi bảng thay vì đặt chung chung là a, b, c hay tbl1, tbl2, …
Ví dụ:
9.3 Dùng EXIST thay cho IN
Khi ta cần kiểm tra một giá trị có tồn tại ở trong 1 cột của bảng không thì hãy ưu tiên sử dụng toán tử EXISTS thay cho toán tử IN vì khi dùng toán tử EXIST, SQL engine sẽ dừng lại ngay khi tìm thấy giá trị đang cần tìm kiếm, còn khi dùng toán tử IN thì SQL engine sẽ phải scan cả bảng, bao gồm cả các giá trị trùng lặp. Chỉ khi cần kiểm tra giá trị có tồn tại trong 1 danh sách có sẵn hay không (1 list các giá trị nhất định cho trước) thì ta mới nên sử dụng IN
Tương tự, ta nên dùng NOT EXISTS thay cho NOT IN.
Ví dụ:
Ta có câu lệnh sử dụng NOT IN để lấy ra thông tin của những khách hàng mà chưa phát sinh hợp đồng nào với công ty trong năm 2022 như sau:
SELECT
cust.CustomerID, cust.CustomerName
FROM customer_table AS cust
WHERE cust.CustomerID NOT IN
(SELECT CustomerID FROM contract_table WHERE ContractDate BETWEEN '2022-01-01' AND '2022-12-31')Tuy câu lệnh trên dễ hiểu, nhưng lại tiêu tốn khá nhiều tài nguyên máy tính, vì nó phải quét qua tất cả các CustomerID ở bảng contract_table (bao gồm cả các giá trị trùng lặp) để lấy ra kết quả của subquery, rồi so sánh giá trị CustomerID của từng dòng trong bảng customer_table với tập các giá trị của subquery đó.
Để giải quyết vấn đề này, ta có thể sử dụng toán tử EXISTS như trong query dưới đây. Tốc độ xử lý của query sẽ nhanh hơn (đặc biệt là khi subquery trả ra kết quả có rất nhiều dòng) vì khi query engine xử lý và tìm thấy 1 dòng dữ liệu trong subquery thỏa mãn điều kiện, nó sẽ dừng lại và không tiếp tục xử lý subquery nữa.
SELECT
cust.CustomerID, cust.CustomerName
FROM customer_table AS cust
WHERE NOT EXISTS
(SELECT *
FROM contract_table AS cont
WHERE ContractDate BETWEEN '2022-01-01' AND '2022-12-31'
AND cont.CustomerID = cust.CustomerID)Một cách viết query khác để đạt được kết quả tương tự như trên đó là sử dụng LEFT JOIN với điều kiện WHERE để trả ra các dòng ở bảng bên trái mà không match được với bảng bên phải:
SELECT
cust.CustomerID, cust.CustomerName
FROM customer_table AS cust
LEFT JOIN contract_table AS cont
ON cust.CustomerID = cont.CustomerID
AND cont.ContractDate BETWEEN '2022-01-01' and '2022-12-31'
WHERE cont.CustomerID IS NULLGiữa 2 cách sử dụng LEFT JOIN và EXISTS, mỗi hệ quản trị cơ sở dữ liệu (DBMS) lại có cơ chế và những sự ưu tiên khác nhau khi xử lý câu lệnh, vì vậy để chắc chắn cách viết query nào tốt hơn trong 2 cách trên thì ta cần thử nghiệm thực tế.
9.4 Hạn chế sử dụng SELECT *
Khi tìm hiểu, khám phá bảng dữ liệu ta thường sử dụng lệnh SELECT * để xem toàn bộ các cột trong bảng, nhưng ta cần nhớ có thêm lệnh LIMIT (hoặc SELECT TOP tuỳ vào SQL dialect ta đang dùng) để tránh việc query toàn bộ dữ liệu của bảng, nhất là đói với các bảng nặng nằm trên môi trường production, gây ra gánh nặng không cần thiết lên hệ thống.
Khi viết query, nếu không phải để khám phá dữ liệu, hãy viết cụ thể tên các cột cần lấy ra thay vì dùng SELECT *. Điều này giúp ta:
kiểm soát được chính xác các cột và thứ tự xuất hiện của các cột trong kết quả trả ra từ query
tạo thói quen chỉ lấy ra các cột cần thiết thay vì lấy hết tất cả
kiểm soát tốt hơn việc query có kích hoạt và sử dụng được index hay không và sử dụng như thế nào
tránh được conflict về tên cột và kiểm soát các cột được trả ra tốt hơn khi JOIN nhiều bảng hoăc/và JOIN các bảng có nhiều cột
9.5 Lưu ý khi sử dụng DISTINCT và UNION
Mệnh đề DISTINCT ngốn nhiều tài nguyên máy tính nên làm chậm quá trình truy vấn dữ liệu. Ta nên tránh đưa DISTINCT vào SQL query khi không cần thiết.
Khi dùng câu lệnh UNION để gộp các kết quả truy vấn lại với nhau, các bản ghi giống nhau thì sẽ được loại bỏ bớt (tương đương như kết quả từ câu SELECT DISTINCT). Vì thế nếu biết chắc rằng các kết quả truy vấn không có bản ghi nào trùng lắp thì ta nên sử dụng câu lệnh UNION ALL thay thế cho UNION để tăng tốc độ xử lý dữ liệu.
Trong các câu truy vấn có 1/nhiều điều kiện OR bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn.
Ví dụ: Hãy viết lại query dưới đây bằng cách sử dụng UNION ALL
SELECT
CustomerID,
CustomerName
FROM customer_table
WHERE CustomerGroup = 'VIP' OR CustomerSpecialGroup = 1 OR AcquisitionCampaign = 'New Year 2023' Đáp án:
9.6 Viết comment cho code
Ta nên tập thói quen viết comment cho code, đặc biệt là để giải thích lý do, tác dụng của các đoạn code. Việc này giúp code của ta trở nên rõ ràng, giúp người review code hoặc người nhận bàn giao code dễ hiểu hơn, hoặc ngay cả với chính ta sau một thời gian cần review lại để chỉnh sửa code thì cũng dễ thực hiện hơn, khi phát sinh lỗi cũng sẽ dễ rà soát và chỉnh sửa hơn.
9.7 Dùng ( ) để phân tách các điều kiện, đặc biệt là khi có cả 2 toán tử AND và OR
Để đảm bảo logic ở điều kiện lọc dữ liệu được thực hiện đúng như những gì ta muốn, khi logic có sử dụng cả 2 toán tử AND và OR thì ta nên sử dụng thêm dấu ngoặc tròn ( ) để phân tách các điều kiện.
Ví dụ: ta có bảng test_logic như dưới đây
DROP TABLE IF EXISTS test_logic;
CREATE TABLE test_logic (
Id INT,
ColA INT,
ColB INT
);
INSERT INTO test_logic
VALUES
(1, 10, 15),
(2, 22, 28),
(3, 7, 26),
(4, 33, 80),
(5, 14, 24),
(6, 40, 22),
(7, 9, 14);| Id | ColA | ColB |
|---|---|---|
| 1 | 10 | 15 |
| 2 | 22 | 28 |
| 3 | 7 | 26 |
| 4 | 33 | 80 |
| 5 | 14 | 24 |
| 6 | 40 | 22 |
| 7 | 9 | 14 |
Ta cần viết query để lấy ra các dòng dữ liệu thoả mãn cả 2 điều kiện sau:
GIá trị ở ColA phải > 30 hoặc < 10
Giá trị ở ColB phải < 15
Xét 2 query dưới đây. Query 1 trả ra kết quả chính xác còn query 2 sai. Lí do là vì trong SQL toán tử AND được ưu tiên thực hiện trước toán tử OR (cũng giống như phép nhân, chia được thực hiện trước cộng, trừ trong phương trình toán học vậy), còn khi dùng dấu ngoặc tròn thì các điều kiện trong ngoặc tròn được ưu tiên thực hiện trước, nên lúc này logic của query chính xác với yêu cầu của đề bài.
| Id | ColA | ColB |
|---|---|---|
| 7 | 9 | 14 |
| Id | ColA | ColB |
|---|---|---|
| 4 | 33 | 80 |
| 6 | 40 | 22 |
| 7 | 9 | 14 |
10 SQL style guide
Khi viết code SQL ta nên tìm hiểu tuân thủ theo các quy tắc đặt tên thông dụng và của đơn vị mà ta đang làm việc (ví dụ như tên bảng, cột dùng snake-cased hay camel-cased, cách viết tắt các chữ, …). Ta cũng nên trình bày code theo các best practices về trình bày.
Các bạn hãy google “SQL style guide” để tìm hiểu và thực hành theo. Các bạn có thể tham khảo style guide sau đây của gitlab: https://about.gitlab.com/handbook/business-technology/data-team/platform/sql-style-guide/