月曜日から始まる週番号の計算

その年の何日目かとその日の曜日が分かる状態で、その日が何週目なのかを数える式について。

週番号の定義

週番号には色々定義がある。
* 1週間は月曜日〜日曜日または日曜日〜月曜日
* 1月頭や12月末を前年に含めるまたは後年に含める
最初の木曜日が入ってる週が1週目とか色々あるけれど、
今回求めたいのは次を満たすもの。

・1週間は月曜日〜日曜日
・1月1日は1週目、12月31日は53週目

プログラムで普通に関数用意されてそうなのですが、hiveにはこの条件を満たすものがなかった。多分。

例

1月1日が日曜日のとき、1月1日は1週目、1月2~8日は2週目、前年の12月31日は前年の53週目
1月1日が月曜日の時、1月1~7日は1週目、1月8~15日は2週目

みたいな感じ。

結論

$D$:その年の何日目か(例：2月3日なら34)
$u$:その日が何曜日か(月=1,...,日=7)
[x]:xを超えない最大の整数（ガウス記号）

[\frac{14 + D - ((D - u + 8) \% 7)}{7}] - [\frac{9 - ((u - (D \% 7) + 8) \% 7)}{7}]

hiveQLで書いてみる

CAST((
  (
    14
    + from_unixtime(unix_timestamp('2017-03-19 00:00:00','yyyy-MM-dd HH:mm:ss'),'D')
    -
    (
      from_unixtime(unix_timestamp('2017-03-19 00:00:00','yyyy-MM-dd HH:mm:ss'),'D') 
      - from_unixtime(unix_timestamp('2017-03-19 00:00:00','yyyy-MM-dd HH:mm:ss'),'u')
      + 8
    )
    % 7
  )
  / 7
) AS BIGINT)
-
CAST( (9 - from_unixtime(unix_timestamp('2017-01-01 00:00:00','yyyy-MM-dd HH:mm:ss'),'u'))/7  AS BIGINT)

最後のところその年の1月1日の曜日を使うことですこし簡潔になります。
treasure dataならTD_DATE_TRUNCとかを使うと年の初日を出せます。
LPAD(CAST~(略)~,2,'0')を使うと1週目は01のように0で埋めれます。('ww'の形式)

もっといろいろ省略できそう。
もしわかったらコメント欄とかで教えて下さい。
%7やってるのに+8してるとか+1でよくない？ってなると思いますが、負になったときちゃんと動いてくれない言語もあるので負にならないようにしました。

なんというかとても汚いので可能ならユーザー定義関数作ってやりたいですね。