横がダメなら縦でやってみる

こんにちは。イメージマジック三浦です。うだるような暑さの毎日から一変し、過ごしやすい気候に変わってきました。寒暖の差が大きくなってきましたので、体調を崩さないように気を付けていきたいところです。 今回はSQLの集計クエリを書いていた時の話です。

やりたいこと

・同じ期間内でテーブルA,Bをそれぞれ日付と区分（区分はAとBの両方にある）で集計し、その結果セットを以下イメージの形式で取りたい。 
　yyyy-mm-dd | 区分1の集計値 | 区分2の集計値 | 区分3の集計値 | …
ただし、テーブルAとBには以下の状態が想定されます。
  1. AとBの両方に含まれる日付がある
  2. Aだけに含まれる日付がある
  3. Bだけに含まれる日付がある

横ではできなかった理由

2の条件と3の条件が両立する可能性があったため、以下のように横方向にデータを結合していく方式では、どうしても集計漏れが出ます。 
SELECT * FROM A INNER JOIN B ON A.日付=B.日付 WHERE …
SELECT * FROM A LEFT  JOIN B ON A.日付=B.日付 WHERE …
SELECT * FROM A RIGHT JOIN B ON A.日付=B.日付 WHERE …
完全外部結合で対応しようとおもいきや、MySQLやmariaDBは完全外部結合が使えませんので、横方向へのデータ結合では要件を満たすことができません。 
※これは使えない
SELECT * FROM A FULL OUTER JOIN B ON A.日付=B.日付 WHERE …

完全外部結合ができないわけではない

このようなクエリにより、完全外部結合を再現することはできます。 
SELECT * FROM A LEFT  JOIN B ON A.日付=B.日付 WHERE …
UNION
SELECT * FROM A RIGHT JOIN B ON A.日付=B.日付 WHERE …
または 
SELECT * FROM A LEFT JOIN B ON A.日付=B.日付 WHERE …
UNION
SELECT * FROM B LEFT JOIN A ON B.日付=A.日付 WHERE …
しかし、今回はAとBそれぞれに集計対象の条件が異なり、同じ集計条件を1クエリ内で2度書く必要がある分、クエリが複雑になります。スポット集計用ならまだしも、これからもずっと運用していく予定のクエリだったので、複雑になることを避けるために導入を見送りました。 しかし、UNIONを使う方針は有力でした。

UNIONを使って事前集計を行う

UNIONを使って縦方向の結合により集計値を取得します。 UNIONを使うためには、SELECT文のカラム数を同じにする必要があるので、カラム数合わせの0をセットします。後の集計のため、エイリアスもつけています。UNIONにより重複が排除される効果もあります。 
SELECT 日付, 区分
, Aの集計値1, Aの集計値2, …
, 0 AS Bの集計値1, 0 AS Bの集計値2, …
FROM A
WHERE …
GROUP BY 日付, 区分
UNION
SELECT 日付, 区分
, 0 AS Aの集計値1, 0 AS Aの集計値2, …
, Bの集計値1, Bの集計値2, …
FROM B
WHERE …
GROUP BY 日付, 区分

本集計

予備集計のクエリをインラインビューとし、インラインビュー内の集計値をさらに集計します。ダミーのカラム値を0としたことで、SUMを実行した時に影響しないようになっています。 
SELECT
  日付
, SUM(CASE WHEN X.区分 = 1 THEN Xの集計値 ELSE 0 END) AS summary1
, SUM(CASE WHEN X.区分 = 2 THEN Xの集計値 ELSE 0 END) AS summary2
, SUM(CASE WHEN X.区分 = 3 THEN Xの集計値 ELSE 0 END) AS summary3
, SUM(CASE WHEN X.区分 = 4 THEN Xの集計値 ELSE 0 END) AS summary4
, …
FROM (
  SELECT 日付, 区分
  , Aの集計値1, Aの集計値2, …
  , 0 AS Bの集計値1, 0 AS Bの集計値2, …
  FROM A
  WHERE …
  GROUP BY 日付, 区分
  UNION
  SELECT 日付, 区分
  , 0 AS Aの集計値1, 0 AS Aの集計値2, …
  , Bの集計値1, Bの集計値2, …
  FROM B
  WHERE …
  GROUP BY 日付, 区分
) X
GROUP BY X.日付
…
SUMの結果について、もれなくエイリアスを定義しておくことが最後のポイントです。これにより、プログラムロジック内で集計値を扱いやすくなります。

小話

「インラインビュー」という言葉を初めて聞いたのは、新卒2年目の時に入った案件で見た設計ドキュメントでした。当時は何も分からないながら、何とかしてクエリを書きましたが、そのクエリは廃棄されてしまったそうです。 おまけに、そのことを聞いたのは案件を外れて半年後でした。 派遣エンジニアだった頃の、1つの思い出です。