月々ごとに集計テーブルを表示させたい

Question

実現したいこと

①mysql上のテーブル：t_general（トランザクションデータ）に作成した果物名ごとに（kind_id）、月々ごとに集計テーブルを表示させたい。

前提

php、mysqlで登録・集計システムを作っています。

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

なし。

該当のソースコード

SELECT kind_id, count(kind_id), kinds.kind AS kind_name FROM t_general LEFT JOIN kinds ON t_general.kind_id=kinds.id GROUP BY kind_id" //（betweenはどうしようか検討中です。。。）

試したこと

②一旦、以下のSQLで、果物名ごとのt_generalの集計はできました。
SELECT kind_id, count(kind_id), kinds.kind AS kind_name FROM t_general LEFT JOIN kinds ON t_general.kind_id=kinds.id GROUP BY kind_id"（betweenはどうしようか検討中です。。。）

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

この次は、予めmysqlに年月カラムと果物名ごとの（kind_id）カラム（全28カラム）からなるkind_totalテーブルを作成し、②の集計結果をkind_totalテーブルにinsertしていけばよいかと考えています。

＜kind_totalテーブル＞
あけび　アサイー　アセロラ 　かぼす．．．28カラム
2023/3月 2 　3 　2 　3
2023/4月 1 　4　 1 　1
2023/5月 1 　2　 4　 3
※これ以降の月は、t_generalテーブルから常に取得するので、その月になったら自動集計させて6月1になったら、自動的に2023/6月のレコードができる。
例えば、2023/6月1日時点で、あけびが２個なら、（あけびアサイーアセロラ かぼす．．）は（２、０、０，０．．．）となる

どのようにしたら、mysql上に作成した日報テーブル上に登録することができますでしょうか？

＜t_generalテーブル＞
-- テーブルの構造 t_general--

CREATE TABLE t_general (
id int(11) NOT NULL,
yearsExp int(11) NOT NULL,
level_id int(11) NOT NULL,
dateOccurre1 date NOT NULL,
・・・
kind_id int(11) NOT NULL,

＜＞
-- テーブルのインデックス t_general

ALTER TABLE t_general
ADD PRIMARY KEY (id),
・・・
ADD KEY kind_id (kind_id),

Comments

[yambejp]

どういったデータからどういった結果をほしいのでしょうか？
例示されたデータがちょっと分かりづらいです。
28個の果物は絶対に増減ないという認識でしょうか？

特定期日時点の在庫データをDBに記載していくなら、それは月末最終日の在庫をみればいいですし・・・
フルーツごとに個々の増減データを日付入りでデータをもって一日何度でも変更されるというならそういう処理が必要になりますので、要求定義次第ですね

[m.ts10806]

テーブル定義も一応コードなのでマークダウンのcodeで、可能な限り省略無しでご提示ください。
※使わないカラムとかあるのでしたら「…」のようにせず、なしの状態で詰めて提示を。ただ、提示されない情報は考慮できないので、不具合や副作用の懸念から、やはり省略しないのがベター

[logres_Fan]

> ②の集計結果をkind_totalテーブルにinsertしていけばよいかと考えています。
クロス集計表示に非対応でしたっけ？それはさておき、見ずらいです。年月、商品、数値、改行を繰り返して、縦方向に並べて下さい。

[yuic.sk12345]

@yambejp様

<28個の果物は絶対に増減ないという認識でしょうか？
増減は無しとなります。

@m.ts10806様
<テーブル定義も一応コードなのでマークダウンのcodeで、可能な限り省略無しでご提示ください。
以後省略なきよう付けます。

@logres_Fan様
ind_totalテーブルにinsert方法も検討しました。ただ、どのタイミングで、誰がinsert処理を実行するのか？　仮にバッチ処理をした場合、テスト環境が整っていないので、自動化も少し難しいと感じました。

そこで、みなさまからのいただいたアドバイスを反芻し、t_generalテーブルの集計を、見やすく表示できるかを検討させていただきます。

ありがとうございました。

[m.ts10806]

質問は編集できますので、いつくるかわからない以後に回すのではなく、今対応しましょう。

Answer 1

集計を想定するとこんな感じがよいのでは？

create table fruit (fid int primary key,val varchar(20));
insert into fruit values
(1,'あけび'),
(2,'アサイー'),
(3,'アセロラ '),
(4,'かぼす');

create table move(id int primary key auto_increment,d date,fid int not null,quantity int);
insert into move(d,fid,quantity) values
('2023-05-01',1,10),
('2023-05-01',1,-1),
('2023-05-01',2,2),
('2023-05-02',1,5),
('2023-05-03',1,20),
('2023-05-05',2,3),
('2023-05-31',3,-10),
('2023-06-01',1,100);

集計

select 
date_format(d,'%Y%m') as `年月`,
sum((fid=1)*quantity) as `あけび`,
sum((fid=2)*quantity) as `アサイー`,
sum((fid=3)*quantity) as `アセロラ `,
sum((fid=4)*quantity) as `かぼす`
from move
group by `年月`;

運用上はprocedureを作っておくと自動化できます
集計を効率的に行うには工夫が必要です

Comments

[yuic.sk12345]

yambejpさま
具体的なコードも含め、ありがとうございます！！

＜＜運用上はprocedureを作っておくと自動化できます
当方勉強不足で、procedureとは、stored-procedures（https://www.php.net/manual/ja/mysqli.quickstart.stored-procedures.php）のことでしょうか？
いまだ、procedureは使用したことがありませんが、自動化に必要な処理としてもう少し勉強深めます。

今振り返ると、プログラミングによる「集計」、エクセルなどの表計算ソフトの「集計」とは、まるでちがう複雑な知識が必要であることがわかりました。。。
ありがとうございます！！