teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問【SQL初心者】履歴状況のテーブルからフラグ作成し、主テーブルへ反映させたい

編集履歴

質問編集履歴

4

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

2018/03/15 09:25

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -98,8 +98,15 @@
98
98
 
99
99
 
100
100
 
101
- 【やりたい内容の追加と現状】3/15 18:15
102
101
 
102
+
103
+
104
+
105
+
106
+
107
+
108
+ 【やりたい内容の追加⓶と現状】3/15 18:15
109
+
103
110
  ・やりたい内容
104
111
  　　・キャンペーン三ヵ月前までのポイント利用履歴から新規/既存のフラグを作り、その各々の数を抽出　→前回までの内容
105
112
  　　・その新規/既存の数の中で、キャンペーン期間中にポイント利用があったかた(新規/既存のそれぞれにて)の数の抽出　→追記した内容

3

やりたい内容の追加

2018/03/15 09:25

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -93,4 +93,62 @@
93
93
  --oubo_date < '2018-02-07' が施策実行条件により絞り込み
94
94
  ) step1
95
95
  )step2
96
- group by shisaku_id, shisaku_name,new_or_existing
96
+ group by shisaku_id, shisaku_name,new_or_existing
97
+
98
+
99
+
100
+
101
+ 【やりたい内容の追加と現状】3/15 18:15
102
+
103
+ ・やりたい内容
104
+ 　　・キャンペーン三ヵ月前までのポイント利用履歴から新規/既存のフラグを作り、その各々の数を抽出　→前回までの内容
105
+ 　　・その新規/既存の数の中で、キャンペーン期間中にポイント利用があったかた(新規/既存のそれぞれにて)の数の抽出　→追記した内容
106
+
107
+ 　〈完成イメージ〉施策ID/施策名/新規,既存フラグ/新規,既存の数/(新規,既存の各々で)施策期間中のポイント消費者数
108
+ 　〈仕様〉
109
+ 　・キャンペーン期間：2018/1/26～2018/2/9
110
+ 　・ポイントの消費があった方：
111
+ 　　fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている(0も含める)方
112
+ 　〈考えていること〉
113
+ 　　・with句とcount文の間に更にSelect文を書き、サブクエリを増やす。
114
+ 　　・with句の中に、キャンペーン開始日と終了日の期間を定義し、条件内で利用(前回、with句内で定義したキャンペーンスタート日は、キャンペーン前日を定義した方が都合がよかったため、1/25をStartPreとして修正致しました)
115
+ 　といった方向で記述を試みているのですが、
116
+ 　"前回頂いたフラグを活かしたまま、さらにその条件の中でキャンペーン期間にポイント消費があった"という表現がわからず、教えて頂けないでしょうか。
117
+
118
+
119
+ 〈追記作成を行ったクエリ〉＊現状はエラーになります
120
+ with param as (
121
+ 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
122
+ )
123
+
124
+ select shisaku_id, shisaku_name, new_or_existing, new_or_existing_count　, shohi_flg, count(*) as shohi_flg
125
+ from (
126
+ select *
127
+ ,case when
128
+ exists(
129
+ select 1 from point_rireki2
130
+ where kihon_id = cmpgn.kihon_id
131
+ and "date" between param.campaign_Start and param.campaign_End
132
+ and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
133
+ )
134
+ then '消費'　else '消費なし'　end as shohi_flg
135
+ from campaign cmpgn cross join param
136
+ where cmpgn.oubo_date < param.oubo_date_limit
137
+ ) step1
138
+ from(
139
+ select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
140
+ from (
141
+ select *
142
+ , case when
143
+ exists(
144
+ select 1 from point_rireki2
145
+ where kihon_id = cmpgn.kihon_id
146
+ and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
147
+ and (fuyo_pt_tj is not null or kan_pt_tj is not null)
148
+ )
149
+ then '既存' else '新規' end as new_or_existing
150
+ from campaign cmpgn cross join param
151
+ where cmpgn.oubo_date < param.oubo_date_limit
152
+ ) step2
153
+ )step3
154
+ group by shisaku_id, shisaku_name, new_or_existing,shohi_flg

2

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

2018/03/15 09:19

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -1,4 +1,4 @@
1
- 【やりたいこと】
1
+ 【やりたいこと】＊修正しました
2
2
  下記のような2つのテーブルから、
3
3
  施策ごとに(施策に反応した)新規ユーザーと既存ユーザーの数を把握したいです。
4
4
 
@@ -8,6 +8,16 @@
8
8
  で区別したいと思っております。
9
9
 
10
10
 
11
+ ↓
12
+ 《やりたいことの修正》
13
+ ・キャンペーン利用者の新規/既存の数を把握したい
14
+ ・新規/既存の判定については、
15
+ 　キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないかです。
16
+ 　　*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。
17
+
18
+
19
+
20
+
11
21
  〈テーブル概要〉　　
12
22
  　・キャンペーン テーブル(実施施策と施策に応募したユーザー、応募日時があるテーブル)　
13
23
  　　　　⇒対象データは、1万行。100KB程度の大きさです。
@@ -63,4 +73,24 @@
63
73
 
64
74
 
65
75
  【現状】
76
+ 下記で記述しているのですが、
77
+ point_A,point_Bが、データとして0が入っている場合、"既存"としたいのですが"新規"となってしまい、
78
+ 困っています。
79
+
80
+
81
+
82
+ select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
83
+ from(
84
+ select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
85
+ from (
86
+ select *
87
+ ,(select sum(coalesce(point_A,0)+coalesce(point_B,0))
66
- ＊更新次第、追記をさせて頂きます。
88
+ from point_rireki2
89
+ where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
90
+ ) as exist_point
91
+ from campaign cmpgn
92
+ where oubo_date < '2018-02-07'
93
+ --oubo_date < '2018-02-07' が施策実行条件により絞り込み
94
+ ) step1
95
+ )step2
96
+ group by shisaku_id, shisaku_name,new_or_existing

1

テーブル内容の更新

2018/03/14 05:51

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -16,7 +16,39 @@
16
16
  〈テーブル例〉
17
17
  http://sqlfiddle.com/#!17/ee6ad/2
18
18
 
19
+ 〔point_rireki Table〕
20
+ CREATE TABLE point_rireki
21
+ ("date" timestamp, "user_id" int, "point_A" int, "point_B" int, "old" int, "area" varchar(3))
22
+ ;
23
+ INSERT INTO point_rireki
24
+ ("date", "user_id", "point_A", "point_B", "old", "area")
25
+ VALUES
26
+ ('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
27
+ ('2017-10-07 00:00:00', 1005, 100, 50, 22, '神奈川'),
28
+ ('2017-10-10 00:00:00', 1010, 99, 10, 30, '東京'),
29
+ ('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
30
+ ('2017-10-22 00:00:00', 1015, 190, 20, 40, '千葉'),
31
+ ('2017-10-11 00:00:00', 1010, 120, 90, 30, '東京'),
32
+ ('2017-10-27 00:00:00', 1010, 170, 80, 30, '東京'),
33
+ ('2017-10-28 00:00:00', 1015, 40, 280, 40, '千葉')
19
34
 
35
+ 〔campaign Table〕
36
+ CREATE TABLE campaign
37
+ ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "user_id" int, "apply_date" timestamp)
38
+ ;
39
+
40
+ INSERT INTO campaign
41
+ ("apply_id", "shisaku_id", "shisaku_name", "user_id", "apply_date")
42
+ VALUES
43
+ (10001, 'AYC100', 'C施策', 1001, '2017-10-01 00:00:00'),
44
+ (10002, 'AYC100', 'C施策', 1005, '2017-10-07 00:00:00'),
45
+ (10003, 'AYC100', 'C施策', 1010, '2017-10-10 00:00:00'),
46
+ (10004, 'AYB100', 'B施策', 1015, '2017-10-22 00:00:00'),
47
+ (10005, 'AYB100', 'B施策', 1010, '2017-10-11 00:00:00'),
48
+ (10006, 'AYC100', 'C施策', 1001, '2017-10-25 00:00:00')
49
+ ;
50
+
51
+
20
52
  【実行環境】
21
53
  Windows
22
54
  Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています。