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

Question

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のものです。

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

---

-------------- ここから、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して、結果を取得する
です。

Comments

[Panzer_vor]

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

[ikedas]

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

[ikedas]

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

[atLast]

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

Answer 1

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

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

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

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

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

さっくりまとめると調査手順は以下です。
0. 集約を解除し非集約の状態でデータ確認
0. 上で想定外なら結合条件が合っているかを見直し
0. 上が間違いないなら結合に利用するテーブルのデータ登録状況を確認

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

---

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

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

Comments

[atLast]

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

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

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

[Panzer_vor]

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

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

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

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

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

[atLast]

Panzer_vorさん

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

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

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