集約関数を使いこなす

集約関数を行う際に知っておくと良いこと

　実際に、集約関数を用いる際には、次のようなことに気をつけると良いでしょう。

　1つ目がオーバーフロー対策です。特に平均を求めるAVG関数を実行する際、第1段階で値の合計値が大きくなりすぎて、親サーバーにてマージする一時テーブルの型をはみ出してしまうことがあります。

　AVG関数は、結果が常識的な数値になるため気づきにくいのですが、計算途中で全体の合計値を用いるため、非常に大きな数になることがあるのです。集計する前の値が、INTであったとしても、合計していくと、INT型の制限をオーバーフローするということをイメージしておくべきです。具体的には、一時テーブルではBIGINTやFLOATなど、大きめの型で定義しておいたほうが安心です。

　並列クエリで得られた平均値が直感に比べてやたら小さいなぁと思った場合には、オーバーフローを疑ってみるべきです。

　2つ目が一時テーブルへのインデックスづけです。1段階目の結果が、相当なレコード数になる場合、2段階目のクエリが予想外に時間がかかる場合があります。GROUP BY 句で集計する集計キーの種類が多いと、2段階目のクエリもそれなりに大きなテーブルに対して行うことになるからです。

　このような場合には、第2段階に用いる一時テーブル（マージテーブル）のテーブル定義において、GROUP BY 句で指定されている集計キーのフィールドにインデックスをつけます。これにより集計の速度が改善されます。

サンプルコードを書き換えてみよう

　今回はサンプルコードはありませんでしたが、前回のサンプルコードにちょっと手を加えれば、今回ご紹介した集計クエリが実現できます。2段階で集計するということ、および、それぞれの集約関数にて、1段階目および2段階目で何を用いるかということが分かれば、比較的簡単です。

　ただし、クエリが複雑になると「クエリを正しく2段階に分割できていなかった」というバグが出る場合も多くあります。並列クエリを求めるような場合は、テストも大変なことが多く、バグ取りに苦労することもあります。基本は簡単ですが、実際に運用する時には、極めて慎重であるべきです。

　理想的には、クエリを解釈するエンジンを書き、そこで全自動でクエリを2段階に分割すると、これらのトラブルから解放されます。テキスト処理が苦手でなければ、そこまで難しいロジックではありません。ただし、ひたすらに面倒です。内容が地味なので、こうした派手な連載でご紹介するようなものではありませんが、実はこうした地味なコードが大切です。腕に自信のある方はチャレンジしてみてください。