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


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

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

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

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

    1. 建立日期主档表

  1. CREATE TABLE DATE_MAIN   
  2. (   
  3.   DATE_TIME                  DATE,   
  4.   DAY_OF_WEEK                INTEGER,   
  5.   WEEK_OF_YEAR               INTEGER,   
  6.   MONTH_OF_YEAR              INTEGER,   
  7.   WEEK_OF_MONTH              INTEGER,   
  8.   YEAR_AND_MONTH             CHAR(7),   
  9.   FIRST_DAY_OF_WEEK          DATE,   
  10.   LAST_DAY_OF_WEEK           DATE,   
  11.   FIRST_DAY_OF_WEEK_OF_MONTH DATE,   
  12.   LAST_DAY_OF_WEEK_OF_MONTH  DATE   
  13. )    

    2. 插入日期资料

  1. declare    
  2.   i integer;   
  3.   begin_date char(10);   
  4. begin   
  5.   begin_date := '2008-01-01';   
  6.   FOR i IN 0..3650   
  7.   LOOP   
  8.       execute IMMEDIATE   
  9.       'INSERT INTO date_main(Date_Time)   
  10.       VALUES(:X)' USING to_date(begin_date,'YYYY-MM-DD') + i;   
  11.   END LOOP;   
  12. end;  

    3. 设置相关字段的值

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

    4. 设置每月的周别

  1. Declare   
  2.   current_year CHAR(4);   
  3.   current_month INT;   
  4.   current_week INT;   
  5.   temp_year CHAR(4);   
  6.   temp_month INT;   
  7.   weekOfMonth INT;   
  8.   dayOfWeek INT;   
  9. begin   
  10.   -- Test statements here   
  11.   current_year := '1999';   
  12.   current_month := -1;   
  13.   current_week :=-1;   
  14.   
  15.   FOR X IN (   
  16.       SELECT to_char(date_time,'YYYYMMDD') dat   
  17.           ,month_of_year   
  18.           ,week_of_year   
  19.           ,day_of_week   
  20.           ,week_of_month   
  21.       FROM date_main s   
  22.       ORDER BY s.date_time   
  23.   )   
  24.   LOOP   
  25.      temp_year := substr(X.dat,1,4);   
  26.   
  27.      IF temp_year <> current_year THEN  --跨年   
  28.          current_year := temp_year;   
  29.          current_month := X.month_of_year;   
  30.          current_week := X.week_of_year;   
  31.          dayOfWeek := X.day_of_week;   
  32.          weekOfMonth := 1;   
  33.      ELSE  --同一年   
  34.          temp_month := X.month_of_year;   
  35.          IF temp_month <> current_month THEN --跨月   
  36.              weekOfMonth := 1;   
  37.              current_month := temp_month;   
  38.          ELSE --同月份   
  39.              IF current_week <> X.week_of_year THEN   
  40.                  current_week := X.week_of_year;   
  41.                  weekOfMonth := weekOfMonth + 1;   
  42.              END IF;   
  43.          END IF;   
  44.      END IF;   
  45.   
  46.      EXECUTE IMMEDIATE   
  47.          'UPDATE date_main '  
  48.          || 'SET week_of_month = :A '  
  49.          || 'WHERE date_time = to_date(:B,''YYYYMMDD'')' USING weekOfMonth,X.DAT;   
  50.   END LOOP;   
  51. end;  

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

  1. --设置每周的第一天   
  2. UPDATE Date_main   
  3.  SET first_day_of_week  = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1;   
  4.   
  5. --设置每周的最后一天   
  6. UPDATE Date_main   
  7. SET last_day_of_week = first_day_of_week + 6;   
  8.   
  9. UPDATE Date_main A   
  10. SET (first_day_of_Week_of_Month,LAST_day_of_week_of_Month) = (   
  11.     SELECT mind,maxd FROM   
  12.     (   
  13.         SELECT MIN(date_time) MIND ,MAX(date_time) MAXD,year_and_month,week_of_month   
  14.         FROM date_main GROUP BY year_and_month,week_of_month   
  15.     ) B    
  16.     WHERE A.Year_And_Month = B.year_and_month AND A.week_of_month = B.week_of_month   
  17. );  

相关内容