質問編集履歴

わかりやすくやりたい内容の追加を見やすくしました

2018/03/15 09:25

投稿

iki

スコア12

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -198,7 +198,21 @@
-【やりたい内容の追加と現状】3/15 18:15
+【やりたい内容の追加⓶と現状】3/15 18:15

やりたい内容の追加

2018/03/15 09:25

投稿

iki

スコア12

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -189,3 +189,119 @@
 )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

実現したい内容を修正致しました。

2018/03/15 09:19

投稿

iki

スコア12

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,4 +1,4 @@
-【やりたいこと】
+【やりたいこと】＊修正しました
 下記のような2つのテーブルから、
@@ -13,6 +13,26 @@
 (=ポイント履歴のテーブルにおいて、応募日から3カ月以内のポイントA,Bがともに(全て)0またはNULL⇒新規、 ポイントが入っている⇒既存)
 で区別したいと思っております。
+↓
+《やりたいことの修正》
+・キャンペーン利用者の新規/既存の数を把握したい
+・新規/既存の判定については、
+　キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないかです。
+　　*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。
@@ -128,4 +148,44 @@
 【現状】
+下記で記述しているのですが、
+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

テーブル内容の更新

2018/03/14 05:51

投稿

iki

スコア12

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -34,6 +34,70 @@
+〔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')
+;
 【実行環境】