Hàm số học trong SQLite: ROUND, ABS, CEIL, FLOOR

SQLite có nhiều hàm toán học hơn bạn nghĩ

SQLite nổi tiếng là tối giản, nhưng nó có đầy đủ các hàm số học: làm tròn, giá trị tuyệt đối, làm tròn lên và xuống, lũy thừa, căn, logarit, lượng giác, số ngẫu nhiên. Phần lớn các hàm toán học được thêm vào từ SQLite 3.35 (2021), nên mọi bản cài tương đối mới (bản đi kèm Python, Node, tổ tiên WebSQL trong trình duyệt của bạn, hay CLI chính thức) đều đã có sẵn.

Đây là vài ví dụ nhanh trước khi đi sâu:

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

Sáu hàm, một hàng kết quả. Phần còn lại của trang sẽ đi qua công dụng của từng nhóm hàm và những điểm cần lưu ý.

ROUND: hàm bạn sẽ dùng nhiều nhất

ROUND(value, digits) làm tròn tới số chữ số thập phân cho trước. Đối số thứ hai là tùy chọn: bỏ đi thì bạn được làm tròn tới số nguyên gần nhất (nhưng vẫn là số thực):

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

Vài điều cần chú ý:

• ROUND(3.14159) trả về 3.0, không phải 3. Nếu muốn số nguyên, dùng CAST(ROUND(x) AS INTEGER), hoặc chỉ CAST(x AS INTEGER) nếu muốn cắt bỏ phần thập phân.
• SQLite làm tròn "nửa ra xa số 0": 2.5 thành 3, -2.5 thành -3. Một số cơ sở dữ liệu dùng kiểu làm tròn của ngân hàng (nửa về số chẵn); SQLite thì không.
• Đối số digits có thể âm: ROUND(1234.5, -2) làm tròn tới hàng trăm gần nhất, cho ra 1200.

Trong thực tế, bạn sẽ viết ROUND(price, 2) để hiển thị số tiền nhiều hơn bất cứ thứ gì khác.

ROUND và CAST: hai việc khác nhau

Nhiều người dùng CAST(x AS INTEGER) khi định làm tròn, và bị bất ngờ:

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

CAST cắt về phía số 0: nó chỉ đơn giản bỏ phần thập phân. ROUND làm tròn tới số nguyên gần nhất. Với 2.9, hai cách chênh nhau trọn một đơn vị. Hãy chọn hàm có đúng hành vi bạn cần.

ABS, SIGN và dấu của một số

ABS(x) trả về giá trị tuyệt đối. SIGN(x) trả về -1, 0 hoặc 1 tùy theo dấu:

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS được dùng nhiều nhất, tiện cho các truy vấn kiểu "hai giá trị này cách nhau bao xa". SIGN ít gặp hơn, nhưng hữu ích khi bạn muốn chia các hàng theo chiều (ghi nợ hay ghi có, lãi hay lỗ) mà không cần CASE tường minh.

CEIL, FLOOR và TRUNC

Các hàm này cho giá trị nguyên mà không làm tròn tới số gần nhất. CEIL luôn làm tròn lên, FLOOR luôn làm tròn xuống, TRUNC luôn cắt về phía số 0:

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

Hãy để ý các trường hợp số âm. FLOOR(-2.9) là -3 (xa số 0 hơn), nhưng TRUNC(-2.9) là -2 (về phía số 0). Với số âm, FLOOR và TRUNC cho kết quả khác nhau, và chọn sai là một lỗi lệch một đơn vị kinh điển.

CEILING là tên khác của CEIL. Dùng cách viết nào bạn thấy dễ đọc hơn.

Chia số nguyên mới là cái bẫy thật sự

Đây không phải một hàm mà là toán tử /, nhưng nó làm khó người mới nhiều hơn bất kỳ hàm toán học nào:

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

Khi cả hai vế là số nguyên, SQLite thực hiện phép chia số nguyên và cắt bỏ phần thập phân. Ngay khi một vế là REAL, cả biểu thức trở thành số thực. Cách sửa là đảm bảo ít nhất một toán hạng là số thực: viết 2.0 thay vì 2, hoặc ép kiểu.

Lỗi này gây hại nhiều nhất với tham chiếu cột: total_cents / 100 trả về số nguyên. total_cents / 100.0 mới trả về số đô la mà bạn thật sự cần.

MOD và toán tử %

MOD(x, y) trả về phần dư của x / y. Toán tử % làm điều tương tự:

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) và 17 % 5 đều trả về 2. Chia lấy dư cho 0 trả về NULL trong SQLite chứ không báo lỗi, khác với hầu hết các ngôn ngữ. Nếu điều này quan trọng với bạn, hãy kiểm tra số chia trước hoặc bọc lệnh gọi trong CASE WHEN y = 0 THEN ... END.

Dạng hàm và dạng toán tử dùng thay thế nhau được. Đa số mọi người chọn % vì nó ngắn hơn.

POWER, SQRT, EXP, LOG

Cho lũy thừa và căn:

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

Vài lưu ý hay khiến người dùng vấp:

• POW là tên khác của POWER.
• LOG(x) trong SQLite là logarit cơ số 10. LN(x) là logarit tự nhiên. LOG(b, x) với hai đối số là logarit cơ số b. (Điều này khác nhiều ngôn ngữ lập trình, nơi log là logarit tự nhiên; quy ước của SQL đã thắng.)
• SQRT của số âm trả về NULL, không báo lỗi.
• Theo quy ước, POWER(0, 0) trả về 1.

Các hàm này hữu ích khi tính lãi kép, quy đổi sang decibel, tính khoảng cách: bất cứ đâu có phép toán hình học hay hàm mũ.

RANDOM và RANDOMBLOB

RANDOM() trả về một số nguyên 64 bit có dấu, nằm bất kỳ đâu trong toàn bộ phạm vi của nó:

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

Để lấy một số trong một khoảng, hãy bọc bằng ABS (vì RANDOM() có dấu) rồi dùng %. Để lấy một số thực từ 0 đến 1, hãy chia cho số nguyên 64 bit lớn nhất. SQLite không có hàm RAND() sẵn trả về giá trị từ 0 đến 1: bạn phải tự tạo.

RANDOMBLOB(n) trả về n byte dữ liệu ngẫu nhiên, hữu ích để sinh token phiên đăng nhập hoặc dữ liệu kiểm thử. Kết hợp với HEX() để có chuỗi in ra được:

SELECT HEX(RANDOMBLOB(8)) AS token;

Mỗi lần gọi tạo ra một giá trị mới. Đừng mong RANDOM() trả về cùng một số hai lần trong cùng một hàng: ngay cả trong một biểu thức, mỗi lần gọi đều độc lập.

Kết hợp tất cả

Một ví dụ nhỏ: tính khoảng cách và làm tròn giá cho một bảng sản phẩm.

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Mug',     1299, 350.5),
    (2, 'Notebook', 850, 180.2),
    (3, 'Pen',      199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

Điểm mấu chốt là price_cents / 100.0: phần .0 biến phép chia thành số thực, rồi ROUND định dạng kết quả với hai chữ số thập phân. Nếu thiếu nó, 1299 / 100 sẽ cho 12 chứ không phải 12.99.

Tiếp theo: ngày và giờ

Các hàm số học lo phần tính toán. Ngày và giờ cần bộ công cụ riêng: SQLite lưu chúng dưới dạng văn bản, số thực hoặc số nguyên, và cung cấp một nhóm hàm nhỏ nhưng đủ mạnh để phân tích, định dạng và tính toán trên chúng. Đó là nội dung của trang tiếp theo.