SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

MySQLで全文検索1

SQL中級・上級

　私は、MySQLをほとんど使わないのであまり考えたこともないのですが、MySQLの全文検索はブランクやカンマで区切られた単語単位でしかインデックスしてくれないので、単語の区切れのない日本語ではほぼ使えません。
　そこで nGram でカットするファンクションを作ってみました。

　nGramにカッとしたりするのは外側の言語でやれば良い。という考え方もあるでしょうが、私は RDBMS で完結するべきと考えています。
　アプリ側に任せてしまうと、例えば、PHPで作ったモジュールを Javaで呼ばないと行けなくなったり、Javaのプログラムに書き直したりということが起こり得ます。

　RDBMSを引っ越すよりも、複数言語を扱うことの方が圧倒的に確率は高いですし、SQL側で行った方がパフォーマンスも良くなります。

　nGramにカットするのは、確かに外側の言語で行った方が RDBMS の負荷は小さくなりますが、基本的には SQL を使いきる方が RDBMS(サーバ）の負荷は小さくなる。

　これよく、ウソをついているプロが多すぎて、私がいい加減なことを言っているように思われることすらあるけれど、プロならウソをついたらアカンで。

　やるとしたら、巨大になれば、インデックスだけの別DB（RDBMS）でやる（つまり、Googleですな）こともあり得ますけれど、データと正規化のプログラムは一体化している方が管理上のメリットはあるでしょう。

　では実際のソース。

ストアドファンクションを作る

　半角カナや異字体を正規化する Normalize ファンクションと、2文字毎にカットする nGram ファンクションを作ります。

DROP FUNCTION IF EXISTS Normalize;

DELIMITER //

CREATE FUNCTION Normalize(pText TEXT)
	RETURNS TEXT
	DETERMINISTIC
BEGIN
	DECLARE vLen          INT;               -- 変換対象の長さ
	DECLARE vPos          INT;               -- 変換対象の文字位置
	DECLARE vChar1        VARCHAR(2);        -- 半角カタカナ（入力）の１文字取り出し
	DECLARE vChar2        VARCHAR(2);        -- 濁音、半濁音操作のため半角カタカナ（入力）の１文字取り出し

	DECLARE vLstPos       INT;               -- リスト中の変換候補の位置
	DECLARE vMChar        VARCHAR(2);        -- 全角カタカナの１文字
	DECLARE vBefor        VARCHAR(255);      -- 変換元
	DECLARE vAfter        VARCHAR(255);      -- 変換先
	DECLARE vRet          TEXT;              -- 全角カタカナ（出力）

	
	IF pText is null THEN
		RETURN null;
	END IF;

	SET vLen = CHAR_LENGTH(pText);

	IF vLen = 0 THEN
		RETURN '';
	END IF;

	SET vRet = ''; 
	SET vPos = 1;
	
	WHILE vPos <= vLen DO
		SET vChar1 = SUBSTR(pText, vPos, 1);

		IF vChar1 < '｡' OR vChar1 > 'ﾟ' THEN        -- 半角カタカナ以外の時
			-- 禁則文字 ※ 編集してください。
			SET vBefor = '!"#$%&()=-~^\|@`{}*:+;_?/.>,<！　”＃＄％＆（）＝−￣＾￥｜＠‘｛｝＊：＋；＿？／．＞，＜…♪';
			
			SET vLstPos = INSTR(vBefor, vChar1);

			IF vLstPos > 0 THEN 
				SET vRet = CONCAT(vRet, ' '); -- ブランクに置き換え
			ELSE
				-- 異体字 ※ 編集してください ここはパフォーマンス上、直書きの方が良いと思う
				SET vBefor = '齊斎齋&#65533;癲嶋嶌';
				SET vAfter = '斉斉斉高島島';
				
				SET vLstPos = INSTR(vBefor, vChar1);
				SET vMChar = SUBSTR(vAfter, vLstPos, 1);
				
				IF vLstPos = 0 THEN 
					SET vRet = CONCAT(vRet, vChar1); -- 変換不要
				ELSE
					SET vRet = CONCAT(vRet, vMChar);
				END IF;
			END IF;
			
		ELSE        -- 半角カタカナか？
			SET vChar2 = SUBSTR(pText, vPos + 1, 1); -- 濁音、半濁音のチェック
			
			CASE
			WHEN (vChar1 = 'ｳ'
					OR vChar1 BETWEEN 'ｶ' AND 'ﾄ'
					OR vChar1 BETWEEN 'ﾊ' AND 'ﾎ')
					AND vChar2 = 'ﾞ' THEN
	
				SET vLstPos = FIELD(CONCAT(vChar1,vChar2)
						, 'ｳﾞ'
						, 'ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ'
						, 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ'
						, 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ'
						, 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ' );

				SET vMChar = ELT(vLstPos                  -- vLstPos = 0 ならば、vMChar = ''
						, 'ヴ'
						, 'ガ', 'ギ', 'グ', 'ゲ', 'ゴ'
						, 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ'
						, 'ダ', 'デ', 'ヅ', 'デ', 'ド'
						, 'バ', 'ビ', 'ブ', 'ベ', 'ボ' );
				SET vRet = CONCAT(vRet, vMChar);    -- リスト中にあり、全角変換
				SET vPos = vPos + 1;    -- 濁点分はチェック済みなのでカウントアップ
			WHEN (vChar1 between 'ﾊ' AND 'ﾎ')
					AND vChar2 = 'ﾟ' THEN
				SET vLstPos = FIELD(CONCAT(vChar1,vChar2)
							, 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ');

				SET vMChar = ELT(vLstPos                   -- vLstPos = 0 ならば、vMChar = ''
						, 'パ', 'ピ', 'プ', 'ペ', 'ポ');   
				SET vRet = CONCAT(vRet, vMChar);    -- リスト中にあり、全角変換
				SET vPos = vPos + 1;    -- 濁点分はチェック済みなのでカウントアップ
			ELSE
				SET vMChar = '';
			END CASE;
			
			-- 濁音、半濁音じゃなかった
			IF vMChar ='' THEN
				SET vBefor = '｡｢｣､･ｦｧｨｩｪｫｬｭｮｯｰｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜﾝﾞﾟ';
				SET vAfter = '     ヲァィゥェォャュョッーアイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワン';   -- ゛゜'; ノーマライズじゃなければ、濁点半濁点にするべきか？……。
				
				SET vLstPos = INSTR(vBefor, vChar1);
				SET vMChar = SUBSTR(vAfter, vLstPos, 1);
				SET vRet = CONCAT(vRet, vMChar); -- vLstPos = 0 ならば、vMChar = '' なので濁点半濁点はカットされる
			END IF;
		END IF;
		SET vPos = vPos + 1;
	END WHILE;

	RETURN vRet;

END;
//


DROP FUNCTION IF EXISTS nGram;

DELIMITER //

CREATE FUNCTION nGram(pText TEXT)
	RETURNS TEXT
	DETERMINISTIC
BEGIN
	DECLARE vLen          INT;               -- 変換対象の長さ
	DECLARE vPos          INT;               -- 変換対象の文字位置
	DECLARE vWrk          TEXT;              -- 全角カタカナ（出力）
	DECLARE vRet          TEXT;              -- 全角カタカナ（出力）
	DECLARE vChar2        VARCHAR(2);        -- 半角カタカナ（入力）の１文字取り出し

	DECLARE vMChar        VARCHAR(2);        -- 切られた2文字
--	DECLARE vBefor        VARCHAR(255);      -- 変換元
--	DECLARE vAfter        VARCHAR(255);      -- 変換先

	
	IF pText is null THEN
		RETURN null;
	END IF;

	SET vWrk = TRIM(Normalize(pText));

	SET vLen = CHAR_LENGTH(vWrk);

	IF vLen = 0 THEN
		RETURN '';
	END IF;

	SET vRet = ''; 
	SET vPos = 1;
	
	WHILE vPos < vLen DO
		SET vMChar = SUBSTR(vWrk, vPos, 2);
		IF SUBSTR(vMChar, 1, 1) != ' ' AND SUBSTR(vMChar, 2, 1) != ' ' THEN
			SET vRet = CONCAT(vRet, ' ', vMChar);
		END IF;
		SET vPos = vPos + 1;
	END WHILE;

	RETURN TRIM(vRet);

END;
//

　長くなったので、使い方は明日書きます。

SQL的（集合的）考え方と、ループ（手続き型）の考え方３

SQL概論

問題を少し変更

　問題）部品在庫から、作成可能な製品の情報をとる。
　※本来はマスタテーブルと組み合わすべきですが、ツールの関係上2テーブルしか同時に表示出来ないので名称で結合する形になります。


SQLで考えるなら

　という風に考えます。

言い換える

　『在庫数が必要量を満みたす製品のみ』 → 『在庫数が必要量を満たさないレコードがある製品を削る』

　『満たさない』＝ NOT EXISTS（あるいは、NOT IN）

　答えは

　　SELECT * -- 本当は必要なもののみ
　　FROM
　　　　部品表 AS bh INNER JOIN 在庫 AS zk
　　　　　　ON bh.材料名 = zk.材料名
　　WHERE
　　　　NOT EXISTS (SELECT *
　　　　　　　　　　FROM 部品表 AS bh_s INNER JOIN 在庫 AS zk_s
　　　　　　　　　　　　　　ON bh_s.材料名 = zk_s.材料名
　　　　　　　　　　WHERE bh_s.製品名 = bh.製品名 -- サブクエリ側と外側を繋ぐ
　　　　　　　　　　　　AND zk_s.数量 < bh_s.必要量);

　ベースはこれだけの話なので、マスターなどが増えても同じです。

　生産管理系のシステムでは、このような処理がほとんどになりますが、手続き型で考えたらそれなりの時間が掛かるものでも、ほとんど何十秒で分かるようになります。このぐらいから、こちらにある問題
http://d.hatena.ne.jp/Sikushima/20100616/1276680064
のレベル程度のものが『イメージ』できるようになれば、実際にコーディングに多少の時間が掛かったとしても、一瞬で答えまでは行けるわけです。

　イメージが出来ていれば仕様書は書きようがありません。
　SQLでやるなら生産管理ぐらいのシステムなら、ER図、テーブル定義、項目移相表があれば、仕様書は要らないのです。

SQL的（集合的）考え方と、ループ（手続き型）の考え方２

SQL概論

もう一度問題

　問題）部品在庫から、作成可能な製品名をとる。
　※本来はマスタテーブルと組み合わすべきですが、ツールの関係上2テーブルしか同時に表示出来ないので名称で結合する形になります。


SQLで考えるなら



　答えは
　　SELECT 製品名
　　FROM
　　　　部品表 INNER JOIN 在庫
　　　　　　ON 部品表.材料名 = 在庫.材料名
　　GROUP BY 製品名
　　HAVING MIN(在庫.数量 - 部品表.必要量) >= 0;

　三枚の図になっていますが、考え方さえ理解していれば数十秒で答えまでたどり着けます。パターン認識じゃないので忘れてもどってことはないのです。

　考え方としてはこんな感じになります。

　必要なテーブルを引っ付け、ゴールを目指して削り出して行けば良い。
　プラモデルと、彫刻の違いです。

言い換えは、他の言語より重要？

　プログラムも翻訳と同じですから、言い換えは必要です。
　上の図の「SQL的思考3」でも、言い換えが必要なのですが、それほど難しい言い換えをしているわけではありません。

　冷静に見て、「部品在庫から、作成可能な製品名をとる」を、「ソートして、製品名がブレイクしたとき……」と意訳することと比べたら、SQLの方がはるかに直訳に近いでしょう。

　逆に、直訳でできるのに、「ソートして、製品名がブレイクしたとき……」なんて仕様書を作られても、ゴミでしかないのです。

SQL的（集合的）考え方と、ループ（手続き型）の考え方１

SQL概論

　前回、勉強会に参加したときに感じたことの続き。

みんなはどう考えているか？

　この問題は正答率が低かった。答えを見たら「あぁ〜」ってなるレベルですが、なかなか、出てこないようです。

　問題）部品在庫から、作成可能な製品名をとる。
　※本来はマスタテーブルと組み合わすべきですが、ツールの関係上2テーブルしか同時に表示出来ないので名称で結合する形になります。

　逆に、私には、なぜ、正答率が低いのか分からずに悩んでいました。そこに、SQLが流行らない理由があるんだろうけれど、私には分からないのです。私はタイピングが滅茶苦茶遅いので時間は掛かるのですが、頭の中では答えは何十秒かで出ています。

　皆さんが難しいと感じるものを、私が数十秒で答えまでたどり着くのは経験の差じゃない。単純に考え方の違いと分かっているのですが、それをどう表現したら良いのか……、何年も考えているのですが、なにぶん、皆さんの考えていることは他人の私に分かるはずもありません。ですからあくまで想像ですけれど……。

数分で分からない人は、以下の様に考えてないか？

　つまり、こんな風に考えているのです。

　上の図の様に考えている人は、何時間考えても答えにはたどり着けません。

　答えを見て、「なるほど、こんな書き方があったか！」と思っても、実際に現場で応用することは難しいでしょう。

　もちろん、数多くのSQLのパターンを覚えて、パターン認識できるようになれば、現場でも使えるかも知れませんが、本質的に分かってないでパターンを忘れたら終わりですし、少し、パターンから外れたら簡単に袋小路に入り込むでしょう。

　では、SQL的に考えるにはどうすれば良いかは次回。

久しぶりに更新

SQL概論

　昨日、SQLWorldの「みんなでSQLを書いてみよう」というハンズオンの企画の勉強会に参加した。

最初にお知らせ

　次回の企画のために、「実務で困っているSQL」を募集するそうです。
　「こんなのSQLで出来るの？」という問題があれば、コメントやメール info@g1sys.co.jp でご相談ください。

やっぱり、できた方が良いと思うよ

　私の興味はSQLとは全く違うところに向いたけれど（苦笑）SQLについて、これだけマシンが速くなったら、パフォーマンスはあんまり拘らなくても良いかなとも思っているのですが、それでもできた方が工数的にもメリットが大きいと実感した。

　特に、最後にやった問題は、実務ではもっとかなり複雑だけれど、その複雑な実務でもSQLで恐らく可能です。在庫とか、生産管理の山積み、山崩しなどの複雑な業務も、数個のSQLで可能ですが……。

　しかし、現実にはグリグリ回してやることになる。

　それが、保守し易いかというとそんなことはない。
　山積み、山崩しの仕様書なんて、私は、大抵読んでも意味が分からない（悪い意味で）。「数個のSQLで出来そうなことが、なぜ、この何百ページの仕様書になるのか？」と……。
　（テーブル定義も母言語でやり易い形になってるしね……）

　「SQLは使い回ししにくいから変更に弱い」というのは一面しか見てないのでしょう。数個のSQLで出来るなら、テーブル定義と項目移送表があれば十分で、仕様書は要らない。
　何百ページの仕様書と、キレイなオブジェクト指向で書かれたモノを直すのと、SQLを書き換えるのと比べたら、保守でも、トータルではSQLを直す方がはるかに楽。

　まあ、程度問題なんですけどね……。

なぜ難しいか？

　多分、出来ないことと、出来ることの判断がつかず、「出来ないことを悩み続けることになるんじゃないか？」という不安が先立つからだと思う。

　「もしかしたら、どうやってもできないかも知れないのに時間を使っている」という不安の裏返しで、「Javaや.Netなどの母言語でやれば、時間は掛かっても100％出来る」と心の中で思っています。

　そのとき、脳味噌の中で「母言語側のループで考える」思考ルーチンを使っています。ところが、SQLは全体を観て考えないと出来ないので、不安とプレッシャで「母言語だったら出来るのに」となったら、全く逆から考えていることになり答えは出ません。ちょっと不安になっただけで、袋小路に迷い込んでしまうのが、恐らく難しく感じさせる問題点なのでしょう。

　結果として、後から考えたら、「手掛かりもないまま、ただ、悩んでいた時間」となってしまって、「SQLなんてクソ」という印象が残るのではないかな？

　こんな風に、

　本当はもっと高い到達点があるけれど、ほとんどの人にとっては、恐怖の泥沼に突っ込むイメージに感じるはず。

　だから、もの凄く中途半端なところで諦めてしまって、プログラムソースは、SQLという言語と、母言語のスパゲッティになって切り離せないか、SQLを使わない方針しかなくなるのです。

　(良かったら、こちらも http://d.hatena.ne.jp/Sikushima/20111228/1325028197 読んでみて)

　しかし、工数とパフォーマンスを考えたら桁違い。更に、スキルは一度付けたらなくならない。実務で一つのプロジェクトでしか使ってないスキルなんていくらでもあるけれど、SQLは、なんやかんやいって、私でも20年近く廃れず使い続けているスキルですから、一番お得なんですけどね。

お勉強するなら

SQL概論
http://d.hatena.ne.jp/Sikushima/searchdiary?of=31&word=%2A%5BSQL%B3%B5%CF%C0%5D
SQL初級
http://d.hatena.ne.jp/Sikushima/searchdiary?of=12&word=%2A%5BSQL%BD%E9%B5%E9%5D

を順に読んでいただければ（多少、重複しています）

どうでも良いけど

　昨日最後に書いたクエリーはもしかしたら、「…… > 0」 って書いたかも知れないけど、「…… >= 0」が正しい（苦笑）　私はその場で考えているのでミスが多いし、タイピングの遅さが素人級であることを実感した。

　まあ、SQLでやるというのは、究極の横着ですから、そんな奴でもなんとかなるのです。

MySQL Cluster: NoSQL について

SQL概論

　MySQLの Cluster: NoSQL がなかなか良さそうです。

　私自身は、それを使う様な案件に恵まれてないので使わないとは思うけれど、チャンスがあれば使ってみたい。所謂、NoSQLは半端すぎて使いにくい。NoSQLという新たなモノを作るよりも、RDBMS が NoSQL を飲み込む、つまり、DB Engineを通さずにダイレクトアクセスできる API を提供して欲しいと考えて来たが、MySQLがその先駆けとしてやってくれました。この先、PostgreSQL も同様の API の提供を予定しているようです。

　この流れが商用RDBMSにも起きてくれたらと思います。

O/Rマッパとどう違うのか？

　要するに、O/RマッパはDB Engine、SQLというボトルネックの上に、抽象度が逆転するラッパーとなっているため、必ず非効率になる。

　DB Engineを利用するならば、効率的なSQLを書いて抽象度が逆転しないように、アプリケーション側ではSQLでできないUIのみにするのが正しい。

　O/Rマッパを使って効率が逆転するのは、DB Engine以下の技術力しか持ち合わせてない人間が使った場合に限られる。レベルが低いだけでなく、それを理解できないのなら、もうプロを名乗るべきではないと言い続けて来たわけです。

　O/Rマッパはボトルネックを重ねることになるので、レベルが低い技術者以外にとっては必ず非効率になりトレードオフはない。

　しかし、MySQL Cluster: NoSQLは、O/Rマッパとは違い、ボトルネックになるDB Engine、SQLをショートカットしている。この形が理想で、工数を見てSQLを使うか、パフォーマンスを見てAPI（NoSQL）を使うか、選択の余地が出てくる。

　商用RDBMSでこの動きはなかなか起きないとは思いますが、是非とも起きて欲しいと思う。

　まだ、ちらっとしか見てないけれど、勉強したい人はこれお勧めです。

　

MySQL Cluster構築・運用バイブル　?仕組みからわかる基礎と実践のノウハウ

MySQL Cluster構築・運用バイブル　?仕組みからわかる基礎と実践のノウハウ

なぜごり押しなのか４

システム設計

　私は基本的に、インターネット上の議論では個別の事情は挟むべきではない。と考えています。
　私が個別の事情を挟んでいるとか、感情的とかいう人がいるけれど、全く逆です。

　相手が【詐欺師レベル】と分かったとき、議論を打ち切って感情的に怒っているだけの話です。

できない人がいるは個別の事情。

　私がSQLが得意だから推す。と考える人は、自分自身が得意なものを推そうとする人でしょう。ですから、そんなこと全く言ってない私の感情を、自分の感情で補完しているだけでしょう。自分の心の醜さが出ているだけです。

　私はRDBMSを使う以上、SQLを使いきることが効率的であると言ってるに過ぎません。

　ただし、以前にも書きましたが過ぎたるは及ばざるがごとし。例えば、【闇雲に】トリガーを仕込みまくったりしたら効率は確かに落ちます。【闇雲に】が重要であって【使ってはいけない】ではないのです。

　私は当たり前のことしか言ってないけれど、反論には、暗に、あるいは明確に「できない人に配慮せよ」っていう感情が乗っている。しかし、できない人はどのぐらいできないか、私にはレベルが分からないから、公の場で議論するにふさわしくないし、公の場でする議論に個別の事情を持ち出しても意味がない。

　個別の事情を排除すれば、結論的には『RDBMSを使う以上、SQLを使いきるべき』としかならない。

できない人に合わせなくても良いのか？

　自分ができないのであれば出来る様になれば良いだけのことですし、できない人が大勢いる PJ ならメンバーの力量を見て、どこまで下げるか考えればよいでしょう。

　私が問題としているのは、合わせた（レベルを下げた）結果、多くの PJ で『初級シスアド ＝ ユーザ』以下という詐欺レベルになっているということです。それが一般化しているから反発が来るのでしょうけどね。そりゃあ『初級シスアド ＝ ユーザ』以下のスキルしかない連中が大半の PJ であれば、SQLを使わない方が効率的ですよ。それも当然の話ですけどね……「自分たちは素人以下」と大声で言ってるのと変わらない。

　『初級シスアド ＝ ユーザ』をある程度超えるスキルがあれば、SQLを使った方が効率的と感じますし、超えていなければ「こんなものを使ったら効率が落ちる」と感じると思います。当たり前の話で使うスキルがないものを無理に使うのは効率的ではありません。例えば、私がF1に乗ってもまずピットを出れないでしょう。

　要するに、効率が悪いと感じるのは、できなくって間違った使い方をしているからでだけなのです。

　ウソだというならば、初級シスアドの問題のテーブルを作って、他の言語からアクセスして解答よりも効率的にできる方法見せてみれば良い。もしできたら私は菓子折持って謝りに行くわ（笑）。もちろん、解答よりもっときれいなSQLにはできるけどね。

SQLのお勉強

　SQLは本当に簡単な言語で、素人にもできるのですからその気になれば誰にでもできます。考え方を少し変えたら良いだけです。もし、勉強しようと思われた方がいらっしゃれば、下のそれぞれのリンクから順に【次の日】と送って行けば大体まとまっていると思います。

　■ SQL概論
http://d.hatena.ne.jp/Sikushima/searchdiary?of=30&word=%2A%5BSQL%B3%B5%CF%C0%5D


　■ SQL初級
http://d.hatena.ne.jp/Sikushima/searchdiary?of=12&word=%2A%5BSQL%BD%E9%B5%E9%5D


　■ SQL中級・上級
http://d.hatena.ne.jp/Sikushima/searchdiary?of=15&word=%2A%5BSQL%C3%E6%B5%E9%A1%A6%BE%E5%B5%E9%5D