Prestoでweek関数とyear関数を組み合わせてグルーピングを行う時の注意点

週次で集計を行いたいときに、以下のようにweek関数とyear関数でグルーピングをしようとしました。

with temp as (
  select
    week(from_unixtime(unixtime)) as week
    , year(from_unixtime(unixtime)) as year
    , id
  from
    table
)

select 
  week
  , year
  , count(id)
from
  temp
group by
  week
  , year 
sort by
   week
   , year

これで週次の集計ができると思っていたのですが、week関数は1/1以降の最初の月曜日からカウントし始めるのに対して、year関数は1/1になると曜日関係なしに年が変わります。

そのため、1/1以降の日でweek関数だとまだ52や53とカウントされていてもyear関数の年が新しい年になっている場合があります。

その影響で、year関数とweek関数を組み合わせてグルーピングしたときに、例えばyyyy/1/1とyyyy/12/31が同じグループに入ってしまう場合があります。

これを避けるためには以下のようにする必要があります。

with temp as (
  select
    week(from_unixtime(unixtime)) as week
    , date_format(from_unixtime(unixtime), '%v') as year
    , id
  from
    table
)

select 
  week
  , year
  , count(id)
from
  temp
group by
  week
  , year 
sort by
   week
   , year