Google BigQuery

Big QueryでWindow関数を用いて、累積和を計算する

こんにちは。エクスチュアでインターンをさせて頂いている中野です。

今回は、Big Query上で累積和を計算する方法を記事にまとめたいと思います。

SQLではGROUP BY句のほかに、Window関数と呼ばれるものを使うことでも集計を行うことができます。
JOINとGROUP BYを併用する方法と、Window関数を用いる方法の2種類をここでは紹介します。
また、おまけとして、フラグをつけたところまでの和を集計する方法も記載します。

この記事が、多くの方の手助けとなれば幸いです。

目次


・累積和を計算する方法①:JOINを用いる
・累積和を計算する方法②:Window関数を用いる
・(おまけ):フラグをつけたところまでの和を計算する

累積和を計算する方法①:JOINを用いる


まずは、データソースをJOINする方法についてです。

考え方を完結にまとめると、
①:累積和を取りたい列を、「<=」という演算子を用いてJOINする
②:GROUP BY句を用いて、SUMをとる
という流れになります。

以下は、今回用いたデータセットです。
各IDごとに、月次の金額の累積和が求めたいものとします。

使用するコードは以下の通りです。

SELECT 
a.ID,
a.month,
SUM(b.money) AS result
FROM `YOUR_PROJECT_NAME.YOUR_DATASET_NAME.YOUR_TABLE_NAME` AS a
INNER JOIN 
`YOUR_PROJECT_NAME.YOUR_DATASET_NAME.YOUR_TABLE_NAME` AS b
ON a.month >= b.month AND a.ID = b.ID
GROUP BY a.ID,a.month
ORDER BY 1,2

これを実行すると、以下のような結果が出力されて、無事に累積和が計算されたことが分かります。

ちなみに、SUM(b.money)とするべきところをSUM(a.money)としてしまうと、全く異なった結果が得られてしまいます。
これは、JOIN後のテーブルが以下のような形になっているからです。
もちろん、JOINの方法が変われば集計方法も変わります。

累積和を計算する方法②:Window関数を用いる


次に、Window関数を用いる方法についてです。

Window関数とは、集計の粒度を独自に設定できる関数です。
SUM()やCOUNT()といった集計関数を用いる際は、GROUP BY句を使用する方法が直ちに頭に浮かぶかと思いますが、GROUP BY句を用いると行が圧縮されてしまうという特徴があります。

つまり、
元のデータ形式を維持したまま、集計結果を新たな列として追加したい場合には、GROUP BY句は向いていないということです。
(単純にSUM()などの結果を加えたいのであれば、GROUP BYした結果をJOINする方法も考えられますが…)

その点、Window関数は、

・自分で設定した粒度で、集計をしてくれる
・行が圧縮されることはなく、元のデータ形式に列を追加するだけ
・多様なオプションがついている(集約したうえで、どの行を使うか選べる等)

といった特徴を持ち合わせているので、今回のような累積和を求める場合には向いています。

使用するコードは以下の通りです。

SELECT
ID,
month,
SUM(money) OVER(PARTITION BY ID ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM `YOUR_PROJECT_NAME.YOUR_DATASET_NAME.YOUR_TABLE_NAME`

こちらの結果も、同様に正しい累積和が計算されていることが分かります。

簡単にWindow関数の使用方法をまとめておくと、

・OVER(PARTITION BY “AAA”,”BBB”,…)で、”AAA”,”BBB”,…という列で集約をかけることができる
・順番が重要になるときは、ORDER BY “CCC”,…を記述することで並び替えが可能
(今回のように、使用する列を明示的に分ける場合や、RANK()関数を用いる場合など)
・ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、集約に使用する行を明示している
(UNBOUNDED PRECEDINGは、先頭の行を意味している。CURRENT ROWは現在の行。他にも、[n] PRECEDINGで、”n行前”などを明示的に示すことができる。)

という感じです。
今回は累積和ということで、先頭の行から現在の行までの和を取りました。

(おまけ):フラグをつけたところまでの和を計算する


最後に、フラグをつけたところまでの和を計算する方法です。

以下のようなデータを考えてみましょう。
上記の例で使用したデータに、フラグが追加された場合です。

状況として、先頭からフラグがつけられた場所までの和が欲しいと仮定しましょう。
つまり、1行と2行の和、1,2,3,4,5,6行までの和、1,2,3,4,5,6,7,8,9行までの和…といった感じです。

このような複雑な集計についても、Window関数を用いれば、簡単に実行することができます。

使用するコードは以下の通りです。

SELECT
id,
ARRAY_AGG(month)[OFFSET(ARRAY_LENGTH(ARRAY_AGG(month)) - 1)],
SUM(SUM(money)) OVER(PARTITION BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM
(SELECT
id,
month,
money,
COUNTIF(flag = "1") OVER(PARTITION BY id ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS flag_count
FROM `YOUR_PROJECT_NAME.YOUR_DATASET_NAME.YOUR_TABLE_NAME`)
GROUP BY id,flag_count

この結果は、以下のようになります。

考え方としては、

・COUNTIF()で、フラグごとの塊を作る
(つまり、1,2行目を一つの塊、3,4,5,6行目を一つの塊…という風にみなす)
・flag_countでGROUP BYをして、フラグの塊ごとに和を取る
・その和を、Window関数を用いて、累積的に足し上げていく
・どこまでの和かが分かるように、ARRAY_AGG()を用いて、monthの最終行を取得する

という感じです。

まとめ


今回は、Big Queryで累積和を計算する方法を、JOINとWindow関数の2パターンで紹介しました。

GROUP BY句のほかに、Window関数を用いて集約を行うことで、より複雑な分析でできると思います。

この記事が、皆さんの手助けになれば幸いです。

弊社はGCPなどを使った分析基盤の構築から、可視化・分析まで行う企業です。
お問い合わせはこちらまで。

ブログへの記事リクエストはこちらまで。

関連記事

  1. Azure

    Google Cloud StorageとAzure Blob Storage間でファイルを並列コピ…

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回はGC…

  2. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューション分析

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回はAd…

  3. プログラミング

    Node.jsでCSVファイル内のダブルクオートで囲まれたカラム内のカンマを除去する

    こんにちは、エクスチュアCTOの権です。いろんなデータを扱う業…

  4. Google Cloud Platform

    Looker: LookerbotをGoogle Cloud Platformで動かす

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回は…

  5. Databricks

    Databricks: Spark DataFramesをJDBCから作成する

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回は…

  6. Adobe Analytics

    Adobe Analyticsに入り切らないデータをBigQueryに投入する

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。Ado…

最近の記事

  1. 【TC19ブログ】新しいデータモデリングを解説する
  2. 【TC19ブログ】セッション紹介:DataRobot×Tab…
  3. AdobeAnalytics Datafeed: BigQu…
  4. 【TC19ブログ】Tableau Conference 20…
  5. 【TC19ブログ】Tableau Conference 20…
  1. Adobe Analytics

    Adobe Analytics + Google BigQueryでよく使うSQ…
  2. Google Tag Manager

    Google Tag Manager: 離脱リンクのクリックをトリガーにする
  3. Adobe Analytics

    AA + GA : SafariのITP2.1に備えてphpでクッキーを永続化す…
  4. Adobe Analytics

    Adobe AnaltyicsとGoogle Analytics の「生Webビ…
  5. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:意図しないデータが混ざる①…
PAGE TOP