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

元開発職→現社内SEの三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。(jehupc.exblog.jpから移転中)

(SQL)顧客毎に日別で売上回数をカウント、売上金額合計を抽出するSQL

いつものごとく苦手SQLのためわかりにくい件名ですが、したいことは下記の通りです。

まず、下記のようなテーブルがあるとします。

Customerテーブル

┌───────────────────┐

│CustomerID CustomerName CustomerKana│

│3 新垣 アラガキ │

│4 戸田 トダ │

│21 蛯原 エビハラ │

└───────────────────┘

Salesテーブル

┌────────────────────┐

│CustomerID LineNo SalesDate Money │

│3 1 20081001 \100 │

│3 2 20081004 \200 │

│3 3 20081010 \300 │

│3 4 20081015 \100 │

│4 1 20081011 \200 │

│4 2 20081014 \100 │

│4 3 20081018 \350 │

│21 2 20081215 \105 │

│21 3 20081215 \1,050 │

│21 4 20081215 \1,120 │

│21 5 20081205 \525 │

│21 6 20081203 \10,500 │

│21 7 20081208 \525 │

└────────────────────┘

で、CustomerテーブルとSalesテーブルを結合し、CustomerID毎の売上(Sales.Money)の合計金額と売上回数を求めたいわけです。

今回厄介なのは売上回数で、これが普通にSalesテーブルのレコード数を求めるだけなら簡単なのですが、日付が同じレコードは1件とみなすという要件があるのです。

つまり、Sales.CustomerID=21の場合、20081215は3件ありますがこれらは1件とみなすということです。

下記が求めたいSQLの結果です。

┌─────────────────────────────┐

│CustomerID CustomerName CustomerKana 売上金額 売上回数│

│3 新垣 アラガキ \700 4 │

│4 戸田 トダ \650 2 │

│21 蛯原 エビハラ \13,825 4 │

└─────────────────────────────┘

後、Customerテーブルにあって、Salesテーブルにないものも抽出対象とします。

(今回のテストデータではありませんが、要は外部結合するということです。)

これらの要件を満たしたSQLは下記のようになります。

SELECT Customer.CustomerID ,

Customer.CustomerName,

Customer.CustomerKana,

売上金額 ,

売上回数

FROM Customer

LEFT OUTER JOIN

(SELECT SUM(tmpSales.Money) AS 売上金額 ,

tmpSales.CustomerID

FROM Sales AS tmpSales

GROUP BY tmpSales.CustomerID

) AS saleSumPrice

ON Customer.CustomerID=saleSumPrice.CustomerID

LEFT OUTER JOIN

(SELECT COUNT(tmpSales3.SalesDate) AS 売上回数 ,

tmpSales3.CustomerID

FROM

(SELECT tmpSales2.SalesDate ,

tmpSales2.CustomerID

FROM Sales AS tmpSales2

GROUP BY SalesDate ,

tmpSales2.CustomerID

) AS tmpSales3

GROUP BY tmpSales3.CustomerID

) AS saleSumNum

ON Customer.CustomerID=saleSumNum.CustomerID

売上金額については簡単ですね。副問い合わせにおいてCustomerIDでグループ化し、CustomerID毎の売上金額とCustomerIDを結合条件とすることで求められます。

売上回数のほうは2重副問合せを使いました。まず、SalesDateとCustomerID毎にグループ化し、SalesDate,CustomerIDの組み合わせが一意になります。

そして、その外側の副問い合わせで、CustomerIDの含む列を求めると、これが売上回数になります。

ちなみに、売上対象期間を設定する場合は下記のようにします。

SalesDateが20081201以降のデータを抽出します。(言い忘れてましたが、SalesDateは文字列で日付を保存しています。)

SELECT Customer.CustomerID ,

Customer.CustomerName,

Customer.CustomerKana,

売上金額 ,

売上回数

FROM Customer

LEFT OUTER JOIN

(SELECT SUM(tmpSales.Money) AS 売上金額 ,

tmpSales.CustomerID

FROM Sales AS tmpSales

WHERE tmpSales.SalesDate>='20081205'

GROUP BY tmpSales.CustomerID

) AS saleSumPrice

ON Customer.CustomerID=saleSumPrice.CustomerID

LEFT OUTER JOIN

(SELECT COUNT(tmpSales3.SalesDate) AS 売上回数 ,

tmpSales3.CustomerID

FROM

(SELECT tmpSales2.SalesDate ,

tmpSales2.CustomerID

FROM Sales AS tmpSales2

WHERE tmpSales2.SalesDate>='20081205'

GROUP BY SalesDate ,

tmpSales2.CustomerID

) AS tmpSales3

GROUP BY tmpSales3.CustomerID

) AS saleSumNum

ON Customer.CustomerID=saleSumNum.CustomerID

これで結果は下記のようになります。

┌─────────────────────────────┐

│CustomerID CustomerName CustomerKana 売上金額 売上回数│

│3 新垣 アラガキ │

│4 戸田 トダ │

│21 蛯原 エビハラ \3,325 3 │

└─────────────────────────────┘

期間以外の条件(たとえば、CustomerNameとか、CustomerIDとか)は最下行の WHERE で結合後の表からフィルタをかけることでできます。

なかなかSQLは慣れんもんですわ。

参考:サブクエリ