BigQueryの週次集計でハマらないためのISOWEEK活用法

BigQuery や各種BIツールを利用して、週次でのデータ分析を行うことは良くあることかと思います。特に、私たちのような放送業界では「週間の視聴率分析」など、週単位での振り返りは非常に馴染みのあるシチュエーションです。

そういった際、「とりあえず週ごとに丸めて集計したい」だけであれば、DATE_TRUNC(date_col, WEEK) を使って週始めの日付に変換してグルーピングするのが定石です。

しかし、分析の要件として以下のような要望が出てくると、話が少し変わってきます。

「週番号（第◯週）で管理したい」
「昨年の第1週と数値を比較したい（昨対比分析）」

「週番号が必要なら EXTRACT(WEEK ...) を使えばいいのでは？」そう考えがちですが、実はその方法だと年末年始の集計結果が分断され、意図しない形になるケースがあります。

今回は、そんな「週番号」が必要なシーンで必須となる ISOWEEK の挙動と、その活用法についてご紹介します。

`EXTRACT(WEEK ...)` の落とし穴

「今日が今年の第何週目か」を知りたい時、最初に思い浮かぶのが EXTRACT(WEEK FROM date_col) です。直感的であり、単年の集計であれば問題ないケースも多いです。

しかし、この関数はあくまでカレンダーの「年」に基づいた週番号を扱うため、年末年始の扱いに注意が必要です。BigQuery の仕様上、EXTRACT(WEEK) は「新しい年になった瞬間（1月1日）」にカウントがリセットされてしまうからです。

例えば、2025年12月29日(月) 〜 2026年1月4日(日) という1週間を想定してみましょう。週間レポートを作成する場合、本来この期間は「年末年始の1週間」としてひと塊で扱いたいところです。

しかし、EXTRACT(WEEK(MONDAY) ...) を使用して月曜始まりとして週次で集計しようとすると、以下のように分断されてしまいます。

2025-12-29 〜 2025-12-31 → 2025年の第52週
2026-01-01 〜 2026-01-04 → 2026年の第0週

このように、本来1つの週であるはずの期間が、年が変わった瞬間に「第52週」と「第0週」という別々のグループになってしまいます。 これでは、「昨年の第1週(0週)との平均視聴率の比較」を行いたくても、週の区切りが年によって異なるため、正しい分析ができなくなってしまいます。

そこで有効なのが、今回ご紹介する ISOWEEK です。

そもそも ISOWEEK とは？

ISOWEEK は、国際規格である ISO 8601 に基づいた週の定義です。 BigQuery 独自の機能ではなく、世界中で広く使われている標準的なルールです。

大きな特徴は以下の2点です。

月曜始まり であること
「その週の木曜日が含まれる年」 をその週の「年」として扱うこと

つまり、「1月1日を含む週」を無条件に新年の第1週とするのではなく、「週の過半数（4日以上）が新しい年に属しているか？」 で所属する年を判定します。

このルールのおかげで、週の途中で年が変わっても、データが分断されることなく「どちらかの年の週」としてひと塊で扱えるのです。

ISOWEEK を利用するメリット

単なる日付の丸めなら DATE_TRUNC で十分ですが、分析の種類によっては EXTRACT(ISOWEEK ...) に明確なメリットがあります。

1. 「共通言語」としての週番号

DATE_TRUNC はあくまで「日付」の情報ですが、ISOWEEK を使えば「これは第1週」「これは第52週」という明確な ラベル を持つことができます。

ビジネスの現場では、「第1週の視聴率が〜」「第52週の売上が〜」といった具合に、週番号でコミュニケーションをとる場面が少なくありません。 ISO規格に基づいた ISOWEEK を使えば、年末年始のような期間であっても 「ここは2026年の第1週として扱う」 という定義ができ、チーム内で認識を統一しやすくなります。

2. 過去数年分の「季節性」を比較しやすい

例えば 「過去5年間、第1週のパフォーマンスはどう推移したか？」 を調べたい場合を考えてみます。日付（DATE_TRUNC）で管理していると、年によって「1/4始まり」「1/3始まり」と日付がズレるため、単純なグルーピング（GROUP BY）が困難です。

一方、ISOWEEK なら全ての年で「1〜53」という共通のインデックスが振られます。 GROUP BY iso_week とするだけで、過去のデータを横並びで集計 できるようになります。

これにより、「例年、第2週は数字が落ち込む傾向がある」といった季節性の可視化が非常にスムーズになります。

実際の挙動を確認する

では、実際のクエリ結果を見てみましょう。問題の「年末年始（2025年12月29日〜2026年1月4日）」を、全て月曜始まりとして、3つの方法(+α)で抽出して比較します。

SELECT
  date_value,
  -- A. 通常の抽出（年またぎで分断される）
  EXTRACT(YEAR FROM date_value) AS normal_year,
  EXTRACT(WEEK(MONDAY) FROM date_value) AS normal_week,

  -- B. DATE_TRUNC（年またぎでも統一されるが、週番号はわからない）
  DATE_TRUNC(date_value, WEEK(MONDAY)) AS trunc_date,

  -- C. ISO規格（年またぎでも統一され、週番号もわかる）
  EXTRACT(ISOYEAR FROM date_value) AS iso_year,
  EXTRACT(ISOWEEK FROM date_value) AS iso_week,

  -- +α. ISO規格(文字列)
  FORMAT_DATE('%V', date_value) AS iso_week_str
FROM UNNEST([
  DATE('2025-12-29'), -- 月曜日
  DATE('2025-12-30'), -- 火曜日
  DATE('2025-12-31'), -- 水曜日
  DATE('2026-01-01'), -- 木曜日（元日）
  DATE('2026-01-02'), -- 金曜日
  DATE('2026-01-03'), -- 土曜日
  DATE('2026-01-04')  -- 日曜日
]) AS date_value;

実行結果：

結果のポイント

normal_week

年が明けた瞬間に 52 から 0 に変わっています。これでは同じ週として集計されません。
trunc_date

日付としては 2025-12-29 に統一されているので集計自体は可能です。ただ、これだけだと「2026年の第1週」という情報は読み取れません。
iso_week

年末の12/29であっても、全て「第1週」 として統一されています。

⚠️ 注意点：ISOYEARも併用するべき

ISOWEEKを使う際は、必ず ISOYEAR とセットで使ってください。もし通常の YEAR と ISOWEEK を組み合わせてしまうと、以下のような矛盾が生じます。

日付：2025-12-30
YEAR：2025
ISOWEEK：1

結果として、 「2025-12-30 = 2025年の第1週」 となってしまいます。

これは本来「2026年の第1週」として扱いたいデータです。カレンダー上の年とISO週番号の年がズレる期間があるため、年も含めて扱う場合は、必ず EXTRACT(ISOYEAR ...) を使用して「ISO定義上の年」を取得する必要があります。

補足：FORMAT_DATE関数

また、+αとして出力した iso_week_str のように FORMAT_DATE('%V', date_value) を使っても、同じロジックで ISO週番号（'01', '53' などの文字列）が取得可能です。計算に使いたい時は EXTRACT、レポートのラベルとしてゼロ埋めで出力したい時は FORMAT_DATE といった使い分けが便利です。

まとめ

今回は ISOWEEK について解説しましたが、全てのシーンでこれが正解というわけではありません。

ざっくり日付の推移が見たいなら 👉 DATE_TRUNC(date, WEEK)
カレンダー通りの「年」で区切りたいなら 👉 EXTRACT(WEEK ...)
年をまたいで分析したり、他システムと基準を合わせるなら 👉 EXTRACT(ISOWEEK ...)

SQLには同じ「週次集計」でもいくつかのアプローチがあります。

「なんとなくこれ」ではなく、 「今やりたい分析にはどの定義が適しているか？」 を意識してクエリを記述できるようになると、集計ミスが減り、分析の質も向上するはずです。

一つの集計においても様々な方法があるため、分析の要件を常に意識しながら、適切なアプローチを選択していきたいですね。