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

ホーム
Google BigQuery, Google Cloud Platform, プログラミング
Big QueryでWindow関数を用いて、累積和を計算する

2019.09.4

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

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

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

SQLではGROUP BY句のほかに、Window関数と呼ばれるものを使うことでも集計を行うことができます。
JOINとGROUP BYを併用する方法と、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の最終行を取得する

という感じです。