Advanced SQL Functions

SQL là một ngôn ngữ mạnh mẽ cho việc truy vấn và quản lý cơ sở dữ liệu. Trong SQL, có nhiều loại hàm nâng cao cho phép bạn thực hiện các tác vụ phức tạp và tính toán trên dữ liệu. Dưới đây là một số hàm và chức năng SQL nâng cao cùng với các ví dụ minh họa:

1. Window Function

Hàm cửa sổ (Window Function) trong SQL là một loại hàm mạnh mẽ cho phép bạn thực hiện các tính toán trên một tập hợp con của dữ liệu được xác định bởi cửa sổ (window). Hàm cửa sổ thường được sử dụng trong câu lệnh SELECT và thường đi kèm với mệnh đề OVER để xác định cửa sổ dữ liệu cụ thể.

Dưới đây là một số loại hàm cửa sổ phổ biến trong SQL:

ROW_NUMBER(): Hàm này gán một số thứ tự (số hàng) cho mỗi hàng trong kết quả dựa trên cửa sổ được xác định.

Ví dụ:

SELECT employee_id, first_name, last_name, ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM employees;

// Sort [90, 85, 85, 80] with ROW_NUMBER() = [1, 2, 3, 4]

RANK(): Hàm RANK() gán một số thứ tự cho mỗi hàng trong kết quả dựa trên cửa sổ và xếp hạng các giá trị theo thứ tự. Nếu có các giá trị giống nhau, chúng sẽ có cùng một hạng.

Ví dụ:

SELECT student_name, score, RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank
FROM exam_scores;

// Sort [90, 85, 85, 80] with RANK() = [1, 2, 2, 4]
// PARTITION BY là một phần của câu lệnh OVER() dùng để nhóm dữ liệu thành từng phần riêng biệt theo 1 cột xác định

DENSE_RANK(): Tương tự như RANK(), nhưng DENSE_RANK() không bỏ qua các hạng trống nếu có giá trị trùng lặp. Nó sẽ không có khoảng cách giữa các hạng trùng lặp.

Ví dụ:

SELECT student_name, score, DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank
FROM exam_scores;

 // Sort [90, 85, 85, 80] DENSE_RANK() = [1, 2, 2, 3]

NTILE(n): Hàm NTILE(n) chia dữ liệu thành n phần bằng nhau và gán một số thứ tự cho mỗi phần.

Ví dụ:

SELECT employee_name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

LEAD() và LAG(): Hàm LEAD() và LAG() cho phép bạn truy cập giá trị của hàng liền kề sau hoặc trước trong kết quả.

Ví dụ:

SELECT order_date, revenue, LEAD(revenue) OVER (ORDER BY order_date) AS next_month_revenue
FROM monthly_sales;

FIRST_VALUE() và LAST_VALUE(): Hàm FIRST_VALUE() trả về giá trị của cột được chỉ định trong hàng đầu tiên của cửa sổ, trong khi hàm LAST_VALUE() trả về giá trị của cột được chỉ định trong hàng cuối cùng của cửa sổ.

Ví dụ:

SELECT order_date, revenue, FIRST_VALUE(revenue) OVER (ORDER BY order_date) AS first_month_revenue
FROM monthly_sales;

Những hàm cửa sổ này cung cấp khả năng phân tích và trích xuất thông tin phức tạp từ dữ liệu trong cơ sở dữ liệu. Chúng là một công cụ mạnh mẽ trong SQL cho việc thực hiện các tính toán và phân tích dựa trên cửa sổ dữ liệu cụ thể.

2. Aggregate Function

Các hàm tổng hợp (Aggregate Function) trong SQL là các hàm được sử dụng để thực hiện tính toán trên một tập hợp của các giá trị và trả về kết quả tổng hợp của các giá trị đó. Dưới đây là một số loại hàm tổng hợp phổ biến trong SQL:

SUM(): Hàm SUM() tính tổng của các giá trị trong một cột.

Ví dụ:

SELECT SUM(salary) AS total_salary
FROM employees;

AVG(): Hàm AVG() tính trung bình của các giá trị trong một cột.

Ví dụ:

SELECT AVG(score) AS average_score
FROM exam_scores;

COUNT(): Hàm COUNT() đếm số lượng hàng trong một tập hợp. Nó có thể được sử dụng để đếm tất cả hoặc đếm các hàng có giá trị không null trong một cột.

Ví dụ:

SELECT COUNT(*) AS total_students
FROM students;

MIN() và MAX(): Hàm MIN() trả về giá trị nhỏ nhất trong một cột, trong khi hàm MAX() trả về giá trị lớn nhất.

Ví dụ:

SELECT MIN(age) AS youngest_age, MAX(age) AS oldest_age
FROM employees;

STDEV() và VARIANCE(): Hàm STDEV() tính độ lệch chuẩn của các giá trị trong một cột, trong khi hàm VARIANCE() tính phương sai của các giá trị.

Ví dụ:

SELECT STDEV(salary) AS salary_std_dev, VARIANCE(salary) AS salary_variance
FROM employees;

SUM() DISTINCT: Các hàm tổng hợp cũng có thể được sử dụng với từ khóa DISTINCT để tính tổng hoặc trung bình của các giá trị duy nhất.

Ví dụ:

SELECT SUM(DISTINCT sales_amount) AS total_unique_sales
FROM sales;

Những hàm tổng hợp này cho phép bạn thực hiện các phân tích dựa trên dữ liệu trong cơ sở dữ liệu và trích xuất thông tin thống kê quan trọng. Chúng là một phần quan trọng của SQL để tính toán và tổng hợp dữ liệu trong các truy vấn phức tạp.

3. Numeric Functions

Các hàm số (Numeric Functions) trong SQL là các hàm được sử dụng để thực hiện các phép toán số học hoặc thao tác trên các giá trị số. Dưới đây là một số loại hàm số phổ biến trong SQL:

Lưu ý: Đã bỏ qua các hàm AVG, SUM, COUNT, MAX, MIN vì bên đã giải thích

ABS(): Hàm ABS() trả về giá trị tuyệt đối của một số. Nó bỏ đi dấu âm (nếu có) và trả về giá trị dương tương ứng.

Ví dụ:

SELECT ABS(-10) AS absolute_value;

ROUND(): Hàm ROUND() làm tròn giá trị số đến một số chữ số thập phân cụ thể.

Ví dụ:

SELECT ROUND(3.14159265359, 2) AS rounded_value; -- Kết quả: 3.14

CEILING(): Hàm CEILING() làm tròn số lên đến số nguyên gần nhất lớn hơn hoặc bằng giá trị đầu vào.

Ví dụ:

SELECT CEILING(4.2) AS rounded_up_value; -- Kết quả: 5

FLOOR(): Hàm FLOOR() làm tròn số xuống đến số nguyên gần nhất nhỏ hơn hoặc bằng giá trị đầu vào.

Ví dụ:

SELECT FLOOR(4.9) AS rounded_down_value; -- Kết quả: 4

SQRT(): Hàm SQRT() tính căn bậc hai của một số.

Ví dụ:

SELECT SQRT(16) AS square_root; -- Kết quả: 4

PI(): Hàm PI() trả về giá trị số PI (π), một số phi tuyệt đối quan trọng trong toán học.

Ví dụ:

SELECT PI() AS pi_value; -- Kết quả: 3.141592653589793

Những hàm số này cho phép bạn thực hiện các phép toán số học và tính toán trên dữ liệu số trong cơ sở dữ liệu. Chúng rất hữu ích trong việc thực hiện các tính toán và phân tích dữ liệu số.

4. Analytic Functions

Hàm phân tích thực hiện các tính toán phân tích trên tập hợp dữ liệu, chẳng hạn như xác định hàng đầu và hàng cuối. Hàm ROWNUMBER(), RANK(),... hãy xem lại ở phần Window Function nhé!

5. Stored Procedures

Thủ tục lưu trữ là các chương trình SQL được lưu trữ trong cơ sở dữ liệu và có thể được gọi để thực hiện các tác vụ cụ thể.

Ví dụ:

CREATE PROCEDURE sp_get_employee_info (IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE employee_id = emp_id;
END;

Trong ví dụ này, chúng ta tạo một thủ tục lưu trữ để lấy thông tin của một nhân viên dựa trên ID.

6. String Functions

Các hàm xử lý chuỗi (String Functions) trong SQL được sử dụng để thực hiện các phép toán và biến đổi trên dữ liệu chuỗi (ký tự). Dưới đây là một số loại hàm chuỗi phổ biến trong SQL:

CONCAT(): Hàm CONCAT() được sử dụng để nối các chuỗi văn bản lại với nhau. Nó có thể nối nhiều chuỗi thành một chuỗi duy nhất. Ví dụ:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

SUBSTRING(): Hàm SUBSTRING() cho phép bạn trích xuất một phần của chuỗi dựa trên vị trí bắt đầu và độ dài. Ví dụ:

SELECT SUBSTRING(description, 1, 50) AS short_description
FROM products;

LENGTH(): Hàm LENGTH() trả về độ dài của một chuỗi (số ký tự). Ví dụ:

SELECT product_name, LENGTH(product_name) AS name_length
FROM products;

UPPER() và LOWER(): Hàm UPPER() chuyển đổi toàn bộ chuỗi thành chữ in hoa, trong khi hàm LOWER() chuyển đổi toàn bộ chuỗi thành chữ thường. Ví dụ:

SELECT UPPER(city) AS capitalized_city
FROM customers;

TRIM(): Hàm TRIM() loại bỏ các khoảng trắng không cần thiết từ đầu và cuối của chuỗi. Ví dụ:

SELECT TRIM('   hello   ') AS trimmed_text;

REPLACE(): Hàm REPLACE() thay thế một chuỗi con trong chuỗi khác bằng một chuỗi mới. Ví dụ:

SELECT REPLACE(description, 'old_text', 'new_text') AS updated_description
FROM products;

CONCAT_WS(): Hàm CONCAT_WS() hoạt động tương tự như CONCAT(), nhưng cho phép bạn chỉ định một dấu phân cách để nối các chuỗi. Ví dụ:

SELECT CONCAT_WS(', ', last_name, first_name) AS full_name
FROM employees;

LEFT() và RIGHT(): Hàm LEFT() trả về một số lượng ký tự bên trái của chuỗi, trong khi hàm RIGHT() trả về một số lượng ký tự bên phải của chuỗi. Ví dụ:

SELECT LEFT(address, 5) AS left_part
FROM customers;

Những hàm chuỗi này giúp bạn thực hiện các phép toán và biến đổi trên dữ liệu văn bản trong cơ sở dữ liệu. Chúng hữu ích trong việc xử lý và trích xuất thông tin từ các trường chuỗi.

7. Date Functions

Các hàm xử lý ngày tháng (Date Functions) trong SQL được sử dụng để thực hiện các phép toán và biến đổi trên dữ liệu thời gian và ngày tháng. Dưới đây là một số loại hàm ngày tháng phổ biến trong SQL:

GETDATE(): Hàm GETDATE() trả về ngày và giờ hiện tại của hệ thống. Ví dụ:

SELECT GETDATE() AS current_datetime;

DATEDIFF(): Hàm DATEDIFF() tính khoảng thời gian giữa hai ngày hoặc giờ. Nó trả về số lượng đơn vị thời gian (ngày, giờ, phút, giây) giữa hai thời điểm. Ví dụ:

SELECT DATEDIFF(day, start_date, end_date) AS day_difference
FROM events;

DATEADD(): Hàm DATEADD() thêm một khoảng thời gian cụ thể (ngày, giờ, phút, giây) vào một ngày hoặc giờ cụ thể. Ví dụ:

SELECT DATEADD(month, 3, order_date) AS new_order_date
FROM orders;

CONVERT(): Hàm CONVERT() được sử dụng để chuyển đổi giá trị ngày tháng từ một định dạng sang một định dạng khác. Ví dụ:

SELECT CONVERT(VARCHAR, order_date, 101) AS formatted_date
FROM orders;

DAY(), MONTH(), YEAR(): Các hàm DAY(), MONTH(), và YEAR() trích xuất ngày, tháng và năm từ một giá trị ngày tháng. Ví dụ:

SELECT order_date, DAY(order_date) AS order_day, MONTH(order_date) AS order_month, YEAR(order_date) AS order_year
FROM orders;

DATEPART(): Hàm DATEPART() trích xuất một phần cụ thể của ngày tháng, chẳng hạn như ngày, tháng hoặc năm, từ một giá trị ngày tháng. Ví dụ:

SELECT order_date, DATEPART(day, order_date) AS order_day, DATEPART(month, order_date) AS order_month
FROM orders;

EOMONTH(): Hàm EOMONTH() trả về ngày cuối cùng của tháng dựa trên một ngày cụ thể. Ví dụ:

SELECT invoice_date, EOMONTH(invoice_date) AS end_of_month
FROM invoices;

FORMAT(): Hàm FORMAT() cho phép bạn định dạng ngày tháng dưới dạng chuỗi với định dạng tùy chỉnh. Ví dụ:

SELECT FORMAT(order_date, 'dd/MM/yyyy') AS formatted_date
FROM orders;

Những hàm ngày tháng này giúp bạn thực hiện các phép toán và biến đổi trên dữ liệu thời gian và ngày tháng trong cơ sở dữ liệu. Chúng hữu ích trong việc tính toán khoảng thời gian, định dạng ngày tháng và trích xuất thông tin thời gian từ các trường dữ liệu.

8. Conditional

Có một số hàm và phép toán điều kiện (Conditional Functions) trong SQL giúp bạn thực hiện các phép so sánh và kiểm tra điều kiện. Dưới đây là một số loại phép toán điều kiện phổ biến:

CASE WHEN: Câu lệnh CASE WHEN được sử dụng để thực hiện các phép so sánh và trả về kết quả dựa trên điều kiện được xác định.

Ví dụ:

SELECT name, age,
    CASE
        WHEN age >= 18 THEN 'Adult'
        WHEN age >= 13 THEN 'Teenager'
        ELSE 'Child'
    END AS age_group
FROM students;

COALESCE(): Hàm COALESCE() thường được sử dụng khi bạn muốn xử lý (thay thế bằng giá trị mặc định) giá trị NULL. Ví dụ khi bạn có một kho hàng, có những sản phẩm được lưu với giá trị NULL nhưng bạn không muốn hiển thị nó ra với giá trị NULL mà muốn thay thế bằng 0.

Ví dụ:

SELECT product_name, COALESCE(price, 0) AS final_price
FROM products;

NULLIF(): Hàm NULLIF() so sánh hai giá trị. Nếu hai giá trị bằng nhau, nó trả về NULL; nếu không, nó trả về giá trị đầu tiên. Ví dụ khi bạn muốn tìm ra các sinh viên có điểm số bằng 90 hoặc có số điểm là NULL.

Ví dụ:

SELECT students.*
FROM students
WHERE 
    NULLIF(score, 90) IS NULL;

IIF(): Hàm IIF() hoạt động giống như câu lệnh IF trong lập trình. Nó kiểm tra một điều kiện và trả về một giá trị nếu điều kiện đúng và một giá trị khác nếu điều kiện sai.

Ví dụ:

SELECT name, age, IIF(age >= 18, 'Adult', 'Child') AS age_group
FROM students;

Các phép toán điều kiện này giúp bạn thực hiện các phép so sánh, kiểm tra điều kiện và xử lý giá trị dựa trên các điều kiện trong câu truy vấn SQL. Chúng giúp bạn tạo ra các cột tính toán và xử lý dữ liệu linh hoạt.