Đã có order_ts thì cần bảng ngày làm gì?
fact_orders.date_key cho biết đơn rơi vào ngày nào. Nhưng nhiều câu hỏi cần những thứ timestamp không chứa:
- Thuộc tính nghiệp vụ của ngày: quý, cuối tuần, ngày lễ, đợt khuyến mãi, quý tài chính (nhiều công ty không bắt đầu năm tài chính từ tháng 1). Tính một lần trong
dim_datethì mọi báo cáo dùng chung một định nghĩa. - Ngày không có sự kiện. Một ngày không có đơn thì không có dòng nào trong fact. Chỉ bảng ngày sinh từ lịch mới biết ngày đó tồn tại.
- Định nghĩa thống nhất. "Tuần" bắt đầu thứ Hai hay Chủ nhật? Ghi một lần ở
dim_date, không để mỗi báo cáo tự quyết.
Sinh cả năm, không sinh từ đơn
CREATE OR REPLACE TABLE dim_date AS
SELECT d::DATE AS date_key, year(d) AS year, quarter(d) AS quarter, month(d) AS month,
dayofweek(d) AS day_of_week, -- 0 = Chủ nhật, 6 = thứ Bảy
dayofweek(d) IN (0, 6) AS is_weekend
FROM generate_series(DATE '2025-01-01', DATE '2025-12-31', INTERVAL 1 DAY) t(d);
365 dòng, 104 ngày cuối tuần. Nếu sinh bằng SELECT DISTINCT order_ts::DATE FROM ... thì bảng ngày chỉ có những ngày đã có đơn, tức mất đúng thứ ta cần nó giữ.
Ngày lễ không suy ra được từ lịch: thường lưu ở một bảng nhỏ do nghiệp vụ cung cấp rồi nối vào thành cột is_holiday.
Ví dụ: đường biểu đồ nối thẳng
Dashboard vẽ số đơn ở cửa hàng theo ngày, dữ liệu lấy thẳng từ fact. Ngày 19/3 cửa hàng không bán được đơn nào, nên fact không có dòng nào cho ngày đó. Biểu đồ nối thẳng từ 18/3 sang 20/3, như thể ngày 19/3 không tồn tại. Trong cả năm 2025, kênh store có 13 ngày như vậy. Đi từ dim_date thì 13 ngày đó hiện ra với số 0.
Bẫy: điều kiện ở WHERE
Đếm ngày của tháng 3 và số đơn ở cửa hàng:
-- Sai: 30 ngày
FROM dim_date d LEFT JOIN fact_orders f ON f.date_key = d.date_key
WHERE d.month = 3 AND f.channel = 'store'
-- Đúng: 31 ngày
FROM dim_date d LEFT JOIN fact_orders f ON f.date_key = d.date_key AND f.channel = 'store'
WHERE d.month = 3
LEFT JOIN trước tiên giữ mọi ngày; ngày không có đơn khớp nhận f.* là NULL. Sau đó WHERE chạy trên kết quả đó: f.channel = 'store' với NULL không ra true, nên ngày đó bị loại. Kết quả giống hệt INNER JOIN.
Quy tắc: điều kiện trên bảng bên phải của LEFT JOIN đặt ở ON; điều kiện trên bảng bên trái đặt ở WHERE. d.month = 3 thuộc bảng trái nên ở WHERE là đúng.
flowchart LR D[dim_date · 31 ngày tháng 3] -->|ON f.channel = 'store'| K[31 ngày, 1 ngày 0 đơn] D -->|WHERE f.channel = 'store'| M[30 ngày, mất ngày 0 đơn]
Khi dim_date hết phạm vi
dim_date chỉ phủ 2025. Đơn của tháng 1/2026 có date_key không khớp dòng nào: với INNER JOIN, chúng biến mất khỏi báo cáo mà không có lỗi nào. Module 04 sẽ gặp đúng chuyện này với lô dữ liệu mới. Phòng bằng hai cách: sinh dim_date rộng hơn phạm vi dữ liệu (vài năm tới) và có phép kiểm "fact không khớp dim" luôn ra 0.
Lệnh hay dùng
| Lệnh | Để làm gì |
|---|---|
generate_series(DATE '...', DATE '...', INTERVAL 1 DAY) |
sinh dãy ngày |
year(d), quarter(d), month(d), dayofweek(d) |
cột thuộc tính của ngày |
FROM fact f LEFT JOIN dim_date d USING (date_key) WHERE d.date_key IS NULL |
đơn không khớp ngày nào, phải ra 0 |
FROM dim_date d LEFT JOIN fact f ON ... AND <điều kiện trên f> |
giữ ngày không có sự kiện |
Lỗi hay gặp
- Sinh dim_date từ dữ liệu sự kiện. Mất đúng những ngày không có đơn.
- Lọc bảng bên phải ở WHERE. LEFT JOIN âm thầm thành INNER JOIN.
count(*)thay vìcount(f.order_id). Ngày không có đơn vẫn có một dòng (với f là NULL), nêncount(*)đếm ra 1 thay vì 0.- Quên mở rộng dim_date. Dữ liệu năm mới không join được và biến mất không báo lỗi.