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. Adobe Analytics

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

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

  2. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

    こんにちは、エクスチュアCTOの権です。Twitter:@ex…

  3. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む

    こんにちは、エクスチュアCTOの権です。Twitter:@ex…

  4. Google Cloud Platform

    Node.js+GAE: 日本語自然文を形態素解析してネガポジ判定をする

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

  5. Adobe Analytics

    Adobe Analytics: DatafeedのログからパスフローレポートをBigQueryで作…

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

  6. Adobe Analytics

    AdobeAnalytics: スマホのスワイプにカスタムリンクを実装する

    こんにちは、エクスチュアの権泳東/コン・ヨンドン(@exturekwo…

最近の記事

  1. GTMのdataLayerをAdobeAnalyticsの「…
  2. Metabase: カスタムマップで日本地図を追加する
  3. オープンソースBI「Metabase」の使い勝手が丁度良かっ…
  4. OWOX BI: GoogleSheetsアドオンでBigQ…
  5. Adobe AEP SDK: リバースプロキシを使ったアプリ…
  1. Adobe Target

    Adobe Target: スマホアプリ上でABテストをする
  2. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条…
  3. GA 360 Suite

    GoogleDataStudio:GoogleAnalyticsのカスタムチャネ…
  4. Linux

    Linux技術者資格のLPIC-3試験に合格しました
  5. Adobe Analytics

    Adobe Analytics:Report Builderの小技:リクエストの…
PAGE TOP