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

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

ただいまの
回答率

90.35%

  • Python 3.x

    7329questions

    Python 3はPythonプログラミング言語の最新バージョンであり、2008年12月3日にリリースされました。

  • MySQL

    6139questions

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

  • SQL

    2534questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

任意の数字6~8桁の中に、任意の数字5桁が存在するかの判断

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 687

atLast

score 8

MySQL+Python3.5環境下で開発しています。

現在、Excelでまとまっている数式をPythonで記述し、MySQLに流し込み、PHPで表示という方法で開発を行っています。

そこで質問なのですが、以下のような数列があるとします。

数列A(mst_nin_s6の中身(抜粋))
1 | 2 | 3 | 4 | 5 | 6
1 | 2 | 3 | 4 | 5 | 7
...
6 | 7 | 8 | 9 | 10 | 11
数値は最大11までで、全てのパターンが網羅されているものとします。

上記数列に対して、以下の数列が存在するかを確認したいです。

数列B(チェック元となるテーブルの中身)
5 | 7 | 11 | 9 | 4

excelでの計算式は以下の通りです。

IF(
    AND(
        OR(5=1, 7=1, 11=1, 9=1, 4=1),
        OR(5=2, 7=2, 11=2, 9=2, 4=2),
        OR(5=3, 7=3, 11=3, 9=3, 4=3),
        OR(5=4, 7=4, 11=4, 9=4, 4=4),
        OR(5=5, 7=5, 11=5, 9=5, 4=5)
    )
,0,1)


数列Bを1つずつに分解して、数列Aに含まれるかを確認している計算式です。

上記Excelの計算式を、PythonとPHPにて実装しようとしています。


まず、以下のPythonを実行し、含まれるかどうかの判定をしています。

def get_nin_id(suffix, num1, num2, num3, num4, num5):
    query = "select id from mst_nin_%s where " % (suffix)
    if (suffix == 's8'):
        for v in range(1, 6):
            if (v != 1):
                query += " AND "
            varname = "num" + str(v);
            query += "'%s' in (num1, num2, num3, num4, num5, num6, num7, num8)" % (eval(varname))

    elif (suffix == 's7'):
        for v in range(1, 6):
            if (v != 1):
                query += " AND "
            varname = "num" + str(v)
            query += "'%s' in (num1, num2, num3, num4, num5, num6, num7)" % (eval(varname))
    elif (suffix == 's6'):
        for v in range(1, 6):
            if (v != 1):
                query += " AND "
            varname = "num" + str(v)
            query += "'%s' in (num1, num2, num3, num4, num5, num6)" % (eval(varname))
...

    cur = con.cursor()
    cur.execute(query)
    rows = cur.fetchall()
    if (rows is None):
        return 0
    else:
        ids = ''
        for row in rows:
            if ids == '':
                ids = str(row['id'])
            else:
                ids += ',' + str(row['id'])

        return ids
...

    # nin s6
    # num1からnum5は、チェック元テーブルの中から取得した物(数列B)
    nin6id = get_nin_id('s6', num1, num2, num3, num4, num5)
    insert(...)

SQLにすると以下になります。

select id from mst_nin_s6 where
 '5' in (num1, num2, num3, num4, num5, num6) AND
 '7' in (num1, num2, num3, num4, num5, num6) AND
 '11' in (num1, num2, num3, num4, num5, num6) AND
 '9' in (num1, num2, num3, num4, num5, num6) AND
 '4' in (num1, num2, num3, num4, num5, num6)

マスタテーブル構造は以下の通です。(mst_nin_s6)
中身は、数列Aのものです。
mst_nin_s6

チェック結果を入れるテーブル構造は以下の通りです。(input_nin_s6)
中のデータは、mst_nin_s6のid値(カンマ区切り)です。

input_nin_s6


-------------- ここから、PHPでの処理 --------------

存在するかを確認するクエリは以下の通です。

select
group_concat(distinct(mb.num1) SEPARATOR ',') as num1,
group_concat(distinct(mb.num2) SEPARATOR ',') as num2,
group_concat(distinct(mb.num3) SEPARATOR ',') as num3,
group_concat(distinct(mb.num4) SEPARATOR ',') as num4,
group_concat(distinct(mb.num5) SEPARATOR ',') as num5,
group_concat(distinct(mb.num6) SEPARATOR ',') as num6
from input_nin_s6 as ib join mst_nin_s6 mb on ib.nin_s6_id REGEXP CONCAT('(^|,)', mb.id, '(,|$)')
group by ib.id


このクエリを投げると、以下の結果になります。

クエリ結果

実際の数字が含まれているものだけを抽出したいのですが、関係ない物(10)が
SQLの戻り値に入ってしまっています。
このため、Excelでの判断結果と違う物になっている状態です。

実際にほしいデータは
2,3,4,1 | 4,5 | 5,6,7 | 7,8,9 | <empty> | 11
です。

なぜこのようなことが起こるのでしょうか。

足りない部分があれば、言って下さい。問題の無い範囲で追記します。

よろしくおねがいします!

---- 2017/01/15 16:40編集 ----
チェック元となるテーブルは、諸事情により公開することができません。申し訳ありません。
中のデータ構造は、最初の方で定義している、数列Bとなります。

---- 2017/01/15 16:50追記 ----
全体の流れをざっくりまとめると
Pythonでチェック元となるテーブルから値を取得して、mst_nin_s6(数列B)とぶつけ、input_nin_s6に結果を入れる
その後、PHPで、input_nin_s6をベースとしてmst_nin_s6とjoinして、結果を取得する
です。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • Panzer_vor

    2017/01/15 03:24

    長文となりますが、修正依頼としては1つ目だけです。2つ目は蛇足なのでスルーしてください。 1つ目:得たい結果とその時の入力(テーブルデータ)のサンプルをより明確にすると早く回答が得られるかもしれません。 2つ目:データの横持ちにはなにかそうでないといけない明確な意図はありますでしょうか。 DBの一般的な考え方となりますが、データの横持ち(num1〜mum6など)はあまり好ましくありません。 (※というかDBがそのようなデータを扱うのが得意じゃない) 特に今回のような存在チェックみたいな突き合わせだと、恐らくデータ行として数列を管理する方が簡単にいくケースが多いです。 (例えばidを主キーに数列名、要素順、要素値のテーブルを定義するとか) 見せ方上都合が悪いなら、 上記テーブルから見せ方だけを変更するようなビュー定義したりもできるので、 データ構造にテーブル定義がひっぱられるような状況は極力避けた方が良いでしょう。

    キャンセル

  • ikedas

    2017/01/15 15:27

    「チェック元となるテーブル」とはmst_nin_s6のことと思われますが、合ってますか。また、input_nin_s6というテーブルが何なのか全く説明がありません。

    キャンセル

  • ikedas

    2017/01/15 17:06 編集

    「マスタテーブル」(mst_nin_s6) というのは、最初の方に出てきた「数列A」に当たるのでしょうか。あと、Pythonのコードでinput_nin_s6に入れる部分が示されていませんので、どんなルールで複数のidが入っているのかがわかりません。とにかく、もう少しご質問を整理して、説明が明確に伝わるようにしていただけないでしょうか。お願いします。

    キャンセル

  • atLast

    2017/01/15 17:32

    了解いたしました。申し訳ありません。

    キャンセル

回答 1

checkベストアンサー

0

うーん・・・現在の情報だけでは的確な回答が難しいかもですね。

ですので回答というよりアドバイスです。

現在、最終結果は集約(GROUP BY)して出してますが、
もしまだ行なっていないのでしたら、
一度集約を外して結合(JOIN)した2テーブルからカラムを全表示してみて、
データ状況を確認してみてはどうでしょう?

恐らくこの時点で想定外のデータの取れ方をしているはずです。

その確認を取ってから結合条件に誤りがないか確認しましょう。

さっくりまとめると調査手順は以下です。

  1. 集約を解除し非集約の状態でデータ確認
  2. 上で想定外なら結合条件が合っているかを見直し
  3. 上が間違いないなら結合に利用するテーブルのデータ登録状況を確認

恐らくこの順番で調査すると何かヒントが掴めるかもしれません。


本題とはそれますが、
テーブル構造がテーブル設計上のアンチパターンを突き抜けてますね・・・。
カンマ区切りも基本的に扱いにくいのでid群は行レコードとして管理したいところですね。
(※著名な書籍でジェイウォークアンチパターンとして紹介されてます。)

そうすれば結合時にREGEXP_CONCAT関数を適用するようなややこしい制御は不要となります。
かつデータベースは特にテーブル同士の単純な結合に秀でている(パフォーマンスが良い)ため、
よほど容量制約などがなければ、
テーブルの正規化やデータの行として保持する方法に徹する方が良いでしょう。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/01/16 11:46

    アドバイスありがとうございます。

    クエリ自体、調べながらではありますが、自力で作成した物のため
    動きは理解しながら作成しています。
    (group byを外すとどうなるか、等ですね)
    ですので、いただいた順番での実動作の確認は終わっています。
    お忙しい中回答いただきましたのに申し訳ありません。
    また、質問内容も拙い物で申し訳ありません。

    ---
    テーブル構造が突き抜けている件ですが
    いくつのデータが入ってくるか分からなかったため、カンマ区切りをやむなく使用しています。
    本来であれば、最大値を調べて、カラムに分割するのが正しいのはわかっているのですが
    急ぎの業務だったため、疎かになってしまっています。
    この点についてのご指摘もありがとうございます。

    キャンセル

  • 2017/01/16 12:57

    > atLastさん
    既に確認済みということで失礼いたしました。

    テーブル構造については、
    若干誤解して伝わってる印象なので補足しておきます。
    最大数を見積もってカラムを余裕をもって用意する、
    という戦略もあるにはありますがそれもあまりお勧めできません。
    当方がそう判断する理由は以下です。
     ①テーブル作成時の見積もりが外れたらテーブル定義のいじり直しが必要
     ②他テーブルと結合する上では扱いにくいデータ構造

    そのためレコードごとにIDを落とす手法の方が好ましいです。
    具体的には以下のようなイメージです。
    group_id mst_id
    1 101
    1 201
    1 301
    2 111
    2 222

    これの「mst_id」とマスタの「id」で結合する場合だと、
    正規表現による分割とかは利用せずシンプルに書けますよというお話ですね。

    まぁ手戻りの大きさなどもあるとは思いますので、
    次回機会がある時の参考にでもしていただけたらと思います。

    キャンセル

  • 2017/01/16 13:32

    Panzer_vorさん

    テーブルについての補足説明、ご丁寧にありがとうございます。
    仰っている意味がようやく理解できました。
    よく、補足情報などを格納するときにやる手法そのままでした。

    今度、テーブル設計からやるときは、気をつけるようにしてみます。

    重ね重ね、ありがとうございました。

    キャンセル

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

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

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

  • Python 3.x

    7329questions

    Python 3はPythonプログラミング言語の最新バージョンであり、2008年12月3日にリリースされました。

  • MySQL

    6139questions

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

  • SQL

    2534questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。