SQL từ cơ bản đến nâng cao
Trang chủ / Module 3
🧱 Module 3 / 16 · 5-6 giờ

Kiểu dữ liệu, biểu thức và hàm thông dụng

Chọn đúng kiểu dữ liệu (đặc biệt tiền và thời gian), thành thạo hàm chuỗi/ngày giờ, CASE, COALESCE, ép kiểu — những thứ dùng trong gần như mọi truy vấn thực tế.

Kiểu dữ liệuNgày giờCASEChuỗi
📌 Vì sao module này quan trọng

Chọn sai kiểu dữ liệu là loại lỗi đắt nhất để sửa vì dữ liệu production đã nằm sẵn trong đó: tiền lưu bằng float cộng ra 0.30000000000000004, giờ lưu không kèm múi giờ lệch 7 tiếng khi chuyển server, email so sánh phân biệt hoa/thường làm tạo trùng tài khoản. Còn hàm ngày giờ và CASE là công cụ hàng ngày của mọi báo cáo.

🎯 Mục tiêu học tập

  • Chọn đúng kiểu cho số, tiền, chuỗi, boolean, ngày giờ; biết khi nào dùng numeric thay vì float
  • Làm việc đúng với timestamptz, múi giờ, date_trunc, extract, interval, generate_series
  • Thành thạo CASE, COALESCE, NULLIF, CAST (::) và các hàm chuỗi thông dụng
  • Tránh các bẫy: chia nguyên, làm tròn, so sánh chuỗi, múi giờ khi nhóm theo ngày

Số: integer, bigint, numeric, float — và quy tắc cho tiền

KiểuPhạm vi / đặc điểmDùng cho
smallint / integer / bigint2 / 4 / 8 byte; ±32K / ±2,1 tỷ / ±9,2×10¹⁸Đếm, id (bigint cho bảng lớn)
numeric(p,s)Chính xác tuyệt đối, chậm hơnTiền, tỉ lệ cần chính xác
real / double precisionDấu phẩy động nhị phân, xấp xỉĐo đạc khoa học, thống kê
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8 AS float_bang_nhau,
       0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_bang_nhau,
       0.1::float8 + 0.2::float8 AS tong_float;
Kết quả
 float_bang_nhau | numeric_bang_nhau |     tong_float
-----------------+-------------------+---------------------
 f               | t                 | 0.30000000000000004
(1 row)
❌ Lỗi thường gặp — Lưu tiền bằng float / double

Số thực nhị phân không biểu diễn chính xác 0.1, nên cộng dồn hàng triệu giao dịch sinh lệch từng đồng và đối soát không bao giờ khớp. Dùng numeric(12,2) (hoặc lưu số nguyên theo đơn vị nhỏ nhất như đồng/xu — bigint). Với VND không có phần lẻ, bigint hoặc numeric(14,0) đều ổn. Tránh kiểu money của PostgreSQL (phụ thuộc locale).

SELECT round(2.5) AS lam_tron_numeric,
       round(2.5::float8) AS lam_tron_float,
       round(1234.5678, 2) AS hai_so_le,
       round(1234.5678, -2) AS hang_tram,
       ceil(7 / 2.0) AS tran, floor(7 / 2.0) AS san, trunc(-3.7) AS cat;
Kết quả
 lam_tron_numeric | lam_tron_float | hai_so_le | hang_tram | tran | san | cat
------------------+----------------+-----------+-----------+------+-----+-----
                3 |              2 |   1234.57 |      1200 |    4 |   3 |  -3
(1 row)
💡 Làm tròn

round(numeric) làm tròn ra xa 0 (2,5 → 3) còn round(float8) theo quy tắc làm tròn về số chẵn gần nhất của chuẩn IEEE (2,5 → 2). Một lý do nữa để dùng numeric cho tiền. Làm tròn ở bước cuối cùng (hiển thị), không làm tròn từng dòng rồi mới cộng — sẽ tích luỹ sai số.

Chuỗi: text, varchar và các hàm hay dùng

Trong PostgreSQL text và varchar(n) hiệu năng như nhau; char(n) (độn khoảng trắng) nên tránh. Mặc định dùng text, thêm CHECK (length(x) <= n) nếu cần giới hạn.

SELECT full_name,
       upper(full_name)                       AS viet_hoa,
       length(full_name)                      AS so_ky_tu,
       split_part(email, '@', 1)              AS ten_dang_nhap,
       left(full_name, strpos(full_name, ' ') - 1) AS ho,
       replace(email, '@example.com', '')     AS bo_domain,
       lpad(id::text, 6, '0')                 AS ma_khach
FROM customers
ORDER BY id
LIMIT 4;
Kết quả
    full_name    |    viet_hoa     | so_ky_tu | ten_dang_nhap |  ho   | bo_domain | ma_khach
-----------------+-----------------+----------+---------------+-------+-----------+----------
 Trần Quốc Dũng  | TRẦN QUỐC DŨNG  |       14 | user1         | Trần  | user1     | 000001
 Lê Ngọc Lan     | LÊ NGỌC LAN     |       11 | user2         | Lê    | user2     | 000002
 Phạm Đức Phúc   | PHẠM ĐỨC PHÚC   |       13 | user3         | Phạm  | user3     | 000003
 Hoàng Minh Châu | HOÀNG MINH CHÂU |       15 | user4         | Hoàng | user4     | 000004
(4 rows)
SELECT trim('  xin chào  ') AS cat_khoang_trang,
       concat_ws(' - ', 'A', NULL, 'C') AS noi_bo_qua_null,
       'A' || NULL || 'C' AS toan_tu_noi_gap_null,
       substring('SKU-12345' FROM 5) AS tu_vi_tri_5,
       regexp_replace('0901-234-567', '\D', '', 'g') AS chi_giu_so,
       string_to_array('a,b,c', ',') AS thanh_mang;
Kết quả
 cat_khoang_trang | noi_bo_qua_null | toan_tu_noi_gap_null | tu_vi_tri_5 | chi_giu_so | thanh_mang
------------------+-----------------+----------------------+-------------+------------+------------
 xin chào         | A - C           | NULL                 | 12345       | 0901234567 | {a,b,c}
(1 row)
❌ Lỗi thường gặp — So sánh chuỗi bỏ qua chuẩn hoá

'A@x.com' = 'a@x.com' là FALSE nên có thể tạo hai tài khoản trùng email. Chuẩn hoá khi lưu (lower(trim(email))) và/hoặc tạo unique index trên biểu thức: CREATE UNIQUE INDEX ON users (lower(email)) (module 9, 11). Với tìm kiếm không phân biệt hoa/thường: ILIKE hoặc extension citext.

❌ Lỗi thường gặp — Dùng LIKE '%abc%' trên bảng lớn rồi thắc mắc vì sao chậm

Mẫu bắt đầu bằng % không dùng được index B-tree thông thường → quét toàn bảng. Giải pháp: pg_trgm + GIN index, hoặc full-text search (module 13).

Ngày giờ: date, timestamp, timestamptz, interval

KiểuLưu gìKhuyến nghị
dateChỉ ngàyNgày sinh, ngày hiệu lực
timestamp (without tz)Ngày giờ "trần", không múi giờHiếm khi nên dùng cho sự kiện thật
timestamptzMột thời điểm tuyệt đối (lưu UTC, hiển thị theo múi giờ phiên)Mặc định cho mọi thời điểm sự kiện
intervalKhoảng thời gianCộng/trừ thời gian
SELECT timestamptz '2024-03-01 00:30+07'                 AS gio_viet_nam,
       timestamptz '2024-03-01 00:30+07' AT TIME ZONE 'UTC'    AS gio_utc,
       (timestamptz '2024-03-01 00:30+07')::date                AS ngay_theo_mui_gio_phien,
       date_trunc('month', timestamptz '2024-03-17 15:45+07')   AS dau_thang,
       date_trunc('week',  timestamptz '2024-03-17 15:45+07')   AS dau_tuan_thu_hai,
       extract(dow FROM date '2024-03-17')                      AS thu_0_la_chu_nhat,
       extract(year FROM date '2024-03-17')                     AS nam;
Kết quả
      gio_viet_nam      |       gio_utc       | ngay_theo_mui_gio_phien |       dau_thang        |    dau_tuan_thu_hai    | thu_0_la_chu_nhat | nam
------------------------+---------------------+-------------------------+------------------------+------------------------+-------------------+------
 2024-03-01 00:30:00+07 | 2024-02-29 17:30:00 | 2024-03-01              | 2024-03-01 00:00:00+07 | 2024-03-11 00:00:00+07 |                 0 | 2024
(1 row)
SELECT date '2024-01-31' + interval '1 month'  AS cong_1_thang,
       date '2024-03-10' - date '2024-03-01'          AS so_ngay_chenh,
       age(date '2024-03-01', date '2019-03-01')       AS tuoi,
       now() - interval '7 days' < now()               AS bieu_thuc_hop_le,
       to_char(timestamptz '2024-03-17 15:45+07', 'DD/MM/YYYY HH24:MI') AS dinh_dang;
Kết quả
    cong_1_thang     | so_ngay_chenh |  tuoi   | bieu_thuc_hop_le |    dinh_dang
---------------------+---------------+---------+------------------+------------------
 2024-02-29 00:00:00 |             9 | 5 years | t                | 17/03/2024 15:45
(1 row)
-- generate_series: tạo dãy ngày để lấp 'ngày không có đơn' trong báo cáo
SELECT d::date AS ngay
FROM generate_series(date '2024-03-01', date '2024-03-05', interval '1 day') AS d;
Kết quả
    ngay
------------
 2024-03-01
 2024-03-02
 2024-03-03
 2024-03-04
 2024-03-05
(5 rows)
❌ Lỗi thường gặp — Nhóm theo ngày mà quên múi giờ

Đơn lúc 00:30 giờ Việt Nam là 17:30 UTC ngày hôm trước. Nếu server/phiên đặt UTC mà bạn GROUP BY ordered_at::date thì doanh thu "ngày 1" bị chuyển sang ngày 29/28 — báo cáo lệch so với kế toán. Luôn chuyển múi giờ rõ ràng: (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date, hoặc date_trunc('day', ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh'). Quy tắc: lưu UTC (timestamptz), hiển thị và nhóm theo múi giờ nghiệp vụ.

-- Đơn đặt sau nửa đêm giờ VN: ngày theo UTC và ngày theo VN khác nhau
SELECT id, ordered_at,
       (ordered_at AT TIME ZONE 'UTC')::date              AS ngay_utc,
       (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS ngay_vn
FROM orders
WHERE (ordered_at AT TIME ZONE 'UTC')::date <> (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date
ORDER BY id
LIMIT 4;
Kết quả
 id |       ordered_at       |  ngay_utc  |  ngay_vn
----+------------------------+------------+------------
  1 | 2024-02-01 03:00:00+07 | 2024-01-31 | 2024-02-01
  9 | 2024-04-14 03:00:00+07 | 2024-04-13 | 2024-04-14
 10 | 2024-05-16 03:00:00+07 | 2024-05-15 | 2024-05-16
 26 | 2024-03-11 03:00:00+07 | 2024-03-10 | 2024-03-11
(4 rows)
SELECT count(*) FILTER (WHERE (ordered_at AT TIME ZONE 'UTC')::date <> (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date) AS don_lech_ngay,
       count(*) AS tong_don
FROM orders;
Kết quả
 don_lech_ngay | tong_don
---------------+----------
            93 |      400
(1 row)
❌ Lỗi thường gặp — Cộng 1 tháng vào ngày 31

'2024-01-31' + interval '1 month' cho 2024-02-29 (kẹp về ngày cuối tháng), không phải 31/2 — và + 1 month + 1 month không bằng + 2 months trong một số trường hợp. Dùng khi tính hạn hợp đồng/đăng ký định kỳ phải kiểm tra quy tắc nghiệp vụ.

CASE, COALESCE, NULLIF, ép kiểu

CASE là "if/else" của SQL — là một biểu thức trả về giá trị, dùng được ở SELECT, WHERE, ORDER BY và bên trong hàm tổng hợp (module 4).

SELECT name, price,
       CASE
         WHEN price < 500000   THEN 'Rẻ'
         WHEN price < 5000000  THEN 'Trung bình'
         WHEN price < 20000000 THEN 'Cao cấp'
         ELSE 'Siêu cao cấp'
       END AS phan_khuc
FROM products
ORDER BY price
LIMIT 8;
Kết quả
           name           |   price   | phan_khuc
--------------------------+-----------+------------
 Cáp USB-C 1m             | 120000.00 | Rẻ
 Áo thun basic            | 199000.00 | Rẻ
 Chuột không dây          | 350000.00 | Rẻ
 Áo sơ mi công sở         | 399000.00 | Rẻ
 Quạt đứng cũ (ngừng bán) | 450000.00 | Rẻ
 Sạc nhanh 65W            | 490000.00 | Rẻ
 Bộ dao nhà bếp 6 món     | 590000.00 | Trung bình
 Áo khoác gió             | 690000.00 | Trung bình
(8 rows)
SELECT status,
       CASE status WHEN 'delivered' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END AS thu_tu_uu_tien
FROM orders
GROUP BY status
ORDER BY thu_tu_uu_tien, status;
Kết quả
  status   | thu_tu_uu_tien
-----------+----------------
 delivered |              1
 shipped   |              2
 cancelled |              3
 paid      |              3
 pending   |              3
(5 rows)
SELECT COALESCE(city, 'Chưa rõ')         AS thanh_pho,
       count(*)
FROM customers
GROUP BY COALESCE(city, 'Chưa rõ')
ORDER BY 2 DESC;
Kết quả
 thanh_pho | count
-----------+-------
 Hà Nội    |    12
 Hải Phòng |    11
 Cần Thơ   |    11
 Đà Nẵng   |    11
 TP.HCM    |    11
 Chưa rõ   |     4
(6 rows)
SELECT 10 / NULLIF(0, 0) AS chia_cho_nullif,
       '123'::int + 1        AS ep_kieu,
       CAST('2024-03-01' AS date) AS ep_ngay,
       '12abc'::int          AS ep_that_bai;
Lỗi PostgreSQL trả về
ERROR:  invalid input syntax for type integer: "12abc"
LINE 4:        '12abc'::int          AS ep_that_bai;
               ^
🧠 Cách chuyên gia — CASE đứng trong ORDER BY và WHERE

Sắp xếp theo thứ tự nghiệp vụ không theo bảng chữ cái: ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END. Lưu ý CASE trả về nhiều nhánh phải cùng kiểu; nhánh ELSE bị quên sẽ trả NULL (dễ quên khi chuyển thành cột báo cáo).

❌ Lỗi thường gặp — Chuyển kiểu hàng loạt làm cả truy vấn chết vì một dòng bẩn

x::int ném lỗi nếu một dòng không hợp lệ ('12abc') và cả truy vấn thất bại. Khi nhập dữ liệu bẩn: làm sạch trước bằng regex (CASE WHEN x ~ '^\d+$' THEN x::int END) hoặc từ PostgreSQL 16 dùng pg_input_is_valid(x, 'int').

🏋️ Bài tập thực hành

Phân loại khách theo thời gian gắn bóDễ

Với mỗi khách hàng (5 khách đầu theo id), hiển thị tên, ngày đăng ký (theo giờ Việt Nam) và nhãn: 'Cũ' nếu đăng ký trước 2024-03-01, ngược lại 'Mới'.

Xem lời giải & giải thích
SELECT id, full_name,
       (created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS ngay_dk,
       CASE WHEN created_at < '2024-03-01' THEN 'Cũ' ELSE 'Mới' END AS nhom
FROM customers ORDER BY id LIMIT 5;
Kết quả
 id |    full_name    |  ngay_dk   | nhom
----+-----------------+------------+------
  1 | Trần Quốc Dũng  | 2024-01-04 | Cũ
  2 | Lê Ngọc Lan     | 2024-01-07 | Cũ
  3 | Phạm Đức Phúc   | 2024-01-10 | Cũ
  4 | Hoàng Minh Châu | 2024-01-13 | Cũ
  5 | Vũ Gia Huy      | 2024-01-16 | Cũ
(5 rows)
Tên đăng nhập và tên riêngTrung bình

Từ customers.full_name (dạng 'Họ Đệm Tên'), lấy tên cuối cùng của 5 khách đầu (gợi ý: regexp_replace hoặc split_part với reverse).

Xem lời giải & giải thích
SELECT full_name,
       (regexp_match(full_name, '(\S+)$'))[1] AS ten
FROM customers ORDER BY id LIMIT 5;
Kết quả
    full_name    | ten
-----------------+------
 Trần Quốc Dũng  | Dũng
 Lê Ngọc Lan     | Lan
 Phạm Đức Phúc   | Phúc
 Hoàng Minh Châu | Châu
 Vũ Gia Huy      | Huy
(5 rows)

regexp_match trả về mảng các nhóm bắt được; [1] lấy nhóm đầu. Cách khác: reverse(split_part(reverse(full_name), ' ', 1)).

Doanh thu theo tuần với dãy ngày đầy đủKhó

Đếm số đơn mỗi ngày từ 2024-03-01 đến 2024-03-07 (theo giờ VN), kể cả ngày không có đơn phải hiện 0.

Xem lời giải & giải thích
SELECT d::date AS ngay, count(o.id) AS so_don
FROM generate_series(date '2024-03-01', date '2024-03-07', interval '1 day') AS d
LEFT JOIN orders o
       ON (o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date = d::date
GROUP BY d
ORDER BY d;
Kết quả
    ngay    | so_don
------------+--------
 2024-03-01 |      1
 2024-03-02 |      1
 2024-03-03 |      1
 2024-03-04 |      0
 2024-03-05 |      2
 2024-03-06 |      2
 2024-03-07 |      0
(7 rows)

Dãy ngày làm bảng bên trái, LEFT JOIN sang dữ liệu; count(o.id) (đếm cột bảng phải) cho 0 với ngày trống — nếu dùng count(*) sẽ ra 1. Chi tiết ở module 4 & 5.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Biết dùng numeric (không float) cho tiền và timestamptz cho thời điểm sự kiện
  • Nhóm theo ngày luôn kèm chuyển múi giờ nghiệp vụ rõ ràng
  • Dùng thành thạo CASE, COALESCE, NULLIF, ::cast và hàm chuỗi/ngày giờ chính
  • Biết cách tạo dãy ngày bằng generate_series để báo cáo không bị 'lỗ hổng' ngày trống