【実現したいこと】
既にある年齢&性別のフラグに、下記のフラグを加えて
特定応募日(2/6)以前までの期間における 性別年代別のキャンペーン利用の有無に関して対象者数を抽出したい
〈付け加えたい対象のフラグ〉
・ポイント履歴のテーブルとキャンペーンのテーブルを突き合わせて、下記を表すフラグを追記したい
・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴と一致する場合 → "キャンペーン利用有り"
・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴にはデータがない場合 → "キャンペーン利用無し"
(ポイント履歴内には)同一ユーザーでキャンペーン利用をしているケースもあれば、していないケースもあると思うので、
その場合はそれぞれ1カウント扱いとしたいです。
〈仕様〉
・テーブルについて:
⓵ポイント履歴のテーブル(point_rireki2 ):利用者へのポイント付与及び、利用者のポイント消費 に関する履歴データが入ったテーブルです。
(キャンペーンの利用/非利用に関係なく履歴が入っています)
⓶キャンペーンのテーブル(campaign):キャンペーン施策によるユーザーの応募履歴が入ったテーブルです。
apply_id ,shisaku_id にNULLは存在しないため、施策外のデータ(=キャンペーン非利用のデータ)は入っていません。
apply_idがPKになります。
テーブルの項目について
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
-kihon_id:ユーザーidになります
-gender:1-男性、2-女性、3-不明となります
・キャンペーン期間:1/26~2/9です
・ポイント履歴の利用判別について =fuyo_pt_tj あるいは kan_pt_tj のカラムに数字が入っている(0や-40などの数値も入ります)
・属性別:下記のようなM1、F2といった条件ごとに数を表示したいです
F1:女性、20~34歳
F2:女性、34~49歳
F3:女性、50歳以上
M1:男性、20~34歳
M2:男性、34~49歳
M3:男性、50歳以上
・キャンペーンテーブルにおいて、2/6までの応募日でデータを絞りたいと思っています。
【アウトプットイメージ】
下記のような 2つのフラグを掛け合わせた数の抽出を行おうと思っています。
(*2つのフラグを掛け合わせて、各々ユニークユーザー数ごとに集計
→ユーザー集計例:1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
キャンペーン利用の有無 | ユーザー属性 | 対象者数(下記のテーブル内容の数を試算した値です)
"キャンペーン利用有り" F1 1
"キャンペーン利用有り" F2 0
"キャンペーン利用有り" F3 0
"キャンペーン利用有り" M1 1
"キャンペーン利用有り" M2 0
"キャンペーン利用有り" M3 0
"キャンペーン利用無し" F1 1
"キャンペーン利用無し" F2 1
"キャンペーン利用無し" F3 0
"キャンペーン利用無し" M1 0
"キャンペーン利用無し" M2 1
"キャンペーン利用無し" M3 0
〈テーブルの内容〉
lang
1CREATE TABLE point_rireki2 2 ("p_date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "gender" int, "area" varchar(3)) 3; 4 5INSERT INTO point_rireki2 6 ("p_date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "gender", "area") 7VALUES 8 ('2018-01-01', 1001, 10, 200, 20, 1, '東京'), 9 ('2018-01-17', 1005, 100, 50, 22, 3, '千葉'), 10 ('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'), 11 ('2018-01-25', 1001, 10, 200, 20, 1, '東京'), 12 ('2018-01-26', 1015, 190, 20, 40, 3, '東京'), 13 ('2018-01-28', 1010, 120, 90, 30, 2, '神奈川'), 14 ('2018-02-01', 1010, 170, 80, 30, 2, '神奈川'), 15 ('2018-02-03', 1015, 40, 280, 40, 3, '東京'), 16 ('2018-02-03', 1020, 10, 190, 40, 2, '神奈川'), 17 ('2018-02-03', 1030, 70, 20, 30, 3, '神奈川'), 18 ('2018-02-04', 1045, 50, 180, 44, 1, '東京') 19 20 21 22CREATE TABLE campaign 23 ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date) 24; 25 26INSERT INTO campaign 27 ("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date") 28VALUES 29 (10001, 'AYC100', 'C施策', 1001, '2018-01-25'), 30 (10002, 'AYC100', 'C施策', 1005, '2018-01-17'), 31 (10003, 'AYC100', 'C施策', 1010, '2018-01-28'), 32 (10004, 'AYB100', 'B施策', 1015, '2018-01-26'), 33 (10005, 'AYB100', 'B施策', 1010, '2018-02-01'), 34 (10006, 'AYC100', 'C施策', 1001, '2018-01-01') 35;
〈テーブルの件数〉
・point_rireki2:400万行
・campaign :1万行
【わからないこと】
キャンペーン利用の有無のフラグを付ける条件式で迷っています。
case when exists 式を用いて、
・select 1で数を抽出
(where条件内で)
・kihon_idとp_date が、kihon_idとapply_dateで一致する場合→キャンペーン利用有り
・そうでない場合→キャンペーン利用無し
とすればよいのではと思ったのですが、、
その場合、ユニークユーザーのカウント(それぞれの条件に該当する場合は、それぞれ1カウント)にならないのではないかとも思い、悩んでおります。。。
どういった形で抽出方法を考えるのがよいでしょうか。。(また、検討中の内容だとキャンペーンなしが該当しないため、その部分の抽出についても悩んでいます)
【検討中のフラグの仮案】
lang
1 , case when 2 exists( 3 select 1 from point_rireki2 pnt left join campaign cmpgn 4 on pnt.kihon_id = cmpgn.kihon_id 5 where pnt.p_date = cmpgn.apply_date 6 ) 7 then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
【現状の全体の抽出文】
lang
1with param as ( 2select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 3) 4 5 6 7select shisaku_id, shisaku_name, demogra_flg, count(distinct kihon_id) as patarn_count 8from ( 9 select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id 10 11 12--【ここに、上記のキャンペーン利用の有無を判定するフラグを入れようと思っています】 13 14 , case gender when 1 then 'M' when 2 then 'F' end || 15 case when old between 20 and 34 then '1' 16 when old between 35 and 49 then '2' 17 when old >= 50 then '3' 18 end as demogra_flg 19 from param left join campaign cmpgn 20 on cmpgn.apply_date < param.apply_date_limit 21 left join point_rireki2 pnt 22 on cmpgn.kihon_id=pnt.kihon_id 23) step1 24group by shisaku_id, shisaku_name,demogra_flg 25order by shisaku_id,demogra_flg
【現在の環境】
Windows
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています