在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   
); 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]