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

編集履歴

回答編集履歴

12

推敲

2018/03/16 00:33

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -160,6 +160,8 @@
160
160
 
161
161
  追記（集計パターンの追加）
162
162
 
163
+ ---
164
+
163
165
  ```SQL
164
166
 
165
167
  with param as (

11

追記

2018/03/16 00:33

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -157,3 +157,59 @@
157
157
  group by shisaku_id, shisaku_name, new_or_existing
158
158
 
159
159
  ```
160
+
161
+ 追記（集計パターンの追加）
162
+
163
+ ```SQL
164
+
165
+ with param as (
166
+
167
+ 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
168
+
169
+ )
170
+
171
+ select shisaku_id, shisaku_name, new_or_existing, shohi_flg, count(*) as patarn_count
172
+
173
+ from (
174
+
175
+ select *
176
+
177
+ , case when exists(
178
+
179
+ select 1 from point_rireki2
180
+
181
+ where kihon_id = cmpgn.kihon_id
182
+
183
+ and "date" between param.campaign_Start and param.campaign_End
184
+
185
+ and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
186
+
187
+ ) then '消費' else '消費なし'
188
+
189
+ end as shohi_flg
190
+
191
+ , case when exists(
192
+
193
+ select 1 from point_rireki2
194
+
195
+ where kihon_id = cmpgn.kihon_id
196
+
197
+ and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
198
+
199
+ and (fuyo_pt_tj is not null or kan_pt_tj is not null)
200
+
201
+ ) then '既存' else '新規'
202
+
203
+ end as new_or_existing
204
+
205
+ from campaign cmpgn cross join param
206
+
207
+ where cmpgn.oubo_date < param.oubo_date_limit
208
+
209
+ ) step1
210
+
211
+ group by shisaku_id, shisaku_name, new_or_existing,shohi_flg
212
+
213
+ order by shisaku_id, new_or_existing,shohi_flg
214
+
215
+ ```

10

修正

2018/03/15 10:06

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -122,7 +122,7 @@
122
122
 
123
123
  with param as (
124
124
 
125
- select '2018-01-26':: date campaign_Start,'2018-02-07':: date oubo_date_limit
125
+ select '2018-01-25':: date campaign_Start,'2018-02-07':: date oubo_date_limit
126
126
 
127
127
  )
128
128
 

9

修正

2018/03/14 09:28

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -112,7 +112,9 @@
112
112
 
113
113
  ---
114
114
 
115
- キャンペーン日から３カ月前までを対象とした。
115
+ ・キャンペーン日から３カ月前までを対象とした。
116
+
117
+ ・ポイント内容も判定条件に加える
116
118
 
117
119
  ※キャンペーン日、応募日をCTEにして、変更箇所を局所化
118
120
 
@@ -120,7 +122,7 @@
120
122
 
121
123
  with param as (
122
124
 
123
- select '2018-01-25':: date campaign_date,'2018-02-07':: date oubo_date
125
+ select '2018-01-26':: date campaign_Start,'2018-02-07':: date oubo_date_limit
124
126
 
125
127
  )
126
128
 
@@ -136,7 +138,11 @@
136
138
 
137
139
  select 1 from point_rireki
138
140
 
141
+ where kihon_id=cmpgn.kihon_id
142
+
139
- where kihon_id=cmpgn.kihon_id and "date" between param.campaign_date - '3 months'::interval and param.campaign_date
143
+ and "date" between param.campaign_Start - '3 months'::interval and param.campaign_Start
144
+
145
+ and (fuyo_pt_tj is not null or kan_pt_tj is not null)
140
146
 
141
147
  )
142
148
 
@@ -144,7 +150,7 @@
144
150
 
145
151
  from campaign cmpgn cross join param
146
152
 
147
- where cmpgn.oubo_date < param.oubo_date
153
+ where cmpgn.oubo_date < param.oubo_date_limit
148
154
 
149
155
  ) step1
150
156
 

8

修正

2018/03/14 09:25

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -112,9 +112,9 @@
112
112
 
113
113
  ---
114
114
 
115
- キャンペーン日からの３カ月前を対象とした。
115
+ キャンペーン日から３カ月前までを対象とした。
116
116
 
117
- ※キャンペーン日をCTEにして、変更箇所を局所化
117
+ ※キャンペーン日、応募日をCTEにして、変更箇所を局所化
118
118
 
119
119
  ```SQL
120
120
 

7

修正

2018/03/14 07:46

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -120,7 +120,7 @@
120
120
 
121
121
  with param as (
122
122
 
123
- select '2018-02-07' :: date campaign_date
123
+ select '2018-01-25':: date campaign_date,'2018-02-07':: date oubo_date
124
124
 
125
125
  )
126
126
 
@@ -144,7 +144,7 @@
144
144
 
145
145
  from campaign cmpgn cross join param
146
146
 
147
- where oubo_date < param.campaign_date
147
+ where cmpgn.oubo_date < param.oubo_date
148
148
 
149
149
  ) step1
150
150
 

6

追記

2018/03/14 07:43

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -107,3 +107,47 @@
107
107
  group by shisaku_id, shisaku_name, new_or_existing
108
108
 
109
109
  ```
110
+
111
+ 追記（条件変更２）
112
+
113
+ ---
114
+
115
+ キャンペーン日からの３カ月前を対象とした。
116
+
117
+ ※キャンペーン日をCTEにして、変更箇所を局所化
118
+
119
+ ```SQL
120
+
121
+ with param as (
122
+
123
+ select '2018-02-07' :: date campaign_date
124
+
125
+ )
126
+
127
+ select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
128
+
129
+ from (
130
+
131
+ select *
132
+
133
+ , case when
134
+
135
+ exists(
136
+
137
+ select 1 from point_rireki
138
+
139
+ where kihon_id=cmpgn.kihon_id and "date" between param.campaign_date - '3 months'::interval and param.campaign_date
140
+
141
+ )
142
+
143
+ then '既存' else '新規' end as new_or_existing
144
+
145
+ from campaign cmpgn cross join param
146
+
147
+ where oubo_date < param.campaign_date
148
+
149
+ ) step1
150
+
151
+ group by shisaku_id, shisaku_name, new_or_existing
152
+
153
+ ```

5

追記

2018/03/14 06:36

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -69,3 +69,41 @@
69
69
  group by shisaku_id, shisaku_name, new_or_existing
70
70
 
71
71
  ```
72
+
73
+ 追記（条件変更）
74
+
75
+ ---
76
+
77
+ 応募日からみて過去３カ月以内のポイント履歴があるものを識別するように変更。
78
+
79
+ ※項目名が変わっているので、そちらに合わせました。
80
+
81
+ ```SQL
82
+
83
+ select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
84
+
85
+ from (
86
+
87
+ select *
88
+
89
+ , case when
90
+
91
+ exists(
92
+
93
+ select 1 from point_rireki
94
+
95
+ where kihon_id=cmpgn.kihon_id and "date" between cmpgn.oubo_date - '3 months'::interval and cmpgn.oubo_date
96
+
97
+ )
98
+
99
+ then '既存' else '新規' end as new_or_existing
100
+
101
+ from campaign cmpgn
102
+
103
+ where oubo_date < '2018-02-07'
104
+
105
+ ) step1
106
+
107
+ group by shisaku_id, shisaku_name, new_or_existing
108
+
109
+ ```

4

追記

2018/03/14 04:34

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -29,6 +29,8 @@
29
29
  dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
30
30
 
31
31
  （そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど）
32
+
33
+ [付録C SQLキーワード](https://www.postgresql.jp/document/9.6/html/sql-keywords-appendix.html)
32
34
 
33
35
 
34
36
 

3

追記

2018/03/13 10:30

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -29,3 +29,41 @@
29
29
  dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
30
30
 
31
31
  （そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど）
32
+
33
+
34
+
35
+ 追記（施策別集計)
36
+
37
+ ---
38
+
39
+
40
+
41
+ ```SQL
42
+
43
+ select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
44
+
45
+ from (
46
+
47
+ select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
48
+
49
+ from (
50
+
51
+ select *
52
+
53
+ ,(select sum(coalesce("point_A",0)+coalesce("point_B",0))
54
+
55
+ from point_rireki
56
+
57
+ where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
58
+
59
+ ) as exist_point
60
+
61
+ from campaign cmpgn
62
+
63
+ ) step1
64
+
65
+ ) step2
66
+
67
+ group by shisaku_id, shisaku_name, new_or_existing
68
+
69
+ ```

2

推敲

2018/03/13 08:41

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -12,7 +12,7 @@
12
12
 
13
13
  from point_rireki
14
14
 
15
- where user_id=cmpgn.user_id and "date" >= cmpgn.apply_date - '3 months'::interval and "date" <= cmpgn.apply_date
15
+ where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
16
16
 
17
17
  ) as exist_point
18
18
 

1

推敲

2018/03/13 07:27

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -12,7 +12,7 @@
12
12
 
13
13
  from point_rireki
14
14
 
15
- where user_id=cmpgn.user_id and "date" >= cmpgn.apply_date - '3 months'::interval and "date" < cmpgn.apply_date
15
+ where user_id=cmpgn.user_id and "date" >= cmpgn.apply_date - '3 months'::interval and "date" <= cmpgn.apply_date
16
16
 
17
17
  ) as exist_point
18
18