Oracle SQL 日期維度報表查詢範本：年月日、季度、年度產生器

應用場景

資料倉庫與 BI 報表常需預先產生「日期維度表」，包含日期鍵、中文顯示名、季度歸屬等屬性，供事實表外鍵關聯。本文提供三段 Oracle SQL 範本，可直接建立或填充日期維度。

一、 完整日期維度產生器（單一查詢）

SELECT
  TO_CHAR(dt, 'YYYYMMDD')          AS DATE_ID,
  TO_CHAR(dt, 'YYYY') || '年' ||
  TO_CHAR(dt, 'MM')   || '月' ||
  TO_CHAR(dt, 'DD')   || '日'      AS DATE_NAME,
  TO_CHAR(dt, 'YYYYMM')            AS MONTH_ID,
  TO_CHAR(dt, 'YYYY') || '年' ||
  TO_CHAR(dt, 'MM')   || '月'      AS MONTH_NAME,
  'Q' || TO_CHAR(dt, 'Q.YYYY')     AS QUARTER_ID,
  TO_CHAR(dt, 'YYYY') || '年第' ||
  TO_CHAR(dt, 'Q')    || '季度'    AS QUARTER_NAME,
  TO_CHAR(dt, 'YYYY')              AS YEAR_ID,
  TO_CHAR(dt, 'YYYY') || '年'      AS YEAR_NAME
FROM (
  SELECT TO_DATE('2000-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS dt
  FROM   user_objects
  WHERE  ROWNUM < 367
  AND    TO_DATE('2000-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) < TO_DATE('2001-01-01', 'YYYY-MM-DD')
);

說明： 利用 user_objects 作為虛擬資料列來源，產生 2000 年全年 366 天（含閏日）記錄；若需更長區間，調整 ROWNUM 上限與結束日期即可。

二、 季度與月份對照表

SELECT DISTINCT
  TO_CHAR(dt, 'Q')                    AS 季度,
  TO_CHAR(TO_DATE('2001-01-01','YYYY-MM-DD') + (ROWNUM - 1), 'YYYYMM') AS 月份_ID
FROM (
  SELECT TO_DATE('2001-01', 'YYYY-MM') + (ROWNUM - 1) AS dt
  FROM   user_objects
  WHERE  ROWNUM < 367
  AND    TO_DATE('2001-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) < TO_DATE('2002-01-01', 'YYYY-MM-DD')
);

輸出範例：Q1 對應 200101~200103，Q2 對應 200104~200106，以此類推。

三、 單年度逐日明細

SELECT TO_CHAR(TO_DATE('2000-01-01','YYYY-MM-DD') + (ROWNUM - 1), 'YYYY-MM-DD') AS 日期
FROM   user_objects
WHERE  ROWNUM < 367
AND    TO_DATE('2000-01-01','YYYY-MM-DD') + (ROWNUM - 1) < TO_DATE('2001-01-01','YYYY-MM-DD');

進階擴充建議

• 加入工作日標記： 使用 TO_CHAR(dt, 'D') 判斷週末，結合節假日表標記是否營業日。
• 財務年度支援： 依公司會計年度起始月（如 7 月）計算財務季度/年度欄位。
• 分區表整合： 將日期維度做為分區鍵，提升大型事實表查詢效能。
• 跨資料庫相容： SQL Server 可用 DATEADD(day, n, '20000101') + 遞迴 CTE；PostgreSQL 可用 generate_series()。