SQL生成日曆

http://jackywood.itpub.net/post/1369/127565

一條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);

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

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，以便日曆對齊。
而max分析函式部分，解決類似12月30日，31日劃分到下一年的問題。
這個實現存在一些問題。
如果元旦是週五，週六或者週日，例如20110101，它是週六，IW會認為這天是2010年的第五十二週。

SQL> select to_char(to_date('20110101','yyyymmdd'),'IW') from dual;

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

  /

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的第一個條件）
6,7日是週日、週一，小於元旦的週二，所以所在周+1，那麼行列轉置後在日曆上就下沉了一層。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-776350/，如需轉載，請註明出處，否則將追究法律責任。

SQL生成日曆

相關文章