期間を指定したデータ取得のSQL文について

Question

発生している問題・エラーメッセージ

Rails+Mysqlでアプリを作成しています。

下記のようにmanage_idを生成する処理をsql文で実装したいのですが、どのようなsql文を書いたら良いか分からず、インターネット上のサンプルでは適当な情報が見当たらず、ご質問させていただきました。

自分で考えた限り、処理の流れとしては、期間指定で1ヶ月ごとにデータを取得し、そのデータの数をカウントし、シーケンス番号を振るような流れになると思っております。

命名規則
<category_id> + <created_atのyyyy/mm> + <各月ごとのシーケンス番号>

モデル名：GyomuDatum
|ID|manage_id|data|category_id|created_at
|:--|:--:|
|1|A201804_001|hoge|A|2018-04-01|
|1|A201804_002|hoge|A|2018-04-01|
|2|B201805_001|huga|B|2018-05-01|
|2|B201805_002|huga|B|2018-05-01|

補足情報（FW/ツールのバージョンなど）

・Rails
Rails 4.2.10

・Mysql
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

Answer 1

以下が参考になるかと思います。
【MySQL】グループ毎に連番を振る

if(@groupid <> group_id, @no:=1, @no:=@no+1) as no

上記部分が連番を採版している箇所なので、<category_id>と <created_atのyyyy/mm>の条件に変更すれば良いかと。

※@変数を使用しない方法もありますが変数使用したほうが高速です。

但し、データ削除がある場合は取得済みの連番の次としなければ重複が発生します。
何れにしても、連番項目をテーブルに持たせそれを条件や更新対象として方がスマートになるかと。

Comments

[koichi8888]

ご回答ありがとうございます。参考URLを確認したのですが、実装方法としては連番用のカラムを追加するということでしょうか。申し訳ありませんが、私の理解が追いついていないのですが、created_atで年月が同じ場合は1を足していくにはどのように実装すれば宜しいのでしょうか。ご回答頂けると大変助かります。

[sazi]

SQLの確認はそんなに大した手間じゃないと思いますので、色々試されて下さい。
if(@category_id <> category_id and @yyyymm <> DATE_FORMAT(created_at,'%Y%m'), @no:=1, @no:=@no+1)
みたいな感じで。
※比較用の変数に値を設定する記述も忘れずに。
@yyyymm := DATE_FORMAT(created_at,'%Y%m')

[sazi]

連番用のカラムについては、経験的に自分だったらそうするかなという感じです。

[koichi8888]

ご回答ありがとうございます。念のための確認なのですが、例として記載して頂いた『@category_id <> category_id』の部分は不要で年月が違う場合に１を足すので、下記のような記述で正しいでしょうか。
if(@yyyymm <> DATE_FORMAT(created_at,'%Y%m'), @no:=1, @no:=@no+1)

[koichi8888]

また、データの抽出方法はおおまかに把握できたのですが、ご存知でしたら教えて頂きたいのですが、railsで既にデータが追加されている場合に新規で管理IDを割り当てる方法、データ登録時に管理IDが生成されるにはコントローラにどのような記述をすれば、宜しいでしょうか。参考となる情報が教えて頂ければ、助かります。

[sazi]

あ、質問をよく見るとそうでですね。category_idの部分は不要です。

[sazi]

railsは詳しくないのです。すみません。

[koichi8888]

早速のご回答ありがとうございました。