日別の集計データを取る SQL の作成を行うことになったのですが、データの種別毎に SQL を発行するとレスポンス的にもまずいかなと思い、一つのクエリで何とか取得出るようにしてみました。
たとえば下記のようなテーブルがあるとします。
Sales テーブル(主キー(CustomerID,LineNo)と集計に必要な列以外は省略)
┌────────────────────────┐
│CustomerID LineNo SalesDate Quantity Money │
│22 4 20090301 1 105 │
│22 1 20090302 2 2520 │
│21 1 20090302 5 10050 │
│22 2 20090310 1 1050 │
│22 3 20090310 1 1260 │
│23 1 20090310 1 14700 │
│16 1 20090310 1 10500 │
│9 1 20090310 1 105 │
│11 1 20090310 2 1050 │
│11 2 20090310 1 105 │
└────────────────────────┘
UseHistory テーブル(主キー(CustomerID,LineNo)と集計に必要な列以外は省略)
┌─────────────────┐
│CustomerID LineNo UseDate Sex │
│21 2 20090301 0 │
│10 2 20090310 1 │
│10 1 20090310 1 │
│22 1 20090310 2 │
│8 1 20090310 1 │
│16 1 20090310 2 │
│23 1 20090310 0 │
└─────────────────┘
※UseDate,SalesDateは文字列 男性:Sex=1 女性:Sex=2 その他:Sex=0,null
これで求めたいのは、日別の販売個数合計(Sales.Quantity)、日別販売額合計(Sales.Money)、日別利用男性合計、日別利用女性合計、日別利用その他合計(UseHistory.Sex)です。
上記のテーブルデータから言うと、出力イメージはちょうど下記のようになります。
┌───────────────────────────────────────┐
│日付 販売個数合計 販売額合計 男性利用合計 女性利用合計 その他利用合計│
│20090301 1 105 1 │
│20090302 7 12,570 │
│20090310 8 28,770 3 2 1 │
└───────────────────────────────────────┘
つまり、Sales,UseHistroy の両テーブルに存在するすべてのレコードを取得し、集計後(合計)に日付を条件として結合したいのです。
このような結合条件を指定し、両方のテーブルに存在するものを組み合わせるのは、たしか FULL OUTER JOIN (完全外部結合)とか使うんだったっけと思いながら調べると、JET では FULL OUTER JOIN サポートされてないんですね。。
でも、UNION と LEFT , RIGHT OUTER JOIN を組み合わせることで、完全外部結合できるようです。
参考にさせてもらったのは、Microsoft Accessで完全外部結合をするです。
2通りのやり方が、紹介されてますが、自分はその2の方が理解しやすかったのでそっちを使ってみました。(ちなみに、このやり方はかなりマイナーだとここの筆者が書いてました。)
要はまず、UNION を使って、結合条件となる値を重複なしで取得し、それに対して、各テーブルに左外部結合をかけていくというやり方みたいです。
UNION 句はあんまり使ったことがないのですが、こういうときに使えるんですね。
ということで、ほしいデータを出すクエリはこんな感じになりました。
SELECT DateAll.UseSalesDate AS 日付 ,
SalesData.SalesNum AS 販売個数合計 ,
SalesData.SalesMoney AS 販売額合計 ,
UseDataMale.UseMaleNum AS 男性利用合計 ,
UseDataWoman.UseWomanNum AS 女性利用合計 ,
UseDataOther.UseOtherNum AS その他利用合計
FROM ((((
/*この副問い合わせで、Sales,UseHistory両テーブルの重複しない日付を取得する(この値が結合条件となる)*/
( SELECT SalesDate AS UseSalesDate
FROM Sales
UNION
SELECT UseDate AS UseSalesDate
FROM UseHistory
) AS DateAll
LEFT OUTER JOIN
/*販売個数合計、販売金額合計を求める*/
( SELECT Sales.SalesDate ,
SUM(Sales.Quantity) AS SalesNum,
SUM(Sales.Money) AS SalesMoney
FROM Sales
GROUP BY SalesDate
) AS SalesData
ON DateAll.UseSalesDate = SalesData.SalesDate)
LEFT OUTER JOIN
/*男性利用合計を求める*/
( SELECT COUNT(*) AS UseMaleNum ,
UseDate
FROM UseHistory
WHERE Sex = 1
GROUP BY UseDate
) AS UseDataMale
ON DateAll.UseSalesDate = UseDataMale.UseDate)
LEFT OUTER JOIN
/*女性利用合計を求める*/
( SELECT COUNT(*) AS UseWomanNum ,
UseDate
FROM UseHistory
WHERE Sex = 2
GROUP BY UseDate
) AS UseDataWoman
ON DateAll.UseSalesDate = UseDataWoman.UseDate)
LEFT OUTER JOIN
/*その他利用合計を求める*/
( SELECT COUNT(*) AS UseOtherNum ,
UseDate
FROM UseHistory
WHERE Sex = 0
OR Sex IS NULL
GROUP BY UseDate
) AS UseDataOther
ON DateAll.UseSalesDate = UseDataOther.UseDate)
ORDER BY DateAll.UseSalesDate
いずれにせよ、日付は1つの列にまとめたかったので、副問い合わせ内で UNION することは必要だったようです。
なので、今回の要件を満たす SQL は SQL Server で FULL OUTER JOIN が使えたとしても、LEFT OUTER JOIN と書き方はたいして変わりそうにないようです。
これが、結合条件の列を別々の列として出すのなら、副問い合わせの UNION 使わず、ただ単に FULL OUTER JOIN すればいいだけなんですが。。
ちょうど下記の感じです。( SQL Server で FULL OUTER JOIN 使った場合。日付列はテーブル毎に出力されるが…)
SELECT SalesDate ,/*Salesテーブルの日付と,UseHistoryテーブルの日付は別々にでてしまう*/
UseDate ,
SalesNum ,
SalesMoney ,
UseMaleNum
FROM
( SELECT Sales.SalesDate ,
SUM(Sales.Quantity) AS SalesNum,
SUM(Sales.Money) AS SalesMoney
FROM Sales
GROUP BY SalesDate
) AS SalesData
FULL OUTER JOIN
( SELECT COUNT(*) AS UseMaleNum ,
UseDate
FROM UseHistory
WHERE Sex = 1
GROUP BY UseDate
) AS UseDataMale
ON UseDataMale.UseDate = SalesData.SalesDate
/*女性、その他はめんどくさいので省略…*/
