カレンダーと第何週目かをOracle Databaseで取得する方法

やりたいこと

対象の日付が、その月の第何週目にあるのかを出したい。(週の開始は日曜日とする)
2019年12月で例えると、イメージは以下。

必要になった背景

週ごとに値を集計するSQLを書くことになり、調べてもto_charの'w'でその日付が第何○曜日であるかの求め方しか出てこなかったので、備忘録として投稿。

結論

ロジックやSQLだけ知りたい人向けに、先に結論を書いておきます。

式

(対象日付 + 月初日付の曜日番号 - 1 + 6 ) ÷ 7

SQLサンプル

SELECT
  TRUNC((TO_NUMBER(TO_CHAR(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'DD')) + TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'MONTH'),'D')) - 1 + 6) / 7,0) as week
 FROM
   DUAL

具体的な方法

まずは、月初めの曜日を気にせずに、その日付が第何週にあるかを7で割った商で求めます。
第1周目は1と出すため、日付に6を足します。
(7日に7を足して14にすると2になってしまうので、6を足します)

例）
x月1日 + 6 / 7 ＝ 1 (あまり0)
x月2日 + 6 / 7 ＝ 1 (あまり1)
x月3日 + 6 / 7 ＝ 1 (あまり2)
x月4日 + 6 / 7 ＝ 1 (あまり3)
x月5日 + 6 / 7 ＝ 1 (あまり4)
x月6日 + 6 / 7 ＝ 1 (あまり5)
x月7日 + 6 / 7 ＝ 1 (あまり6)

ここまでで、以下の式が完成します。
(対象日付 + 6) ÷ 7

次に、その月の開始曜日を求めます。
カレンダーは必ず日曜日で始まってくれるわけではないので、考慮する必要があります。。。

そこで、開始日付の曜日番号を取得します。
※Oracleでは、曜日番号はto_charの'D'で取得できます。

各曜日番号は以下の通りです。

番号	曜日
1	日曜日
2	月曜日
3	火曜日
4	水曜日
5	木曜日
6	金曜日
7	土曜日

2020年1月を例に挙げると、以下のようにします。

日曜日から開始曜日までの差分を求めます。
2020年1月は開始曜日が水曜日で、日曜日から3日経っているので先程の式の対象日付のあとに曜日番号 - 1を加えます。
(対象日付 + 曜日番号 - 1 + 6) ÷ 7

これで式が完成しました。
試しに2020/01/01を対象日付にすると、以下のようになります。
(1 + 4 - 1 + 6) ÷ 7 = 1週目

SQLでカレンダーを表示しその日付に対応する週番号を表示する

これまでの考え方を使用して、2020/01/01～2020/01/31の日付と週番号を求めるサンプルは以下のようになります。

SQLサンプル

SELECT
  TRUNC(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'MONTH') + ROWNUM - 1 as ac_date,
  TRUNC((TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'MONTH') + ROWNUM - 1,'DD')) + TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'MONTH'),'D')) - 1 + 6) / 7,0) as week
 FROM
   ALL_CATALOG
 WHERE
   TRUNC(TO_DATE('2020/01/01', 'YYYY/MM/DD'),'MONTH') + ROWNUM - 1 <= LAST_DAY(TO_DATE('2020/01/01', 'YYYY/MM/DD'))

実行結果

AC_DATE        WEEK
-------- ----------
20-01-01          1
20-01-02          1
20-01-03          1
20-01-04          1
20-01-05          2
20-01-06          2
20-01-07          2
20-01-08          2
20-01-09          2
20-01-10          2
20-01-11          2
20-01-12          3
20-01-13          3
20-01-14          3
20-01-15          3
20-01-16          3
20-01-17          3
20-01-18          3
20-01-19          4
20-01-20          4
20-01-21          4
20-01-22          4
20-01-23          4
20-01-24          4
20-01-25          4
20-01-26          5
20-01-27          5
20-01-28          5
20-01-29          5
20-01-30          5
20-01-31          5

31行が選択されました。