SQL trong Data Analysis: Procedure và Function – 2 công cụ không thể thiếu

Xin chào các bạn đã quay trở lại chuỗi bài SQL trong Data Analysis 101. Ở bài này, chúng ta sẽ được học một trong các kỹ thuật quan trọng trong việc xây dựng dữ liệu. Sử dụng Procedure và Function.

Giới thiệu

Procedure và Function là hai thành phần quan trọng trong PostgreSQL, cho phép chúng ta thực hiện các tác vụ phức tạp và có thể tái sử dụng truy vấn trong cơ sở dữ liệu của chúng ta.

Ví dụ: bạn có một truy vấn kiểm tra độ tuổi và giới tính của khách hàng thuộc nhóm nào. Đoạn truy vấn này lâu dần được sử dụng bởi nhiều team khác nhau. Và trong một thời điểm nào đó, sếp bảo bạn thay đổi lại cách tính nhóm khách hàng. Bạn sẽ thấy vấn đề bỗng phát sinh, đoạn truy vấn được sử dụng rộng rãi, giờ thay đổi lại cách tính đồng nghĩa mọi team khác phải thay đổi theo. Bạn nên làm gì (DRY – Don’t Repeat Yourself)?

Procedure là gì?

Procedure (Thủ Tục) trong PostgreSQL là một tập hợp các câu lệnh SQL được nhóm vào chung một đoạn truy vấn và được đặt tên. Chúng được định nghĩa bởi từ khóa “PROCEDURE” và có thể nhận tham số (parameters) đầu vào và trả về kết quả (result) đầu ra. Các thủ tục được lưu trữ trong cơ sở dữ liệu và có thể được gọi và thực thi từ ứng dụng hoặc bởi một câu truy vấn khác.

Đặc điểm của Procedure

• có thể chứa các lệnh SQL, các câu lệnh điều khiển (ví dụ: IF, LOOP) để thực hiện các tác vụ phức tạp.
• có thể được gọi từ các Function hoặc câu lệnh SQL.
• không trả kết quả về như Function.
• Chúng ta có thể truyền các tham số vào Procedure và có thể sử dụng bởi các truy vấn bên trong Procedure.
• Trong PostgreSQL, Function không hỗ trợ một transaction, nhưng Procedure thì có. Transaction ý chỉ một nhóm các truy vấn được thực thi cùng nhau, nếu có bất kỳ câu lệnh nào bị lỗi thì toàn bộ kết quả của các truy vấn trước đó sẽ bị hồi lại.

Ví dụ

CREATE OR REPLACE PROCEDURE check_avg_weight(input_cate_name varchar)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    total_products INT;
    average_weight FLOAT;
BEGIN
    SELECT avg(product_weight_g), count(product_id) into average_weight, total_products
    FROM "brazilian-ecommerce".olist_products_dataset
    WHERE product_category_name = input_cate_name;
    IF total_products > 0 THEN
        RAISE NOTICE 'Average weight for category % is: %', input_cate_name, average_weight;
    ELSE
        RAISE EXCEPTION 'No products found for category %', input_cate_name;
    END IF;
END;$procedure$;

Kết quả

call check_avg_weight('perfumaria');
-> 
Average weight for category perfumaria is: 529.2569124423964

call check_avg_weight('error_for_sure');
->
SQL Error [P0001]: ERROR: No products found for category error_for_sure
Where: PL/pgSQL function check_avg_weight(character varying) line 12 at RAISE

Function là gì

Function trong PostgreSQL cũng là một tập hợp các câu lệnh SQL, nhưng Function sẽ có thể trả về kết quả mong muốn sau khi thực thi chuỗi lệnh SQL đó. Được định nghĩa bởi từ khóa “FUNCTION” và có thể nhận đầu vào và trả về kết quả. Các Functions cũng được lưu trữ trong cơ sở dữ liệu và có thể được gọi từ ứng dụng hoặc câu lệnh SQL.

Đặc điểm của Function

• trả về kết quả như một giá trị duy nhất, ví dụ: số, chuỗi, bảng kết quả, vv. Lưu ý: mặc dù chỉ trả về một giá trị duy nhất, nhưng giá trị này có thể định nghĩa là một dòng dữ liệu, JSON, hoặc RECORDS etc… Điều này có nghĩa là bạn có thể gắn nhiều giá trị vào trong các dạng dữ liệu đó.
• Tương tự Procedure, Function các lệnh SQL, các câu lệnh điều khiển (ví dụ: IF, LOOP) để thực hiện các tác vụ phức tạp.
• Chúng ta có thể truyền tham số vào Function và sử dụng chúng trong Function.
• có thể được gọi từ câu lệnh SQL hoặc trong Procedure.

CREATE OR REPLACE FUNCTION get_products_weight_more_than(gram int) RETURNS INT
LANGUAGE plpgsql
AS $function$
DECLARE
    products_count INT;
BEGIN
    SELECT COUNT(*) INTO products_count
    FROM "brazilian-ecommerce".olist_products_dataset
    WHERE product_weight_g > gram;
    RETURN products_count;
END;
$function$;

Kết quả

select get_products_weight_more_than(2000);

-> 7749

Ứng dụng của Procedure và Function trong PostgreSQL

• Tối ưu hóa: Procedure và Function cho phép chúng ta viết mã phức tạp một lần và sử dụng lại ở nhiều nơi trong cơ sở dữ liệu hoặc được gọi từ ứng dụng. Note: với trường hợp là một DA, Procedure và Function còn có thể giúp bạn tự động build dữ liệu theo nhu cầu.
• Bảo mật: Chúng ta có thể thực hiện các thao tác nhất định qua Procedure hoặc Function mà không cần cấp quyền trực tiếp tới các tables cho người dùng, giúp bảo vệ dữ liệu của cơ sở dữ liệu.
• Dễ quản lý: cả hai giúp tổ chức truy vấn thành các đơn vị nhỏ hơn, dễ quản lý và duy trì.
• Hiệu suất: Việc sử dụng Procedure và/hoặc Function có thể cải thiện hiệu suất truy vấn bằng cách giảm lượng dữ liệu truyền qua mạng và tối ưu hóa các thao tác cơ sở dữ liệu.

Tổng kết

Procedure và Function trong PostgreSQL cung cấp cho chúng ta khả năng viết và thực thi một chuỗi truy vấn phức tạp trong cơ sở dữ liệu. Chúng giúp tối ưu hóa truy vấn, có thể tái sử dụng, bảo mật và quản lý dự án, cũng như cải thiện hiệu suất của cơ sở dữ liệu (do truy vấn được biên soạn, compiled, sẵn).

Đối với các bạn mong muốn trở thành nhận sự các vị trí Data (Data Engineer, Data Analysis), thì việc sử dụng cả hai rất quan trọng. Vì nó giúp các bạn dễ dàng xây dựng các dataset. Vì theo kinh nghiệm cá nhân, theo thời gian số lượng dataset các bạn phải quản lý sẽ rất lớn, vài trăm là bình thường. Nên cần phải chỉnh chu từ bây giờ.

Hi vọng bài viết này đã giúp bạn hiểu rõ hơn về Procedure và Function trong PostgreSQL và cách chúng hoạt động.