teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問1レコードから複数（毎月）の集計を行うSQLの作成方法

編集履歴

回答編集履歴

3

chousei

2019/08/28 06:12

投稿

yambejp
yambejp

スコア114843

test CHANGED
@@ -261,3 +261,71 @@
261
261
  |2019-09-01|1|1|
262
262
 
263
263
  |2019-10-01|1|1|
264
+
265
+
266
+
267
+ # カレンダーを利用
268
+
269
+ ```SQL
270
+
271
+ create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
272
+
273
+ insert into user_info_table values
274
+
275
+ (1,'20190401','20191001',1),
276
+
277
+ (2,'20190301','20190801',1),
278
+
279
+ (3,'20190401','20190601',2);
280
+
281
+
282
+
283
+ /*とりあえず1年分投入してますが、使い回すので100年分くらい投入しておくとよい*/
284
+
285
+ create table nengetu(d date unique);
286
+
287
+ insert into nengetu values
288
+
289
+ ('20190101'),
290
+
291
+ ('20190201'),
292
+
293
+ ('20190301'),
294
+
295
+ ('20190401'),
296
+
297
+ ('20190501'),
298
+
299
+ ('20190601'),
300
+
301
+ ('20190701'),
302
+
303
+ ('20190801'),
304
+
305
+ ('20190901'),
306
+
307
+ ('20191001'),
308
+
309
+ ('20191101'),
310
+
311
+ ('20191201');
312
+
313
+ ```
314
+
315
+
316
+
317
+ - 集計
318
+
319
+ ```SQL
320
+
321
+ select d,RegistrationCode,count(*) as cnt from user_info_table as t1
322
+
323
+ inner join nengetu as t2
324
+
325
+ on t2.d between t1.UserInsertDate and t1.UpdateDate
326
+
327
+ group by d,RegistrationCode
328
+
329
+ order by d,RegistrationCode;
330
+
331
+ ```

2

procedure

2019/08/28 06:12

投稿

yambejp
yambejp

スコア114843

test CHANGED
@@ -51,3 +51,213 @@
51
51
  という結果を発生させるのですか？
52
52
 
53
53
  カウントが1の理由がわかりません
54
+
55
+
56
+
57
+ # procedure
58
+
59
+ この手の処理はprocedureを使ってテンポラリーテーブルを作って処理します
60
+
61
+
62
+
63
+ - 元データ
64
+
65
+ ```SQL
66
+
67
+ create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
68
+
69
+ insert into user_info_table values
70
+
71
+ (1,'20190401','20191001',1),
72
+
73
+ (2,'20190301','20190801',1),
74
+
75
+ (3,'20190401','20190601',2);
76
+
77
+ ```
78
+
79
+
80
+
81
+ - procedure作成
82
+
83
+ 1回だけ以下を実行
84
+
85
+ ```SQL
86
+
87
+ create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
88
+
89
+ insert into user_info_table values
90
+
91
+ (1,'20190401','20191001',1),
92
+
93
+ (2,'20190301','20190801',1),
94
+
95
+ (3,'20190401','20190601',2);
96
+
97
+ ;
98
+
99
+
100
+
101
+ drop procedure if exists proc_test;
102
+
103
+ delimiter //
104
+
105
+ create procedure proc_test()
106
+
107
+ begin
108
+
109
+ declare a int;
110
+
111
+ declare b date;
112
+
113
+ declare c date;
114
+
115
+ declare d int;
116
+
117
+ declare done int default 0;
118
+
119
+ declare cur cursor for
120
+
121
+ select id,UserInsertDate,UpdateDate,RegistrationCode from user_info_table;
122
+
123
+ declare continue handler for sqlstate '02000' set done = 1;
124
+
125
+ set @sql="";
126
+
127
+ open cur;
128
+
129
+ repeat
130
+
131
+ fetch cur into a,b,c,d;
132
+
133
+ if not done then
134
+
135
+ set @date=b;
136
+
137
+ while @date<=c do
138
+
139
+ if @sql="" then
140
+
141
+ create temporary table tmp(nengetu date,RegistrationCode int);
142
+
143
+ set @sql="insert into tmp ";
144
+
145
+ else
146
+
147
+ set @sql=concat(@sql,"union all ");
148
+
149
+ end if;
150
+
151
+ set @sql=concat(@sql,"select '",@date,"',",d," ");
152
+
153
+ set @date=@date + interval 1 month;
154
+
155
+ end while;
156
+
157
+ end if;
158
+
159
+ until done end repeat;
160
+
161
+ close cur;
162
+
163
+ PREPARE stmt from @sql;
164
+
165
+ EXECUTE stmt;
166
+
167
+ end
168
+
169
+ //
170
+
171
+ delimiter ;
172
+
173
+ ```
174
+
175
+ - テスト
176
+
177
+ ```SQL
178
+
179
+ call proc_test;
180
+
181
+ select * from tmp;
182
+
183
+ ```
184
+
185
+ |nengetu|RegistrationCode|
186
+
187
+ |:--|--:|
188
+
189
+ |2019-04-01|1|
190
+
191
+ |2019-05-01|1|
192
+
193
+ |2019-06-01|1|
194
+
195
+ |2019-07-01|1|
196
+
197
+ |2019-08-01|1|
198
+
199
+ |2019-09-01|1|
200
+
201
+ |2019-10-01|1|
202
+
203
+ |2019-03-01|1|
204
+
205
+ |2019-04-01|1|
206
+
207
+ |2019-05-01|1|
208
+
209
+ |2019-06-01|1|
210
+
211
+ |2019-07-01|1|
212
+
213
+ |2019-08-01|1|
214
+
215
+ |2019-04-01|2|
216
+
217
+ |2019-05-01|2|
218
+
219
+ |2019-06-01|2|
220
+
221
+
222
+
223
+
224
+
225
+ - 集計
226
+
227
+ ```SQL
228
+
229
+ call proc_test;
230
+
231
+ select nengetu,RegistrationCode,count(*) as cnt from tmp
232
+
233
+ group by nengetu,RegistrationCode;
234
+
235
+
236
+
237
+ ```
238
+
239
+ |nengetu|RegistrationCode|cnt|
240
+
241
+ |:--|--:|--:|
242
+
243
+ |2019-03-01|1|1|
244
+
245
+ |2019-04-01|1|2|
246
+
247
+ |2019-04-01|2|1|
248
+
249
+ |2019-05-01|1|2|
250
+
251
+ |2019-05-01|2|1|
252
+
253
+ |2019-06-01|1|2|
254
+
255
+ |2019-06-01|2|1|
256
+
257
+ |2019-07-01|1|2|
258
+
259
+ |2019-08-01|1|2|
260
+
261
+ |2019-09-01|1|1|
262
+
263
+ |2019-10-01|1|1|

1

tuiki

2019/08/28 02:15

投稿

yambejp
yambejp

スコア114843

test CHANGED
@@ -7,3 +7,47 @@
7
7
  具体的には命題の情報では回答が難しいので
8
8
 
9
9
  サンプルをcreate table+insert形式で例示ください
10
+
11
+
12
+
13
+ # 追記
14
+
15
+
16
+
17
+ ```SQL
18
+
19
+ create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
20
+
21
+ insert into user_info_table values
22
+
23
+ (1,'20190401','20191001',1);
24
+
25
+ ```
26
+
27
+ というレコードが１つしかないテーブルから
28
+
29
+
30
+
31
+ |年月|RegistrationCode|カウント|
32
+
33
+ |--:|--:|--:|
34
+
35
+ |4月|1|1|
36
+
37
+ |5月|1|1|
38
+
39
+ |6月|1|1|
40
+
41
+ |7月|1|1|
42
+
43
+ |8月|1|1|
44
+
45
+ |9月|1|1|
46
+
47
+ |10月|1|1|
48
+
49
+
50
+
51
+ という結果を発生させるのですか？
52
+
53
+ カウントが1の理由がわかりません