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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

3回答

2625閲覧

重複データを統合するスマートな方法

退会済みユーザー

退会済みユーザー

総合スコア0

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

0グッド

0クリップ

投稿2016/07/01 23:17

編集2016/07/02 10:00

データの一部だけ違う、重複データを統合するための効率のよい方法を探しています。

データが以下であった時

idnamecategorydata
------------------------------------
1aaahoged01
2aaafugad02
3bbbhoged03
4bbbfugad04
5ccchoged05
6cccfugad06
7dddfugad07
8eeehoged08
9ffffugad09
------------------------------------

name で統合し、category カラムを追加して、そこに data を入れたいと考えています。

idnamehogefuga
---------------------------------
1aaad01d02
3bbbd03d04
5cccd05d06
7dddNULLd07
8eeed08NULL
9fffNULLd09
---------------------------------

idは任意のものであれば良いので、振り直しでも新規追加でもなんでも良いです。

現在、id1 から順に name で検索し、同じものを探してcategory と data を読み、新テーブルに記述しているのですが、行数が数十万行あり、時間がかかるので改善したいと考えています。

select * from XXX where id = $i

で抽出したもの $data1 とし、新テーブル YYY に $data1['name'] がなければ、

select category,data from XXX where name = $data1['name']

で、旧テーブル XXX から $data2 として再抽出。
$data1$data2['category'] $data2['data'] の組を追加して、新テーブル YYY へ insert
これを行数分実施

name に関して重複はありますが、name + category の組み合わせで重複はありません。
サンプル data は id と紐付いて見えますが、実際の data は全く独立しています。

良い方法を教えていただけると幸いです。
よろしくお願いします。

環境
PHP
mysql

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Zuishin

2016/07/02 00:21

とりあえず、今お使いのスクリプトを追記してください。
Zuishin

2016/07/02 00:23

あと、OS や DB などの環境もお書きになったら回答が付きやすいかと思います。
guest

回答3

0

create table hoge(id int,name varchar(20),category varchar(20),data varchar(20)); insert into hoge values(1,'aaa','hoge','d01'),(2,'aaa','fuga','d02'),(3,'bbb','hoge','d03'),(4,'bbb','fuga','d04'),(5,'ccc','hoge','d05'),(6,'ccc','fuga','d06'),(7,'ddd','fuga','d07'),(8,'eee','hoge','d08'),(9,'fff','fuga','d09');

として、抽出

select t1.id,t1.name ,max(if(t2.category='hoge',t2.data,null)) as hoge ,max(if(t2.category='fuga',t2.data,null)) as fuga from (select min(id) as id,name from hoge group by name) as t1 left join hoge as t2 on t1.name =t2.name group by name;

投稿2016/07/03 05:30

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/07/03 06:15

まだ理解しきれていないのですが、一発で回せるのですね。 面白い。 ありがとうございます。
guest

0

ベストアンサー

以下のSQL文なら、いかがでしょうか?

sql

1INSERT INTO YYY (id, name, hoge, fuga) 2SELECT hoge.id, hoge.name, hoge.data, fuga.data 3FROM XXX AS hoge INNER JOIN XXX AS fuga ON hoge.name = fuga.name 4WHERE hoge.category = 'hoge' AND fuga.category = 'fuga';

http://sqlfiddle.com/#!9/99f73/1


hoge,fugaは空の場合もある

のであれば、ワンクエリでの実現は難しそうですね。

統合先のテーブルのnameカラムにユニークインデックスが張ってある、または張ることが可能であれば、以下のSQL文で実現可能です。

sql

1START TRANSACTION; 2 3INSERT INTO YYY (id, name, hoge, fuga) 4SELECT id, name, data, NULL FROM XXX WHERE category = 'hoge'; 5 6INSERT INTO YYY (id, name, hoge, fuga) 7SELECT id, name, NULL, data FROM XXX WHERE category = 'fuga' 8ON DUPLICATE KEY UPDATE fuga = data; 9 10COMMIT;

http://sqlfiddle.com/#!9/371ece/1

ちなみに、

進捗が分かるように

する最も手っ取り早い方法は、1つ目のINSERT文と2つ目のINSERT文の間に適当なメッセージを出力してやることでしょうか。

さらに細かく進捗を表示したい場合は、SELECT構文にWHERE句を追加し、idを適当な範囲で区切ってINSERTを繰り返す方法が考えられます。
ただし、それだけ実装が複雑になりますので、どこまでやるかはコスト(とリスク)とメリットを比較して判断してください。

なお、この方法でデータを統合する場合、XXXテーブルのcategoryカラムにインデックスを張ると、速度の改善が期待できます。
SELECT-INSERT文のSELECT時に、インデックスが利用できるからです。

投稿2016/07/02 09:17

編集2016/07/02 15:08
KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/07/02 10:06

回答ありがとうございます。 ちょうど、自己結合での対応を実験していました。 処理としては綺麗になるのですが、大体5分ぐらいかかるので、進捗が分かるように他のやり方を試すべきか悩ましいところです。 サンプルが誤解を与えるものだったので、修正しました。 hoge,fugaは空の場合もあるので、もしそれでより効率的な書き方があるようであれば 教えて下さい。
KiyoshiMotoki

2016/07/02 15:09

回答欄に追記しました。 ご確認ください。
退会済みユーザー

退会済みユーザー

2016/07/03 05:56

ありがとうございました。 ちょっと試すのに時間がかかってしまいました。 実環境では、試せなかったのですが、テスト環境でユニークインデックスをはり試してみました。 ON DUPLICATE KEY UPDATE は使ったことがなかったので、新鮮でした。 今までPHP側でやっていた内容が、こちらで出来そうなので、今回の件以外でも便利そうです。
guest

0

変換にはどのようなスクリプトをお使いですか?
追記お願いします。

ちなみに PowerShell だとこれで

PowerShell

1$data = @{}; sql $udl "select * from t" |% { if (-not $data.ContainsKey($_.name)) { $data[$_.name] = @{} }; $data[$_.name][$_.category] = $_.data } 2$data.Keys |% {$d = $data[$_]; $fields = [string]::Join(",", ($d.Keys |% {$_})); $values = [string]::Join(",",($d.Keys |% {"'" + $d[$_] + "'"})); "insert into newTable (name,{0}) values({2},{1});" -f $fields, $values, $_}

このような結果が得られます。

insert into newTable (name,hoge,fuga) values('bbb','d03','d04'); insert into newTable (name,hoge,fuga) values('ccc','d05','d06'); insert into newTable (name,hoge,fuga) values('aaa','d01','d02');

もっと高速化できるとは思いますが、数十万程度ならこれで十分かと。
あと、DB にもよるのでしょうが、insert 文を大量に投げるより、CSV からインポートした方がいい場合もありますよ。
一度 CSV にエクスポートしてから、Perl なり Python なり好きなスクリプトを使って加工し、インポートするのが一番簡単で、すでにそのような方法を取られている場合、数十万件程度でそこまでの時間(ここで質問して答えが返ってくるまでの時間)がかかるとは思えないんですが。

投稿2016/07/02 00:17

編集2016/07/02 01:08
Zuishin

総合スコア28656

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2016/07/02 01:59

タグが適切ではなかったので、修正しました。 現在、ダンプされたデータが定期的届くので、それを加工し、表示用データとしています。 そのため、データが届くたびに時間をかけるのは効率よくないなぁと。 時間がかかると言っても、ストレスに感じるぐらいで、実際に数時間かかるとかではありません。 ダンプの代わりにCSVでデータを入手するほうが、時間短縮になるかもしれないのですね。 ちょっとそちらのやり方も試してみます。
Zuishin

2016/07/02 03:00

select を数十万件× 3 回繰り返せばそれなりに時間はかかりますので、なるべくこの回数を減らすのがよろしいかと。とりあえず最初の where id=$i はまったく無駄だと思います。 変更を最小限にするならまずループの外で select distinct name from XXX として name の重複のない一覧を取得し、次にそれをキーに name の数だけループすれば、それだけでも多少違ってくると思います。 ループの中の select が 3 回から 1 回になり、ループする数自体も name の重複の分だけ減りますので、呼び出される select の数は数分の 1 になります。 もちろん、CSV を入手して加工できるなら、その方が速いと思います。
退会済みユーザー

退会済みユーザー

2016/07/02 10:10

select distinct name from XXX これは盲点でした。 これに突っ込んでいくのであれば、進捗も追えそうです。 確かに有効ですね。 ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問