(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テーブルのレコード数を求めるだけなら簡単なのですが、日付が同じレコードは１件とみなすという要件があるのです。

つまり、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