在Oracle数据库中创建日期主档表

    Oracle提供的日期函数可以满足我们日常用到的对日期的所有操作。通常，在设计表结构的时候，我们也常常会遇到日期类型的字段。如果业务里面需要类似汇总给定日期所在周的资料，汇总给定日期所在月的各周资料等操作，那直接写SQL语句将比较复杂。

    所以，我是直接在数据里面建立一个日期的主档表，在里面将可能用到的统计字段先计算后，后面业务逻辑的汇总只需连接这个表就可以得到想要的结果。

 

    下面是这个表的建立和数据的生成过程，可以在里面新加字段以满足不同的需求，相关的索引可以根据自己的需要去建立！

    同时欢迎大家一起讨论更好的方法！不足之处，请不吝指教！

 

    1. 建立日期主档表

CREATE TABLE DATE_MAIN ( DATE_TIME DATE, DAY_OF_WEEK INTEGER, WEEK_OF_YEAR INTEGER, MONTH_OF_YEAR INTEGER, WEEK_OF_MONTH INTEGER, YEAR_AND_MONTH CHAR(7), FIRST_DAY_OF_WEEK DATE, LAST_DAY_OF_WEEK DATE, FIRST_DAY_OF_WEEK_OF_MONTH DATE, LAST_DAY_OF_WEEK_OF_MONTH DATE )

   

    2. 插入日期资料

declare i integer; begin_date char(10); begin begin_date := '2008-01-01'; FOR i IN 0..3650 LOOP execute IMMEDIATE 'INSERT INTO date_main(Date_Time) VALUES(:X)' USING to_date(begin_date,'YYYY-MM-DD') + i; END LOOP; end;

 

    3. 设置相关字段的值

update DATE_MAIN SET month_of_year = to_number(to_char(date_time,'MM')), week_of_year = to_number(to_char(date_time,'IW')), day_of_week = to_number(to_char(date_time,'D')); --设置周一为每周的第一天 UPDATE date_main SET day_of_week =day_of_week - 1; UPDATE date_main SET day_of_week = 7 WHERE day_of_week =0;

   

    4. 设置每月的周别

Declare current_year CHAR(4); current_month INT; current_week INT; temp_year CHAR(4); temp_month INT; weekOfMonth INT; dayOfWeek INT; begin -- Test statements here current_year := '1999'; current_month := -1; current_week :=-1; FOR X IN ( SELECT to_char(date_time,'YYYYMMDD') dat ,month_of_year ,week_of_year ,day_of_week ,week_of_month FROM date_main s ORDER BY s.date_time ) LOOP temp_year := substr(X.dat,1,4); IF temp_year <> current_year THEN --跨年 current_year := temp_year; current_month := X.month_of_year; current_week := X.week_of_year; dayOfWeek := X.day_of_week; weekOfMonth := 1; ELSE --同一年 temp_month := X.month_of_year; IF temp_month <> current_month THEN --跨月 weekOfMonth := 1; current_month := temp_month; ELSE --同月份 IF current_week <> X.week_of_year THEN current_week := X.week_of_year; weekOfMonth := weekOfMonth + 1; END IF; END IF; END IF; EXECUTE IMMEDIATE 'UPDATE date_main ' || 'SET week_of_month = :A ' || 'WHERE date_time = to_date(:B,''YYYYMMDD'')' USING weekOfMonth,X.DAT; END LOOP; end;

 

    5.设置每周的第一天和最后一天

    --设置每周的第一天 UPDATE Date_main SET first_day_of_week = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1; --设置每周的最后一天 UPDATE Date_main SET last_day_of_week = first_day_of_week + 6; UPDATE Date_main A SET (first_day_of_Week_of_Month,LAST_day_of_week_of_Month) = ( SELECT mind,maxd FROM ( SELECT MIN(date_time) MIND ,MAX(date_time) MAXD,year_and_month,week_of_month FROM date_main GROUP BY year_and_month,week_of_month ) B WHERE A.Year_And_Month = B.year_and_month AND A.week_of_month = B.week_of_month );