Các lệnh SQL dành cho Nhà khoa học dữ liệu

Là một nhà khoa học dữ liệu hoặc nhà phân tích dữ liệu, dữ liệu đương nhiên là trung tâm của công việc. Có rất nhiều nguồn mà bạn có thể thu thập dữ liệu. Thường thì dữ liệu nằm trong cơ sở dữ liệu SQL, khi đó hiểu các lệnh truy vấn SQL có thể là điều thiết yếu để bạn thực hiện tốt vai trò này. Bài viết này sẽ giới thiệu cho bạn một số lệnh cơ bản, rồi đến các thao tác nâng cao hơn sẽ hữu ích khi làm việc với vai trò nhà phân tích dữ liệu hoặc nhà khoa học dữ liệu.

Để hiểu các lệnh liệt kê ở trên, chúng ta sẽ dùng cơ sở dữ liệu mã nguồn mở DVD rental để minh họa cách sử dụng. Bạn có thể xem sơ đồ quan hệ thực thể (EDR) tại đây.

Trong phạm vi bài viết này, chúng tôi đã thiết lập cơ sở dữ liệu cục bộ và thực hiện tất cả truy vấn bằng pgAdmin4, nên chúng tôi đang dùng postgreSQL. Phần lớn, nếu không muốn nói là tất cả, các lệnh sẽ hoạt động trên các máy chủ SQL khác. Việc cài đặt postgreSQL và thiết lập cơ sở dữ liệu nằm ngoài phạm vi của bài viết; chúng tôi khuyến khích bạn tham khảo các tài nguyên khác như Hướng dẫn PostgreSQL cho người mới bắt đầu của DataCamp để được tư vấn về các quy trình này.

Truy xuất dữ liệu đơn giản

SELECT FROM

Ví dụ đơn giản nhất về truy xuất là lấy toàn bộ nội dung từ một bảng cụ thể trong cơ sở dữ liệu. Nếu muốn biết tất cả danh mục phim trong bảng 'Category', bạn có thể nhập lệnh sau:

SELECT *
FROM category
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Ở đây chúng ta đã chỉ định tên bảng sau FROM. Vì muốn tất cả nội dung trong bảng, chúng ta dùng * sau SELECT để chỉ định lấy mọi thứ.

DISTINCT

Có những trường hợp chúng ta chỉ quan tâm đến các giá trị duy nhất. Ví dụ, nếu muốn biết tất cả xếp hạng MPAA từ bảng film? Dùng DISTINCT cho phép chúng ta chỉ lấy các giá trị không trùng lặp:

SELECT DISTINCT(rating)
FROM film
Được hỗ trợ bởi

Lệnh này trả về:

Truy xuất dữ liệu với điều kiện đơn giản

WHERE

Cũng có những tình huống ta chỉ muốn truy xuất các bản ghi thỏa mãn điều kiện cụ thể. Ta có thể đưa ra các điều kiện phải đáp ứng trước khi trả dữ liệu bằng mệnh đề WHERE. Về bản chất, ta đang lọc bản ghi theo điều kiện. Ví dụ, nếu chỉ muốn biết các phim được xếp hạng PG-13?

SELECT title
FROM film
WHERE rating = 'PG-13'
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Ngoài ra, ta có thể liệt kê các phim có rental rating từ 4.5 trở lên:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

ORDER BY

Sắp xếp kết quả trả về thường hữu ích để chúng ta hiểu dữ liệu tốt hơn. Nếu muốn danh sách PG-13 ở trên nhưng được sắp theo độ dài phim từ dài đến ngắn, kèm độ dài tương ứng? Dùng ORDER BY là thực hiện được ngay:

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

LIMIT

Đôi khi ta chỉ quan tâm đến một số lượng bản ghi giới hạn. Ở đây, chúng ta chỉ quan tâm 10 phim PG-13 dài nhất, nên có thể dùng mệnh đề LIMIT.

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Được hỗ trợ bởi

Lệnh này trả về:

Phép tổng hợp

GROUP BY & COUNT( )

Các phép tổng hợp thường dùng để tóm tắt tập dữ liệu nhằm rút ra thông tin. Chúng thường đi kèm mệnh đề GROUP BY. Ví dụ, nếu cần biết mỗi khách hàng đã thuê bao nhiêu lần, ta có thể đếm số lượt thuê và sắp xếp kết quả theo lượt thuê giảm dần.

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

SUM( )

Còn tổng số tiền mỗi khách đã chi thì sao? Ta có thể làm được bằng hàm tổng hợp SUM. Ở đây, chúng ta muốn người chi nhiều nhất ở trên cùng.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

AVG( ) & HAVING

Ta có thể thêm điều kiện sau khi đã tính tổng hợp theo nhóm bằng HAVING. Ví dụ, ta muốn biết các hạng mục xếp hạng MAAP có phim dài trung bình hơn 115 phút?

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Được hỗ trợ bởi

Lệnh này trả về:

MIN( ) & Bí danh

Không phải lúc nào dùng tổng hợp cũng cần nhóm. Nếu muốn biết độ dài phim ngắn nhất, ta chỉ cần dùng hàm MIN. Ta cũng muốn cột hiển thị là 'shortest_movie_length', có thể thực hiện bằng cách đặt bí danh với 'AS'.

SELECT MIN(length) AS shortest_movie_length
FROM film
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Bạn cũng có thể dùng bí danh cho tên bảng. Điều này hữu ích khi làm việc với tên bảng dài, đặc biệt khi join (xem bên dưới). Chúng ta cũng nên chú ý loại bí danh sử dụng vì nó có thể ảnh hưởng lớn đến khả năng đọc của mã.

Còn nhiều hàm tổng hợp hữu ích khác, bạn có thể tìm hiểu thêm trong khóa Introduction to SQL.

Join

INNER JOIN (JOIN)

Bạn có lẽ nhận ra rằng chỉ nhìn vào một bảng thì làm được rất ít việc. Ta thường muốn xem dữ liệu từ nhiều bảng. Đây là lúc dùng JOIN. Ta có thể join các bảng dựa trên khóa chung. Ví dụ, nếu ta muốn tìm các phim không phải tiếng Anh:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'
Được hỗ trợ bởi

(Lưu ý rằng trong cơ sở dữ liệu này không có phim nào ngoài tiếng Anh. Bạn sẽ nhận được bảng rỗng với truy vấn ngay trên.)

Bạn cũng có thể join nhiều bảng. Ví dụ bên dưới, chúng ta muốn biết các danh mục và điểm đánh giá trung bình tương ứng. Chúng ta cũng sắp xếp kết quả theo điểm trung bình giảm dần, cao nhất trước.

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Có nhiều loại join: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Ở đây, chúng ta dùng JOIN, mặc định là INNER JOIN, tức chỉ trả các bản ghi nếu chúng xuất hiện ở cả hai bảng. Nếu muốn tìm hiểu thêm về sự khác nhau giữa các loại join này, bạn có thể xem khóa Joining Data in SQL.

Thay đổi kiểu dữ liệu

CAST( )

Trong ví dụ phía trên nơi chúng ta tính số tiền đô, bạn có thể để ý rằng SQL chỉ coi các giá trị là số. Ta có thể chuyển chúng thành số tiền bằng hàm CAST:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Tiền tệ không phải là thứ duy nhất ta có thể chuyển. Ta có thể chuyển số sang số thực (float), văn bản (text), thậm chí ngày giờ.

ROUND( )

Tương tự, ta cũng có thể làm tròn số. Ở ví dụ trên khi tính điểm thuê trung bình, có nhiều chữ số thập phân trong giá trị tính được. Ta có thể dùng hàm ROUND để làm tròn đến hai chữ số thập phân:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Điều kiện phức tạp

Mệnh đề CASE

Bạn có thể hình dung rằng bằng cách kết hợp nhiều hàm và mệnh đề ở trên, bạn có thể thực hiện truy vấn khá phức tạp. Tuy nhiên, có những tình huống các phần cơ bản là chưa đủ. Ví dụ, nếu chúng ta muốn thêm một cột phân loại độ dài phim. Ta có thể gán nhãn phim là 'Short Film' nếu dưới 50 phút, 'Long Film' nếu trên 150 phút, và 'Medium Film' cho phần còn lại. Ta có thể thực hiện bằng mệnh đề CASE:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Như bạn thấy, kết quả được trả khi điều kiện chỉ định được thỏa mãn. Điều này tương tự câu lệnh 'if-else' trong các ngôn ngữ lập trình.

Truy vấn lồng (Subquery)

Bây giờ ta có thể muốn biết phim nào có độ dài lớn nhất và tiêu đề tương ứng của chúng. Với điều này, ta có thể dùng mệnh đề WHERE đã giới thiệu ở trên, chỉ là điều kiện phức tạp hơn:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)
Được hỗ trợ bởi

Lệnh này trả về:

Các truy vấn phức tạp có thể được thực hiện bằng truy vấn lồng. Trong ví dụ này, chúng ta muốn biết danh sách tiêu đề phim có điểm thuê cao hơn điểm thuê trung bình của tất cả phim hài:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Trong ví dụ này, trước tiên ta tìm điểm thuê trung bình của phim hài. Giá trị tìm được trong truy vấn này (nằm trong ngoặc đơn phía trên) sau đó được dùng làm điều kiện trong mệnh đề WHERE để truy ra tiêu đề phim.

Biểu thức bảng chung (CTE)

Ở một ví dụ phức tạp hơn, ta có thể dùng biểu thức bảng chung (CTE) để lưu một bảng tạm, từ đó trích xuất thông tin cần thiết. Ở ví dụ này, chúng ta muốn biết số lượt thuê trung bình mỗi tuần mà mỗi nhân viên xử lý.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Được hỗ trợ bởi

Lệnh này trả về:

Trong trường hợp trên, chúng ta lưu số lượt thuê theo tuần vào một bảng tạm gọi là 'weekly_rentals', sau đó có thể thực hiện truy vấn khác trên đó để lấy kết quả cuối cùng.

Hàm cửa sổ (Window)

Các hàm cửa sổ là một nhóm kỹ năng SQL quan trọng khác và có thể đưa bạn lên cấp độ tiếp theo. Dù được xem là kỹ năng trung cấp đến nâng cao, nhiều nhà phân tích và nhà khoa học dữ liệu coi chúng là thiết yếu cho công việc. Vì vậy, đáng để bạn dành thời gian làm quen với chúng.

OVER( ) & PARTITION BY Ở đây, giả sử chúng ta muốn tìm số lượt thuê theo tuần của mỗi nhân viên và tổng số lượt thuê toàn bộ, kèm ID nhân viên và số tuần. Ta có thể tái sử dụng CTE tạo ở ví dụ trên, như sau:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Có OVER trong mã cho biết đây là hàm cửa sổ. Ở đây, chúng ta nói rằng hàm cửa sổ (SUM) được áp dụng cho mọi hàng. Ta cũng có thể dùng hàm cửa sổ cùng với PARTITION BY để áp dụng hàm ở các 'phân vùng' chỉ định. Giả sử giờ ta muốn tính tổng số lượt thuê theo tuần thay vì tổng toàn bộ (bên cạnh các cột khác). Trong ví dụ dưới đây, một lần nữa chúng ta dùng CTE đã tạo trước đó.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Được hỗ trợ bởi

Lệnh này trả về (chỉ hiển thị 10 hàng đầu tiên):

Cột bổ sung này chứa tổng số lượt thuê theo tuần thay vì tổng tích lũy.

Chúng tôi chỉ giới thiệu một vài ví dụ đơn giản về hàm cửa sổ quan trọng cho công việc nhà phân tích hoặc nhà khoa học dữ liệu tại nhiều công ty. Chúng tôi khuyến khích bạn tìm hiểu thêm về các lệnh này qua các liên kết cung cấp. Bạn cũng có thể xem khóa Intermediate SQL để học hoặc ôn lại kỹ năng SQL. Ngoài ra, bạn có thể luyện tập bằng cách lấy một cơ sở dữ liệu mã nguồn mở và thử nghiệm các lệnh khác nhau trong khi suy nghĩ về các câu hỏi phù hợp cần trả lời.