http://jackywood.itpub.net/post/1369/127565
一条SQL语句生成年历。
链接是作者的解读,我感觉年历的实现主要有三个步骤
1.生成一年之中所有的日期
2.在上步的基础上,得到每个日期所在周、月、年的具体信息。
3.行列转置
其中new_yweek这部分看的真是云里雾里,它主要解决Oracle to_char函数IW和WW坑爹的问题。
按照上述算法,WW的结果相差较远,而IW比较贴近我们对于日期的认识。
但是问题是IW存在边界问题。它会认为12月30日和31日是2014年的第一周,以此统计年历,则会出现问题。所以new_yweek解决的应该是这个问题。
可以使用下面的方法解决IW边界问题。
而max分析函数部分,解决类似12月30日,31日划分到下一年的问题。
这个实现存在一些问题。
如果元旦是周五,周六或者周日,例如20110101,它是周六,IW会认为这天是2010年的第五十二周。
6,7日是周日、周一,小于元旦的周二,所以所在周+1,那么行列转置后在日历上就下沉了一层。
一条SQL语句生成年历。
- select case
- when (new_yweek = min(new_yweek)over(partition by mon order by new_yweek)) then
- mon_name
- else
- null
- end as month,
- new_yweek as yweek,
- row_number() over(partition by mon order by new_yweek) as mweek,
- sum(decode(wday, '1', mday, null)) as sun,
- sum(decode(wday, '2', mday, null)) as mon,
- sum(decode(wday, '3', mday, null)) as tue,
- sum(decode(wday, '4', mday, null)) as wed,
- sum(decode(wday, '5', mday, null)) as thu,
- sum(decode(wday, '6', mday, null)) as fri,
- sum(decode(wday, '7', mday, null)) as sat
- from (select dayofyear as everyday,
- to_char(dayofyear, 'mm') as mon,
- to_char(dayofyear, 'Month') as mon_name,
- to_char(dayofyear, 'w') as mweek,
- to_char(dayofyear, 'ww') as yweek,
- case
- when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
- (to_char(dayofyear, 'd') <
- to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
- to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
- else
- to_char(dayofyear, 'ww')
- end as new_yweek,
- to_char(dayofyear, 'd') as wday,
- to_char(dayofyear, 'dd') as mday
- from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
- from dual
- connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
- )
- )
- group by mon, mon_name, new_yweek
- /
1.生成一年之中所有的日期
2.在上步的基础上,得到每个日期所在周、月、年的具体信息。
3.行列转置
其中new_yweek这部分看的真是云里雾里,它主要解决Oracle to_char函数IW和WW坑爹的问题。
select to_char(d,'yyyy-mm-dd'),to_char(d,'d') dayofweek,to_char(d,'WW') WW,to_char(d,'IW') IW from (
select to_date('20131229','yyyymmdd')+level-1 as d from dual connect by level<=10);
select to_date('20131229','yyyymmdd')+level-1 as d from dual connect by level<=10);
TO_CHAR(D, D WW IW
---------- - -- --
2013-12-29 1 52 52
2013-12-30 2 52 01
2013-12-31 3 53 01
2014-01-01 4 01 01
2014-01-02 5 01 01
2014-01-03 6 01 01
2014-01-04 7 01 01
2014-01-05 1 01 01
2014-01-06 2 01 02
2014-01-07 3 01 02
已选择10行。
对比日历,发现2013年12月30,31日的IW,均划分到了2014年的第一周
MONTH YWE MWEEK SUN MON TUE WED THU FRI SAT
------ --- ----- ----- ----- ----- ----- ----- ----- -----
12月 49 1 1 2 3 4 5 6 7
50 2 8 9 10 11 12 13 14
51 3 15 16 17 18 19 20 21
52 4 22 23 24 25 26 27 28
53 5 29 30 31
---------- - -- --
2013-12-29 1 52 52
2013-12-30 2 52 01
2013-12-31 3 53 01
2014-01-01 4 01 01
2014-01-02 5 01 01
2014-01-03 6 01 01
2014-01-04 7 01 01
2014-01-05 1 01 01
2014-01-06 2 01 02
2014-01-07 3 01 02
已选择10行。
对比日历,发现2013年12月30,31日的IW,均划分到了2014年的第一周
MONTH YWE MWEEK SUN MON TUE WED THU FRI SAT
------ --- ----- ----- ----- ----- ----- ----- ----- -----
12月 49 1 1 2 3 4 5 6 7
50 2 8 9 10 11 12 13 14
51 3 15 16 17 18 19 20 21
52 4 22 23 24 25 26 27 28
53 5 29 30 31
1)ww的算法为每年1月1日为第一周开始,date+6为每一周结尾
例如20050101为第一周的第一天,而第一周的最后一天为20050101+6=20050107
公式 每周第一天 :date + 周 * 7 - 7
每周最后一天:date + 周 * 7 - 1
2)iw的算法为星期一至星期日算一周,且每年的第一个星期一为第一周,
例如20050101为星期六,所以用iw的算法是前年的53周,而20050103之后才是第一周的开始。
公式 每周第一天 :next_day(date) + 周 * 7 - 7
每周最后一天:next_day(date) + 周 * 7 - 1
按照上述算法,WW的结果相差较远,而IW比较贴近我们对于日期的认识。
但是问题是IW存在边界问题。它会认为12月30日和31日是2014年的第一周,以此统计年历,则会出现问题。所以new_yweek解决的应该是这个问题。
可以使用下面的方法解决IW边界问题。
select case
when (yweek = min(yweek)over(partition by mon order by yweek)) then
mon_name
else
null
end as month,
yweek as yweek,
row_number() over(partition by mon order by yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
)
)
group by mon, mon_name, yweek
/
解决IW边界问题:
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek
decode部分,如果日期是周日,则将iw的值+1,以便日历对齐。when (yweek = min(yweek)over(partition by mon order by yweek)) then
mon_name
else
null
end as month,
yweek as yweek,
row_number() over(partition by mon order by yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
)
)
group by mon, mon_name, yweek
/
解决IW边界问题:
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek
而max分析函数部分,解决类似12月30日,31日划分到下一年的问题。
这个实现存在一些问题。
如果元旦是周五,周六或者周日,例如20110101,它是周六,IW会认为这天是2010年的第五十二周。
SQL> select to_char(to_date('20110101','yyyymmdd'),'IW') from dual;
TO
--
52
对于这个问题,我没有解决的方法,但是一个同事有另外一个方案,用自定义的周序列,使用偏移量。TO
--
52
select case
when (yweek = min(yweek)over(partition by mon order by yweek)) then
mon_name
else
null
end as month,
yweek as yweek,
row_number() over(partition by mon order by yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
ceil(to_number(dayIndex)/7) as yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (
select
to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear,
to_char(s.firstday,'d')+rownum dayIndex
from
dual,(select to_date(&year || '0101','yyyymmdd')-1 firstday from dual) s
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
)
)
group by mon, mon_name, yweek
/
when (yweek = min(yweek)over(partition by mon order by yweek)) then
mon_name
else
null
end as month,
yweek as yweek,
row_number() over(partition by mon order by yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
ceil(to_number(dayIndex)/7) as yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (
select
to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear,
to_char(s.firstday,'d')+rownum dayIndex
from
dual,(select to_date(&year || '0101','yyyymmdd')-1 firstday from dual) s
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
)
)
group by mon, mon_name, yweek
/
case when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1')
and
(to_char(dayofyear, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd'))
then
to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
else
to_char(dayofyear, 'ww')
end as new_yweek
以2013年一月为例,元旦是周二(不是周日,满足了Case When的第一个条件)and
(to_char(dayofyear, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd'))
then
to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
else
to_char(dayofyear, 'ww')
end as new_yweek
6,7日是周日、周一,小于元旦的周二,所以所在周+1,那么行列转置后在日历上就下沉了一层。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-776350/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-776350/