SQL đã thiết kế sẵn nhiều hàm tích hợp để người dùng có thể biến đổi và xử lý dữ liệu một cách dễ dàng. Các hàm này có thể chia nhành một số nhóm cơ bản, bao gồm:
- Hàm vô hướng
- Hàm logic
- Hàm ranking
- Hàm rowset
- Hàm aggregate
Trong bài viết này, mình sẽ giới thiệu tới các bạn lần lượt từng nhóm hàm này.
Các nội dung chính
Hàm tích hợp Scalar
Các hàm Scalar (hàm vô hướng) trả về một giá trị duy nhất và thường hoạt động trên một hàng dữ liệu. Số lượng giá trị đầu vào mà chúng lấy có thể là 0 (ví dụ: GETDATE), một (ví dụ: UPPER) hoặc nhiều (ví dụ: ROUND). Vì các hàm vô hướng luôn trả về một giá trị duy nhất, chúng có thể được sử dụng ở bất cứ đâu mà một giá trị (kết quả) có thể tồn tại theo đúng nghĩa của nó. Chúng được sử dụng phổ biến nhất trong mệnh đề SELECT và vị từ mệnh đề WHERE. Chúng cũng có thể được sử dụng trong mệnh đề SET của câu lệnh UPDATE.
Các hàm Scalar có sẵn có thể được chia thành nhiều loại, chẳng hạn như chuỗi, chuyển đổi, logic, toán học và các loại khác. Một số lưu ý khi sử dụng các hàm scalar bao gồm:
- Tính xác định: Nếu hàm trả về cùng một giá trị cho cùng một giá trị đầu vào và cơ sở dữ liệu mỗi khi nó được gọi, chúng ta nói rằng nó là xác định. Ví dụ: ROUND (1.1, 0) luôn trả về giá trị 1.0. Tuy nhiên, cũng có nhiều hàm là không xác định. Ví dụ, GETDATE () trả về ngày và giờ hiện tại. Kết quả từ các hàm không xác định không thể có index, điều này gây ảnh hưởng đến khả năng của bộ xử lý truy vấn.
- Đối chiếu: Khi sử dụng các hàm thao tác với dữ liệu dạng ký tự, đối chiếu nào sẽ được sử dụng? Một số hàm sử dụng đối chiếu (thứ tự sắp xếp) của giá trị đầu vào; những người khác sử dụng đối chiếu của cơ sở dữ liệu nếu không có đối chiếu đầu vào nào được cung cấp.
Ví dụ các hàm tích hợp Scalar
Mình sẽ giới thiệu với các bạn một số hàm scalar thông thường thông qua các ví dụ ngay sau đây.
Ví dụ giả định sau sử dụng một số hàm về thời gian:
SELECT SalesOrderID,
OrderDate,
YEAR(OrderDate) AS OrderYear,
DATENAME(mm, OrderDate) AS OrderMonth,
DAY(OrderDate) AS OrderDay,
DATENAME(dw, OrderDate) AS OrderWeekDay,
DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;
Kết quả một phần được hiển thị bên dưới:
SalesOrderID | OrderDate | OrderYear | OrderMonth | OrderDay | OrderWeekDay | YearsSinceOrder |
71774 | 2008-06-01T00:00:00 | 2008 | June | 1 | Sunday | 13 |
… | … | … | … | … | … | … |
Ví dụ tiếp theo bao gồm các hàm toán học:
SELECT TaxAmt,
ROUND(TaxAmt, 0) AS Rounded,
FLOOR(TaxAmt) AS Floor,
CEILING(TaxAmt) AS Ceiling,
SQUARE(TaxAmt) AS Squared,
SQRT(TaxAmt) AS Root,
LOG(TaxAmt) AS Log,
TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;
Một phần kết quả:
TaxAmt | Rounded | Floor | Ceiling | Squared | Root | Log | Randomized |
70.4279 | 70.0000 | 70.0000 | 71.0000 | 4960.089098 | 8.392133221 | 4.254589491 | 28.64120429 |
… | .. | … | … | … | … | … | … |
Ví dụ sau sử dụng một số hàm text (văn bản):
SELECT CompanyName,
UPPER(CompanyName) AS UpperCase,
LOWER(CompanyName) AS LowerCase,
LEN(CompanyName) AS Length,
REVERSE(CompanyName) AS Reversed,
CHARINDEX(' ', CompanyName) AS FirstSpace,
LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;
Một phần kết quả:
CompanyName | UpperCase | LowerCase | Length | Reversed | FirstSpace | FirstWord | RestOfName |
A Bike Store | A BIKE STORE | a bike store | 12 | erotS ekiB A | 2 | A | Bike Store |
Progressive Sports | PROGRESSIVE SPORTS | progressive sports | 18 | stropS evissergorP | 12 | Progressive | Sports |
Advanced Bike Components | ADVANCED BIKE COMPONENTS | advanced bike components | 24 | stnenopmoC ekiB decnavdA | 9 | Advanced | Bike Components |
… | … | … | … | … | … | … | … |
Hàm logic
Các hàm logic đánh giá một biểu thức đầu vào và trả về một giá trị thích hợp dựa trên kết quả.
Hàm IIF
Hàm IIF đánh giá một biểu thức đầu vào Boolean và trả về một giá trị được chỉ định nếu biểu thức đánh giá là True và một giá trị thay thế nếu biểu thức cho giá trị là False.
Ví dụ: hãy xem xét truy vấn sau đây, truy vấn này đánh giá loại địa chỉ của khách hàng. Nếu giá trị là “Văn phòng chính”, biểu thức trả về “Thanh toán”. Đối với tất cả các giá trị kiểu địa chỉ khác, biểu thức trả về “Gửi thư”.
SELECT AddressType,
IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;
Một phần kết quả:
AddressType | UseAddressFor |
Main Office | Billing |
Shipping | Mailing |
… | … |
Hàm CHOOSE
Hàm CHOOSE đánh giá một biểu thức số nguyên và trả về giá trị tương ứng từ một danh sách dựa trên thứ tự vị trí của nó (bắt đầu từ 1).
SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;
Kết quả:
SalesOrderID | Status | OrderStatus |
1234 | 3 | Delivered |
1235 | 2 | Shipped |
1236 | 2 | Shipped |
1237 | 1 | Ordered |
… | … | … |
Hàm Ranking
Các hàm ranking (xếp hạng) cho phép bạn thực hiện các phép tính dựa trên tập hợp các hàng xác định.
Ví dụ này sử dụng hàm RANK để tính toán xếp hạng dựa trên ListPrice theo thứ tự giảm dần (giá cao nhất xếp hạng 1):
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Kết quả:
ProductID | Name | ListPrice | RankByPrice |
749 | Road-150 Red, 62 | 3578.27 | 1 |
750 | Road-150 Red, 44 | 3578.27 | 1 |
751 | Road-150 Red, 48 | 3578.27 | 1 |
771 | Mountain-100 Silver, 38 | 3399.99 | 4 |
772 | Mountain-100 Silver, 42 | 3399.99 | 4 |
775 | Mountain-100 Black, 38 | 3374.99 | 6 |
… | … | … | … |
Bạn có thể sử dụng mệnh đề OVER để xác định phân vùng hoặc nhóm trong dữ liệu. Ví dụ: truy vấn sau mở rộng ví dụ trước để tính toán xếp hạng dựa trên giá cho các sản phẩm trong mỗi danh mục.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
Kết quả:
Category | Product | ListPrice | RankByPrice |
Bib-Shorts | Men’s Bib-Shorts, S | 89.99 | 1 |
Bib-Shorts | Men’s Bib-Shorts, M | 89.99 | 1 |
Bike Racks | Hitch Rack – 4-Bike | 120 | 1 |
Bike Stands | All-Purpose Bike Stand | 159 | 1 |
Bottles and Cages | Mountain Bottle Cage | 9.99 | 1 |
Bottles and Cages | Road Bottle Cage | 8.99 | 2 |
Bottles and Cages | Water Bottle – 30 oz. | 4.99 | 3 |
Bottom Brackets | HL Bottom Bracket | 121.49 | 1 |
Bottom Brackets | ML Bottom Bracket | 101.24 | 2 |
Bottom Brackets | LL Bottom Bracket | 53.99 | 3 |
… | … | … | … |
Hàm Rowset
Các hàm Rowset trả về một bảng ảo có thể được sử dụng trong mệnh đề FROM làm nguồn dữ liệu. Các hàm này nhận các tham số cụ thể cho chính hàm rowet. Chúng bao gồm OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML và OPENJSON.
Các hàm OPENDATASOURCE, OPENQUERY và OPENROWSET cho phép bạn chuyển một truy vấn đến một máy chủ cơ sở dữ liệu từ xa. Máy chủ từ xa sau đó sẽ trả về một tập hợp các hàng kết quả. Ví dụ: truy vấn sau sử dụng OPENROWSET để lấy kết quả của truy vấn từ phiên bản SQL Server có tên là SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Lưu ý: Để sử dụng máy chủ từ xa, bạn phải bật một số tùy chọn nâng cao trong phiên bản SQL Server nơi bạn đang chạy truy vấn.
Các hàm OPENXML và OPENJSON cho phép bạn truy vấn dữ liệu có cấu trúc ở định dạng XML hoặc JSON và trích xuất các giá trị vào tập hợp hàng dạng bảng.
Hàm Aggregate
T-SQL cung cấp các hàm tổng hợp (hàm aggregate) như SUM, MAX và AVG để thực hiện các phép tính nhận nhiều giá trị và trả về một kết quả duy nhất. Mình đã chia sẻ về các hàm aggregate trong một bài viết riêng, các bạn có thể tìm đọc tại đây.
Các bạn có thể đọc kỹ hơn về các hàm trong SQL tại đây nhé.