engineers_blogみなさん、こんにちは!マネーフォワードでビジネス分析とかしてる人、酒井です。現在、私は定量分析を民主化すべく、BigQueryに各種集計に使うテーブルを集めたデータウェアハウス(以下、DWH)を構築しています。集めるだけでなく、テーブル間のリレーションやフィールドの意味など、専門的な知識を上手に隠蔽したサマリテーブルを作ることで、データ分析の裾野が広がることを期待しています。その中、特に経営分析や戦略策定において「売上」をいかに解像度高く確認できるかが重要です。今回のDWHも、事業部・プロダクト・チーム・Stock/Flowなど、あらゆる断面で切っても同じ定義の「売上」が出たサマリテーブルを作ることが求められています。(この事と現状のカオスさからこのプロジェクト名をフラクタルと命名しました)一方で、経理処理は財務諸表を作ることが目的なので高解像度で売上を集計しておらず、また処理自体もExcelによる手作業であるためリードタイムがあり速報性が落ちてしまうなど、現状の経理プロセスには経営分析上の問題がありました。結果的に、表題のとおり経理処理をBigQueryのSQLで実装する必要が出たわけです。ご提供している方法のひとつである、チケットの処理です。チケットには、下記のような特性があります。(平均法)基本的にはシンプルな処理ではありますが、特に4番が曲者なので、例を出してご説明します。月初に使われてないチケットを調べると、単価100円のチケットが10枚あった。(=前受収益が1,000円)月末までに、使われたチケットの枚数は5枚だった。100円×5枚=500円の売上。月初に使われてないチケットを調べると、単価100円のチケットが5枚あった。(=前受収益が500円)月末までに、単価200円のチケットを15枚売った。(=前受収益が3,500円)月末までに、使われたチケットの枚数は5枚だった。→(100円×5枚+200円×15枚)/(5枚+15枚)=175円が平均単価なので、売上は875円。例をみて、なんとなく何が変数になるかおわかりでしょうか?一つの答えは下記のとおりです。各月の消費枚数2については=枚数×単価なので単価といっても良いのですが、定式化の都合で前受収益とさせてください。(前受収益とは、お金はもらっているけどまだ売上と見做せないもの、という勘定科目です)定式化してみるどのように処理を書くものか悩みますが、ここまで算数的な問題であれば一度算数にしてみたいと思います。「今までの合計販売枚数」みたいな概念が出るということはΣみたいな、アレルギー性のある記号が発生しそうな予感がするので、簡単のために一度Excelで表現してみます。?少しは視覚的に理解できましたか?D14が今月の消費量[枚]で、それ以降が単価[円/枚]です。2行目の(SUM()-SUM())で残っている前受金を計算しています。3行目の(SUM()-SUM())は残っているチケットの枚数を計算しています。この理解があれば十分に実装できるのですが、あえて一度数式に落としましょう。アレルゲンな数式が出てきました。ここで気づけるのは以下の2つです。配列を渡す必要がありそうBigQueryで再帰を実装可能か確認する再帰なんて、それこそ大学の授業でフラクタル図形書いてみよう!という課題に取り組まされて以来です。そもそもSQLで再帰って実装できるのか?ということから確認しました。SQLで再帰を書く方法には、下記の2つがあります。再帰SQLを書くUDF(ユーザー定義関数を使う)現在BigQueryでは再帰SQLをサポートしていませんので、今回はUDFを書くことにしました。UDFは、SQL内で呼び出せる関数を独自に作成するという機能で、BigQueryの場合はJavascriptで定義することが可能です。いえ、本当にBigQueryのUDFで再帰を実装できるのか不安だったので、まずフィボナッチ数の吐き出しを実装できるか確認しました。下記が典型的なコードで、fib()内でfib()が呼ばれているのがわかります。55調べるとfibonacci(10)は確かに55なので問題なくBQで再帰を使えそうです。BigQueryで「配列を引数に配列を返すUDF」を実装できるか確認するこちらも結論としては可能なのですが、そもそもなぜこの確認が必要か考えます。下記再掲ですが、いたるところにΣがあり、t=1からt=直近月までの各変数の配列を引数で渡す必要があることに気づくことができます。ARRAY<INT64>)といった渡し方になるはずです。引数に知りたい月を与えることで、その月の売上高だけを返すこともできますが、少々計算量がかさみます。いうのも、N月の売上を計算する際にはN-1月以前の売上も全て計算する、すなわち最終月の計算には、それ以前の全ての売上計算がされるので、このUDFの呼び出しは1回にして全月の結果を配列で返してくれるのがエコになります。下記が「配列を引数に配列を返すUDF」の実装が可能だということを証明するクエリです。配列化とunnestを何度かやっているのでSQL部分が長いですが、ARRAYを引数にARRAY返すUDFがきちんと実装可能だというのが肝要です。連番が入ってるだけのテーブルを作る。offset;経理処理をUDFに落として、BigQueryで実装するここまでの定式化と検証を組み合わせると、下記の通り経理処理を実装できました。(注:僕には変数をvarで初期化する悪癖があります...。offset;さて、この実装は正しい計算をするのですが、大きな問題がありました。このスクリプトのオーダーを調べるとO(2^n)と完全にだめなアルゴリズムで、実行したとしても地球文明の崩壊のほうが早そうです。(チケット販売開始から50ヶ月程度立っているのですが、2^50≒1000兆です)メモ化を使ってオーダーを削減するこういうときはメモ化です。今回、あくまで売上の計算は高々50ヶ月分しかないのに、一回計算した月の売上を何億何兆回といままでは繰り返し計算していました。一度計算したものをどこかに記録しておけば、この無駄な計算を削減できます。(と、それっぽく語っていますが、新卒エンジニアに教えてもらいました。)どのスコープにメモの配列置くかちょっと混乱しながらも、下記の通り書くと期待通りの挙動をしました。O(2^n)がO(n)と線形になり、実行時間は2秒程度になりました。[0]は、突っ込むデータの列数が0の場合のresultです。[hoge]をreturnしています。まとめ再帰計算が必要なとき、多くの場合はスプレッドシートの世界に行くほうが簡単ではあります。一方で、処理をSQLで行えたほうがプロセスとしてエレガントで、前後の処理に対しボトルネック化を防ぐ事ができます。数式は多くの人にとってアレルゲンですが、頭を整理する一つのツール/言語として有用なので、ビジネスロジックの実装をする際には積極的に書いてみてはいかがでしょうか。しても苦手な方であれば、スプレッドシートで実装してみて、右脳的に解釈するのもおすすめです。今回はBigQueryを採用したため再帰計算にUDFを使っていますが、環境さえあれば標準的な機能(再帰SQL)で実装しきるのが良いのかもしれません。調べた限りだと、再帰SQLのメモ化実装例が出てこないので、オーダーがスケールしちゃいそうなときは注意してください。