質問編集履歴
9
条件の追加
test
CHANGED
File without changes
|
test
CHANGED
@@ -278,7 +278,7 @@
|
|
278
278
|
|
279
279
|
, case when p_date between param.campaign_Start and param.campaign_End
|
280
280
|
|
281
|
-
and (fuyo_pt_tj is not null or kan_pt_tj is not null
|
281
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null )
|
282
282
|
|
283
283
|
then '消費' else '消費なし'
|
284
284
|
|
@@ -292,6 +292,8 @@
|
|
292
292
|
|
293
293
|
, case when p_date between param.campaign_Start and param.campaign_End
|
294
294
|
|
295
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null )
|
296
|
+
|
295
297
|
and (shisaku_id is not null)
|
296
298
|
|
297
299
|
then '施策利用なし' else '施策利用あり'
|
8
順序
test
CHANGED
File without changes
|
test
CHANGED
@@ -324,9 +324,9 @@
|
|
324
324
|
|
325
325
|
) step1
|
326
326
|
|
327
|
-
group by shisaku_id, shisaku_name,shohi_flg,demogra_flg
|
327
|
+
group by shisaku_id, shisaku_name,shohi_flg,shisaku_umu_flg, demogra_flg
|
328
|
-
|
328
|
+
|
329
|
-
order by shisaku_id,shohi_flg,demogra_flg
|
329
|
+
order by shisaku_id,shohi_flg,shisaku_umu_flg,demogra_flg
|
330
330
|
|
331
331
|
|
332
332
|
|
7
修正
test
CHANGED
File without changes
|
test
CHANGED
@@ -270,7 +270,7 @@
|
|
270
270
|
|
271
271
|
)
|
272
272
|
|
273
|
-
select shisaku_id, shisaku_name, shohi_flg,demogra_flg, count(distinct kihon_id) as patarn_count
|
273
|
+
select shisaku_id, shisaku_name, shohi_flg, shisaku_umu_flg , demogra_flg, count(distinct kihon_id) as patarn_count
|
274
274
|
|
275
275
|
from (
|
276
276
|
|
6
→追加
test
CHANGED
File without changes
|
test
CHANGED
@@ -246,7 +246,7 @@
|
|
246
246
|
|
247
247
|
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
|
248
248
|
|
249
|
-
|
249
|
+
→キャンペーンテーブルにある、施策の履歴がない(=キャンペーンテーブルとは紐づかない形でのポイント利用がある)
|
250
250
|
|
251
251
|
|
252
252
|
|
@@ -256,7 +256,7 @@
|
|
256
256
|
|
257
257
|
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
|
258
258
|
|
259
|
-
|
259
|
+
→キャンペーンテーブルにある、施策利用に紐づいたポイント利用となっている
|
260
260
|
|
261
261
|
|
262
262
|
|
5
条件の追加
test
CHANGED
File without changes
|
test
CHANGED
@@ -229,3 +229,111 @@
|
|
229
229
|
Windows
|
230
230
|
|
231
231
|
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています
|
232
|
+
|
233
|
+
|
234
|
+
|
235
|
+
|
236
|
+
|
237
|
+
|
238
|
+
|
239
|
+
【追記】
|
240
|
+
|
241
|
+
やりたい内容: キャンペーン利用の有無によるポイント消費者を区分けするフラグを作りたい
|
242
|
+
|
243
|
+
〈定義〉
|
244
|
+
|
245
|
+
キャンペーン利用をせずにポイント利用がある人:"施策利用なし"と定義したい
|
246
|
+
|
247
|
+
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
|
248
|
+
|
249
|
+
キャンペーンテーブルにある、施策の履歴がない(=キャンペーンテーブルとは紐づかない形でのポイント利用がある)
|
250
|
+
|
251
|
+
|
252
|
+
|
253
|
+
|
254
|
+
|
255
|
+
キャンペーン利用にてポイント利用がある人:"施策利用あり"と定義したい
|
256
|
+
|
257
|
+
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
|
258
|
+
|
259
|
+
キャンペーンテーブルにある、施策利用に紐づいたポイント利用となっている
|
260
|
+
|
261
|
+
|
262
|
+
|
263
|
+
```lang-sql
|
264
|
+
|
265
|
+
|
266
|
+
|
267
|
+
with param as (
|
268
|
+
|
269
|
+
select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
|
270
|
+
|
271
|
+
)
|
272
|
+
|
273
|
+
select shisaku_id, shisaku_name, shohi_flg,demogra_flg, count(distinct kihon_id) as patarn_count
|
274
|
+
|
275
|
+
from (
|
276
|
+
|
277
|
+
select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id
|
278
|
+
|
279
|
+
, case when p_date between param.campaign_Start and param.campaign_End
|
280
|
+
|
281
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
|
282
|
+
|
283
|
+
then '消費' else '消費なし'
|
284
|
+
|
285
|
+
end as shohi_flg
|
286
|
+
|
287
|
+
|
288
|
+
|
289
|
+
|
290
|
+
|
291
|
+
|
292
|
+
|
293
|
+
, case when p_date between param.campaign_Start and param.campaign_End
|
294
|
+
|
295
|
+
and (shisaku_id is not null)
|
296
|
+
|
297
|
+
then '施策利用なし' else '施策利用あり'
|
298
|
+
|
299
|
+
end as shisaku_umu_flg
|
300
|
+
|
301
|
+
|
302
|
+
|
303
|
+
|
304
|
+
|
305
|
+
|
306
|
+
|
307
|
+
, case gender when 1 then 'M' when 2 then 'F' end ||
|
308
|
+
|
309
|
+
case when old between 20 and 34 then '1'
|
310
|
+
|
311
|
+
when old between 35 and 49 then '2'
|
312
|
+
|
313
|
+
when old >= 50 then '3'
|
314
|
+
|
315
|
+
end as demogra_flg
|
316
|
+
|
317
|
+
from param left join campaign cmpgn
|
318
|
+
|
319
|
+
on cmpgn.apply_date < param.apply_date_limit
|
320
|
+
|
321
|
+
left join point_rireki2 pnt
|
322
|
+
|
323
|
+
on cmpgn.kihon_id=pnt.kihon_id
|
324
|
+
|
325
|
+
) step1
|
326
|
+
|
327
|
+
group by shisaku_id, shisaku_name,shohi_flg,demogra_flg
|
328
|
+
|
329
|
+
order by shisaku_id,shohi_flg,demogra_flg
|
330
|
+
|
331
|
+
|
332
|
+
|
333
|
+
|
334
|
+
|
335
|
+
|
336
|
+
|
337
|
+
|
338
|
+
|
339
|
+
```
|
4
表記内容を修正しました
test
CHANGED
File without changes
|
test
CHANGED
@@ -10,7 +10,7 @@
|
|
10
10
|
|
11
11
|
・テーブルの項目について:
|
12
12
|
|
13
|
-
[テーブル項目](http://sqlfiddle.com/#!17/
|
13
|
+
[テーブル項目](http://sqlfiddle.com/#!17/5602f/1)
|
14
14
|
|
15
15
|
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
|
16
16
|
|
@@ -52,7 +52,7 @@
|
|
52
52
|
|
53
53
|
CREATE TABLE point_rireki2
|
54
54
|
|
55
|
-
("date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "
|
55
|
+
("p_date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "gender" int, "area" varchar(3))
|
56
56
|
|
57
57
|
;
|
58
58
|
|
@@ -60,7 +60,7 @@
|
|
60
60
|
|
61
61
|
INSERT INTO point_rireki2
|
62
62
|
|
63
|
-
("date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "
|
63
|
+
("p_date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "gender", "area")
|
64
64
|
|
65
65
|
VALUES
|
66
66
|
|
@@ -80,40 +80,38 @@
|
|
80
80
|
|
81
81
|
('2018-02-03', 1015, 40, 280, -3, 1, '東京')
|
82
82
|
|
83
|
+
|
84
|
+
|
85
|
+
|
86
|
+
|
87
|
+
CREATE TABLE campaign
|
88
|
+
|
89
|
+
("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date)
|
90
|
+
|
83
91
|
;
|
84
92
|
|
85
|
-
|
86
|
-
|
87
|
-
|
88
|
-
|
93
|
+
|
94
|
+
|
89
|
-
|
95
|
+
INSERT INTO campaign
|
90
|
-
|
96
|
+
|
91
|
-
("apply_id"
|
97
|
+
("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date")
|
98
|
+
|
99
|
+
VALUES
|
100
|
+
|
101
|
+
(10001, 'AYC100', 'C施策', 1001, '2018-01-25'),
|
102
|
+
|
103
|
+
(10002, 'AYC100', 'C施策', 1005, '2018-01-17'),
|
104
|
+
|
105
|
+
(10003, 'AYC100', 'C施策', 1010, '2018-01-28'),
|
106
|
+
|
107
|
+
(10004, 'AYB100', 'B施策', 1015, '2018-01-26'),
|
108
|
+
|
109
|
+
(10005, 'AYB100', 'B施策', 1010, '2018-02-01'),
|
110
|
+
|
111
|
+
(10006, 'AYC100', 'C施策', 1001, '2018-01-01')
|
92
112
|
|
93
113
|
;
|
94
114
|
|
95
|
-
|
96
|
-
|
97
|
-
INSERT INTO campaign
|
98
|
-
|
99
|
-
("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date")
|
100
|
-
|
101
|
-
VALUES
|
102
|
-
|
103
|
-
(10001, 'AYC100', 'C施策', 1001, '2018-01-25'),
|
104
|
-
|
105
|
-
(10002, 'AYC100', 'C施策', 1005, '2018-01-17'),
|
106
|
-
|
107
|
-
(10003, 'AYC100', 'C施策', 1010, '2018-01-28'),
|
108
|
-
|
109
|
-
(10004, 'AYB100', 'B施策', 1015, '2018-01-26'),
|
110
|
-
|
111
|
-
(10005, 'AYB100', 'B施策', 1010, '2018-02-01'),
|
112
|
-
|
113
|
-
(10006, 'AYC100', 'C施策', 1001, '2018-01-01')
|
114
|
-
|
115
|
-
;
|
116
|
-
|
117
115
|
```
|
118
116
|
|
119
117
|
|
@@ -172,7 +170,7 @@
|
|
172
170
|
|
173
171
|
where kihon_id = cmpgn.kihon_id
|
174
172
|
|
175
|
-
and "date" between param.campaign_Start and param.campaign_End
|
173
|
+
and "p_date" between param.campaign_Start and param.campaign_End
|
176
174
|
|
177
175
|
and (fuyo_pt_tj is not null or kan_pt_tj is not null )
|
178
176
|
|
@@ -186,27 +184,27 @@
|
|
186
184
|
|
187
185
|
|
188
186
|
|
189
|
-
, case when
|
187
|
+
, case when gender = 1 and old >= 20 and old <= 34
|
190
188
|
|
191
189
|
then'M1'
|
192
190
|
|
193
|
-
when
|
191
|
+
when gender = 1 and old >= 35 and old <= 49
|
194
192
|
|
195
193
|
then'M2'
|
196
194
|
|
197
|
-
when
|
195
|
+
when gender = 1 and old >= 50
|
198
196
|
|
199
197
|
then'M3'
|
200
198
|
|
201
|
-
when
|
199
|
+
when gender = 2 and old >= 20 and old <= 34
|
202
200
|
|
203
201
|
then'F1'
|
204
202
|
|
205
|
-
when
|
203
|
+
when gender = 2 and old >= 35 and old <= 49
|
206
204
|
|
207
205
|
then'F2'
|
208
206
|
|
209
|
-
when
|
207
|
+
when gender = 2 and old >= 50
|
210
208
|
|
211
209
|
then'F3'
|
212
210
|
|
3
データの件数を追加
test
CHANGED
File without changes
|
test
CHANGED
@@ -118,6 +118,18 @@
|
|
118
118
|
|
119
119
|
|
120
120
|
|
121
|
+
〈テーブルの件数〉
|
122
|
+
|
123
|
+
・point_rireki2:400万行
|
124
|
+
|
125
|
+
・campaign :1万行
|
126
|
+
|
127
|
+
|
128
|
+
|
129
|
+
|
130
|
+
|
131
|
+
|
132
|
+
|
121
133
|
|
122
134
|
|
123
135
|
【わからないこと】
|
2
指摘頂いた内容を修正させて頂きました!
test
CHANGED
File without changes
|
test
CHANGED
@@ -140,7 +140,7 @@
|
|
140
140
|
|
141
141
|
with param as (
|
142
142
|
|
143
|
-
select '2018-02-07':: date
|
143
|
+
select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
|
144
144
|
|
145
145
|
)
|
146
146
|
|
@@ -162,7 +162,7 @@
|
|
162
162
|
|
163
163
|
and "date" between param.campaign_Start and param.campaign_End
|
164
164
|
|
165
|
-
and (fuyo_pt_tj is not null or kan_pt_tj is not null
|
165
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null )
|
166
166
|
|
167
167
|
) then '消費' else '消費なし'
|
168
168
|
|
@@ -170,7 +170,7 @@
|
|
170
170
|
|
171
171
|
from campaign cmpgn cross join param
|
172
172
|
|
173
|
-
where cmpgn.
|
173
|
+
where cmpgn.apply_date < param.apply_date_limit
|
174
174
|
|
175
175
|
|
176
176
|
|
1
ご指摘頂いた内容を修正しました
test
CHANGED
File without changes
|
test
CHANGED
@@ -10,9 +10,9 @@
|
|
10
10
|
|
11
11
|
・テーブルの項目について:
|
12
12
|
|
13
|
-
http://sqlfiddle.com/#!
|
13
|
+
[テーブル項目](http://sqlfiddle.com/#!17/7fcf5/1)
|
14
|
-
|
14
|
+
|
15
|
-
-fuyo_pt_tj
|
15
|
+
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
|
16
16
|
|
17
17
|
-kihon_id:ユーザーidになります
|
18
18
|
|
@@ -34,7 +34,7 @@
|
|
34
34
|
|
35
35
|
M2:男性、34~49歳
|
36
36
|
|
37
|
-
|
37
|
+
M3:男性、50歳以上
|
38
38
|
|
39
39
|
|
40
40
|
|
@@ -42,6 +42,84 @@
|
|
42
42
|
|
43
43
|
|
44
44
|
|
45
|
+
|
46
|
+
|
47
|
+
|
48
|
+
|
49
|
+
〈テーブルの内容〉
|
50
|
+
|
51
|
+
```lang-sql
|
52
|
+
|
53
|
+
CREATE TABLE point_rireki2
|
54
|
+
|
55
|
+
("date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "sex" int, "area" varchar(3))
|
56
|
+
|
57
|
+
;
|
58
|
+
|
59
|
+
|
60
|
+
|
61
|
+
INSERT INTO point_rireki2
|
62
|
+
|
63
|
+
("date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "sex", "area")
|
64
|
+
|
65
|
+
VALUES
|
66
|
+
|
67
|
+
('2018-01-01', 1001, 10, 200, 10, 1, '東京'),
|
68
|
+
|
69
|
+
('2018-01-17', 1005, 100, 50, 22, 3, '千葉'),
|
70
|
+
|
71
|
+
('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'),
|
72
|
+
|
73
|
+
('2018-01-25', 1001, 10, 200, NULL, 1, '東京'),
|
74
|
+
|
75
|
+
('2018-01-26', 1015, 190, 20, 40, 3, '東京'),
|
76
|
+
|
77
|
+
('2018-01-28', 1010, 120, 90, 30, 2, '神奈川'),
|
78
|
+
|
79
|
+
('2018-02-01', 1010, 170, 80, 30, 2, '神奈川'),
|
80
|
+
|
81
|
+
('2018-02-03', 1015, 40, 280, -3, 1, '東京')
|
82
|
+
|
83
|
+
;
|
84
|
+
|
85
|
+
|
86
|
+
|
87
|
+
|
88
|
+
|
89
|
+
CREATE TABLE campaign
|
90
|
+
|
91
|
+
("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date)
|
92
|
+
|
93
|
+
;
|
94
|
+
|
95
|
+
|
96
|
+
|
97
|
+
INSERT INTO campaign
|
98
|
+
|
99
|
+
("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date")
|
100
|
+
|
101
|
+
VALUES
|
102
|
+
|
103
|
+
(10001, 'AYC100', 'C施策', 1001, '2018-01-25'),
|
104
|
+
|
105
|
+
(10002, 'AYC100', 'C施策', 1005, '2018-01-17'),
|
106
|
+
|
107
|
+
(10003, 'AYC100', 'C施策', 1010, '2018-01-28'),
|
108
|
+
|
109
|
+
(10004, 'AYB100', 'B施策', 1015, '2018-01-26'),
|
110
|
+
|
111
|
+
(10005, 'AYB100', 'B施策', 1010, '2018-02-01'),
|
112
|
+
|
113
|
+
(10006, 'AYC100', 'C施策', 1001, '2018-01-01')
|
114
|
+
|
115
|
+
;
|
116
|
+
|
117
|
+
```
|
118
|
+
|
119
|
+
|
120
|
+
|
121
|
+
|
122
|
+
|
45
123
|
【わからないこと】
|
46
124
|
|
47
125
|
現状に記載している、サブクエリ内のcase文の書き方が分からず困っています。
|
@@ -58,6 +136,8 @@
|
|
58
136
|
|
59
137
|
【現状】
|
60
138
|
|
139
|
+
```lang-sql
|
140
|
+
|
61
141
|
with param as (
|
62
142
|
|
63
143
|
select '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
|
@@ -130,7 +210,7 @@
|
|
130
210
|
|
131
211
|
order by shisaku_id, new_or_existing,shohi_flg
|
132
212
|
|
133
|
-
|
213
|
+
```
|
134
214
|
|
135
215
|
|
136
216
|
|