Xây dựng báo cáo từ một tập dữ liệu là kỹ năng thiết yếu khi bạn làm việc với dữ liệu. Bởi cuối cùng, bạn muốn có thể trả lời các câu hỏi kinh doanh then chốt bằng dữ liệu mình có. Nhiều khi, các câu trả lời này được trình bày dưới dạng biểu đồ báo cáo. Nhưng đôi lúc, cũng cần báo cáo dưới dạng bảng. Ở cả hai trường hợp, bạn có thể cần tóm tắt dữ liệu bằng các phép tính đơn giản. Trong SQL, bạn có thể tóm tắt/tổng hợp dữ liệu bằng các hàm tổng hợp. Với các hàm này, bạn có thể trả lời những câu hỏi như:
Giá trị lớn nhất của some_column_from_the_table là bao nhiêu? hoặc
Những giá trị nhỏ nhất của some_column_from_the_table theo another_column_from_the_table là gì?
và còn nhiều câu hỏi khác.
Hãy bắt đầu thực hiện một số phép tổng hợp dữ liệu.
Lưu ý: Để theo kịp hướng dẫn này, bạn cần biết cách viết các truy vấn cơ bản trong PostgreSQL (bạn sẽ dùng hệ quản trị CSDL này). Hướng dẫn này có thể giúp bạn ôn tập nhanh.
Thiết lập cơ sở dữ liệu
Trước tiên, hãy thiết lập một cơ sở dữ liệu PostgreSQL và khôi phục bản sao lưu này chứa bảng bạn sẽ dùng trong hướng dẫn. Nếu bạn muốn học cách khôi phục bản sao lưu cơ sở dữ liệu trong PostgreSQL, bạn có thể theo dõi phần đầu của hướng dẫn này.
Nếu bạn đã khôi phục thành công, bạn sẽ thấy một bảng tên international_debt trong cơ sở dữ liệu (bạn sẽ cần tạo một cơ sở dữ liệu trước nếu chưa có). Hãy nhanh chóng xem vài dòng đầu tiên của bảng (một truy vấn select đơn giản sẽ giúp bạn làm điều này):
Bảng chứa thông tin về thống kê nợ của các quốc gia trên toàn cầu trong năm hiện tại theo các danh mục khác nhau (tham khảo các cột indicator_name và indicator_code). Cột debt cho thấy số nợ (USD) mà một quốc gia có trong một danh mục cụ thể. Dữ liệu thuộc lĩnh vực kinh tế và thường được dùng để phân tích tình hình kinh tế của các quốc gia. Dữ liệu được thu thập từ Ngân hàng Thế giới.
Giờ bạn đã thiết lập xong cơ sở dữ liệu, hãy chạy vài truy vấn đơn giản để hiểu thêm về dữ liệu. Mở công cụ pgAdmin và bắt đầu.
Thông tin đơn giản cũng quan trọng
Từ hình trên, bạn có thể thấy có nhiều bản ghi trùng cho một quốc gia nhưng thuộc các danh mục khác nhau. Một câu hỏi nhanh chóng được đặt ra là:
Các quốc gia khác nhau mà bảng có bản ghi là những quốc gia nào?
Nếu bạn chỉ chạy một truy vấn select với cột country_name, bạn sẽ không nhận được câu trả lời đúng vì kết quả sẽ có trùng lặp. Hãy dùng từ khóa DISTINCT để xử lý điều này.
selectdistinct country_name from international_debt;Được hỗ trợ bởi
Và truy vấn sẽ trả về như sau:
Giờ bạn đã có câu trả lời tương đối cho câu hỏi trên. Thêm một câu nữa trước khi bạn chuyển sang các hàm tổng hợp:
Có bao nhiêu loại chỉ báo nợ khác nhau trong bảng?
Truy vấn để trả lời sẽ tương tự như trên. Bạn chỉ cần đổi tên cột. Hãy coi như một bài tập nhỏ nhé? Kết quả sẽ giống như sau:
Các hàm tổng hợp
Hãy bắt đầu bằng cách chạy một truy vấn với hàm tổng hợp và tiếp tục theo đó. Trong quá trình này, bạn sẽ học thêm về cú pháp và các cấu trúc cần tuân theo khi áp dụng các hàm tổng hợp trong SQL.
Với hàm tổng hợp SUM(), bạn có thể tính tổng số học trên một cột (chứa giá trị số). Với truy vấn trên, bạn biết được tổng số nợ mà các quốc gia trong bảng đang nợ.
Lưu ý rằng SUM() không tính các giá trị NULL khi cộng. Giờ hãy tìm câu trả lời cho câu hỏi:
Tương tự như SUM(), MAX() cũng không xét các bản ghi NULL khi tính toán. Cũng có hàm MIN() tương tự. Hãy cho tôi biết giá trị nhỏ nhất của cột debt trong phần Comments nhé? Giờ sẽ là ý hay nếu kiểm tra xem có bản ghi không hợp lệ nào trong cột debt không để đảm bảo kết quả đến giờ là chính xác.
Lưu ý rằng bạn dùng các hàm này ở dạng chữ thường như minh họa ở trên.
Khi bạn thực thi truy vấn này: select * from international_debt where debt is null;, bạn sẽ nhận được kết quả rỗng. Giờ hãy tìm tổng số quốc gia phân biệt có trong bảng.
selectcount(distinct(country_name))from international_debt;Được hỗ trợ bởi
Và bạn thấy có tổng cộng 124 quốc gia khác nhau trong bảng. Hãy chú ý kỹ chuỗi hàm bạn áp dụng trong truy vấn trên. Đúng vậy, điều này được phép, tức là có thể kết nối nhiều hàm tổng hợp theo cách logic.
Giờ giả sử bạn muốn xem giá trị trung bình của cột debt. Hàm cần dùng là AVG():
select avg(debt) from international_debt;
Bạn thấy giá trị là 1306633214.966397971 (USD). Trình bày các kết quả này với tên cột phù hợp là một ý hay. Từ các kết quả trên, bạn có thể thấy PostgreSQL đổi tên cột thành tên hàm tổng hợp (được đưa vào truy vấn) khi trả về kết quả. Vì vậy, đặt bí danh thích hợp cho các cột này là điều nên làm. Bạn có thể làm như sau:
selectavg(debt)as Average_Debt_By_A_Country from international_debt;Được hỗ trợ bởi
Kết quả dễ hiểu hơn nhiều:
Giờ hãy nâng độ phức tạp lên một chút. Để có thể trả lời các câu như What are the minimum values of some_column_from_the_table with respect to another_column_from_the_table?, bạn cần kết hợp một hàm tổng hợp với mệnh đề GROUP BY. Hãy xem cách làm.
Hàm tổng hợp + GROUP BY + hơn nữa
Giả sử bạn muốn tạo một báo cáo hiển thị country_name và tổng số nợ của họ. Ví dụ như sau:
Những báo cáo như thế này rất thường gặp trong thực tế. Vậy truy vấn nào để có báo cáo như vậy? Bạn sẽ phải dùng hàm SUM() trên debt. Và bạn cũng cần hiển thị country_name cùng tổng nợ. Hãy chạy truy vấn sau:
select country_name,sum(debt)from international_debt;Được hỗ trợ bởi
Có phải nó tạo ra lỗi sau không?
ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function
Giờ hãy hiểu ý nghĩa thực sự của thông báo này. Khi bạn dùng một hàm tổng hợp (như SUM() cùng một cột chưa tổng hợp như country_name) bạn cần đưa cột chưa tổng hợp đó vào mệnh đề GROUP BY. Vậy truy vấn đúng sẽ là:
select country_name,sum(debt)as total_debt from international_debt groupby country_name;Được hỗ trợ bởi
Và kết quả là chính xác:
Lưu ý cách dùng bí danh trong truy vấn.
Giờ giả sử bạn cần sắp xếp báo cáo này theo total_debt giảm dần. Nhớ mệnh đề ORDER BY chứ? Đúng vậy, bạn cũng có thể kết hợp hàm tổng hợp với mệnh đề ORDER BY:
select country_name,sum(debt)as total_debt from international_debt
groupby country_name orderby total_debt desc;Được hỗ trợ bởi
Kết quả giờ sẽ được sắp xếp ngược:
Lưu ý cột bạn dùng trong mệnh đề ORDER BY.
Giờ là một câu hỏi quan trọng khác:
Mức nợ cao nhất theo các danh mục khác nhau (được sắp xếp ngược) là bao nhiêu?
Bạn sẽ cần dùng hàm MAX() ở đây. Viết truy vấn để trả lời câu hỏi này giờ sẽ không khó nữa.
select indicator_code,max(debt)as maximum_debt from international_debt
groupby indicator_code orderby maximum_debt desc;Được hỗ trợ bởi
Và bạn có một báo cáo gọn gàng:
Bạn cũng có thể giới hạn số dòng trong các báo cáo như thế này. Giả sử bạn chỉ muốn lấy 5 mục hàng đầu trong báo cáo trên. Bạn có thể làm bằng mệnh đề LIMIT.
select indicator_code,max(debt)as maximum_debt from international_debt
groupby indicator_code orderby maximum_debt desclimit5;Được hỗ trợ bởi
Đến lúc cho báo cáo cuối cùng của hướng dẫn này. Bạn cần thêm tên quốc gia vào báo cáo trên. Làm thế nào? Truy vấn sau sẽ giúp bạn làm điều đó:
select country_name, indicator_code,max(debt)as maximum_debt from international_debt
groupby country_name, indicator_code orderby maximum_debt desc;Được hỗ trợ bởi
Một báo cáo tốt khác:
Trong truy vấn trên, bạn đã thêm cột country_name sau mệnh đề SELECT và cũng thêm nó sau GROUP BY. Bạn có thể mở rộng mẫu này cho bất kỳ số lượng cột nào.
Thứ tự của GROUP BY, ORDER BY và LIMIT rất quan trọng khi tạo các báo cáo như thế. Nếu bạn thay đổi thứ tự, bạn sẽ gặp lỗi. Tự mình kiểm chứng nhé:
select country_name,sum(debt)as total_debt from international_debt
orderby total_debt descgroupby country_name;Được hỗ trợ bởi
Và bạn nhận được:
ERROR: syntax error at or near "group"
LINE 1: ...from international_debt orderby total_debt descgroupby c...Được hỗ trợ bởi
Trong truy vấn trên, bạn đặt mệnh đề ORDER BY trước GROUP BY điều này là không được phép. Thực tế, ngay cả khi bạn không dùng hàm tổng hợp thì cũng không áp dụng được. Thứ tự đúng là - GROUP BY -> ORDER BY -> LIMIT. Hãy luôn ghi nhớ.
Tiến xa hơn
Chúc mừng! Bạn đã đi đến cuối hướng dẫn này. Trong hướng dẫn này, bạn đã biết về các hàm tổng hợp khác nhau trong PostgreSQL và cách dùng chúng để tạo ra các báo cáo hữu ích. Đây thực sự là những kỹ năng quan trọng cho một nhà khoa học dữ liệu. Để nâng cao kỹ năng SQL một cách hệ thống, bạn có thể học các khóa DataCamp sau:
Xây dựng báo cáo từ một tập dữ liệu là kỹ năng thiết yếu khi bạn làm việc với dữ liệu. Bởi cuối cùng, bạn muốn có thể trả lời các câu hỏi kinh doanh then chốt bằng dữ liệu mình có. Nhiều khi, các câu trả lời này được trình bày dưới dạng biểu đồ báo cáo. Nhưng đôi lúc, cũng cần báo cáo dưới dạng bảng. Ở cả hai trường hợp, bạn có thể cần tóm tắt dữ liệu bằng các phép tính đơn giản. Trong SQL, bạn có thể tóm tắt/tổng hợp dữ liệu bằng các hàm tổng hợp. Với các hàm này, bạn có thể trả lời những câu hỏi như:
some_column_from_the_tablelà bao nhiêu? hoặcsome_column_from_the_tabletheoanother_column_from_the_tablelà gì?và còn nhiều câu hỏi khác.
Hãy bắt đầu thực hiện một số phép tổng hợp dữ liệu.
Lưu ý: Để theo kịp hướng dẫn này, bạn cần biết cách viết các truy vấn cơ bản trong PostgreSQL (bạn sẽ dùng hệ quản trị CSDL này). Hướng dẫn này có thể giúp bạn ôn tập nhanh.
Thiết lập cơ sở dữ liệu
Trước tiên, hãy thiết lập một cơ sở dữ liệu PostgreSQL và khôi phục bản sao lưu này chứa bảng bạn sẽ dùng trong hướng dẫn. Nếu bạn muốn học cách khôi phục bản sao lưu cơ sở dữ liệu trong PostgreSQL, bạn có thể theo dõi phần đầu của hướng dẫn này.
Nếu bạn đã khôi phục thành công, bạn sẽ thấy một bảng tên
international_debttrong cơ sở dữ liệu (bạn sẽ cần tạo một cơ sở dữ liệu trước nếu chưa có). Hãy nhanh chóng xem vài dòng đầu tiên của bảng (một truy vấn select đơn giản sẽ giúp bạn làm điều này):Bảng chứa thông tin về thống kê nợ của các quốc gia trên toàn cầu trong năm hiện tại theo các danh mục khác nhau (tham khảo các cột
indicator_namevàindicator_code). Cột debt cho thấy số nợ (USD) mà một quốc gia có trong một danh mục cụ thể. Dữ liệu thuộc lĩnh vực kinh tế và thường được dùng để phân tích tình hình kinh tế của các quốc gia. Dữ liệu được thu thập từ Ngân hàng Thế giới.Giờ bạn đã thiết lập xong cơ sở dữ liệu, hãy chạy vài truy vấn đơn giản để hiểu thêm về dữ liệu. Mở công cụ
pgAdminvà bắt đầu.Thông tin đơn giản cũng quan trọng
Từ hình trên, bạn có thể thấy có nhiều bản ghi trùng cho một quốc gia nhưng thuộc các danh mục khác nhau. Một câu hỏi nhanh chóng được đặt ra là:
Nếu bạn chỉ chạy một truy vấn select với cột
country_name, bạn sẽ không nhận được câu trả lời đúng vì kết quả sẽ có trùng lặp. Hãy dùng từ khóaDISTINCTđể xử lý điều này.Và truy vấn sẽ trả về như sau:
Giờ bạn đã có câu trả lời tương đối cho câu hỏi trên. Thêm một câu nữa trước khi bạn chuyển sang các hàm tổng hợp:
Truy vấn để trả lời sẽ tương tự như trên. Bạn chỉ cần đổi tên cột. Hãy coi như một bài tập nhỏ nhé? Kết quả sẽ giống như sau:
Các hàm tổng hợp
Hãy bắt đầu bằng cách chạy một truy vấn với hàm tổng hợp và tiếp tục theo đó. Trong quá trình này, bạn sẽ học thêm về cú pháp và các cấu trúc cần tuân theo khi áp dụng các hàm tổng hợp trong SQL.
Và kết quả:
Với hàm tổng hợp
SUM(), bạn có thể tính tổng số học trên một cột (chứa giá trị số). Với truy vấn trên, bạn biết được tổng số nợ mà các quốc gia trong bảng đang nợ.Lưu ý rằng
SUM()không tính các giá trịNULLkhi cộng. Giờ hãy tìm câu trả lời cho câu hỏi:Đây là lúc hàm tổng hợp
MAX()phát huy tác dụng:Và câu trả lời là:
Tương tự như
SUM(),MAX()cũng không xét các bản ghiNULLkhi tính toán. Cũng có hàmMIN()tương tự. Hãy cho tôi biết giá trị nhỏ nhất của cộtdebttrong phầnCommentsnhé? Giờ sẽ là ý hay nếu kiểm tra xem có bản ghi không hợp lệ nào trong cộtdebtkhông để đảm bảo kết quả đến giờ là chính xác.Lưu ý rằng bạn dùng các hàm này ở dạng chữ thường như minh họa ở trên.
Khi bạn thực thi truy vấn này:
select * from international_debt where debt is null;, bạn sẽ nhận được kết quả rỗng. Giờ hãy tìm tổng số quốc gia phân biệt có trong bảng.Và bạn thấy có tổng cộng 124 quốc gia khác nhau trong bảng. Hãy chú ý kỹ chuỗi hàm bạn áp dụng trong truy vấn trên. Đúng vậy, điều này được phép, tức là có thể kết nối nhiều hàm tổng hợp theo cách logic.
Giờ giả sử bạn muốn xem giá trị trung bình của cột
debt. Hàm cần dùng làAVG():Bạn thấy giá trị là 1306633214.966397971 (USD). Trình bày các kết quả này với tên cột phù hợp là một ý hay. Từ các kết quả trên, bạn có thể thấy PostgreSQL đổi tên cột thành tên hàm tổng hợp (được đưa vào truy vấn) khi trả về kết quả. Vì vậy, đặt bí danh thích hợp cho các cột này là điều nên làm. Bạn có thể làm như sau:
Kết quả dễ hiểu hơn nhiều:
Giờ hãy nâng độ phức tạp lên một chút. Để có thể trả lời các câu như
What are the minimum values of some_column_from_the_table with respect to another_column_from_the_table?, bạn cần kết hợp một hàm tổng hợp với mệnh đềGROUP BY. Hãy xem cách làm.Hàm tổng hợp + GROUP BY + hơn nữa
Giả sử bạn muốn tạo một báo cáo hiển thị
country_namevà tổng số nợ của họ. Ví dụ như sau:Những báo cáo như thế này rất thường gặp trong thực tế. Vậy truy vấn nào để có báo cáo như vậy? Bạn sẽ phải dùng hàm
SUM()trêndebt. Và bạn cũng cần hiển thịcountry_namecùng tổng nợ. Hãy chạy truy vấn sau:Có phải nó tạo ra lỗi sau không?
Giờ hãy hiểu ý nghĩa thực sự của thông báo này. Khi bạn dùng một hàm tổng hợp (như
SUM()cùng một cột chưa tổng hợp nhưcountry_name) bạn cần đưa cột chưa tổng hợp đó vào mệnh đềGROUP BY. Vậy truy vấn đúng sẽ là:Và kết quả là chính xác:
Lưu ý cách dùng bí danh trong truy vấn.
Giờ giả sử bạn cần sắp xếp báo cáo này theo
total_debtgiảm dần. Nhớ mệnh đềORDER BYchứ? Đúng vậy, bạn cũng có thể kết hợp hàm tổng hợp với mệnh đềORDER BY:Kết quả giờ sẽ được sắp xếp ngược:
Lưu ý cột bạn dùng trong mệnh đề
ORDER BY.Giờ là một câu hỏi quan trọng khác:
Bạn sẽ cần dùng hàm
MAX()ở đây. Viết truy vấn để trả lời câu hỏi này giờ sẽ không khó nữa.Và bạn có một báo cáo gọn gàng:
Bạn cũng có thể giới hạn số dòng trong các báo cáo như thế này. Giả sử bạn chỉ muốn lấy 5 mục hàng đầu trong báo cáo trên. Bạn có thể làm bằng mệnh đề
LIMIT.Đến lúc cho báo cáo cuối cùng của hướng dẫn này. Bạn cần thêm tên quốc gia vào báo cáo trên. Làm thế nào? Truy vấn sau sẽ giúp bạn làm điều đó:
Một báo cáo tốt khác:
Trong truy vấn trên, bạn đã thêm cột
country_namesau mệnh đềSELECTvà cũng thêm nó sauGROUP BY. Bạn có thể mở rộng mẫu này cho bất kỳ số lượng cột nào.Thứ tự của
GROUP BY,ORDER BYvàLIMITrất quan trọng khi tạo các báo cáo như thế. Nếu bạn thay đổi thứ tự, bạn sẽ gặp lỗi. Tự mình kiểm chứng nhé:Và bạn nhận được:
Trong truy vấn trên, bạn đặt mệnh đề
ORDER BYtrướcGROUP BYđiều này là không được phép. Thực tế, ngay cả khi bạn không dùng hàm tổng hợp thì cũng không áp dụng được. Thứ tự đúng là -GROUP BY->ORDER BY->LIMIT. Hãy luôn ghi nhớ.Tiến xa hơn
Chúc mừng! Bạn đã đi đến cuối hướng dẫn này. Trong hướng dẫn này, bạn đã biết về các hàm tổng hợp khác nhau trong PostgreSQL và cách dùng chúng để tạo ra các báo cáo hữu ích. Đây thực sự là những kỹ năng quan trọng cho một nhà khoa học dữ liệu. Để nâng cao kỹ năng SQL một cách hệ thống, bạn có thể học các khóa DataCamp sau: