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

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

ただいまの
回答率

89.20%

【SQL初心者】履歴状況のテーブルからフラグ作成し、主テーブルへ反映させたい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,683

iki

score 12

【やりたいこと】*修正しました
下記のような2つのテーブルから、
施策ごとに(施策に反応した)新規ユーザーと既存ユーザーの数を把握したいです。

新規、既存の判定は、
キャンペーンテーブルにあるuser_idの応募日を確認し、その応募日から3カ月以内に同一ユーザーの利用履歴があるかどうか
(=ポイント履歴のテーブルにおいて、応募日から3カ月以内のポイントA,Bがともに(全て)0またはNULL⇒新規、 ポイントが入っている⇒既存)
で区別したいと思っております。


《やりたいことの修正》
・キャンペーン利用者の新規/既存の数を把握したい
・新規/既存の判定については、
キャンペーン期間の3カ月前までの期間(2017/10/25~2018/1/25)において、ポイント利用履歴があるかないかです。
*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。

〈テーブル概要〉  
・キャンペーン テーブル(実施施策と施策に応募したユーザー、応募日時があるテーブル) 
⇒対象データは、1万行。100KB程度の大きさです。
・ポイント履歴 テーブル(ユーザーごとの利用履歴があるテーブル)
⇒対象データは、1050000行。150,000KB程度の大きさです。
〈テーブル例〉
http://sqlfiddle.com/#!17/ee6ad/2

〔point_rireki Table〕
CREATE TABLE  point_rireki
("date" timestamp, "user_id" int, "point_A" int, "point_B" int, "old" int, "area" varchar(3))
;  
INSERT INTO  point_rireki
("date", "user_id", "point_A", "point_B", "old", "area")
VALUES
('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
('2017-10-07 00:00:00', 1005, 100, 50, 22, '神奈川'),
('2017-10-10 00:00:00', 1010, 99, 10, 30, '東京'),
('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
('2017-10-22 00:00:00', 1015, 190, 20, 40, '千葉'),
('2017-10-11 00:00:00', 1010, 120, 90, 30, '東京'),
('2017-10-27 00:00:00', 1010, 170, 80, 30, '東京'),
('2017-10-28 00:00:00', 1015, 40, 280, 40, '千葉')

〔campaign Table〕
CREATE TABLE campaign
("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "user_id" int, "apply_date" timestamp)
;

INSERT INTO campaign
("apply_id", "shisaku_id", "shisaku_name", "user_id", "apply_date")
VALUES
(10001, 'AYC100', 'C施策', 1001, '2017-10-01 00:00:00'),
(10002, 'AYC100', 'C施策', 1005, '2017-10-07 00:00:00'),
(10003, 'AYC100', 'C施策', 1010, '2017-10-10 00:00:00'),
(10004, 'AYB100', 'B施策', 1015, '2017-10-22 00:00:00'),
(10005, 'AYB100', 'B施策', 1010, '2017-10-11 00:00:00'),
(10006, 'AYC100', 'C施策', 1001, '2017-10-25 00:00:00')
;

【実行環境】
Windows 
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています。

【困っている点】
テーブルを結合した際の条件の表現方法がわからず困っております。
・キャンペーンテーブルにあるuser_idの応募日を確認し、その応募日から3カ月以内に同一ユーザーのポイント履歴でフラグを作成
⇒この表現方法で悩んでいます。

【現状】
下記で記述しているのですが、
point_A,point_Bが、データとして0が入っている場合、"既存"としたいのですが"新規"となってしまい、
困っています。

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(point_A,0)+coalesce(point_B,0)) 
from point_rireki2 
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where     oubo_date < '2018-02-07'    
--oubo_date < '2018-02-07' が施策実行条件により絞り込み            
) step1
)step2    
group by shisaku_id, shisaku_name,new_or_existing

【やりたい内容の追加⓶と現状】3/15 18:15

・やりたい内容
・キャンペーン三ヵ月前までのポイント利用履歴から新規/既存のフラグを作り、その各々の数を抽出 →前回までの内容
・その新規/既存の数の中で、キャンペーン期間中にポイント利用があったかた(新規/既存のそれぞれにて)の数の抽出 →追記した内容

〈完成イメージ〉施策ID/施策名/新規,既存フラグ/新規,既存の数/(新規,既存の各々で)施策期間中のポイント消費者数
〈仕様〉
・キャンペーン期間:2018/1/26~2018/2/9
・ポイントの消費があった方:
fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている(0も含める)方
〈考えていること〉
・with句とcount文の間に更にSelect文を書き、サブクエリを増やす。
・with句の中に、キャンペーン開始日と終了日の期間を定義し、条件内で利用(前回、with句内で定義したキャンペーンスタート日は、キャンペーン前日を定義した方が都合がよかったため、1/25をStartPreとして修正致しました)
といった方向で記述を試みているのですが、
"前回頂いたフラグを活かしたまま、さらにその条件の中でキャンペーン期間にポイント消費があった"という表現がわからず、教えて頂けないでしょうか。

〈追記作成を行ったクエリ〉*現状はエラーになります
with param as (
select '2018-01-25':: date campaign_StartPre, '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)

select shisaku_id, shisaku_name, new_or_existing, new_or_existing_count , shohi_flg, count(*) as shohi_flg
from (
select *
,case when
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id 
and "date" between param.campaign_Start and param.campaign_End
and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)    
)    
then '消費' else '消費なし' end as shohi_flg
from campaign cmpgn cross join param 
where cmpgn.oubo_date < param.oubo_date_limit
) step1
from(
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
select *
, case when 
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id 
and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
and (fuyo_pt_tj is not null or kan_pt_tj is not null)
)
then '既存' else '新規' end as new_or_existing
from campaign cmpgn cross join param 
where cmpgn.oubo_date < param.oubo_date_limit
) step2
)step3
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 1

checkベストアンサー

+2

前回のサブクエリーの相関元が変わるだけです。

select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
from (
  select *
        ,(select sum(coalesce("point_A",0)+coalesce("point_B",0)) 
          from point_rireki 
          where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
         ) as exist_point
  from campaign cmpgn
) step1
order by user_id, apply_date


※尚、テーブル定義上"で囲った場合、大文字と小文字が識別されるので、SQLの記述上面倒ですよ。
dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
(そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど)
付録C SQLキーワード

追記(施策別集計)

select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
    select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
    from (
      select *
        ,(select sum(coalesce("point_A",0)+coalesce("point_B",0)) 
          from point_rireki 
          where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
         ) as exist_point
      from campaign cmpgn
    ) step1
) step2
group by shisaku_id, shisaku_name, new_or_existing

追記(条件変更)

応募日からみて過去3カ月以内のポイント履歴があるものを識別するように変更。
※項目名が変わっているので、そちらに合わせました。

select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
    select *
         , case when 
            exists(
                select 1 from point_rireki 
                where kihon_id=cmpgn.kihon_id and "date" between cmpgn.oubo_date - '3 months'::interval and cmpgn.oubo_date
            )
           then '既存' else '新規' end as new_or_existing
      from campaign cmpgn
      where oubo_date < '2018-02-07'    
    ) step1
group by shisaku_id, shisaku_name, new_or_existing

追記(条件変更2)

・キャンペーン日から3カ月前までを対象とした。
・ポイント内容も判定条件に加える
※キャンペーン日、応募日をCTEにして、変更箇所を局所化

with param as (
    select '2018-01-25':: date campaign_Start,'2018-02-07':: date oubo_date_limit 
)
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
    select *
         , case when 
            exists(
                select 1 from point_rireki
                where kihon_id=cmpgn.kihon_id 
                 and "date" between param.campaign_Start - '3 months'::interval and param.campaign_Start
                 and (fuyo_pt_tj is not null or kan_pt_tj is not null)
            )
           then '既存' else '新規' end as new_or_existing
      from campaign cmpgn cross join param 
      where cmpgn.oubo_date < param.oubo_date_limit
    ) step1
group by shisaku_id, shisaku_name, new_or_existing

追記(集計パターンの追加)

with param as (
select '2018-01-25':: date campaign_StartPre, '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)
select shisaku_id, shisaku_name, new_or_existing, shohi_flg, count(*) as patarn_count
from (
    select  *
          , case when exists(
                select 1 from point_rireki2
                where kihon_id = cmpgn.kihon_id 
                  and "date" between param.campaign_Start and param.campaign_End
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
              ) then '消費' else '消費なし' 
            end as shohi_flg
          , case when exists(
                select 1 from point_rireki2
                where kihon_id = cmpgn.kihon_id 
                  and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null)
              ) then '既存' else '新規' 
            end as new_or_existing
    from  campaign cmpgn cross join param 
    where cmpgn.oubo_date < param.oubo_date_limit
) step1
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg
order by shisaku_id, new_or_existing,shohi_flg

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/03/15 19:44

    すいません。ありがとうございます!!

    サブクエリ内の実行順番でお伺いしたいのですが、
    step1内の2つあるcase when文で処理の順番は、
     shohi_flg と new_or_existing では、どちらが先行して処理されるのでしょうか。

    patarn_countの数で(例えば、既存 消費となっている行において)、
    キャンペーン期間の三ヵ月前の利用有無で利用有の方の内、キャンペーン期間に利用している方、といった前後の文脈を考慮する必要があるのではと気になってしまいました。(2つあるcase whenの順番を変えても出力される数は同じなため、 定義の文脈上の前後関係は関係ないのかなとも、思っているのですが、念のため、教えていただきたいです。。(初歩的で恐縮です。。。))

    キャンセル

  • 2018/03/16 00:33

    同一のレベルでは前後はありません。
    結果を利用するような場合には、処理をネストさせます。

    それから、長いスレッドは情報量がありすぎて利用者にとってはあまり有用ではありません。
    質問のポイントを絞って別質問にして下さい。

    キャンセル

  • 2018/03/16 09:21

    ありがとうございます!

    >それから、長いスレッドは情報量がありすぎて利用者にとってはあまり有用ではありません。
    >質問のポイントを絞って別質問にして下さい。
    すいません。了解致しました!観点を絞って、立てさせて頂きます!ありがとうございます。

    キャンセル

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

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