質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.49%

3つのレコードを1つにするSQL

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,629

score 19

現在、社内の部品採番台帳を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のコードがないものもあります

これを以下のように1つのレコードにしたいと考えています。

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のものを1つ1つ先に
テーブルにインサートして、CATEGORY=1のものをKEYCODEで検索してUPDATEして・・・
なければインサートして・・・
という処理も視野に入れていますが、全然スマートじゃない気がして質問しました。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+1

最近の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で結合する方法ですね。
その場合でも、インデックスがあった方が早いと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/21 15:01

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

    キャンセル

  • 2018/02/21 15:14

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

    ------------------------------------
    インデックス        |  なし  |  なし  |  あり  |
    チューニング        |  なし  |  あり  |  あり  |
    ------------------------------------
    相関サブクエリ       |*****s|*****s| 4.50s|
    join          |16.59s|11.80s|10.64s|
    group_concat  | 4.59s| 3.08s| 1.76s|
    ------------------------------------
    *は300秒以上の為、途中で中断。

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

    キャンセル

+1

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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/17 01:40

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

    キャンセル

+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の処理です。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/21 15:04

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る