データベースをクロス集計して、実行結果の配列を結合したい

Question

前提・実現したいこと

登録したデータベースは、このような感じになっています。

1. 製品名｜数量｜工順｜工程コード
2. 製品（1）｜2個｜2｜制作01
3. 製品（1）｜2個｜3｜制作03
4. 製品（1）｜2個｜4｜制作06
5. 製品（1）｜2個｜6｜制作09
6. 製品（2）｜5個｜1｜制作01
7. 製品（2）｜5個｜2｜制作04
8. 製品（3）｜1個｜5｜制作05
9. 製品（3）｜1個｜8｜制作10

これを、下記の通りに「配列操作」してMySQLに登録したいと思っています。
0. 製品名｜数量｜工程コード[0]｜[1]｜[2]｜[3]｜・・・
0. 製品（1）｜2個｜制作01｜制作03｜制作06｜制作09
0. 製品（2）｜5個｜制作01｜制作04
0. 製品（3）｜1個｜制作05｜制作10

＜方法（1）＞
上記サンプルのようにすることができれば一番理想の形なのですが、私の頭脳では組み立てできませんでした。
なので、順番通り登録して再度出力際にGROUP_CONCAT(process_code)構文でカンマ区切りの結果を出して、テキストエディタを使って「囲み文字（”）」を【すべて置換」で一括削除して使おうと思っています。

＜方法（2）＞
登録時に主キーを設定し、「製品」と「工順」のテーブルに分けて登録し、出力時にテーブル0001とテーブル0002を結合して出力したいと思っています。

（テーブル0001）
製品ID（主キー）｜製品名｜数量
0. ｜製品（1）｜2個
0. ｜製品（2）｜5個
0. ｜製品（3）｜1個

（テーブル0002）
テーブルID｜製品ID（結合用キー）｜工程コード[0]｜[1]｜[2]｜[3]｜・・・
0. ｜1｜制作01｜制作03｜制作06｜制作09
0. ｜2｜制作01｜制作04
0. ｜3｜制作05｜制作10

試したこと

1. Windows10ProにXAMPP7.3.6をインストール・起動
2. XAMPPにある「phpMyAdmin」からデータベースとテーブルを追加
3. 「phpMyAdmin」の”インポート機能”を使ってデータの登録　※3万2千件程度
4. 下記のようなSQL構文を作成し、最終的な結果目標となる構文を設計。

SELECT saler_name,customer_code,customer_name,product_num,direction_num,branch_num,parts_num,parts_name,product_quantity,desired_date,answer_date,work_date,unit_price,amount_money,GROUP_CONCAT(process_code)
FROM innodb_utf8db
GROUP BY product_num,direction_num
ORDER BY customer_code

補足情報

登録時はきっとPHPを使った方法を考えていますが、すでにCSVのコピー的な登録を行った後、SQL構文を使って配列操作する実行コマンドで分割・置換・結合できるようであれが、PHP動作で解決していきたいと思っています。

また、当方利用環境ではXAMPPを使用しており、「PHP」と「MySQL（mariadb）」での運用を考えていますが、他の環境であってもご教授いただけるのであれば「MySQL」さえ使っていただけるのならば、言語は問いません。教えていただけますでしょうか。

Comments

[m.ts10806]

質問なのに「提案」とはどういう意味合いで使われている表現なのでしょうか。

[退会済みユーザー]

すみません、ボキャブラリーが少なく、思いつく言葉で書いてしまいました。
よく考えたら、＜提案＞より＜方法＞の方が適切だったかもしれません。

[m.ts10806]

なるほど「自身が思いついた方法」ですかね。
質問は編集できますので表現調整してもらえたらと。

[退会済みユーザー]

ご指摘ありがとうございます。ご指摘箇所の修正と、タイトルを自分なりにわかりやすくしてみました。

[sazi]

MySQLには配列型などありませんが、テーブル0002の定義はどのように考えていますか？

[退会済みユーザー]

SAZI様、回答ありがとうございます。
おっしゃる通りであれば、そもそもできないってことになってしまいます。。。

なんとかして方法を見つけたいと思います

[sazi]

Json型にするとか、文字型で内容を,で区切るという持ち方もありますけどね。

Answer 1

たくさんの回答を頂き、ありがとうございました。
もう少し頑張ってみて、PHPでやってみようかと思います。

貴重な時間を私のために割いていただきましてありがとうございました。

Answer 2

要件はクロス集計ですね。
テーブル0002の工程コードの取る値の範囲が、固定なら問題ないですが可変と言われるなら、テーブルも動的に変化させることになり、一時テーブル若しくは動的SQLでの問い合わせになるでしょうね。

SQLは問い合わせの列を動的にすることは得意ではありませんので、データを畳んだ形で取り出してPHP側で配列なりに割り当てるようにした方が良いと思います。

MySQLならテーブルは正規化の状態でGROUP_CONCAT()を使用する。
postgresなどは配列での定義が可能ですが、どのみちPHP側で変換が必要だと思いますので、そこは大差ないのかと思います。

Answer 3

SQLをシンプルにパフォーマンス良く実行できるように、[データベースのテーブルはデータベースの正規化 に沿って設計します。通常、第3正規化まで行います。第1正規化で繰り返し(今回は配列)を排除します。

配列はテーブル上に持たずに、SELECT時にGROUP_CONCATを使っては？

Comments

[退会済みユーザー]

貴殿のおっしゃる方法でいいますと、試したことにありますように「GROUP_CONCAT(process_code)」で配列操作はできました。

ただ、GROUP_CONCAT(process_code)でカンマで仕切ったとしても、その後その仕切を配列に割り当て、既存の配列に結合できた状態を出力したいと思っています。

そのためには、現状では出力結果をテキストエディタなどを併用して、囲い文字を消去することで対応していますが、私のようにパソコンに手慣れた人だけがこの作業を行うわけではないので、できる限り、出力結果を加工せずに使用できる方法を模索しています。

せっかくご回答いただきましたが、ご期待に添えず申し訳ございませんでした。

[Orlofsky]

SELECT REPLACE(GROUP_CONCAT(...), '囲い文字', '') ...
などで対応できるのでは？

[退会済みユーザー]

早々にお返事ありがとうございます。
SQL初心者なのでググってみましたら、どうやら主旨がうまく伝わっていないようでございます。

REPLACEでは、文字列を結合することはできますが、ここでは”配列を結合する”ことなんです。
（Excelでいうところの”ピボッドテーブル？”のような実行結果になります。）

せめて、データベース自体はそのままであっても、「ビューの実行結果？」がGROUP_CONCATの結果をGROUP_CONCAT[’$n個’]みたいに、配列に割り当てた結果として出力できれば、なんとか対応できそうです。

きっとSQL正規表現としては扱っていけないことなのかもしれません。
しかしながら、どうしてもこの処理ができるようになりたいんです。
よろしくお願いいたします。

[Orlofsky]

質問にSELECT結果を追加しては？

[退会済みユーザー]

お昼ちょっと前にMySQLには「ビュー」という機能があることを学習して、下記のように構文までは考えています。

CREATE VIEW view_product_plan AS
SELECT saler_name,customer_code,customer_name,product_num,direction_num,branch_num,parts_num,parts_name,product_quantity,desired_date,answer_date,work_date,unit_price,amount_money,GROUP_CONCAT(process_code)
FROM innodb_utf8db
GROUP BY product_num,direction_num
ORDER BY customer_code

この中のSELECT文最後にある「GROUP_CONCAT(process_code)」をphpmyadminの実行結果として見たところ、実行結果としては”カンマ区切り”となっています。
これをprocess_code[0],process_code[1],process_code[2]・・・と続いていくようにしたいんです。

回答からすると
amount_money,GROUP_CONCAT(process_code)を
amount_money,SELECT(GROUP_CONCAT(process_code)) FROM innodb_utf8db)
になりますでしょうか。

[退会済みユーザー]

残念ですが、amount_money,SELECT(GROUP_CONCAT(process_code)) FROM innodb_utf8db)にしてみましたがうまくいきませんでした。

[yukikp]

既存の配列が、
$product_arr = array(
'製品（1）' => array( 'process01', 'process02', 'process03' ),
'製品（2）' => array( 'process02', 'process04', 'process06' ),
'製品（3）' => array( 'process01', 'process03', 'process06' ),
);

のようなフォーマットであると仮定して、

" 製品名 AS product_name, GROUP_CONCAT(process_code) AS group_process_code "で取得し、PHP側で、

foreach( $results as $r ){
if( !empty( $product_arr[$r['product_name']] ){
array_merge( $product_arrr[$r['product_name']] , explode( ',', $r['group_process_code'] ) );
//重複するのが嫌な場合は、さらにarray_unique(), array_values(), sort()などで処理。
}else{
$product_arr += array( $r['product_name'] => explode( ',', $r['group_process_code'] ) );
}
}

のように処理をするのはどうでしょう？

[退会済みユーザー]

yukikp様、お返事ありがとうございます。

ご回答いただいた方法では、processのarray数が可変（回答例に例えるとarrayが3列固定ではなく、1個のときもあれば5個のときもある）でも対応できますでしょうか。

しかしながら、PHPもあまり良くわからないので、できればデータベース自体の構造をいじるか、ビューを併用して出力結果をいじるか、この2つで解決したいのが本音です。

これからPHPを準備してみますので、回答は遅くなるかもしれませんがご承知おきいただきたく存じます。

[yukikp]

>ご回答いただいた方法では、processのarray数が可変（回答例に例えるとarrayが3列固定ではなく、1個のときもあれば5個のときもある）でも対応できますでしょうか。

それぞれ0個でも100個でも大丈夫です。

それと、ちょっと気になったのが

＞REPLACEでは、文字列を結合することはできますが、ここでは”配列を結合する”ことなんです。

（修正）SQLで出力されるそれぞれの要素・セルの中には、あくまで1次元の文字列しか保持できないのではないでしょうか？

[yukikp]

なので、GROUP_CONCATで「配列にしたい部分を文字列に変換」して保持し、PHPなどのプログラムでその文字列を処理して配列に直すという手間をかけているのだと思っています。

[Orlofsky]

VIEWにしてもSELECTの内容は変わりません。インラインビューで、
MySQL window関数を利用したグループ連番 https://hit.hateblo.jp/entry/MYSQL/8.0/WINDOW
で連番を取得し
SELECT ... GROUP_CONCAT(CONCAT(process_code, '[', 連番, ']')) ...
でできるかも？
質問に矛盾しない内容でテーブル定義をCREATE TABLE、何件かデータをINSERTで、SELECT結果を載せては？

[Orlofsky]

この続きに載せても https://teratail.com/help#about-markdown の [コードを入力] が使えないからダメです。

[退会済みユーザー]

Orlofskyさん、ありがとうございます。
CSVの内容にID（行番号）を追加して連番部分にIDを割り当て、前記の回答内容でビューを作成したら、実行結果としては

MA[42],L1[43],BT[44],L2[45],MDT[46],ZI1[47],HCQ[48],LG[49],LTH[50],GM[51],M2[52],GE[53],GI[54],ZI2[55],ZZ[56]　✕レコード数

になりました。
この部分をデータの”文字列”とグルーピングできれば良いわけでなくて、

SELECT　”通常の配列”+
array('
['0']　->　’process_code[process_orderの最初のレコード]’;
['1']　->　’process_code[process_orderの2回目のレコード]’;
['2']　->　’process_code[process_orderの3回目のレコード]’;
['3']　->　’process_code[process_orderの4回目のレコード]’;
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　つづく
')
みたいにできないかと。。。
（このときのカラム型は適当に連番をふるなどしてあれば良くて、定形ワードでなければならないわけではありません。）

これは、sazi様が最初に回答していただきましたとおりであれば「配列型」なのかと思いまして、であればMySQL単体ではできないのかもしれないですね。

mariaDBには、それとなくあるような記事もあったので、諦めずに頑張ってみます。

P.S.MySQLにこだわる理由は、
（1）PHPなどのプログラムを作れないから
（2）アドオン「MySQL for Excel」を併用し、プログラムを使用せず、エクセルからインポートしてビューの結果でワークシートを作りたいから
（3）そもそもAcessなどを使いたいのですが、6万件以上を取り扱うため、インポートだけでも時間がかかるから
があげられます。

ご尽力頂いておりますみなさまには感謝しかありません。
本当にありがとうございます。

[Orlofsky]

何度も書いていますが、質問を直してください。

[退会済みユーザー]

本日初めてこのサービスを使用したのですが、アドブロックが聞いていたのをすっかり忘れていて、更新したつもりでいました。
先程修正・更新しました。

[sazi]

> MySQLにこだわる理由は、
>（1）PHPなどのプログラムを作れないから
そんな事ないです。
> （3）そもそもAcessなどを使いたいのですが、6万件以上を取り扱うため、インポートだけでも時間がかかるから

リンクテーブルにすればフロントとしてAccess使えますから、(2)じゃなくても良いのでは。
Access使うなら、配列化されたテーブルではなく正規化した状態でないとクロス集計使えませんけどね。

[退会済みユーザー]

現段階で申し上げれるのは、私個人の私物としてはAccess2007を持っています。会社としてAccessが未導入なので、他の方法を検討してみます。
ありがとうございました。