質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.10%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 643

score 11

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件として取得したいのですがどのようにすればよいでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+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);


というレコードが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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/28 15:00

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

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

    キャンセル

  • 2019/08/28 15:13

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

    キャンセル

  • 2019/08/28 15:20

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.10%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る