DataOps Peach

Data Foundations · Dimensional modeling và Gold · Bài 2/5

dim_date

Sinh bảng ngày cho cả năm từ lịch, không từ đơn hàng, để ngày không có sự kiện vẫn hiện với số 0. Và cái bẫy biến LEFT JOIN thành INNER JOIN.

Học tương tác →

Đã 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:

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

Học tương tác →