4月１日を含む週を先頭にした週番号

Question

前提・実現したいこと

ＩＳＯ規格だと１月からスタートした週番号で
かつ、過半数が含まれているほうに週番号'1'が設定される（１月１日が前年の５３週目になる）
４月１日を含む週を週番号の'1'にしたい
（４月１日が金曜だった場合３月２８日の週から順番に番号を振っていく）

試したこと

単純に月を減算する→日付のデータ自体は変わらないことに気づく
４月１日の曜日を抽出し、対応する日数を日付から減算する
→他テーブルを使わないといけなくなり頓挫

補足情報（FW/ツールのバージョンなど）

SQLデベロッパ-バージョン17.4.0.355を使用

曜日番号は日曜日が１で土曜日が７の順序になり、求める週番号は月曜スタートになります。
今年だと4/1は月曜になるので
4/14/7までが週番号'1'
4/84/14までが週番号'2'
となっていくようにしたいです。

テーブル名：'DATE'
カラム:YEAR char
MONTH char
DAY char
NYEAR(年度) char
K_DAY(曜日番号) char
YMD(年月日) DATE
KI(0→前期,1→後期) char

Comments

[m.ts10806]

曜日番号は０が日曜日で開始ということで良いですよね？
仕様をもう少し明確に書かれた方がいいかもしれません。

[m.ts10806]

あと念のためSQLのデータベースの種類とバージョンを追記してください（種類についてはタグにも追加をお願いします）

[mather]

今年でいうと4/1が月曜日なので、
3/30(土) ->
3/31(日) ->
4/1(月) ->
4/6(土) ->
4/7(日) ->
のような具体的な日付がどんな週番号になるべきかを質問に追記しましょう。

Answer 1

日次書式モデル ISO 8601標準 IW, IYYY

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD' ;

SELECT
    TO_CHAR(A.YMD, 'YYYY/MM/DD IYYYIW') AS YMD
FROM(
    SELECT TO_DATE('2019/01/01', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/03/30', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/03/31', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/04/01', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/04/07', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/04/08', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2019/12/31', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/01/01', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/03/30', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/03/31', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/04/01', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/04/07', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/04/08', 'YYYY/MM/DD') AS YMD FROM DUAL UNION ALL
    SELECT TO_DATE('2020/12/31', 'YYYY/MM/DD') AS YMD FROM DUAL
    ) A ;

YMD
----------------------------------
2019/01/01 201901
2019/03/30 201913
2019/03/31 201913
2019/04/01 201914
2019/04/07 201914
2019/04/08 201915
2019/12/31 202001
2020/01/01 202001
2020/03/30 202014
2020/03/31 202014
2020/04/01 202014
2020/04/07 202015
2020/04/08 202015
2020/12/31 202053

14行が選択されました。

だから、
IW > 13 は マイナス 13 それ以外はプラス13 で 4月頭頃(4月1日が初日ではない)を年度の第1週とするFUNCTIONを作って対応しては？
これで良いかは社内できちんと検討してください。

Comments

[bottle]

回答ありがとうございます。
社内で話し合ってみます！

Answer 2

4月1日は1月1日から数えて90ないし91日後（31+28or29+31）なので、曜日のずれは90 mod 7 = 6（つまり一つ戻る）か、91 mod 7 = 0（同じ曜日）になります。

1月1日の曜日	平年の4月1日の曜日	閏年の4月1日の曜日
月	日	月
火	月	火
水	火	水
木	水	木
金	木	金
土	金	土
日	土	日

ISO 8601 ではぶっちゃけ、1月1日が「金・土・日」の場合は前の週から始まったことになるので、これを4月1日基準にする場合も同様に補正してやらねばならないでしょう。
※上の表で太字になっている部分は、週番号が1減る

従って通常は1月1日起算の週番号で、（m月d日の週番号）-（4月1日の週番号）でよいのですが、
・平年で4月1日が日曜日の場合
・平年で4月1日が木曜日の場合
は、補正が必要になるかと思います。

Comments

[bottle]

回答ありがとうございます。
ISOの週番号で計算して４月１日の週番号を減算
曜日を取得してCASE~WHEN文などで補正を加える
ということで問題ないでしょうか？

Answer 3

思いつきレベルですが、5/1の週番号(おそらく仕様からすると5)を求めたいとして
↓こういうことではないのですか？

SELECT WEEK('2019-05-01',3) - WEEK('2019-04-01',3) + 1;

同じ年度かの検証は、1/1はじまりでも必要な話なのでやっているとして、負になったら4/1の週番号と求めたい日の週番号の加算(と1の調整)で求まりそうなんですが？

※ すみません、MySQLでやってるので、WEEKの使いかたは考えてません。

Comments

[bottle]

同年度の検証は行っております。

WEEKについてなのですが、無効な識別子とエラーが出て使用できません。

[bottle]

すいません！回答ありがとうございます！返信送れて申し訳ありません！

[papinianus]

WEEKはOracleだとTO_DATE(日付, 'IW')みたいな表現っぽいです

[bottle]

返信ありがとうございます。
TO_DATE構文でいろいろ試してみたのですがエラーがでて実行できません。
エラーは　日付の書式コードが無効です　とでます。
クオーテーションをはずしたりカラム名で問い合わせてみたりしたのですが、

[papinianus]

Oracleで週番号がそもそも得られない、という質問だとは思っていなかったので、すみませんがお力になれません。
回答の趣旨は、目的の日の週番号と基準としたい日の週番号との加算または減算で求まる性質の事柄ではないでしょうか、という点に尽きます。

[bottle]

返信ありがとうございます。
TO_CHAR構文をさらにくっ付けると週番号は求めることができたのですが（１月基準）
減算などをしようとするとエラーが出るようです。

[papinianus]

数値じゃないのですね。では週番号から加減算しようとするのはoracleでは無理みたいですね。失礼しました。