３つのレコードを１つにするSQL

Question

現在、社内の部品採番台帳をAccessからMySQLに移行する作業をしています。
Accessの約6万件のデータをMySQLのワークテーブルに入れ込みました。

現在の部品台帳のワークテーブルは以下のようになっています。
|CODE|KEYCODE|CATEGORY|NAME|備考|
|:--|:--:|--:|
|AA1000-01|AA1000-01|0|抵抗1|特に別管理を必要としないもの|
|BA1000-01|AA1000-01|1|抵抗1|AA1000-01と同じ部品だけど△△管理するもの|
|CA1000-01|AA1000-01|2|抵抗1|AA1000-01と同じ部品だけど○○管理するもの|
|BA1001-01|AA1001-01|1|抵抗2|このようにAAのコードがないものもあります|
|CA1002-01|AA1002-01|2|抵抗3|このようにAAのコードがないものもあります|

これを以下のように１つのレコードにしたいと考えています。
|KEYCODE|CODE-A|CODE-B|CODE-C|NAME|
|:--|:--:|--:|
|AA1000-01|AA1000-01|BA1000-01|CA1000-01|抵抗1|
|AA1001-01|NULL|BA1001-01|NULL|抵抗2|
|AA1002-01|NULL|NULL|CA1002-01|抵抗3|

これを実現するSQL（できる限り実行速度が速いもの）を教えていただけないでしょうか。
自分で考えたサブクエリーで引き当てしようとすると
6万件のデータでは遅くて一向に処理が返ってきませんでした。

---

実はAccessのデータをCSV(SJIS)に落として
C#で作ったアプリで文字分解してKEYCODE、CATEGORYを作成し
UTF8変換してMYSQLのワークテーブルに入れ込みました。
SQLでスマートにできない場合は、この処理過程でCATEGORY=0のものを１つ１つ先に
テーブルにインサートして、CATEGORY=1のものをKEYCODEで検索してUPDATEして・・・
なければインサートして・・・
という処理も視野に入れていますが、全然スマートじゃない気がして質問しました。

Answer 1

ピボットテーブルのような処理をしたいのですよね？
以下参考程度に

• 元データ

create table tbl(CODE varchar(10),KEYCODE varchar(10),CATEGORY int,NAME varchar(10),備考 text);
insert into tbl values
('AA1000-01','AA1000-01','0','抵抗1','特に別管理を必要としないもの'),
('BA1000-01','AA1000-01','1','抵抗1','AA1000-01と同じ部品だけど△△管理するもの'),
('CA1000-01','AA1000-01','2','抵抗1','AA1000-01と同じ部品だけど○○管理するもの'),
('BA1001-01','AA1001-01','1','抵抗2','このようにAAのコードがないものもあります'),
('CA1002-01','AA1002-01','2','抵抗3','このようにAAのコードがないものもあります');

• 集計

select KEYCODE
,group_concat(CASE CATEGORY WHEN 0 THEN CODE END) AS CODE_A
,group_concat(CASE CATEGORY WHEN 1 THEN CODE END) AS CODE_B
,group_concat(CASE CATEGORY WHEN 2 THEN CODE END) AS CODE_C
,group_concat(DISTINCT NAME) AS NAME
from tbl
group by KEYCODE;

解説

念のため簡単な解説だけつけておきます
今回質問者さんがやろうとしていることはピボットテーブルといって
縦軸のデータを横軸に表示しなおすことです。
具体的なロジックはまずこういうこと

select KEYCODE
,CASE CATEGORY WHEN 0 THEN CODE END AS CODE_A
,CASE CATEGORY WHEN 1 THEN CODE END AS CODE_B
,CASE CATEGORY WHEN 2 THEN CODE END AS CODE_C
,NAME AS NAME
from tbl;

結果こうなります

|KEYCODE|CODE_A|CODE_B|CODE_C|NAME||
|:--|:--|:--|:--|:--|
|AA1000-01|AA1000-01|NULL|NULL|抵抗1|
|AA1000-01|NULL|BA1000-01|NULL|抵抗1|
|AA1000-01|NULL|NULL|CA1000-01|抵抗1|
|AA1001-01|NULL|BA1001-01|NULL|抵抗2|
|AA1002-01|NULL|NULL|CA1002-01|抵抗3|

これをKEYCODEをキーに縦方向にぐしゃっとつぶします
AA1000-01のCODE_Aは「AA1000-01,null,null」ですから
これをgroup_concatするとnullは無視されて（concat系の処理はnullを無視する）
「AA1000-01」だけになるわけです。CODE_B、CODE_Cについても同様

最後にNAMEについてEYCODEにたいしてNAMEは1対1なので
本来はKEYCODEでgroup byすればNAMEは確定できるはずですが
SQLの文法上許されません。
そのままgroup_concatすると「抵抗1,抵抗1,抵抗1」となってしまいます
これを最初の定義1対1で決まる前提があるのでDISTINCTをすると
複数の抵抗1がひとつにまとまるわけです

本当は、KECODEとNAMEを別テーブルに正規化しておいて集計結果に
JOINするのが本来のSQLの処理です。

ピボットテーブルはデータ管理上の問題なのでデータ出力後
クライアント側で整形処理をする方が効率はよいかもしれません

Comments

[退会済みユーザー]

group_concat()は知りませんでした。
実行速度もとても速かったです。

Answer 2

最近のMySQLでは相関サブクエリーでも早くなっていると聞いています。
因みに、ワークテーブルにインデックスは作っていますか？
(KEYCODE,CODE)のインデックスがあれば、相関サブクエリーでもそこそこイケるんじゃないでしょうか？※NAMEもあったほうがより早くなるかも。

因みに相関サブクエリーの場合ってこんな感じかと思いますけど。

select keycode
     ,(select code from tbl where keycode=t.keycode and code like 'A%') as code_a
     ,(select code from tbl where keycode=t.keycode and code like 'B%') as code_b
     ,(select code from tbl where keycode=t.keycode and code like 'C%') as code_c
     , name
from tbl t
group by keycode, name

インデックスがある状態で遅いなら、A.Ichiさんの提示されるjoinで結合する方法ですね。
その場合でも、インデックスがあった方が早いと思います。

Comments

[退会済みユーザー]

最初にサンプルにしたコードが相関クエリを使ったものでした。
saziさんの仰る通り、インデックスを作ってなかったことが遅かった原因でした。
さらに言うと、サーバマシンにXAMPPをいれて、チューニングしてなかった事も原因でした。

[退会済みユーザー]

A.Ichiさん、saziさん、yambejpさんに教えてもらった方法を
チューニングの有無、インデックスの有無で実行させてみました。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
インデックス　　　　　　　　｜　　なし　　｜　　なし　　｜　　あり　　｜
チューニング　　　　　　　　｜　　なし　　｜　　あり　　｜　　あり　　｜
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
相関サブクエリ　　　　　　　｜＊＊＊＊＊ｓ｜＊＊＊＊＊ｓ｜　４．５０ｓ｜
ｊｏｉｎ　　　　　　　　　　｜１６．５９ｓ｜１１．８０ｓ｜１０．６４ｓ｜
ｇｒｏｕｐ＿ｃｏｎｃａｔ　　｜　４．５９ｓ｜　３．０８ｓ｜　１．７６ｓ｜
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
＊は300秒以上の為、途中で中断。

結果としてはgroup_concat()が一番早いことがわかりましたが、
今回のそもそもの原因はインデックス貼ってない為だったので
ベストアンサーはsaziさんの回答にさせていただきました。

Answer 3

6万件のデータでは遅くて一向に処理が返ってきませんでした。

同様なスピードかもしれませんが

select t1.`KEYCODE`, t2.`CODE` 'CODE-A',t3.`CODE` 'CODE-B',t4.`CODE` 'CODE-C', t1.`NAME` from
(select distinct `KEYCODE`,`NAME` from `hoge_table`) t1
left join (select `KEYCODE`,`CODE` from `hoge_table` where `CODE` Like 'AA%') t2
on t1.`KEYCODE`=t2.`KEYCODE`
left join (select `KEYCODE`,`CODE` from `hoge_table` where `CODE` Like 'BA%') t3
on t1.`KEYCODE`=t3.`KEYCODE`
left join (select `KEYCODE`,`CODE` from `hoge_table` where `CODE` Like 'CA%') t4
on t1.`KEYCODE`=t4.`KEYCODE`
order by 1;

Comments

[退会済みユーザー]

ごめんなさい。目的のサンプルが間違ってました。修正します。