应minitoy要求，关于minitoy年周函数测试结果

Question

--函数如下
create or replace function GetWeekNumber(p_date date) return number is
  retval number(2);
begin
  if (to_char(trunc(p_date, 'y'), 'd') >= 2 and
     to_char(trunc(p_date, 'y'), 'd') + trunc(p_date) - trunc(p_date, 'y') <= 8) or
     (to_char(trunc(p_date, 'y'), 'd') = 1 and
     trunc(p_date) = trunc(p_date, 'y')) then
    retval := 1;
  else
    select decode(to_char(trunc(p_date, 'yyyy'), 'd'),
                  2,
                  to_char(p_date, 'WW'),
                  1,
                  to_char(p_date - 1, 'WW') + 1,
                  to_char(p_date - (9 - to_char(trunc(p_date, 'yyyy'), 'd')),
                          'WW') + 1)
      into retval
      from dual;
  end if;  return retval;
end;---注明，我在我的日历函数t_day_populate_ce中调用了GetWeekNumber函数
SQL> execute t_day_populate_ce(2010);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2009);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2008);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2007);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2010);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2011);
 
PL/SQL procedure successfully completedSQL> execute t_day_populate_ce(2011);
PL/SQL procedure successfully completed
SQL> --测试语句，各年份依次测过
select count(1), t_day_week_of_year --年第几周
  from t_day
 where id between 20110101 and 20111231
 group by t_day_week_of_year
 order by t_day_week_of_year;
--测试结果
--好像不会贴图。。囧
--所测年份没发现什么问题，呵呵
COUNT(1）T_DAY_WEEK_OF_YEAR
2 1
7 2
7 3
7 4
7 5
7 6
7 7
7 8
7 9
7 10
7 11
7 12
7 13
7 14
7 15
7 16
7 17
7 18
7 19
7 20
7 21
7 22
7 23
7 24
7 25
7 26
7 27
7 28
7 29
7 30
7 31
7 32
7 33
7 34
7 35
7 36
7 37
7 38
7 39
7 40
7 41
7 42
7 43
7 44
7 45
7 46
7 47
7 48
7 49
7 50
7 51
7 52
6 53--最后附上我们老大修改nGX20080110后的，测试通过
--最终版本
CREATE OR REPLACE FUNCTION UDF_WEEKOFYEAR(dt date) return char as
  v_num   number;
  v_weeks number := 0;
begin
  if to_char(trunc(dt, 'year'), 'day') <> '星期一' then--如当年的第一天不是星期一
    --获取当天和本年第一天的天数差，减去第一个周一前剩余的天数，最后除以7
    select (trunc(dt) - trunc(dt, 'year') -
           decode(to_char(trunc(dt, 'year'), 'day'),
                   '星期二',
                   6,
                   '星期三',
                   5,
                   '星期四',
                   4,
                   '星期五',
                   3,
                   '星期六',
                   2,
                   '星期日',
                   1)) / 7
      into v_num
      from dual;
    if v_num >= 0 then --如除数大于0，则直接向下取整+2
      v_weeks:= floor(v_num)+2; 
     elsif v_num < 0 then--如小于0，则为第一周
      v_weeks:=1;
    end if;
  else --如当年的第一天是星期一，取当天和本年第一天的天数差，除以7，向下取整+1
      v_weeks:= floor((trunc(dt) - trunc(dt, 'year'))/7)+1; 
  end if;
  --return v_weeks;
  return(case when v_weeks <= 9 then '0'||to_char(v_weeks) else to_char(v_weeks) end);
end;

Answer 1

哦，我那改动的是wkc168的，谢谢其余各位友情参与！！！！

Answer 2

注：关于wkc168 的fun_weeks函数bug
--2008年1月1日测试结果为0
select fun_weeks(to_date('20080101','YYYYMMDD')) from dual；

Answer 3

CREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(abs(v_num1)) into v_weeks from dual;
else
select round(v_num1)+1 into v_weeks from dual;
end if;
elsif v_num1=0 then
select v_num1+1 into v_weeks from dual;
end if;
else
select (trunc(dt)-trunc(dt,'year'))/7 into v_num from dual;
if (v_num=0 or (instr(v_num,'.')>0 and substr(v_num,instr(v_num,'.')+1,1)<5) or instr(v_num,'.')=0) then
v_num1:=round(v_num)+1;
v_weeks:=v_num1;
elsif (v_num<>0 or substr(v_num,instr(v_num,'.')+1,1)>4) then
v_num1:=round(v_num);
v_weeks:=v_num1;
end if;
end if;
return v_weeks;
end;
/SQL> select fun_weeks(to_date('20080101','YYYYMMDD')) from dual
  2  /FUN_WEEKS(TO_DATE('20080101','YYYYMMDD'))
-----------------------------------------
                                        1已用时间:  00: 00: 00.01

Answer 4

--测试语句
select count(1), t_day_week_of_year
  from t_day
 where id between 20080101 and 20081231
 group by t_day_week_of_year
 order by t_day_week_of_year;
---另外，单独给出一个日期
--结果有误
select fun_weeks(to_date('20080113','YYYYMMDD')) from dual;
--结果是0
COUNT(1) T_DAY_WEEK_OF_YEAR
51 0
6 1
6 2
6 3
6 4
6 5
6 6
6 7
6 8
6 9
6 10
6 11
6 12
6 13
6 14
6 15
6 16
6 17
6 18
6 19
6 20
6 21
6 22
6 23
6 24
6 25
6 26
6 27
6 28
6 29
6 30
6 31
6 32
6 33
6 34
6 35
6 36
6 37
6 38
6 39
6 40
6 41
6 42
6 43
6 44
6 45
6 46
6 47
6 48
6 49
6 50
6 51
6 52
3 53

Answer 5

CREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(abs(v_num1)) into v_weeks from dual;
else
select round(v_num1)+1 into v_weeks from dual;
end if;
elsif v_num1=0 or instr(v_num1,'.')<=0 then        -----忽略了天数之差除以7为整数的情况
select v_num1+1 into v_weeks from dual;
end if;
else
select (trunc(dt)-trunc(dt,'year'))/7 into v_num from dual;
if (v_num=0 or (instr(v_num,'.')>0 and substr(v_num,instr(v_num,'.')+1,1)<5) or instr(v_num,'.')=0) then
v_num1:=round(v_num)+1;
v_weeks:=v_num1;
elsif (v_num<>0 or substr(v_num,instr(v_num,'.')+1,1)>4) then
v_num1:=round(v_num);
v_weeks:=v_num1;
end if;
end if;
return v_weeks;
end;
/SQL> select fun_weeks(to_date('20080113','YYYYMMDD')) from dual;FUN_WEEKS(TO_DATE('20080113','YYYYMMDD'))
-----------------------------------------
                                        2  1* select fun_weeks(to_date('20080114','YYYYMMDD')) from dual
SQL> /FUN_WEEKS(TO_DATE('20080114','YYYYMMDD'))
-----------------------------------------
                                        3

Answer 6

[Quote=引用 11 楼 wkc168 的回复:]
SQL codeCREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(abs(v_num1)) into v_weeks from dual;
else
select round(v_num1)+1 into v_weeks from dual;
end if;
elsif v_num1=0 or instr(v_num1,'.')<=0 then        -----忽略了天数之差除以7为整数的情况
select v_num1+1 into v_weeks from dual;
end if;
else
select (trunc(dt)-trunc(dt,'year'))/7 into v_num from dual;
if (v_num=0 or (instr(v_num,'.')>0 and substr(v_num,instr(v_num,'.')+1,1)<5) or instr(v_num,'.')=0) then
v_num1:=round(v_num)+1;
v_weeks:=v_num1;
elsif (v_num<>0 or substr(v_num,instr(v_num,'.')+1,1)>4) then
v_num1:=round(v_num);
v_weeks:=v_num1;
end if;
end if;
return v_weeks;
end;
/--测试年份
SQL> execute t_day_populate_ce(2008);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2007);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2009);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2010);
 
PL/SQL procedure successfully completed
 
SQL> execute t_day_populate_ce(2018);
 
PL/SQL procedure successfully completed--测试语句，以2008年为例，其他部分年份测过，无误
select to_char(to_date('20080101','YYYYMMDD'),'DAY'),fun_weeks(to_date('20080101','YYYYMMDD')) from dual;
--查询结果
星期二 ，1
--测试语句
select count(1), t_day_week_of_year
  from t_day
 where id between 20080101 and 20081231
 group by t_day_week_of_year
 order by t_day_week_of_year;
---测试结果
COUNT(1) T_DAY_WEEK_OF_YEAR
6 1
7 2
7 3
7 4
7 5
7 6
7 7
7 8
7 9
7 10
7 11
7 12
7 13
7 14
7 15
7 16
7 17
7 18
7 19
7 20
7 21
7 22
7 23
7 24
7 25
7 26
7 27
7 28
7 29
7 30
7 31
7 32
7 33
7 34
7 35
7 36
7 37
7 38
7 39
7 40
7 41
7 42
7 43
7 44
7 45
7 46
7 47
7 48
7 49
7 50
7 51
7 52
3 53恭喜