1レコードから複数（毎月）の集計を行うSQLの作成方法

Question

mysqlのバージョンは5.6.26です

1レコードから複数（毎月）集計を行うSQLをどう作成すればよいか考えております。

現在のSQL文はこのようになっています。

SELECT  DATE_FORMAT(UserInsertDate, '%m') UserInsertDate, RegistrationCode  RegistrationCode, count(RegistrationCode) count 
FROM user_info_table
WHERE DATE_FORMAT(UserInsertDate, '%Y%m') < DATE_FORMAT(UpdateDate, '%Y%m')
GROUP BY DATE_FORMAT(UserInsertDate, '%m'), RegistrationCode

現在このSQL文ですと、user_info_table1レコードにつきcount1件しか取得できませんが、
DATE_FORMAT(UserInsertDate, '%Y%m')< DATE_FORMAT(UpdateDate, '%Y%m')の全てのレコードの月をcount1件として取得したいのですがどのようにすればよいでしょうか？

Answer 1

年+月のデータを使って集計したいなら別途カレンダーテーブルをつくって
ダミーで100年分くらいデータを作っておき、joinすればよいでしょう

具体的には命題の情報では回答が難しいので
サンプルをcreate table+insert形式で例示ください

追記

create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
insert into user_info_table values
(1,'20190401','20191001',1);

というレコードが１つしかないテーブルから

年月	RegistrationCode	カウント
4月	1	1
5月	1	1
6月	1	1
7月	1	1
8月	1	1
9月	1	1
10月	1	1

という結果を発生させるのですか？
カウントが1の理由がわかりません

procedure

この手の処理はprocedureを使ってテンポラリーテーブルを作って処理します

• 元データ

create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
insert into user_info_table values
(1,'20190401','20191001',1),
(2,'20190301','20190801',1),
(3,'20190401','20190601',2);

• procedure作成

1回だけ以下を実行

create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
insert into user_info_table values
(1,'20190401','20191001',1),
(2,'20190301','20190801',1),
(3,'20190401','20190601',2);
;

drop procedure if exists proc_test;
delimiter //
create procedure proc_test()
begin
declare a int;
declare b date;
declare c date;
declare d int;
declare done int default 0;
declare cur cursor for
select id,UserInsertDate,UpdateDate,RegistrationCode from user_info_table;
declare continue handler for sqlstate '02000' set done = 1;
set @sql="";
open cur;
repeat
fetch cur into a,b,c,d;
  if not done then
    set @date=b;
    while @date<=c do
      if @sql="" then
        create temporary table tmp(nengetu date,RegistrationCode int);
        set @sql="insert into tmp ";
      else
        set @sql=concat(@sql,"union all ");
      end if;
      set @sql=concat(@sql,"select '",@date,"',",d," ");
   set @date=@date + interval 1 month;
   end while;
  end if;
  until done end repeat;
close cur;
PREPARE stmt from @sql;
EXECUTE stmt;
end 
//
delimiter ;

• テスト

call proc_test;
select * from tmp;

nengetu	RegistrationCode
2019-04-01	1
2019-05-01	1
2019-06-01	1
2019-07-01	1
2019-08-01	1
2019-09-01	1
2019-10-01	1
2019-03-01	1
2019-04-01	1
2019-05-01	1
2019-06-01	1
2019-07-01	1
2019-08-01	1
2019-04-01	2
2019-05-01	2
2019-06-01	2

• 集計

call proc_test;
select nengetu,RegistrationCode,count(*) as cnt from tmp
group by nengetu,RegistrationCode;

nengetu	RegistrationCode	cnt
2019-03-01	1	1
2019-04-01	1	2
2019-04-01	2	1
2019-05-01	1	2
2019-05-01	2	1
2019-06-01	1	2
2019-06-01	2	1
2019-07-01	1	2
2019-08-01	1	2
2019-09-01	1	1
2019-10-01	1	1

カレンダーを利用

create table user_info_table(id int primary key,UserInsertDate date,UpdateDate date,RegistrationCode int);
insert into user_info_table values
(1,'20190401','20191001',1),
(2,'20190301','20190801',1),
(3,'20190401','20190601',2);

/*とりあえず1年分投入してますが、使い回すので100年分くらい投入しておくとよい*/
create table nengetu(d date unique);
insert into nengetu values
('20190101'),
('20190201'),
('20190301'),
('20190401'),
('20190501'),
('20190601'),
('20190701'),
('20190801'),
('20190901'),
('20191001'),
('20191101'),
('20191201');

• 集計

select d,RegistrationCode,count(*) as cnt from user_info_table as t1
inner join nengetu as t2
on t2.d between t1.UserInsertDate and t1.UpdateDate
group by d,RegistrationCode
order by d,RegistrationCode;

Comments

[planet]

ご回答ありがとうございます。ドロップダウンリストから年を選択させるので、1年分のデータで大丈夫です。
あと、こちらの不手際で申し訳ありませんでしたが、毎月のレコードの作成ではなく集計でした。タイトルも紛らわしいので修正致します。

[yambejp]

すみません、回答の意図が伝わってないかもしれませんので
レコードのサンプルと、期待する結果を提示いただかないと
具体的なSQLが書けないということです。
回答を希望するなら是非サンプル提示を検討ください

[planet]

年月,RegistrationCode1.カウント
1月,1,13
2月,1,34
3月,1,23
4月,1,12
……
12月1,23

こんな感じです。よろしくお願いします。

[yambejp]

user_info_tableの情報がないと無理です

[planet]

失礼

UserInsertDate,UpdateDate,RegistrationCode
20190401,20191001,1

このデータの場合は
年月,RegistrationCode.カウント
4月,1,1
5月,1,1
6月,1,1
7月,1,1
8月,1,1
9月,1,1
10月,1,1

こうなるようにお願いします。

[yambejp]

ちょっと意味がわかりません
追記した箇所を読んで、それでいいのか確認してください

[kirato]

もしかして
UserInsertDate,UpdateDate,RegistrationCode
20190401,20191001,1
20190401,20190801,1

このデータの場合は
年月,RegistrationCode.カウント
4月,1,2
5月,1,2
6月,1,2
7月,1,2
8月,1,2
9月,1,1
10月,1,1
ってなるってことですか？

[yambejp]

年月なのか複数年分あっても月が同じであれば一緒に集計するのか
そのあたりも気になります。

> 1年分のデータで大丈夫

ということなので、絶対に年をまたがないという制限がかかっているのでしょうか？
たとえば4月から翌年3月まででも、1～12月みたいな時系列とはいえない表示でよいのでしょうか？

[planet]

凄く返事が遅くなって申し訳ありません。
kiratoさんの書き込みの通りの結果となります。
時系列で言えば

UserInsertDate,UpdateDate,RegistrationCode
20181001,20190401,1

この場合で、選択年度が2019であれば
1月,1,1
2月,1,1
3月,1,1
4月,1,1
となります。

遅くなりましたが、よろしくお願いします。

[yambejp]

仮に
20181001,20191201,1
のような場合はどうなのでしょう？
(1)13ヶ月以上にはぜったいならない
(2)10,11,12月がカウント２になる
(3)気にせずにカウントは1

[planet]

質問ありがとうございます。

13か月以上絶対ならないというよりも、カウントの年をドロップダウンで指定するので、その年のカウントが1になります。

20181001,20191201,1
の例で行くと、2018年の指定で行くと、2018年10月分の1のみカウントされます。
その場合、2018年1月～2018年12のカウント数しか気にしなくても大丈夫です。

[yambejp]

一応procedureの書き方を追記しておきました
きちんとサンプル提示してくれないので仕様がさっぱりわかりません
今回の回答もあっているかどうかわかりませんが
とりあえず形にはしておきました

[planet]

その回答で合っています。ただし、procedureを利用したやり方は、恐らく使えないと思いますが。
しっかりと作り方を提示して頂いたyambejpさんをベストアンサーとします。ありがとうございました。
私ももっと質問の仕方や条件の提示を上手くならないといけないですね。そこが反省点です。

また、SQLとは別のコーディングのやり方で解決しました。質問と意図が違うので解決方法を載せる意味が無いと思うのでこれで終了とさせて頂きます。

[yambejp]

procedureがダメなら最初にかいたとおりカレンダーテーブルを
用意して利用するのが妥当
追記してありますので参考にしてください

[planet]

わざわざありがとうございました！