バスケット分析（併売分析）のためのSQL
いわゆる売上トランザクションテーブル、こんなデータから
| OrderID | Date | ProductID | ・・・ |
|---|---|---|---|
| 0000001 | 2017-07-01 | A001 | ・・・ |
| 0000001 | 2017-07-01 | A002 | ・・・ |
| 0000002 | 2017-07-02 | A003 | ・・・ |
| 0000003 | 2017-07-02 | A001 | ・・・ |
| 0000003 | 2017-07-02 | A002 | ・・・ |
| 0000003 | 2017-07-02 | A004 | ・・・ |
ある商品を買った人がほかの商品をどれだけ併売しているかを調べるためのこんなテーブルを作る。
| YearMonth | MainProduct | SubProduct | # of Purchase |
|---|---|---|---|
| 2017-07 | A001 | A002 | 2 |
| 2017-07 | A001 | A004 | 1 |
| 2017-07 | A002 | A001 | 2 |
| 2017-07 | A004 | A001 | 1 |
こんな感じでSQLを書く。書いてしまうと普通のクエリなんだけど、これがひらめいたときは結構感動した。
SELECT date_format(a.Date, '%Y-%m') YearMonth ,a.ProductID MainProduct ,b.ProductID SubProduct ,count(*) `# of Purchase` FROM Sales_T a ,Sales_T b WHERE a.OrderID = b.OrderID and a.ProductID <> b.ProductID GROUP BY date_format(a.Date, '%Y-%m') ,a.ProductID ,b.ProductID
