質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.34%

  • Amazon Redshift

    5questions

    Amazon Redshiftは、Amazon社が提供する 高速かつ完全マネージド型でペタバイト規模の クラウドデータウェアハウスサービスです。

新規、復帰ユーザー抽出

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 294

sato_day

score 14

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

以下のようなテーブルがあり、以下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                                     

恐れいりますが、ご教示

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sato_day

    2017/11/07 15:15

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

    キャンセル

  • masaya_ohashi

    2017/11/07 15:21

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

    キャンセル

  • sato_day

    2017/11/07 15:34

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

    キャンセル

回答 1

checkベストアンサー

+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;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/08 17:02 編集

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

    キャンセル

  • 2017/11/08 17:17

    ただ、以下の数字が同じで抽出されてしまったのでこちらはもう少し考えたいとおもいます!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

    キャンセル

  • 2017/11/08 17:28

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

    キャンセル

  • 2017/11/08 17:30

    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

    キャンセル

  • 2017/11/08 17:52

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

    キャンセル

  • 2017/11/08 18:02

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

    キャンセル

  • 2017/11/08 18:51

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • Amazon Redshift

    5questions

    Amazon Redshiftは、Amazon社が提供する 高速かつ完全マネージド型でペタバイト規模の クラウドデータウェアハウスサービスです。