用SQL語句得到指定日期所在的周是本年度第幾周

昨天，客戶提了一個需求，需求中有要用到求指定日期所在的周是本年度的第幾周
看到這個問題，我很自然的想到了to_char(d,'ww')這個用法：
SQL> select date'2010-01-1' + rownum - 1 r,
  2         to_char(date'2010-01-1' + rownum - 1,'d')-1 wn,
  3         to_number(to_char(date'2010-01-1' + rownum - 1,'ww')) nthweek
  4    from dual
  5  connect by rownum <= date'2010-01-27' - date'2010-01-1' + 1
  6  /
 
R                   WN    NTHWEEK
----------- ---------- ----------
2010-1-1             5          1
2010-1-2             6          1
2010-1-3             0          1
2010-1-4             1          1
2010-1-5             2          1
2010-1-6             3          1
2010-1-7             4          1
2010-1-8             5          2
2010-1-9             6          2
2010-1-10            0          2
2010-1-11            1          2
2010-1-12            2          2
2010-1-13            3          2
2010-1-14            4          2
2010-1-15            5          3
2010-1-16            6          3
2010-1-17            0          3
2010-1-18            1          3
2010-1-19            2          3
2010-1-20            3          3
2010-1-21            4          3
2010-1-22            5          4
2010-1-23            6          4
2010-1-24            0          4
2010-1-25            1          4
2010-1-26            2          4
2010-1-27            3          4
 
27 rows selected
從以上資料來看，第一組顏色是對應的真正的第一週，但這個函式卻把第一個7天作為了第一週，使得實際的第一週跟得到的第一週差了4天的時間，這四天直接導致了後面資料的不準確。貌似網上也沒有查到過直接可以解決這個問題的Oracle系統函式，沒辦法，只好自己寫了個SQL：
SQL> with tmp as(select date'2010-01-27' d from dual)
  2  select r, wn,
  3         sum(decode(wn, 1, 1, 0))over(order by rownum) +
  4         decode(to_char(trunc(d, 'y') - 1, 'd'),1,1,1) nthweek
  5    from (select trunc(d, 'y') + rownum - 1 r, d,
  6                 to_char(trunc(d, 'y') + rownum - 1, 'd') - 1 wn
  7            from tmp
  8          connect by rownum <= d - trunc(d, 'y') + 1)
  9  /
 
R                   WN    NTHWEEK
----------- ---------- ----------
2010-1-1             5          1
2010-1-2             6          1
2010-1-3             0          1
2010-1-4             1          2
2010-1-5             2          2
2010-1-6             3          2
2010-1-7             4          2
2010-1-8             5          2
2010-1-9             6          2
2010-1-10            0          2
2010-1-11            1          3
2010-1-12            2          3
2010-1-13            3          3
2010-1-14            4          3
2010-1-15            5          3
2010-1-16            6          3
2010-1-17            0          3
2010-1-18            1          4
2010-1-19            2          4
2010-1-20            3          4
2010-1-21            4          4
2010-1-22            5          4
2010-1-23            6          4
2010-1-24            0          4
2010-1-25            1          5
2010-1-26            2          5
2010-1-27            3          5
 
27 rows selected
這次，顯示的結果便是真正要求的第幾周了。
下面的指令碼是直接得到第幾周的：
with tmp as(select date'2010-01-27' d from dual)
select sum(1)+decode(to_char(t.d,'d'),1,1,1) nthweek
  from (select to_char(trunc(d, 'y') + rownum - 1, 'd') - 1 nd
          from tmp
        connect by rownum <= d - trunc(d, 'y') + 1), tmp t
 where nd = 1
實現的原理其實很簡單，即首先把當前所在日期的年的第一天取出，然後構造從第一天到改天的所有資料（最多也就366條），並把這些天都轉換成星期幾（與實際星期幾對應），最後把每個星期一都取出來進行累加，考慮到年初第一天可能不是週一，需要特殊處理外，其他直接相加即可