SQL trong Data Analysis: Một số hàm thường dùng trong Postgresql

Xin chào bạn đã quay lại bài viết trong chuỗi bài SQL trong Data Analysis. Ở bài này chúng ta sẽ điểm qua một số hàm (function) có sẵn và hay được dùng nhất trong Postgresql.

Các hàm toán học (Mathematical Functions)

Các hàm hay dùng bao gồm

• SQRT(): Tính căn bậc 2 của một số.
• POWER(): Tính số mũ của một số, gồm 02 tham số, số căn bậc và số mũ.
• ROUND(): Làm tròn số theo dấu chấm động.
• CEIL(): Làm tròn lên số thập phân lên số nguyên.
• FLOOR(): Làm tròn xuống số thập phân xuống số nguyên.
• ABS(): Giá trị tuyệt đối của một số.

Chúng ta sẽ chạy các đoạn SQL dưới đây để hiểu rõ hơn nhé.

-- Calculate the square root of a number
SELECT sqrt(25); -- Result: 5

-- Calculate the power of a number
SELECT power(5, 2); -- Result: 25

-- Round a number to 2 decimal places
SELECT round(3.14159, 2); -- Result: 3.14

-- Round a number up to nearest whole number
SELECT ceil(3.14159); -- Result: 4

-- Round a number down to nearest whole number
SELECT floor(3.14159); -- Result: 3

-- Get the absolute value of a number.
select abs(-2); -- result: 2

Như vậy, bạn chỉ cần thay các con số thành các cột dữ liệu số trong các bảng dữ liệu để có thể thao tác.

Các hàm xử lý chuỗi (String Functions)

Bao gồm các hàm hay sử dụng như sau:

• CONCAT(): Nối 02 hay nhiều chuỗi thành một chuỗi.
• LENGTH(): Trả về độ dài của một chuỗi.
• SUBSTRING(): Cắt chuỗi thành một chuỗi nhỏ hơn.
• UPPER(): Chuyển đổi tất cả kí tự trong chuỗi thành kí tự in hoa.
• LOWER(): Chuyển đổi tất cả kí tự trong chuỗi thành kí tự in thường.
• TRIM(): Xóa khoản trắng cả đầu và đuôi của một chuỗi.

Ví dụ:

-- Concatenate two strings
SELECT concat('Vn', 'DataProduct'); -- Result: VnDataProduct

-- Get the length of a string
SELECT length('VnDataProduct'); -- Result: 13

-- Extract a substring from a string
SELECT substring('VnDataProduct', 3, 13); -- Result: DataProduct

-- Convert a string to uppercase
SELECT upper('vndataproduct'); -- Result: VNDATAPRODUCT

-- Convert a string to lowercase
SELECT lower('VNDATAPRODUCT'); -- Result: vndataproduct

-- Remove leading and trailing whitespace from a string
SELECT trim('   VnDataProduct   '); -- Result: "VnDataProduct" (without leading/trailing whitespace)

Các hàm tổng hợp (Aggregate Functions)

Các bạn có thể theo dõi ở bài viết này. Với một Data Analyst thì các hàm tổng hợp tương đối quan trọng.

Các hàm xử lý ngày và giờ (Date and Time Functions)

Danh sách các hàm xử lý thời gian thường dùng trong Postgresql:

• NOW(): Trả về thời gian hiện tại theo ngày và giờ.
• EXTRACT(): Lấy năm, tháng, ngày, giờ… từ kiểu dữ kiểu dữ liệu ngày và timestamp. Ví dụ hôm nay là 2023-06-20, nếu bạn muốn lấy tháng hiện tại, thì sẽ dùng hàm extract() để lấy ra 6.
• DATE_TRUNC(): Làm tròn kiểu dữ liệu ngày tới tháng, ngày, giờ mong muốn. Ví dụ, bây giờ là 15 giờ ngày 2023-06-20, bạn muốn lấy ngày đầu tháng thôi (ví dụ cho trường hợp bạn muốn group by dữ liệu) thì sẽ cần dùng câu lệnh này.
• AGE(): Tính số thời gian khác nhau giữa 02 dữ liệu thời gian.
• INTERVAL(): Interval là chu kỳ, ví dụ bạn muốn biết cách đây 02 tuần của ngày hôm nay (2023-06-20) là ngày mấy (2023-06-06).

-- Get the current date and time
SELECT now(); -- Example result: 2023-06-20 10:30:00

-- Extract the year from a date
SELECT extract(year FROM date '2023-09-15'); -- Result: 2023

-- Calculate the age in years between two dates
SELECT age('2000-01-01', '2023-06-20'); -- Result: 23 years 5 months 19 days

-- Truncate a timestamp to the nearest hour
SELECT date_trunc('hour', timestamp '2023-06-20 14:32:45'); -- Result: 2023-06-20 14:00:00
-- Truncate a date to the nearest month
SELECT date_trunc('month', date '2023-06-20'); -- Result: 2023-06-01

-- Subtract an interval of 2 weeks from a date
SELECT date '2023-06-20' - interval '2 weeks'; -- Result: 2023-06-06

Các hàm điều kiện (Conditional Functions)

Các hàm thông dụng bao gồm

• CASE: Dựa trên một số điều kiện để trả về kết quả tương ứng với điều kiện đó. Ví dụ: Bạn có 100 sản phẩm, trong đó có 60 sản phẩm giá trên 100k và, 40 sản phẩm giá dưới 100k. Bạn muốn tạo một cột dữ liệu phân loại giá “Trên 100k” và “Dưới 100k”. CASE sẽ phù hợp để bạn làm giải quyết vấn đề này.

Lần này chúng ta sẽ quay lại sử dụng bộ dữ liệu “brazilian-ecommerce” nhé.

select product_id , case when product_category_name = 'perfumaria' then 'Dầu thơm nè' else 'Not dầu thơm' end 
from "brazilian-ecommerce".olist_products_dataset opd ;

Kết quả

• COALESCE(): Trả về kết quả dữ liệu không NULL đầu tiên được truyền vào hàm COALESCE. Ví dụ bạn muốn xác định giá bán hiện tại của một sản phẩm. Gồm giá bán thường và giá khuyến mãi, nếu đang có khuyến mãi thì cột giá khuyến mãi sẽ có giá trị, ngược lại là NULL. Và điều kiện lấy là nếu có giá khuyến mãi thì lấy giá khuyến mãi, không thì sẽ lấy giá thường. Hàm COALESCE sẽ giúp bạn chuyện đó.

select product_id, opd.product_category_name, pcnt.product_category_name_english, 
	COALESCE(pcnt.product_category_name_english, opd.product_category_name) 
	from "brazilian-ecommerce".olist_products_dataset opd 
	left join "brazilian-ecommerce".product_category_name_translation pcnt 
		on opd.product_category_name  = pcnt.product_category_name where pcnt.product_category_name_english is null;

Câu truy vấn trên lấy danh mục của sản phẩm tiếng Bồ Đào Nha liên kết với bản dịch để lấy ra danh mục tiếng Anh. Đương nhiên sẽ có một số danh mục chưa được dịch, nên chúng ta sẽ cần lấy bản mặc định (tiếng Bồ Đào Nha). Kết quả

• NULLIF() sẽ trả về NULL dựa vào giá trị truyền vào trong hàm. Chỉ cần kết quả của giá trị bên trái giống với giá trị bên phải thì kết quả sẽ trả về NULL.

select product_id, product_category_name ,
nullif(product_category_name, 'perfumaria') from "brazilian-ecommerce".olist_products_dataset opd ;

Kết quả

Tổng kết

Các hàm trên rất thông dụng trong công việc thực tế trong ngành Data Analysis. Hy vọng qua bài viết này, các bạn có thể nắm bắt và thuộc được các hàm, hoặc ít nhất có thể nhớ trong Postgresql cũng có các hàm này để xử lý dữ liệu.

Xin cám ơn.