【実現したいこと】
ユーザーのポイントの履歴テーブルと施策キャンペーンの2つのテーブルから、
特定施策において、キャンペーン期間中にポイント利用があった方の数を属性別に抽出したい。
〈仕様〉
・テーブルの項目について:
テーブル項目
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
-kihon_id:ユーザーidになります
-sex: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までの応募日でデータを絞りたいと思っています。
〈テーブルの内容〉
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, 10, 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, NULL, 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, -3, 1, '東京') 16 17 18CREATE TABLE campaign 19 ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date) 20; 21 22INSERT INTO campaign 23 ("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date") 24VALUES 25 (10001, 'AYC100', 'C施策', 1001, '2018-01-25'), 26 (10002, 'AYC100', 'C施策', 1005, '2018-01-17'), 27 (10003, 'AYC100', 'C施策', 1010, '2018-01-28'), 28 (10004, 'AYB100', 'B施策', 1015, '2018-01-26'), 29 (10005, 'AYB100', 'B施策', 1010, '2018-02-01'), 30 (10006, 'AYC100', 'C施策', 1001, '2018-01-01') 31;
〈テーブルの件数〉
・point_rireki2:400万行
・campaign :1万行
【わからないこと】
現状に記載している、サブクエリ内のcase文の書き方が分からず困っています。
2つのcase文にて、それぞれの条件の絞り込みに必要なテーブルが異なる場合、どう記述すればよいのでしょうか。
(また、サブクエリ内の定義も下記のような形で上記の内容を実現できているかも教えていただきたいです)
【現状】
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 6select shisaku_id, shisaku_name, shohi_flg,demogra_flg, count(*) as patarn_count 7from ( 8 select * 9 , case when exists( 10 select 1 from point_rireki2 11 where kihon_id = cmpgn.kihon_id 12 and "p_date" between param.campaign_Start and param.campaign_End 13 and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 14 ) then '消費' else '消費なし' 15 end as shohi_flg 16 from campaign cmpgn cross join param 17 where cmpgn.apply_date < param.apply_date_limit 18 19 , case when gender = 1 and old >= 20 and old <= 34 20 then'M1' 21 when gender = 1 and old >= 35 and old <= 49 22 then'M2' 23 when gender = 1 and old >= 50 24 then'M3' 25 when gender = 2 and old >= 20 and old <= 34 26 then'F1' 27 when gender = 2 and old >= 35 and old <= 49 28 then'F2' 29 when gender = 2 and old >= 50 30 then'F3' 31 else null 32 end as demogra_flg 33 from point_rireki2 34) step1 35group by shisaku_id, shisaku_name,shohi_flg,demogra_flg 36order by shisaku_id, new_or_existing,shohi_flg
【現在の環境】
Windows
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています
【追記】
やりたい内容: キャンペーン利用の有無によるポイント消費者を区分けするフラグを作りたい
〈定義〉
キャンペーン利用をせずにポイント利用がある人:"施策利用なし"と定義したい
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
→キャンペーンテーブルにある、施策の履歴がない(=キャンペーンテーブルとは紐づかない形でのポイント利用がある)
キャンペーン利用にてポイント利用がある人:"施策利用あり"と定義したい
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
→キャンペーンテーブルにある、施策利用に紐づいたポイント利用となっている
lang
1 2with param as ( 3select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 4) 5select shisaku_id, shisaku_name, shohi_flg, shisaku_umu_flg , demogra_flg, count(distinct kihon_id) as patarn_count 6from ( 7 select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id 8 , case when p_date between param.campaign_Start and param.campaign_End 9 and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 10 then '消費' else '消費なし' 11 end as shohi_flg 12 13 14 15 , case when p_date between param.campaign_Start and param.campaign_End 16 and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 17 and (shisaku_id is not null) 18 then '施策利用なし' else '施策利用あり' 19 end as shisaku_umu_flg 20 21 22 23 , case gender when 1 then 'M' when 2 then 'F' end || 24 case when old between 20 and 34 then '1' 25 when old between 35 and 49 then '2' 26 when old >= 50 then '3' 27 end as demogra_flg 28 from param left join campaign cmpgn 29 on cmpgn.apply_date < param.apply_date_limit 30 left join point_rireki2 pnt 31 on cmpgn.kihon_id=pnt.kihon_id 32) step1 33group by shisaku_id, shisaku_name,shohi_flg,shisaku_umu_flg, demogra_flg 34order by shisaku_id,shohi_flg,shisaku_umu_flg,demogra_flg 35 36 37 38
回答2件
あなたの回答
tips
プレビュー