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