oracle生成时间维度表_oracle 生成日期维度表-CSDN博客

本文链接：https://blog.csdn.net/weixin_42451919/article/details/90607714

本文介绍了如何在Oracle数据库中生成日期维度表，用于多维数据分析。内容包括创建包含年、季度、月、日的表，并说明如何自定义生成未来一年的记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

生成日期维度，用作多维数据分析。

首先创建表：

年

-- Create table
create table DIM_YEAR
(
  year_id     NUMBER not null,
  year_name   VARCHAR2(40),
  start_time  DATE,
  end_time    DATE,
  day_num     NUMBER,
  update_time DATE
);
-- Add comments to the table 
comment on table DIM_YEAR
  is '年份表';
-- Add comments to the columns 
comment on column DIM_YEAR.year_id
  is '年份编号';
comment on column DIM_YEAR.year_name
  is '年份名';
comment on column DIM_YEAR.start_time
  is '起始时间';
comment on column DIM_YEAR.end_time
  is '结束时间';
comment on column DIM_YEAR.day_num
  is '天数';
comment on column DIM_YEAR.update_time
  is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_YEAR
  add constraint PK_DIM_YEAR primary key (YEAR_ID);

季度

-- Create table
create table DIM_QUARTER
(
  quarter_id   NUMBER not null,
  quarter_name VARCHAR2(40),
  year_id      NUMBER,
  quarter_desc VARCHAR2(40),
  start_time   DATE,
  end_time     DATE,
  day_num      NUMBER,
  update_time  DATE
);
-- Add comments to the table 
comment on table DIM_QUARTER
  is '季度表';
-- Add comments to the columns 
comment on column DIM_QUARTER.quarter_id
  is '季度编号';
comment on column DIM_QUARTER.quarter_name
  is '季度名';
comment on column DIM_QUARTER.year_id
  is '年份编号';
comment on column DIM_QUARTER.quarter_desc
  is '季度描述';
comment on column DIM_QUARTER.start_time
  is '起始时间';
comment on column DIM_QUARTER.end_time
  is '结束时间';
comment on column DIM_QUARTER.day_num
  is '天数';
comment on column DIM_QUARTER.update_time
  is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_QUARTER
  add constraint PK_DIM_QUARTER primary key (QUART