TRY_CAST thay cho CAST
CAST('N/A' AS DECIMAL(18,2)) báo lỗi và dừng cả câu lệnh: một giá trị bẩn chặn 5020 dòng. TRY_CAST trả NULL cho giá trị không ép được và chạy tiếp.
Đổi lại, TRY_CAST không báo gì khi thất bại. Vì vậy sau mỗi lần ép kiểu phải đếm số NULL phát sinh. Con số đó là tín hiệu, không phải phiền toái:
- Bằng số lỗi đã biết từ lúc profiling (305 giá hỏng): logic đúng.
- Lớn hơn nhiều: logic ép kiểu đang sai ở đâu đó.
Ví dụ: quên bỏ , và VND rồi TRY_CAST(unit_price AS DECIMAL(18,2)) thẳng thì ra 4540 NULL thay vì 305. Giá "2,310,000 VND" hợp lệ nhưng không phải số cho tới khi được làm sạch chuỗi:
TRY_CAST(replace(replace(unit_price, ',', ''), ' VND', '') AS DECIMAL(18,2))
Tiền dùng DECIMAL, không dùng DOUBLE: số thực dấu phẩy động có sai số làm tròn khi cộng dồn.
Nhiều định dạng ngày
order_ts có ba định dạng. TRY_STRPTIME(chuỗi, định_dạng) trả NULL khi chuỗi không khớp định dạng; COALESCE lấy kết quả khác NULL đầu tiên:
COALESCE(
TRY_STRPTIME(order_ts, '%Y-%m-%d %H:%M:%S'),
TRY_STRPTIME(order_ts, '%d/%m/%Y %H:%M'),
TRY_STRPTIME(order_ts, '%Y-%m-%dT%H:%M:%S')
)
Sau đó đếm số dòng còn NULL. Còn NULL là còn định dạng chưa biết: bỏ định dạng thứ ba khỏi danh sách trên thì 1633 dòng ra NULL. Khi nguồn thêm định dạng thứ tư, phép đếm này là thứ báo cho bạn biết.
Chuẩn hoá giá trị phân loại
region có 9 cách viết cho 3 vùng: khác hoa thường, thừa khoảng trắng đầu cuối và giữa các từ. Ba hàm xử lý ba vấn đề:
| Hàm | Xử lý |
|---|---|
trim(region) |
khoảng trắng đầu và cuối |
regexp_replace(..., '\s+', ' ', 'g') |
nhiều khoảng trắng liền nhau thành một ('g': thay mọi chỗ khớp) |
lower(...) |
hoa thường |
Spark SQL và PostgreSQL có INITCAP để viết hoa chữ đầu mỗi từ; DuckDB không có hàm này, nên lab đưa về chữ thường. Viết kiểu nào không quan trọng bằng việc chỉ còn một kiểu.
Cách này không sửa được lỗi mất dấu: Hà Nội và Ha Noi vẫn là hai giá trị, cần một bảng ánh xạ. Hậu quả nếu bỏ qua: báo cáo theo vùng tách một vùng thành nhiều dòng, mỗi dòng doanh thu thấp, và giám đốc vùng có thể cắt ngân sách sai cả một quý.
Tương tự: status về lower(trim(status)) (còn 3 giá trị), channel rỗng về NULL bằng NULLIF(trim(channel), '') để chuỗi rỗng và NULL không thành hai nhóm.
flowchart LR D[_dedup_orders: chuỗi] -->|TRY_CAST, TRY_STRPTIME| T[_typed_orders: đúng kiểu] T --> N[Đếm NULL: 305 giá, 0 ngày] C[silver_customers] -->|trim, regexp_replace, lower| R[region: 3 giá trị]
Lệnh hay dùng
| Lệnh | Để làm gì |
|---|---|
SELECT count(*) FROM t WHERE TRY_CAST(... AS DECIMAL(18,2)) IS NULL |
đếm giá trị không ép được trước khi ghi bảng |
COALESCE(TRY_STRPTIME(c, fmt1), TRY_STRPTIME(c, fmt2)) |
đọc cột ngày có nhiều định dạng |
count(*) FILTER (WHERE cột IS NULL) |
đếm NULL từng cột sau khi ép kiểu |
DESCRIBE _typed_orders |
xem kiểu từng cột |
Lỗi hay gặp
- Dùng CAST trên dữ liệu chưa sạch. Pipeline chết giữa đêm vì một giá trị
TBD. - Dùng TRY_CAST mà không đếm NULL. Lỗi ép kiểu biến thành dữ liệu thiếu, không ai biết.
- Ép kiểu rồi ghi đè cột cũ ở bronze. Giá trị gốc mất; ép kiểu luôn ghi ra bảng mới.
- Chuẩn hoá một nửa. Chỉ
lowermà quên khoảng trắng thìmiền bắcvàmiền bắcvẫn là hai vùng.