新規、復帰ユーザー抽出

Question

またも質問させていただきます！

以下のようなテーブルがあり、以下3条件で抽出したいのですがうまくいきません。

①2017/5/1以降に購入したユーザー数
②2017/5/1以降初めて購入ユーザー数
③2017/5/1以降に、1年&6ヶ月以上購入がなかった復帰ユーザー数

■テーブル名:purchase

date uid
2017/4/30 1111
2017/5/1 1111
2017/5/2 1111
2016/4/1 2222
2017/5/1 2222
2016/11/1 3333
2017/5/1 3333
2017/5/1 4444
2017/5/1 5555
2017/5/2 5555
2017/5/3 5555

①と②は以下で出るかと思ったのですが、ttlとfstの値が同じになってしまいます、、

select	
   count(distinct case when date >= '2017-05-01' then uid  else null end) as ttl
  ,count(distinct case when first_order >= '2017-05-01' then uid  else null end) as fst 
  ,count(distinct case when first_order < '2017-05-01' then uid  else null end) as rep					
FROM(							
select							
      uid
　　 ,date									
     ,MIN(date) as first_order				
  from purchase					
   GROUP BY 1,2						
) z							
						

また、③は以下にすると5/1以降に初めて購入したユーザーが含まれてしまうので
うまくいきません。。

select					
  count(distinct case when  last_odr - first_odr > 365 then a.uid  else null end) as r_y
 ,count(distinct case when  last_odr - first_odr > 180 and last_odr - first_odr <= 365 then a.uid  else null end) as r_m				
FROM(					
select					
        uid								
	 ,MIN(date) as last_odr				
  from purchase			
   where date >= '2017-05-01'					
   GROUP BY 1					
) as a 					
left join(select					
         uid									
	 ,MIN(date) as first_odr				
  from purchase				
   where date < '2017-05-01'					
   GROUP BY 1	) as b on a.uid = b.uid 									

恐れいりますが、ご教示

Comments

[masaya_ohashi]

③2017/5/1以降に、1年&6ヶ月以上購入がなかった復帰ユーザー数の定義がいまいちわかりません。1年6ヶ月という期間はどこを起点にどこまでですか？2017年5月1日より前の最後の買い物から、5月1日以降最初の買い物の期間という認識で合っていますか？

[sato_day]

大変ん失礼しました、、、2017/5/1以降に注文があったユーザーの中で1年たっての復帰した人と、6ヶ月たっての復帰をそれぞれ出したく、

[masaya_ohashi]

つまり、5月1日以降の最初の買い物から遡って、前回の買い物から1年以上の人、6ヶ月以上の人の数、ということですね？これは6ヶ月以上には1年以上の人が含まれますか？

[masaya_ohashi]

あ、よく見たら下のSQLでは含めないようにしていますね

[masaya_ohashi]

これはワンクエリでttlとfstとrepが取れないとだめな要件ですか？

[sato_day]

■1年復帰　・・・　2017/5/1以降に購入した人のうち、1年以上買い物履歴がなかった方
■6ヶ月復帰・・・　2017/5/1以降に購入した人のうち、6ヶ月以上買い物履歴がなかった方
という意味でした。。失礼しました。

[sato_day]

いえ、ぜんぜんワンクエリではなくても大丈夫です、数字さえ出れば！

[masaya_ohashi]

確認ですが、1年は365日、6ヶ月は180日の固定値でよいのでしょうか？例えばうるう年や大の月は計算に入れないでよいのでしょうか？

[sato_day]

あ、そこはもうだいたいでいいので１年365日、6ヶ月は180日固定で大丈夫です！

Answer 1

まず集計に必要なデータが何なのかを考えましょう。
①の条件を検索するには「全てのユーザの、全ての購入についての日付」が必要です。
②の条件を検索するには「全てのユーザの、2017−05-01以降の最初の購入日付」が必要です。
③の条件で検索するには「全てのユーザの、2017-05-01より前の最後の購入、2017-05-01以降の最初の購入の空白期間の日数」が必要です。

それらを得られるクエリがまずこちらになります。
※SQLの予約語は全て大文字にして見分けやすくしています。

SELECT
	purchase.uid
    ,date
    ,first_order
    ,DATEDIFF(first_order_after_may_first, last_order_before_may_first) AS blank_days
FROM purchase
    # 全期間を通して最初の購入をfirst_orderとする
	LEFT JOIN (
		SELECT
			uid
			,MIN(date) AS first_order
        FROM purchase
        GROUP BY uid
		) AS first_orders ON purchase.uid = first_orders.uid
    # 2017-05-01以降の最初の購入をfirst_order_after_may_firstとする
	LEFT JOIN (
		SELECT
			uid
			,MIN(date) AS first_order_after_may_first
        FROM purchase
        WHERE date >= '2017-05-01'
        GROUP BY uid
		) AS first_order_after_may_firsts ON purchase.uid = first_order_after_may_firsts.uid
    # 2017-05-01より前の最後の購入をlast_order_before_may_firstとする
	LEFT JOIN (
		SELECT
			uid
			,MAX(date) AS last_order_before_may_first
        FROM purchase
        WHERE date < '2017-05-01'
        GROUP BY uid
		) AS last_order_before_may_firsts ON purchase.uid = last_order_before_may_firsts.uid;

そして、こちらのテーブルを元に、①②③(③は半年、1年それぞれ)を得たものがこちらになります。

SELECT
	COUNT(DISTINCT CASE WHEN date >= '2017-05-01' THEN uid ELSE NULL END) as ttl
    ,COUNT(DISTINCT CASE WHEN first_order >= '2017-05-01' THEN uid ELSE NULL END) as fst
    ,COUNT(DISTINCT CASE WHEN blank_days > 180 AND blank_days < 365 THEN uid ELSE NULL END) AS rep180
    ,COUNT(DISTINCT CASE WHEN blank_days >= 365 THEN uid ELSE NULL END) AS rep365
FROM (
	SELECT
		purchase.uid
        ,date
		,first_order
		,DATEDIFF(first_order_after_may_first, last_order_before_may_first) AS blank_days
	FROM purchase
		# 全期間を通して最初の購入をfirst_orderとする
		LEFT JOIN (
			SELECT
				uid
				,MIN(date) AS first_order
			FROM purchase
			GROUP BY uid
			) AS first_orders ON purchase.uid = first_orders.uid
		# 2017-05-01以降の最初の購入をfirst_order_after_may_firstとする
		LEFT JOIN (
			SELECT
				uid
				,MIN(date) AS first_order_after_may_first
			FROM purchase
			WHERE date >= '2017-05-01'
			GROUP BY uid
			) AS first_order_after_may_firsts ON purchase.uid = first_order_after_may_firsts.uid
		# 2017-05-01より前の最後の購入をlast_order_before_may_firstとする
		LEFT JOIN (
			SELECT
				uid
				,MAX(date) AS last_order_before_may_first
			FROM purchase
			WHERE date < '2017-05-01'
			GROUP BY uid
			) AS last_order_before_may_firsts ON purchase.uid = last_order_before_may_firsts.uid
	) AS purchase;

Comments

[sato_day]

すっごく理解しました！以下エラーとなったのですが、以下にしたら出ました！！DATEDIFF(day,first_order_after_may_first, last_order_before_may_first) AS blank_days

[sato_day]

ただ、以下の数字が同じで抽出されてしまったのでこちらはもう少し考えたいとおもいます！COUNT(DISTINCT CASE WHEN date >= '2017-05-01' THEN uid ELSE NULL END) as ttl
,COUNT(DISTINCT CASE WHEN first_order >= '2017-05-01' THEN uid ELSE NULL END) as fst

[masaya_ohashi]

それはですね、そちらの書かれたSQLだとuidとdateでグルーピングしたものの中からMINで最小の日付を探しているので、意図した動作になっていないからです。試しにサブクエリの部分だけ抜き出して実行してみてください。first_orderがおかしなことになるはずです。
select
uid
　　 ,date
,MIN(date) as first_order
from purchase
GROUP BY 1,2 ;

[masaya_ohashi]

first_orderはユーザでのグルーピング内でMINを求めなければならないのに、ユーザと日付2つのカラムでグルーピングしたものからMINを求めているのでdateとfirst_orderが必ずイコールになります。なので、以下のクエリはまったく同じ結果になるのです。
COUNT(DISTINCT CASE WHEN date >= '2017-05-01' THEN uid ELSE NULL END) as ttl
,COUNT(DISTINCT CASE WHEN first_order >= '2017-05-01' THEN uid ELSE NULL END) as fst

[sato_day]

あーーーーーーーーーーーーーーーーーなるほどなるほどそうですよね、、失礼しました。一番出したかったのは③で、それがばっちりでたのでとっても助かりました、勉強になりました！頭が固いのか、いろいろ考えが及ばないのですが精進いたします！！

[masaya_ohashi]

まあこういう発想は経験の数がものをいうので、いろいろなことにぶつかっていってください。そのうち「あ、ここ進研ゼミでやったとこだ！」のような感覚で問題が解けるようになります。

[sato_day]

進研ゼミｗｗｗｗ　まず思ったことを形にする想像力が足りない気がするので、映画とかみてイマジネーション力を身につけたいと思います、、（違