文章內(nèi)容

在Oracle數(shù)據(jù)庫中創(chuàng)建日期主檔表

發(fā)布時間: 2012/9/17 16:58:38

Oracle提供的日期函數(shù)可以滿足我們?nèi)粘Ｓ玫降膶θ掌诘乃胁僮�。通常，在設(shè)計表結(jié)構(gòu)的時候，我們也常常會遇到日期類型的字段。如果業(yè)務(wù)里面需要類似匯總給定日期所在周的資料，匯總給定日期所在月的各周資料等操作，那直接寫SQL語句將比較復(fù)雜。

所以，我是直接在數(shù)據(jù)里面建立一個日期的主檔表，在里面將可能用到的統(tǒng)計字段先計算后，后面業(yè)務(wù)邏輯的匯總只需連接這個表就可以得到想要的結(jié)果。-

下面是這個表的建立和數(shù)據(jù)的生成過程，可以在里面新加字段以滿足不同的需求，相關(guān)的索引可以根據(jù)自己的需要去建立！

同時歡迎大家一起討論更好的方法！不足之處，請不吝指教！

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. 設(shè)置相關(guān)字段的值

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

--設(shè)置周一為每周的第一天
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. 設(shè)置每月的周別

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.設(shè)置每周的第一天和最后一天

--設(shè)置每周的第一天
UPDATE Date_main
SET first_day_of_week = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1;

--設(shè)置每周的最后一天
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