日付データの月毎の集計

Question

前提・実現したいこと

例えば下記のような日付データがあったとし、

1,2019-12-26
2,2019-12-09
3,2019-08-04
4,2018-08-03
5,2018-06-01

下記のような月毎の件数表を出力したいと思っています。

2019-06,1
2019-07,0
2019-08,2
2019-09,0
2019-10,0
2019-11,0
2019-12,2

データが無い箇所は0で補完する必要があるため、最初に下記のように仮テーブルを作る必要があるかと思います。
下記だと開始が2019-05-01から7か月分のデータと固定になってしまっていますが、
これをデータの最小・最大で求める方法はありますでしょうか？

SELECT
date_format(date_add('2019-05-01', interval td.generate_series month), '%Y-%m') as d
FROM
(
SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL
SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7
) as td

Answer 1

最大-最小の間隔で必要な行は計算できると思います。
【MySQL】日付時刻関数を使用して、日付や時刻の差分を取得する
これをlimitとして与えれば良さそうですけど、limitに対しては定数でないと駄目みたいなので、
プロシージャやサブクエリでLIMIT句を使う場合の注意点

row_number()で行番号を作成して、where条件で限定すれば良さそうです。
但し、row_number()はMySql8.0以降でないと使用できません。
MySQLのSELECT結果に行番号を振る。

Comments

[mame708]

ご回答ありがとうございます。
実際にやりたいことはもう少し複雑で、定数定義も必要であれば
プログラムを組んでSQL呼び出しした方がよいかなと思ってきました。
載せていただいたサイトを参考にいたします。

Answer 2

MySQLのdatetime型のカラムから日付部分のみを取得する から年月のみとレコード件数を取得するところが参考になるかと。

CREATE TABLE は質問に提示できた方が適切なコメントが付き易いかと。

Comments

[mame708]

ご回答ありがとうございます。
実際に試せるものがあった方がコメント頂きやすいですよね。
次回から載せるようにいたします。