【GCP】BigQueryでウィンドウ関数（分析関数）を使ってデータ分析してみる

  -- 分析関数を使わずに、user_action_allテーブルから、各userIDの最新のデータを全カラムSELECT
SELECT
  user_action_all.*
FROM
  `evocative-entry-277709.yamaguchi_test.user_action_all` AS user_action_all
INNER JOIN (
  SELECT
    userID,
    MAX(timestamp) AS max_timestamp
  FROM
    `evocative-entry-277709.yamaguchi_test.user_action_all`
  GROUP BY
    1 ) AS latest_user_action
ON
  user_action_all.userID = latest_user_action.userID
  AND user_action_all.timestamp = latest_user_action.max_timestamp;

-- 分析関数を使って、user_action_allテーブルから、各userIDの最新のデータを全カラムSELECT
SELECT
    *
  FROM
    (
      SELECT
          *,
          ROW_NUMBER() OVER(PARTITION BY userID ORDER BY timestamp DESC) AS rn
        FROM
          `evocative-entry-277709.yamaguchi_test.user_action_all`
    ) AS t
  WHERE t.rn = 1;

先ほどよりスッキリしたSQLですが、実はさらにスッキリさせることができます（詳細は後述）。

SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY userID ORDER BY timestamp DESC) AS rn
FROM
  `evocative-entry-277709.yamaguchi_test.user_action_all`
QUALIFY
  rn=1

実行するとこちらも抽出したいデータを得ることができました。

よく見ると、SQLに見慣れない一文があります。

ROW_NUMBER() OVER(PARTITION BY userID ORDER BY timestamp DESC) AS rn

この部分です。ここがウィンドウ関数の正体です。

ここからは章を分けて、ウィンドウ関数について詳細を見ていきます。

ウィンドウ関数とは（詳細）

先ほどはウィンドウ関数について、図を使ってイメージを把握していただきましたが、ここからもう少し詳しく見ていきます。

ウィンドウ関数の「ウィンドウ」は、結果セットの中の特定の一部分のことを指します。

下図のような、「窓越しに風景の一部を見る」絵を想像していただけるとわかりやすいかと思います。

またまたイメージの話になってしまいましたが、本題に移ります。

ウィンドウ関数を使用するには、「OVER句」が必要になります。このOVER句内の3要素によって、ウィンドウを細かくコントロールすることができます。

• パーティション：データを分割
• オーダー：パーティション内の結果に順序付け
• フレーム：現在の行に対して相対的に移動するスライディングウィンドウフレームを指定可能（※今回は省略）

今回、実践で使用したデータに当てはめてみます。

ROW_NUMBER() OVER(PARTITION BY userID ORDER BY timestamp DESC) AS rn

• PARTITION BY userID：userIDごとにデータを分割
• ORDER BY timestamp DESC：userIDごとのデータをtimestampで降順にソート

ここまでで、下記のデータが取得されます。

上記結果を見ると、一番右の「rn」列でタイムスタンプをもとにランク付けがされていることがわかります。

今回のケースでは、ウィンドウ関数で取得したそれぞれのユーザのデータから、ランク（rn）が「1」のものを取得することで各ユーザの最新のデータを取得することができます。

ウィンドウ関数の結果を絞り込みは、QUALIFYを使用して実現することができます。

QUALIFY   rn=1

先ほど実践にて「実はさらにスッキリさせることができます（詳細は後述）」と、せっかくGeminiが生成したSQLに少しケチをつけてしまいましたが、ウィンドウ関数の結果を絞り込む際はQUALIFYを使用することをオススメします。

QUALIFYを使用することで、今回のケースのように「サブクエリが削減できる」等のメリットがあります。