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

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

新規登録して質問してみよう
ただいま回答率
85.51%
GROUP BY

GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

MySQL

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

Q&A

3回答

7496閲覧

Mysql 5.6でGroup BY(複数カラム)を実行しても重複が残るのですが、理由がわかりません。

Mikan

総合スコア7

GROUP BY

GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

MySQL

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

1グッド

0クリップ

投稿2015/12/09 13:01

編集2022/01/12 10:55

正規化されていないテーブル(table_1)の共通項目を別テーブル(table_2)にくくりだし、それを参照するようにしようとしています。

そこで、まずtable_1のデータをgroup byし重複を取り除いて、table_2に登録しようとしたのですが、group byしたはずなのにtable_2に重複行が現れます。(実行したのは下記の手順)

1 空のtable_2に対して、group byしたtable_1のデータを登録する。

sql

1INSERT table_2(col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10) 2SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 3FROM table_1 4GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10

2 table_2をgroup byする 重複行が見つかる。

sql

1SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 2FROM table_2 3GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10

なお、条件は

  • グループ化するカラムは、integerまたはdecimalまたはVARCHAR
  • col_1, col_2以外にはNULLが入っている可能性がある。
  • table_1のデータ数は, 22000件程度

このような結果になるのは何故なのでしょうか。

よろしくお願いします。

以下追記

カラムの数及び型に記載の誤りがありました。

  • カラムは全部で10個
  • 型に文字型がある(太字で修正してます。)

なお、詳細なtable等の条件は

  • 各テーブルにはidとcreated_at, updated_atがある。(idはオートインクリメント)
  • 各テーブルはPrimary keyはidのみ
  • 各テーブルにPrimary以外のユニーク制約はない。
  • col_1: int(11)
  • col_2 ~ col_9: VARCHAR(255)
  • col_10: DECIMAL(4,1)
  • 文字コードは全カラムがCAHRSET: utf-8, COLLATE: utf8_unicode_ci
  • 今回のケースでtable_2にinsertされた件数は7000件弱

のようになっています。

また、2の重複の見つけ方について、補足・修正します。

2.1 table_1にtable_2への参照を持たせるために、下記のようなsqlを実行すると重複エラーが起きる。

sql

1UPDATE table_1 t1 2SET id_of_table_2 = ( 3 SELECT id FROM table_2 t2 4 WHERE t1.col_1 = t2.col_1 5 AND t1.col_2 =t2.col_2 6 : 7 : 8 AND t1.col_10 =t2.col_10

2.2 重複がないことをチェックするため、下記のsqlを投げると1件もHITしない。

sql

1SELECT * 2FROM table_2 3GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 4HAVING COUNT(id) > 1

2.3 2.1のupdate文をwhere句で範囲指定し、重複エラーをはいている行を特定し、table_2の該当行を見つける。

すると、idの1と2が同じであるように見受けられる。

2.4 2行あると見受けられるものに絞ってGROUP BYしてみる。1件HITする。

sql

1SELECT * 2FROM table_2 3WHERE id IN(1,2) 4GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 5HAVING COUNT(id) > 1

2.5 何度か2.2と2.4のSQLを交互に実行してみても結果変わらず。(2.4の行をコピペしてwhere句を削って実行しているだけなので、typoはしていないと思う。)

というような手順で重複行が見つかってると判断しています。

以下追記2(質問に対する回答で使用した方法)

調べること

  • 重複してる行(ID1とID2)の値が本当に一致しているか。

調べた際の条件

  • table_2のID1とID2は目で見た感じはcol_1~col_10の値が同じ
  • ID1とID2に関してはすべてのカラム(col_1~col_10)に値がある。

流したSQL

sql

1SELECT b.* 2FROM table_2 a, table_2 b 3WHERE a.id = ID1 4 AND a.id <> b.id 5 AND a.col_1 = b.col_1 6 : 7 : 8 AND a.col_10 = b.col_10

上記のsqlを流すと、ID2のデータが返ってくる。
そのため、ID1とID2のcol_1~col_10の値は同値と判断した。

ikuwow👍を押しています

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

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

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

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

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

KiyoshiMotoki

2015/12/09 13:33

手元の環境で簡単に検証してみましたが、再現しません。 もっと詳しい状況が知りたいので、差し支えない範囲で以下の情報を追記いただけますか? 1. table_1、table_2のテーブル定義(CREATE TABLE文) 2. 重複が発生しているデータの、実際の値 あと、 > 2 table_2をgroup byする。重複行が見つかる。 で提示されているSQL文では重複の有無は分からないはずですが、 「重複が発生している」と判断した理由を教えていただけますか?
Mikan

2015/12/10 03:23

申し訳ございません。記載にミスがあり文字型もデータに含まれていました。 間違った情報でお時間を取らせてしまいすいませんでした。 記載内容を修正し、重複を発見したと考える手順を追記いたしました。
guest

回答3

0

情報の追記、ありがとうございます。

追記いただいた情報をもとに調査してみましたが、私も原因が分かりませんorz
そのため、以下に記載する内容は具体的な回答ではない事を前置きさせていただきます。


まず、念のため以下を確認させていただきますが、お気を悪くなさらないで下さい(^^;

1 空のtable_2に対して、group byしたtable_1のデータを登録する。

の前は、

  • "table_2"テーブルは空でしたね?

実行したINSERT文は

  • ご質問に記載した通りのものですね?実はGROUP BY句に指定するカラムを一つ、忘れたりしていませんね?

INSERT文を実行した後、

  • "table_1", "table_2"双方ともデータの更新・追加は一切行なっていませんね?
  • 実はMikan様以外のメンバーがこっそり触っていたり、
  • 稼働中のサービスがアクセスしていたりしませんね?

で、ここから本題です。

疑わしいレコードを特定できているようなので、次は
「疑わしいレコードのどのカラムに差分が発生しているか」
を特定しましょう。
原因の箇所を絞り込めば、それだけ調査は前進できると思いますので。

カラムを特定する方法ですが、

以下追記2(質問に対する回答で使用した方法)

で実施されたSQL文の

sql

1AND a.col_[n] = b.col_[n]

部分を1行ずつ、以下のように変更しながら繰り返し実施してみて下さい。

sql

1AND a.col_[n] = BINAARY b.col_[n]

以下のような要領です。

1回目 : col_1のみ

sql

1SELECT b.* 2FROM table_2 a, table_2 b 3WHERE a.id = ID1 4 AND a.id <> b.id 5 AND a.col_1 = BINARY b.col_1 6 AND a.col_2 = b.col_2 7 : 8 AND a.col_10 = b.col_10

2回目 : col_1col_2

sql

1SELECT b.* 2FROM table_2 a, table_2 b 3WHERE a.id = ID1 4 AND a.id <> b.id 5 AND a.col_1 = BINARY b.col_1 6 AND a.col_2 = BINARY b.col_2 7 : 8 AND a.col_10 = b.col_10

3回目 : col_1col_2col_3

sql

1(以下略)

もし、バイナリレベルの差分があれば、
原因のカラムを比較する式にBINARY句を追加した時点で
ID2 のデータが返却されなくなるはずです。

投稿2015/12/10 14:16

編集2015/12/10 14:20
KiyoshiMotoki

総合スコア4791

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

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

Mikan

2015/12/13 05:58

回答が遅くなってしまいすいません。 まず、前提条件の部分なのですが、再度冷静に確認/実行しましたが、カラム数やtable_2の状態は質問内容通りとなっていました。 ここからが本題の方ですが、ご指摘いただいた方法でbinary比較をしていったところ、col_6にbinaryをつけた段階で表示結果が変わりましたので、col_6の値がbinaryレベルで違うことがわかりました。ありがとうございます。 ところで、今回の結果からgroup byの際の比較ではbinaryレベルで行い、=を用いた比較ではbinaryレベルではない比較(何で比較しているのかはよく分からない)しているように思うのですが、これらの認識であっているでしょうか。 よろしくお願いします。
KiyoshiMotoki

2015/12/13 07:26 編集

Mikan様、ご報告ありがとうございます。 > col_6の値がbinaryレベルで違うことがわかりました。 では、 ・どの文字がどのように異なっているのか ・table_1とtable_2との間で、値が異なっていないか  (INSERT時にデータが壊れていないか) など、さらに踏み込んだ調査が可能になりますね。 > group byの際の比較ではbinaryレベルで行い、=を用いた比較ではbinaryレベルではない比較(何で比較しているのかはよく分からない)しているように思うのですが、これらの認識であっているでしょうか。 私の認識では、どちらも同じ方法 (対象のカラムに指定されている"COLLATE"属性)で比較しているはずです。 以下のように、GROUP BY句にBINARY句をつけた場合とつけない場合とで結果が変わる事からも、 この認識で間違いないように思います。 mysql> SELECT * FROM table_3; +------+ | name | +------+ | a | | a | # 見た目では分からないが、実は'a 'という文字(末尾に半角スペースがある) | A | | a | +------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM table_3 GROUP BY name; +------+ | name | +------+ | a | +------+ 1 row in set (0.00 sec) mysql> SELECT * FROM table_3 GROUP BY BINARY name; +------+ | name | +------+ | A | | a | | a | +------+ 3 rows in set (0.00 sec)
guest

0

実際にデータが重複するのを再現出来るだけの情報を提示された方が解決が早いですが、

SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9

FROM table_2
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9

SELECT DISTINCT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9
FROM table_2

としたらどうなりますか?
SELECTされた結果を一意にするには DISTINCT を使います。
GROUP BY はグループ関数とセットで使うのがマナーです。SQLはきちんと覚えた方が良いです。

投稿2015/12/09 18:36

Orlofsky

総合スコア16415

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

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

Mikan

2015/12/10 03:27

なるほど、そんなマナーがあるとは知りませんでした。ご指摘ありがとうございます。 ただ、ご指摘いただいたSQLを流してみてもやはり結果は変わりませんでした。 なお、DISTINCT と GROUP BY を行った場合で、内部の処理ロジック/パフォーマンスが変わったりするのでしょうか。 よろしくお願いいたします。
KiyoshiMotoki

2015/12/10 15:07

Mikan様、Orlofsky様、横から失礼します。 > DISTINCT と GROUP BY を行った場合で、内部の処理ロジック/パフォーマンスが変わったりするのでしょうか。 変わる場合も、変わらない場合もあります。 テーブルや取得するカラム、抽出条件を様々に変えながら "SELECT DISTINCT"と"GROUP BY"それぞれの実行計画(*)を取得してみると分かりますが、 両者で実行計画が同じ場合も、異なる場合もあるからです。 SQLは、異なる書き方で同じ結果が得られる文を複数、書く事ができますが、 操作対象のテーブルのレコード数や格納されているデータ、インデックスの有無など、様々な状況によって 劇的にパフォーマンスに差が出る場合があります。 そのため、画一的に 「こちらを使うべき」 と考える必要はなく、ケースバイケースで最適な方法を選択するのが良いです。 (これはSQLに限った話ではありませんが) * 実行計画については以下を参照 https://dev.mysql.com/doc/refman/5.6/ja/explain.html http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
Mikan

2015/12/13 06:29 編集

KiyoshiMotoki様 >テーブルや取得するカラム、抽出条件を様々に変えながら"SELECT DISTINCT"と"GROUP BY"それぞれの実行計画(*)を取得してみると分かりますが、両者で実行計画が同じ場合も、異なる場合もあるからです。 なるほど、内部では各々の計算手法で最適と思われる実行計画を選んでいるので一緒になる場合もあれば、違う場合もあるという感じなのですね。 >そのため、画一的に「こちらを使うべき」と考える必要はなく、ケースバイケースで最適な方法を選択するのが良いです。 やはり銀の弾丸みたいなものはどこにもないんですね〜。教えていただいた実行計画の取り方やパフォーマンスチューニングなどの方法を調べながら、そのあたりの知見を増やしていこうと思います。丁寧な解説ありがとうございました。
Orlofsky

2015/12/13 22:11

KiyoshiMotoki様、フォローありがとうございます。 SQLはきちんと統計情報を取得した上で実行計画を確認するのがいちおうの目安。処理時間を実測してベストなSQLをチョイスって手間はかかります。 Mikan様はSQLをきちんと学んでください。
guest

0

2番目のSQLだけでも重複行が存在するというのは変に思えますね。

因みに重複行があるとはco1_1~col_9の何れを比較しても一致する行が2行以上あるということですよね?ソートするとまったく同じ行があったみたいな。
という事は次のどれかでもcol_xに重複が現れるのでしょうか?

SQL

1select co1_1,count(*) as n from table_2 group by co1_1 2select co1_2,count(*) as n from table_2 group by co1_2 3select co1_3,count(*) as n from table_2 group by co1_3 4select co1_4,count(*) as n from table_2 group by co1_4 5select co1_5,count(*) as n from table_2 group by co1_5 6select co1_6,count(*) as n from table_2 group by co1_6 7select co1_7,count(*) as n from table_2 group by co1_7 8select co1_8,count(*) as n from table_2 group by co1_8 9select co1_9,count(*) as n from table_2 group by co1_9

もし重複行が出たとしたらそのカラムに問題があるのかも知れません。

しかし、データ型が文字なら見た目同じでもデータは異なったというケースが想定できますが、数値ではそれも無いでしょうし。

投稿2015/12/09 17:47

編集2015/12/09 17:49
hirohiro

総合スコア2068

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

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

Mikan

2015/12/10 03:23

申し訳ございません。記載にミスがあり文字型もデータに含まれていました。 間違った情報でお時間を取らせてしまいすいませんでした。
hirohiro

2015/12/10 04:18 編集

むむ?2.2でHITゼロなのに、2.4に抽出行があるのですか? 2.4は2.2に絞込み条件を追加しただけに見えますが
Mikan

2015/12/10 04:31

その通りです。絞り込み条件(見た目が重複っぽいもの)を加えると見つかるとい現象が起きています。 最初書き間違いを疑ったので、2.4の行を実行→2,4のsqlをコピーwhere句を消し、それを実行というやり方をしてみたのですが、結果が変わらず。 絞り込み条件を除いてまったく同じはずなので、見えない文字やutf8_unicode_ciによる影響もないような気がするんですが・・・
hirohiro

2015/12/10 05:07 編集

すみません。コメント内容に誤りがあり一部修正しました。 なるほど何故2.2でHITが無く、2.4にHITがあるのかは私にはわかりません。 table_2のidはユニークなのでしょうか? SELECT * FROM table_2 WHERE id IN(1,2) これで3行以上HITしたりしないでしょうか? 後はtable_2のIDはユニークだとして、ID1とID2のco1_1~col_10は実際には一致していますか?
Mikan

2015/12/10 05:57 編集

table_1, table_2ともにidのみがPRIMARY KEYとなってます。(記載漏れ度々すいません。) 念のため調べてみたのですが、ID1, ID2に該当するものはそれぞれ一つずつしか現れませんでした。 以下、投稿内容を修正しました。 一致しているかの判断をどうすればいいかわからなかったので、とりあえず投稿に加筆したSQLを流してみて一致しているという判断を下しました。 何かベストな確認方法があればお教えください。
hirohiro

2015/12/10 10:05

原因や理由はさっぱりわからないのですが、データが重複と判定されるか異なると判定されるかの違いは、データがサブクエリを経由したかどうかに掛かっているようですね。(MySQLではIN句は実際にはサブクエリが動作しているようですので。) select * from table_2 where id < 3 group by col_1,col_2,....,col_10 HAVING COUNT(id) > 1 を試みても抽出はゼロになるのではないかと予測します。 実際のデータが一致しているか異なっているのかはバイナリ値を比較してみればわかると思います。 ただデータが違った(もしくは一致していた)が判明したとして、なんでSQL上で本件のような現象が起きているのかは私にはわかりません。 中途半端でスミマセン。
Mikan

2015/12/13 06:15

丁寧な回答をしていただきありがとうございます。 ご指摘いただいたようにbinary値の比較をしてみると、col_6で違いが出てきました。 ありがとうございます。 where句の有無に関する部分については、もうちょっと調べてみて何かわかったら追記しておきます。
hirohiro

2015/12/13 08:07 編集

>>binary値の比較をしてみると、col_6で違いが出てきました。 興味深いですね。特定(?)のバイナリ値で差異をもつデータは特定の条件下で同じと判定されているということですね。内部的に型変換が動いているのかも知れませんね。(もしかするとDBに一定の条件で起動する関数を登録してたりはしませんか?それが勝手に型変換やTAB/空白排除をしてる可能性もあるかも知れません。) >>where句の有無に関する部分については あ、前のコメントに書いたのはwhere句ではなくて、幾つかの結果を見たらサブクエリを経由した場合にbinary値の異なる値を重複と判定しているように見えましたので、inをwhereに置換してサブクエリを排除したらどうなるのかなと思ったものです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.51%

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

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

質問する

関連した質問