人気ブログランキング | 話題のタグを見る
(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は慣れんもんですわ。
参考:サブクエリ
by jehoshaphat | 2008-12-15 23:32 | SQL


<< (VB.Net)ADO.Net... (VB.Net)テキストボック... >>