【BigQuery】カテゴリー別に分析するならPARTITION BYを使おう！LAG関数の使い方を紹介

以前LAG関数の使い方についてまとめました。

今回は前回紹介しきれなかった「PARTITION BY」の使い方についてご紹介します。
「PARTITION BY」を使うとカテゴリー別に分析ができるので便利ですよ！

サンプルデータ〜入場者数を曜日別に分析する〜
LAG（PARTITION BY）を使ってみる
まとめ

サンプルデータ〜入場者数を曜日別に分析する〜

ここに2023年7月の美術館の入場者数データがあります。（見切れているが7月21日以降もあり）

日付
曜日
1日の入場者数

この3つのカラムを持っており、前週の同じ曜日と比較してどれくらい増減したか求めていきましょう。

LAG（PARTITION BY）を使ってみる

LAG関数の使い方をおさらいです。

LAG (対象カラム, nレコード分だけ下にずらす) OVER( [PARTITION BY ～] ORDER BY ～ )

LAG関数を使うと対象カラムをn個分レコードをずらしてカラム追加ができます。

レコードを曜日別に分類する

前の週と比較しやすいように曜日別に並び替えましょう。

見やすくなりましたね。
続いて「前週の入場者数が入ったカラム」を追加しましょう！

「前週の入場者数カラム」を追加する

SELECT
  date AS `日付`,
  day AS `曜日`,
  num AS `1日の入場者数`,
  LAG(num, 1) OVER( PARTITION BY day ORDER BY day asc) AS `前週の入場者数` 
FROM
  `sql-book-384011.sample.20230723_musium_partition_by_day`
ORDER BY
  day ASC,
  date asc

「PARTITION BY」に day を指定することで前週の入場者数カラムが追加できました。
もし「PARTITION BY」に day がないと6行目の「前週の入場者数」には 3500 が入ってしまいます。

3500 は2023年7月29日の土曜日の入場者数なので、2023年7月2日の日曜日の入場者数のセルには入ってほしくありません。

このように「PARTITION BY」を指定することで曜日別に正しく分析ができました。

最後に「前週との入場者数の差」と「増減率」を求めましょう！

「前週との入場者数の差」と「増減率」を求める

SELECT
  date AS `日付`,
  day AS `曜日`,
  num AS `1日の入場者数`,
  LAG(num, 1) OVER(PARTITION BY day ORDER BY day asc) AS `前週の入場者数`,
  num - LAG(num, 1) OVER(PARTITION BY day ORDER BY date ASC) AS `前週との入場者数の差`,
  ROUND((num - LAG(num, 1) OVER(PARTITION BY day ORDER BY date ASC)) / (LAG(num, 1) OVER(PARTITION BY day ORDER BY day asc)), 2) * 100 AS `増減率`
FROM
  `sql-book-384011.sample.20230723_musium_partition_by_day`
ORDER BY
  day ASC,
  date asc

「前週との入場者数の差」と「増減率」が求まりました。
クエリが長くてちょっと読みづらいですね。そんなときはWITH句でクエリ分割すると良いかもしれません。