1 Hướng dẫn

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).
Truy cập link sqlfiddle có ở mỗi câu hỏi và phần bài tập thực hành để viết lệnh SQL mà không cần cài đặt

2 SQL order of execution

Để viết code SQL tốt thì ta cần nắm rõ thứ tự thực hiện của các mệnh đề trong 1 câu lệnh SQL.

Thứ tự thực hiện các mệnh đề cơ bản trong câu lệnh SQL như sau:

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ả

Ví dụ:

Ta có bảng test1 như sau

-- create test1 table
DROP TABLE IF EXISTS test1;

CREATE TEMPORARY TABLE test1 (
    ID int,
    CustomerName VARCHAR(50)
);

INSERT INTO test1
VALUES (1, 'Nguyen Van A'), (2, 'Nguyen Thi B'), (3, 'Hoang Ngoc C'), (4, 'Le Thi D')
;

ID	CustomerName
1	Nguyen Van A
2	Nguyen Thi B
3	Hoang Ngoc C
4	Le Thi D

Query dưới đây khi chạy thì có báo lỗi hay không? (query được viết theo syntax của MySQL)


SELECT
    SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1) AS FamilyName,
    COUNT(1) as NoRows   
FROM test1
WHERE FamilyName IN ('Nguyen', 'Le')
GROUP BY SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1)
ORDER BY FamilyName;

sqlfiddle link: http://sqlfiddle.com/#!9/30cef0/1

Đáp án:

--Query sẽ báo lỗi do mệnh đề WHERE được thực hiện trước mệnh đề SELECT, nên
--hệ thống sẽ không tìm thấy cột FamilyName để thực hiện mệnh đề WHERE
SELECT
    SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1) AS FamilyName,
    COUNT(1) as NoRows   
FROM test1
WHERE FamilyName IN ('Nguyen', 'Le')
GROUP BY SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1)
ORDER BY FamilyName;


--Sửa lại query như sau sẽ chạy được và cho ra kết quả đúng
SELECT
    SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1) AS FamilyName,
    COUNT(1) as NoRows   
FROM test1
WHERE SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1) IN ('Nguyen', 'Le')
GROUP BY SUBSTRING(CustomerName, 1, POSITION(' ' IN CustomerName)-1)
ORDER BY FamilyName;

--Mệnh đề ORDER BY có thứ tự thực hiện sau mệnh đề SELECT, vì vậy ta có thể sử dụng
--tên cột FamilyName ở mệnh đề ORDER BY

Kết quả trả ra từ query đúng:

FamilyName	NoRows
Le	1
Nguyen	2

3 CTE (Common Table Expressions)

Ta có thể hiểu đơn giản CTE chính là 1 bảng được tạo ra bởi subquery trong 1 SQL query, và vì vậy nó chỉ tồn tại trong khoảng thời gian mà SQL query được thực thi.

CTE giúp bạn viết code dễ hơn, đặc biệt là trong những trường hợp phải dùng nhiều subquery

Ví dụ:

Ta có bảng dữ liệu “orders” như sau:

CREATE TABLE order_details (
  OrderID INT,
  ProductID INT,
  Amount FLOAT, -- amount in USD
  OrderDate DATE
);

INSERT INTO order_details
VALUES
      (1, 23, 149.5, '2023-06-01'),
    (1, 24, 288, '2023-06-04'),
    (1, 24, 59, '2023-06-05'),
    (1, 26, 150, '2023-06-01'),
    (2, 41, 288.5, '2023-06-01'),
    (2, 42, 588.5, '2023-06-11'),
    (2, 41, 600, '2023-06-12'),
    (3, 24, 899, '2023-06-10'),
    (3, 25, 420, '2023-06-17');

df <- read.csv2("data/order_details.csv", header = TRUE, sep = ",", quote = "\"")
formattable(df)

OrderID	ProductID	Amount	OrderDate
1	23	149.5	2023-06-01
1	24	288	2023-06-04
1	24	59	2023-06-05
1	26	150	2023-06-01
2	41	288.5	2023-06-01
2	42	588.5	2023-06-11
2	41	600	2023-06-12
3	24	899	2023-06-10
3	25	420	2023-06-17

Ta nhận được yêu cầu trích xuất dữ liệu như sau:

Lấy ra dữ liệu về các đơn hàng, bao gồm Mã đơn hàng (OrderID), Mã sản phẩm (ProductID), tổng giá trị của mỗi sản phẩm được mua trong từng đơn hàng
Chỉ lấy dữ liệu của các đơn hàng có tổng giá trị đơn hàng >USD1000

Để thực hiện yêu cầu này, ta có thể sử dụng CTE trong SQL query như sau:

WITH order_product_cte AS (
    SELECT
        OrderID,
        ProductID,
        SUM(Amount) AS TotalAmount
    FROM order_details
  GROUP BY OrderID, ProductID
),
order_cte AS(
    SELECT
        OrderID,
    SUM(TotalAmount) AS TotalAmount
  FROM order_product_cte
  GROUP BY OrderID
  HAVING SUM(TotalAmount) > 1000
)
SELECT
    *
FROM order_product_cte
WHERE EXISTS (
    SELECT 1 
  FROM order_cte 
  WHERE order_cte.OrderID = order_product_cte.OrderID
);

Lưu ý: Với ví dụ trên, ta thấy rằng CTE sau có thể tham chiếu tới CTE trước, hay nói cách khác, CTE sau có thể được tạo nên từ CTE đứng trước trong câu lệnh SQL.

df <- read.csv2("data/cte_exercise_result.csv", header = TRUE, sep = ",", quote = "\"")
formattable(df)

OrderID	ProductID	TotalAmount
2	41	888.5
2	42	588.5
3	24	899
3	25	420

sqlfiddle link: http://sqlfiddle.com/#!9/d3f0678/1

4 Temporary table

Bảng tạm (temporary table) là 1 loại bảng đặc biệt, cho phép ta lưu trữ dữ liệu tạm thời để có thể sử dụng nhiều lần trong 1 phiên làm việc (session).

Khi việc truy xuất, xử lý dữ liệu bằng 1 query duy nhất quá phức tạp, khiến code trở nên khó theo dõi hoặc khiến cho performance của query kém thì ta có thể chia nhỏ query thành các phần và lưu trữ kết quả của query trước vào bảng tạm để tái sử dụng trong query sau.

Ví dụ:

Vẫn với bảng dữ liệu “orders” và yêu cầu trích xuất dữ liệu như phần CTE bên trên, nhưng lần này ta dùng bảng tạm thay vì CTE:

DROP TABLE IF EXISTS order_tmp;

-- create a temp table to calculate TotalAmount of each Order for order having total amount > USD1000 
CREATE TEMPORARY TABLE order_tmp AS
SELECT
    OrderID,
    SUM(Amount) AS TotalAmount
FROM order_details
GROUP BY OrderID
HAVING TotalAmount > 1000;

-- Show orders with sum of each product
SELECT
    OrderID,
    ProductID,
    SUM(Amount) AS TotalAmount
FROM order_details
WHERE EXISTS (
    SELECT 1 
  FROM order_tmp
  WHERE order_tmp.OrderID = order_details.OrderID
)
GROUP BY OrderID, ProductID;

Kết quả trả ra giống hệt với query sử dụng CTE:

df <- read.csv2("data/cte_exercise_result.csv", header = TRUE, sep = ",", quote = "\"")
formattable(df)

OrderID	ProductID	TotalAmount
2	41	888.5
2	42	588.5
3	24	899
3	25	420

sqlfiddle link: http://sqlfiddle.com/#!9/5c1910/1

Do sqlfiddle không cho phép tạo bảng tạm nên trong đường link bên trên ta chỉ có thể tạo bảng order_tmp dưới dạng bảng vật lý bình thường (CREATE TABLE thay vì CREATE TEMPORARY TABLE)

Lưu ý:

Tùy vào quy định của từng đơn vị, tổ chức và vị trí của bạn mà bạn có được cấp quyền tạo bảng tạm hay không, vì vậy hãy cố gắng sử dụng CTE trước khi sử dụng bảng tạm
Khi tạo View bạn sẽ không thể sử dụng bảng tạm mà chỉ có thể sử dụng CTE

Job Seeker - Lesson 1 extra
Một số nội dung quan trọng trong sử dụng SQL để chuẩn bị cho Lesson 2

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com

06/2023

1 Hướng dẫn

2 SQL order of execution

3 CTE (Common Table Expressions)

4 Temporary table

Job Seeker - Lesson 1 extra Một số nội dung quan trọng trong sử dụng SQL để chuẩn bị cho Lesson 2

Tuan Phan https://datatute.vn tuanphan.datatute@gmail.com

06/2023

1 Hướng dẫn

2 SQL order of execution

3 CTE (Common Table Expressions)

4 Temporary table

Job Seeker - Lesson 1 extra
Một số nội dung quan trọng trong sử dụng SQL để chuẩn bị cho Lesson 2

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com