日期与期别对照表

为什么80%的码农都做不了架构师？>>>   

--drop table t_period purge;
--1、建表
create table t_period
(
  period VARCHAR2(10) not null,
  type   NUMBER(1) not null,--type：'1月 2季 3年 4天 5周'
  yw     VARCHAR2(10),  --一年中第几周
  mw     VARCHAR2(10),  --一月中第几周
  min_day  VARCHAR2(10),--期别开始日期
  max_day  VARCHAR2(10),--期别结束日期
  day    DATE,
  constraint PK_t_period primary key (TYPE, PERIOD)
);
  
--2、插入日初始数据
declare
  d date := to_date('2010-01-01', 'yyyy-mm-dd');
begin
  for i in 1 .. 7305 loop --10年1月1日到29年12月31日，共20年
    insert into t_period(period, type, day)values(to_char(d, 'yyyy-mm-dd'), 4, d);
    d := d + 1;
  end loop;
end;
commit;
  
--3、更新年周
/*1月1日在用iw转换的时候可能是上一年的第52周或第53周
select to_char(to_date('2010-01-01', 'yyyy-mm-dd'), 'yyyy-iw') as w10,
       to_char(to_date('2011-01-01', 'yyyy-mm-dd'), 'yyyy-iw') as w11,
       to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy-iw') as w12,
       to_char(to_date('2012-12-31', 'yyyy-mm-dd'), 'yyyy-iw') as w1212,
       to_char(to_date('2013-01-01', 'yyyy-mm-dd'), 'yyyy-iw') as w13,
       to_char(to_date('2013-12-31', 'yyyy-mm-dd'), 'yyyy-iw') as w1312,
       to_char(to_date('2014-01-01', 'yyyy-mm-dd'), 'yyyy-iw') as w14
  from dual;
*/
update t_period
   set yw = to_number(to_char(day, 'yyyy')) + case
              when to_char(day, 'mm') = '12' and
                   to_char(day, 'iW') = '01' then
               1
              when to_char(day, 'mm') = '01' and
                   to_char(day, 'iW') > '50' then
               -1
              else
               0
            end || '-' || to_char(day, 'iW');
commit;
       
--4、插入月份数据
insert into t_period
  (period, type)
  select distinct to_char(day, 'yyyy-mm'), 1
    from t_period
   where type = 4;
  
--5、插入季度数据
insert into t_period
  (period, type)
  select distinct to_char(day, 'yyyy-"Q"q'), 2
    from t_period
   where type = 4;
  
--6、插入年份数据
insert into t_period
  (period, type)
  select distinct to_char(day, 'yyyy'), 3 from t_period where type = 4;
commit;
  
--7、更新月周
--每天属于月份第几周
declare
  --2010-01-01为12-W5
  w integer := 5; --当月第几周
  mm varchar2(2) := '12';    --月份
begin
  for cur in (select t.rowid,t.* from t_period t where type=4 order by period)
  loop
    
    --逢周一时周会变化，月可能不变
    if 2 = to_char(cur.day,'d') then
      w := w+1;
      mm := to_char(cur.day,'mm');
      
      --周四在下月，当月最后一周小于4天算到下月第一周
      if to_char(cur.day, 'mm') <> to_char(cur.day + 3, 'mm') then
        w := 1;
        mm := to_char(add_months(cur.day, 1),'mm');
      end if;
      
      --周四在上月
      if to_char(cur.day, 'mm') <> to_char(cur.day - 4, 'mm') then
        w := 1;
      end if;
      
    end if;
  
    update t_period set mw = mm || '-W' || w where rowid = cur.rowid;
  end loop;
end;
  
insert into t_period(period,type,mw)
select distinct yw, 5, mw from t_period where type = 4;
commit;
  
--8、更新周、季第一天和最后一天
begin
  for cur in (select min(period) p1, max(period) p2, yw
                from t_period
               where type = 4
               group by yw
              having count(*) = 7) loop
    update t_period
       set min_day = cur.p1, max_day = cur.p2
     where period = cur.yw
       and type = 5;
  end loop;
  
  for cur in (select min(period) p1,
                     max(period) p2,
                     to_char(day, 'yyyy-"Q"q') p
                from t_period
               where type = 4
               group by to_char(day, 'yyyy-"Q"q')) loop
    update t_period
       set min_day = cur.p1, max_day = cur.p2
     where period = cur.p
       and type = 2;
  end loop;
end;
  
--9、去掉过程列
alter table t_period drop column day;
  
select * from t_period order by type, period;

转载于:https://my.oschina.net/h2do/blog/268154