注文のリピート数をカウントしたい

質問

受注テーブルがあって、注文日付があるテーブルがあるのですが、例えば以下のような簡易なテーブルがあったとします。

id unique,
kokyaku_id bigint,
order_date datetime

2015年4月～翌年3月までの月毎のリピーターの数を知りたいとき、どのように抽出すればいいでしょうか。

データは2015年以前から入っているとして、2015年4月には、過去すべての注文テーブル上から検索して初めて購入した人は○人、複数回購入したことがある人は○人というのを知りたいです。

ちょっと複雑な気がするのですが、何かおわかりの方はよろしくお願いします。

回答No.6

maiko333

2010年1月1日～2016年3月31日のデータで、2015年4月から2016年3月に初めて購入された方は○人、
2010年1月1日～2016年3月31日のデータで、2015年4月から2016年3月に2回以上購入された方は○人。

で良ければ
select 年月, 回数, count(*) from ( select substr(order_date,1,6) as 年月, '1' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20160331' and kokyaku_id not in ( select kokyaku_id from 受注テーブル where order_date between '20100101' and '20150331') group by substr(order_date,1,6), kokyaku_id having count(*) = 1 union select substr(order_date,1,6) as 年月, '2' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20160331' and kokyaku_id not in ( select kokyaku_id from 受注テーブル where order_date between '20100101' and '20150331') group by substr(order_date,1,6), kokyaku_id having count(*) >= 2) group by 年月, 回数"

となります。

補足

それを、4月に初めて購入された方は○人、2回以上購入された方は○人、5月に初めて購入された方は○人、2回以上購入された方は○人、6月・・・、7月・・・、翌3月というのは無理でしょうか？

回答No.5

maiko333

select 年月, 回数, count(*) from ( select substr(order_date,1,6) as 年月, '1' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20150430' and kokyaku_id not in ( select kokyaku_id from 受注テーブル where order_date between '20100101' and '20150331') group by substr(order_date,1,6), kokyaku_id having count(*) = 1 union select substr(order_date,1,6) as 年月, '2' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20150430' and kokyaku_id not in ( select kokyaku_id from 受注テーブル where order_date between '20100101' and '20150331') group by substr(order_date,1,6), kokyaku_id having count(*) >= 2) group by 年月, 回数

いかがでしょうか？
条件について不明瞭だったのでこちらで判断しています。
2010年1月1日～2016年3月31日のデータで、2015年4月に初めて購入された方は○人、
2010年1月1日～2016年3月31日のデータで、2015年4月に2回以上購入された方は○人。
と出てきます。

補足

ありがとうございます！
条件についてはその通りです。こちらのデータで件数の正否は不明ですが、カウントされているのが確認できました。

あと、これは追加？になるのですが、4月～翌3月まで1回のSQL文で出すことは可能ですか？

回答No.4

maiko333

「複数回購入したことがある人」を
１回○人２回以上○人としたいならNO1を、
１回○人２回○人３回○人４回○人・・・としたければNo3を選択してください。

回答No.3

山田 太郎（@f_a_007）

【補足】最終的に[Orders クエリ]をサブクエリにすればOKです。

SELECT X.OrederCounter, COUNT(*) AS 注文者数
FROM [SELECT Customer_ID, Count(*) AS OrederCounter
FROM Orders
GROUP BY Customer_Id]. AS X
GROUP BY X.OrederCounter;

これが、目的のSQL文かと思います。

補足

下にも書きましたが、利用したいデータの期間と集計したい期間が異なるのですが、それでも可能は方法はご存じでしょうか。

示していただいた方法も、今後使えると思いますので、参考にさせていただきます。

回答No.2

山田 太郎（@f_a_007）

[Orders クエリ]

SELECT Customer_ID, Count(*) AS OrederCounter
FROM Orders
GROUP BY Customer_Id;

次に

SELECT [Orders クエリ].OrederCounter, COUNT(*) AS 注文者数
FROM [Orders クエリ]
GROUP BY [Orders クエリ].OrederCounter;

で、最終的に[Orders クエリ]をサブクエリにすればOKです。

補足

細かく回数ごとに人数が出るのはありがたいですが、その期間で、月ごとに新規とリピーターの人数を知りたいです。

回答No.1

maiko333

select 回数, count(*) from ( select '1' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20160331' group by kokyaku_id having count(*) = 1 union select '2' as 回数, kokyaku_id from 受注テーブル where order_date between '20150401' and '20160331' group by kokyaku_id having count(*) >= 2) group by 回数

ですね。

補足

こちらの意図通り、１回のみ購入されている方と、２回以降購入されている方の人数がわかっていいのですが、月ごとのそれぞれの人数が知りたいです。

あと、集計したい期間と、データ期間が違うのですがわかりますか。
2010年1月1日～2016年3月31日のデータで、2015年4月に初めて購入された方は○人、2回以上購入された方は○人とは可能でしょうか。