3流プログラマのメモ書き

元開発職→社内SE→派遣で営業支援→開発戻り浦島太郎状態の三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。

(SQL)Access(JET)で FULL OUTER JOIN を行う

日別の集計データを取る 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 することは必要だったようです。

なので、今回の要件を満たす SQLSQL 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

/*女性、その他はめんどくさいので省略…*/