如何基于Oracle表日期字段创建含年月日计算列的日期表/视图

实现说明 #

使用前需要将代码中的2处占位符替换为实际业务信息：

• YOUR_SOURCE_TABLE：存储原始日期字段的业务表名
• YOUR_DATE_COLUMN：业务表中对应的日期类型字段名
  方案默认返回和需求示例完全一致的字段结构：去重后的格式化日期、两位补零的日、两位补零的月份、英文全称月份名、四位年份。

方案1：创建自动同步的日期视图（推荐） #

视图无需额外维护数据，会随源表的日期数据自动更新，适合绝大多数场景：

CREATE OR REPLACE VIEW DIM_DATE AS
SELECT DISTINCT
    TO_CHAR(YOUR_DATE_COLUMN, 'DD-MM-YYYY') AS "DATE",
    TO_CHAR(YOUR_DATE_COLUMN, 'DD') AS "DAY",
    TO_CHAR(YOUR_DATE_COLUMN, 'MM') AS "MONTH",
    TO_CHAR(YOUR_DATE_COLUMN, 'FMMonth', 'NLS_DATE_LANGUAGE = ENGLISH') AS "MONTH_NAME",
    TO_CHAR(YOUR_DATE_COLUMN, 'YYYY') AS "YEAR"
FROM YOUR_SOURCE_TABLE
WHERE YOUR_DATE_COLUMN IS NOT NULL;

方案2：创建固定数据的实体日期表 #

如果需要固化日期数据、避免源表数据变动影响日期维度，可以创建实体表，后续按需定期重刷数据即可：

CREATE TABLE DIM_DATE AS
SELECT DISTINCT
    TO_CHAR(YOUR_DATE_COLUMN, 'DD-MM-YYYY') AS "DATE",
    TO_CHAR(YOUR_DATE_COLUMN, 'DD') AS "DAY",
    TO_CHAR(YOUR_DATE_COLUMN, 'MM') AS "MONTH",
    TO_CHAR(YOUR_DATE_COLUMN, 'FMMonth', 'NLS_DATE_LANGUAGE = ENGLISH') AS "MONTH_NAME",
    TO_CHAR(YOUR_DATE_COLUMN, 'YYYY') AS "YEAR"
FROM YOUR_SOURCE_TABLE
WHERE YOUR_DATE_COLUMN IS NOT NULL;

注意事项 #

1. 代码中FMMonth格式符用于去除月份名称前后的自动补位空格，保证返回值和示例格式完全匹配；指定NLS_DATE_LANGUAGE = ENGLISH参数可以规避数据库默认语言非英文时返回非英文月份名的问题。
2. 返回的DATE字段为字符串类型，查询排序时需要转回日期类型再排序，否则会出现字符串排序导致的日期顺序错乱，排序查询示例：

SELECT * FROM DIM_DATE 
ORDER BY TO_DATE("DATE", 'DD-MM-YYYY');

内容的提问来源于stack exchange，提问作者Amar