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

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

ただいまの
回答率

90.47%

  • MySQL

    7133questions

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

  • GROUP BY

    16questions

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

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

受付中

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,783

Mikan

score 3

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

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

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

INSERT table_2(col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10)
SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
FROM table_1
GROUP 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する 重複行が見つかる。

SELECT col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
FROM table_2
GROUP 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を実行すると重複エラーが起きる。

UPDATE table_1 t1
SET id_of_table_2 = (
  SELECT id FROM table_2 t2
  WHERE t1.col_1 = t2.col_1
    AND t1.col_2 =t2.col_2
            :
            :
   AND t1.col_10 =t2.col_10

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

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

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

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

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

SELECT *
FROM table_2
WHERE id IN(1,2)
GROUP BY col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10
HAVING 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

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
    AND a.col_1 = b.col_1
                  :
                  :
    AND a.col_10 = b.col_10

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • KiyoshiMotoki

    2015/12/09 22:33

    手元の環境で簡単に検証してみましたが、再現しません。

    もっと詳しい状況が知りたいので、差し支えない範囲で以下の情報を追記いただけますか?
    1. table_1、table_2のテーブル定義(CREATE TABLE文)
    2. 重複が発生しているデータの、実際の値

    あと、
    > 2 table_2をgroup byする。重複行が見つかる。
    で提示されているSQL文では重複の有無は分からないはずですが、
    「重複が発生している」と判断した理由を教えていただけますか?

    キャンセル

  • Mikan

    2015/12/10 12:23

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

    キャンセル

回答 3

0

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

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

select co1_1,count(*) as n from table_2 group by co1_1
select co1_2,count(*) as n from table_2 group by co1_2
select co1_3,count(*) as n from table_2 group by co1_3
select co1_4,count(*) as n from table_2 group by co1_4
select co1_5,count(*) as n from table_2 group by co1_5
select co1_6,count(*) as n from table_2 group by co1_6
select co1_7,count(*) as n from table_2 group by co1_7
select co1_8,count(*) as n from table_2 group by co1_8
select co1_9,count(*) as n from table_2 group by co1_9

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

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/12/10 12:23

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

    キャンセル

  • 2015/12/10 13:05 編集

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

    キャンセル

  • 2015/12/10 13:31

    その通りです。絞り込み条件(見た目が重複っぽいもの)を加えると見つかるとい現象が起きています。

    最初書き間違いを疑ったので、2.4の行を実行→2,4のsqlをコピーwhere句を消し、それを実行というやり方をしてみたのですが、結果が変わらず。

    絞り込み条件を除いてまったく同じはずなので、見えない文字やutf8_unicode_ciによる影響もないような気がするんですが・・・

    キャンセル

  • 2015/12/10 13:53 編集

    すみません。コメント内容に誤りがあり一部修正しました。

    なるほど何故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は実際には一致していますか?

    キャンセル

  • 2015/12/10 14:22 編集

    table_1, table_2ともにidのみがPRIMARY KEYとなってます。(記載漏れ度々すいません。)

    念のため調べてみたのですが、ID1, ID2に該当するものはそれぞれ一つずつしか現れませんでした。

    以下、投稿内容を修正しました。
    一致しているかの判断をどうすればいいかわからなかったので、とりあえず投稿に加筆したSQLを流してみて一致しているという判断を下しました。

    何かベストな確認方法があればお教えください。

    キャンセル

  • 2015/12/10 19:05

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

    キャンセル

  • 2015/12/13 15:15

    丁寧な回答をしていただきありがとうございます。

    ご指摘いただいたようにbinary値の比較をしてみると、col_6で違いが出てきました。
    ありがとうございます。

    where句の有無に関する部分については、もうちょっと調べてみて何かわかったら追記しておきます。

    キャンセル

  • 2015/12/13 17:02 編集

    >>binary値の比較をしてみると、col_6で違いが出てきました。
    興味深いですね。特定(?)のバイナリ値で差異をもつデータは特定の条件下で同じと判定されているということですね。内部的に型変換が動いているのかも知れませんね。(もしかするとDBに一定の条件で起動する関数を登録してたりはしませんか?それが勝手に型変換やTAB/空白排除をしてる可能性もあるかも知れません。)

    >>where句の有無に関する部分については
    あ、前のコメントに書いたのはwhere句ではなくて、幾つかの結果を見たらサブクエリを経由した場合にbinary値の異なる値を重複と判定しているように見えましたので、inをwhereに置換してサブクエリを排除したらどうなるのかなと思ったものです。

    キャンセル

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/10 12:27

    なるほど、そんなマナーがあるとは知りませんでした。ご指摘ありがとうございます。

    ただ、ご指摘いただいたSQLを流してみてもやはり結果は変わりませんでした。

    なお、DISTINCT と GROUP BY を行った場合で、内部の処理ロジック/パフォーマンスが変わったりするのでしょうか。

    よろしくお願いいたします。

    キャンセル

  • 2015/12/11 00: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

    キャンセル

  • 2015/12/13 15:29 編集

    KiyoshiMotoki様

    >テーブルや取得するカラム、抽出条件を様々に変えながら"SELECT DISTINCT"と"GROUP BY"それぞれの実行計画(*)を取得してみると分かりますが、両者で実行計画が同じ場合も、異なる場合もあるからです。
    なるほど、内部では各々の計算手法で最適と思われる実行計画を選んでいるので一緒になる場合もあれば、違う場合もあるという感じなのですね。

    >そのため、画一的に「こちらを使うべき」と考える必要はなく、ケースバイケースで最適な方法を選択するのが良いです。

    やはり銀の弾丸みたいなものはどこにもないんですね〜。教えていただいた実行計画の取り方やパフォーマンスチューニングなどの方法を調べながら、そのあたりの知見を増やしていこうと思います。丁寧な解説ありがとうございました。

    キャンセル

  • 2015/12/14 07:11

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

    Mikan様はSQLをきちんと学んでください。

    キャンセル

0

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

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


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

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

の前は、

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

実行したINSERT文は

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

INSERT文を実行した後、

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

で、ここから本題です。

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

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

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

で実施されたSQL文の

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

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

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

以下のような要領です。

1回目 : col_1のみ

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
    AND a.col_1 = BINARY b.col_1
    AND a.col_2 = b.col_2
                  :
    AND a.col_10 = b.col_10

2回目 : col_1col_2

SELECT b.*
FROM table_2 a, table_2 b
WHERE a.id = ID1
        AND a.id <> b.id
    AND a.col_1 = BINARY b.col_1
    AND a.col_2 = BINARY b.col_2
                  :
    AND a.col_10 = b.col_10

  3回目 : col_1col_2col_3

(以下略)

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/12/13 14:58

    回答が遅くなってしまいすいません。

    まず、前提条件の部分なのですが、再度冷静に確認/実行しましたが、カラム数やtable_2の状態は質問内容通りとなっていました。

    ここからが本題の方ですが、ご指摘いただいた方法でbinary比較をしていったところ、col_6にbinaryをつけた段階で表示結果が変わりましたので、col_6の値がbinaryレベルで違うことがわかりました。ありがとうございます。

    ところで、今回の結果からgroup byの際の比較ではbinaryレベルで行い、=を用いた比較ではbinaryレベルではない比較(何で比較しているのかはよく分からない)しているように思うのですが、これらの認識であっているでしょうか。

    よろしくお願いします。

    キャンセル

  • 2015/12/13 16:17 編集

    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)

    キャンセル

  • 2015/12/14 07:13

    Mikanさんは最初から
    http://qiita.com/sawarame/items/aa99408ce24930ad7def
    テーブルのCREATE文を提示すると解決が早かったかも?
    できればダブっているデータを含んでいるINSERT文も。

    キャンセル

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

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

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

  • MySQL

    7133questions

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

  • GROUP BY

    16questions

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