The date functions provided by Oracle can meet all the daily operations on the date. Generally, we often encounter date fields when designing table structures. If the business needs to summarize the data of the week where the given date is located, and summarize the data of each week in the month where the given date is located, it will be complicated to write SQL statements directly.
Therefore, I directly create a date master table in the data, and calculate the statistical fields that may be used first, you only need to connect to the table to obtain the desired result.
The following describes how to create a table and how to generate data. You can add new fields to meet different requirements. You can create related indexes as needed!
We also welcome you to discuss better methods! Do not hesitate to advise!
1. Create a date primary table
- 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. Insert Date data
- Declare
- I integer;
- Begin_dateChar(10 );
- Begin
- Begin_date: ='2017-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. Set the values of related fields
- Update DATE_MAIN
- SET month_of_year = to_number (to_char (date_time,'Mm')),
- Week_of_year = to_number (to_char (date_time,'Aw')),
- Day_of_week = to_number (to_char (date_time,'D'));
- -- Set Monday to the first day of each week
- 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. Set the monthly week
- 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: ='123';
- 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 -- Cross-year
- Current_year: = temp_year;
- Current_month: = X. month_of_year;
- Current_week: = X. week_of_year;
- DayOfWeek: = X. day_of_week;
- WeekOfMonth: = 1;
- ELSE -- same year
- Temp_month: = X. month_of_year;
- IF temp_month <> current_month THEN -- Cross-month
- WeekOfMonth: = 1;
- Current_month: = temp_month;
- ELSE -- same month
- 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 =:'
- |'Where date_time = to_date (: B ,''Yyyymmdd'')'USING weekOfMonth, X. DAT;
- End loop;
- End;
5. Set the first and last days of the week.
- -- Set the first day of each week
- UPDATE Date_main
- SET first_day_of_week = trunc (date_time,'Dd')-To_char (date_time-1,'D') + 1;
- -- Set the last day of each week
- UPDATE Date_main
- SET last_day_of_week = first_day_of_week + 6;
- UPDATE Date_main
- 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
- );