【テーブル設計/SQL】

And条件
Select Anken.*
From Anken
Inner join ( Select Distinct kanriNumber
From Keyword
Where keyword Like '%a%') As key1
On Anken.kanriNumber = key1.kanriNumber
Inner join ( Select Distinct kanriNumber
From Keyword
Where keyword Like '%b%') As key2
On key1.kanriNumber = key2.kanriNumber
Inner join ( Select Distinct kanriNumber
From Keyword
Where keyword Like '%c%') As key3
On key2.kanriNumber = key3.kanriNumber

Or条件
Select Anken.*
From Anken
Inner join ( Select Distinct kanriNumber
From Keyword
Where keyword Like '%a%'
Or keyword Like '%b%'
Or keyword Like '%c%' ) As key1
On Anken.kanriNumber = key1.kanriNumber

１つのSQLで様々な条件に合わせられるようにLIKEを使っているのだとすれば
その考え方自体が問題かもしれません。
処理を高速化したいのであればLIKEを使わずに
プログラム側で動的にSQLを生成するほうがよろしいかと思います。
そうすればシンプルな構造１のほうが高速動作が期待できると思います。

RDBMSも判らなければデータ量も判らないので何とも言えないのですが
コメント欄のSQLについては
INNER JOINのネストは重かろうと思います。
　　AND条件はWHERE と COUNT
　　OR条件はWHERE と EXISTS
の構文のほうが高速処理してくれそうな気がします。

kn1967 様
コメントありがとうございます。

> RDBMSも判らなければデータ量も判らない
SQLSerever2005です。
データ量は案件が400行程度、キーワードが1案件に10程度で、１案件が1Mバイトくらいです。(ファイルを格納するので

> 処理を高速化したいのであればLIKEを使わずに
> プログラム側で動的にSQLを生成するほうがよろしいかと思います。
この部分想定できないのですが、例えば"bab"というキーワードをaという単語で検索するとき、どうしたらいいのでしょうか。

> AND条件はWHERE と COUNT
これは、条件設定した個数がcountの値と一致するか、で見ればいいですか。
SQL文を編集するしかないものの、innner joinがひとつになっていいですね。
検討します。

回答へのコメントができないので後にします。

キーワードに設定した文字列は本文には含まれないようなのです。
そのため全文検索も要件として存在します。
こちらはlikeで検索するしかないですよね。

キーワードはlikeを使わない方向で考えます。ありがとうございます。


ところで、テーブル設計についてもご教示願いたいと思います。
2案よりは1案のほうが速度が速いという感じなのですかね。
こちらはすっきりしたSQLを思いつかなくて困ってます。

１案のほうはオーバーヘッドがかかりませんし
仮にLIKEを使わないのであればインデックスも多少効くかもしれません。

ただし、仮に
　　keyword1,keyword2
　　みかん,果実
　　果実,りんご
というようにキーワードが無秩序に入っているとした場合などには
２案でないと対応が難しい。

そうではなくて
　　keyword1,keyword2
　　みかん,果実
　　りんご,果実
というようにフィールド毎に意味が分けられているならば１案が望ましい。

話の流れ的にデータベース概論的な方向になってしまいますが
データベース構築の際にはフィールド毎に意味を持たせるのが基本となりますので
それに則って案１にすべきか案２にすべきかを決めるべきかと思います。
キーワードが無秩序にあるのであれば必然的に案２になるのではないでしょうか？

いずれにしても必要に応じて動的にSQLを生成するほうが楽かと個人的には思います。

>データ量は案件が400行程度、キーワードが1案件に10程度で

SQLが少々複雑になっても、インデックスが効かなくても
それほど気にするほどの応答時間はかからないと思いますよ。
もちろん、SQLが簡単になるほうがメンテナンスの面などで後々が楽ですけど。

案１のSQLはWHERE句にツラツラと条件を並べるだけですから・・・
動的生成で必要な部分だけ書くようにすればシンプルですよね。
ただ、話の流れから考えると今回の件は案２のほうがヨサゲですが・・・。

kn1967 様
ありがとうございます。

>　　keyword1,keyword2
>　　みかん,果実
>　　果実,りんご
とくに決め事がないので恐らくこれになります。
なので、1案だと、keyword1からもkeyword2からも検索しなければならず大変だな、と思っていました。
たぶん最大で20x20の400の検索条件ですよね。。。

まだ詳細は決めていませんが案2になりそうですね。
とても参考になりました。

回答されていらっしゃらないので後ほどポイント送信いたします。

それほど具体的な事を書いてもいないのでコメント欄を使わせていただいただけですから、あまりお気遣い無く。
それよりも実は、回答１と回答２の投稿者からのフォローが見ものだったりしてます（笑）

もう使わない予定ですが、あとから参考にされる方もいらっしゃるかもしれませんので書き直しておきます。

And条件
Select Anken.*
From Anken
Inner join ( Select kanriNumber, Count(*) As counter
From Keyword
Where keyword Like '%a%'
Or keyword Like '%b%'
Or keyword Like '%c%'
Group by kanriNumber ) As Keyword
On Anken.kanriNumber = Keyword.kanriNumber
Where Keyword.counter >= 3

Or条件
Select Anken.*
From Anken
Where Exists ( Select *
From Keyword
Where Anken.kanriNumber = Keyword.kanriNumber
And ( keyword Like '%a%'
Or keyword Like '%b%'
Or keyword Like '%c%' ) )

でもこの記述だと、キーワードの内容が、以下だったときもヒットしちゃいますね。

1a1
2b2
3a3


解消する方法があるのかな。

書き直し失敬。

20個のキーワードがあらかじめ決まっている場合なら、単純に2^20の整数値（当然ながら32ビット値に収まる）で集合を表せますので、それでキーワードテーブルを作成すれば（つまり、どのキーワードのキーナンバーも、2^nの形の整数にする）、検索のオーバーヘッドはほとんど無視できます。

また、キーワードを文字列にしてしまえば、テーブルは案件テーブル一つにする事もできます。データベースは大きくなってしまいそうですが。

または、キーワードが今後増える可能性があるなら、テーブルを三つにしては？
-案件テーブル
-キーワードテーブル
-キーワード集合テーブル
まず、検索キーワードの組み合わせをキーワード集合テーブルで検索して、検索用のキーナンバーを得ます。そのキーナンバーで案件テーブルを検索すれば、結果的には最初に書いたのと同じ事になります。
テーブルの管理とクエリの形はかえって面倒くさくなりますが、検索のオーバーヘッドがどうしても気になるほど大きくなる事が予想されるのでしたら、管理が多少面倒でも速い方を選ぶしかないですよね。

どこまで正規化するかは、結局、何を選ぶかのトレードオフでしかありませんよね。

補足：
> キーワードを文字列に

キーワードを全部つなげた文字列に、です。カラム一つで済みます。
ただし、カラムを可変長にしなければならないかもしれません。

または、キーワードテーブルのキーナンバーをつないだ文字列にする方法もありますね。

kuro-yo 様

コメントありがとうございます。

> キーワードを全部つなげた文字列に、です。カラム一つで済みます。

そうですね。この方法も考えたのですが、1a1と2b2のキーワードを12で検索したときヒットすべきでないのにヒットしてしまうのですね。
それを防ぐためにスペース区切りなどにしても、今度はキーワードとしてスペースが入ってきたときに置換しなければならない、など問題が多く今回は見送りました。

> 単純に2^20の整数値で集合を表せる
> キーワードテーブルのキーナンバーをつないだ文字列にする

これらはわかりません。
どういうことなんでしょう。

> 単純に2^20の整数値で集合を表せる

２進数で表すと、各桁の値で、その桁に対応するキーワードの存在の有無を表せます。プログラミングの常套手段です。

すなわち、n番目のキーワードを整数値2^(n-1)に対応付けます。
最初のキーワードを1、次のキーワードを2、３番目のキーワードを4、４番目のキーワードを8、…、
という具合に倍々の整数値に対応付けていくわけです。

で、案件テーブルの方には、指定されたキーワードに対応付けた整数値の合計を格納しておくわけです。
例えば、１番目のキーワードと５番目のキーワードと７番目のキーワードが指定されていたら、81（=1+16+64）を格納しておけばいいです。
検索するときも同様で、検索したいキーワードに対応付けた整数値の合計で検索すればいいわけです。

ただしこの方法では、OR条件で検索する時や、案件に指定されているキーワードを取得する際に、ビット演算が必要になります。

> キーワードテーブルのキーナンバーをつないだ文字列にする

キーワードの文字列をそのまま検索キーに使う必然性はないわけですので、キーワードに対応付けた別の文字列（数字がいいでしょうか）をつないだ文字列を１カラムに格納しても構わないわけです。

例えば、１番目のキーワードと５番目のキーワードと７番目のキーワードが指定されていたら"1.5.7."を格納し、また検索する時も同様にします（※"1.5.7."は一例です。他のやりかたもあると思います）。

> それを防ぐためにスペース区切りなどにしても、今度はキーワードとしてスペースが入ってきたときに置換しなければならない、など問題が多く今回は見送りました。

トリッキーですが、スペースではなく、改行で区切るという方法もあるかと思います（推奨するわけではありません）。

kuro-yo 様

ありがとうございます。

すこし認識に相違があるかもしれません。
今回、キーワードに指定する文字列は何番目という意味を持ちません。
abcというキーワードは1-20のどこにいれてもよく、bで検索したときはどこに入っていてもヒットさせる、という要件です。
-> この辺はabcでなければヒットしない、という方向にもっていきますが。

で、順番に意味がないので前につめて格納することになります。
8が空きだったら9-20は空きなのです。
そして、キーワードはフリーの文字列です。固定の文字列ならもう少し楽なのですが・・・。
キーワードテーブルにkeyNumberがあったので誤認があったのだと思いますが、登録順序の保持と主キーの設定のために設けている項目です。


> トリッキーですが、スペースではなく、改行で区切るという方法もあるかと思います

これは面白いですね。
確かに改行が入ってくることはありません。

まず僕の認識の上での補足ですが、

> 今回、キーワードに指定する文字列は何番目という意味を持ちません。

テーブルに格納してIDがつけば、意味がありますよ。
何番目というのは、説明のために持ち出した序数にすぎません。
実際には、IDを使っても、論理的な何の違いはありません。

（ただ、２進数の桁で表すほうは、最初に断っているように、20個のキーワードがあらかじめ決まっている場合、に限られますが、これも物理的限界があるからであって、例えば多倍長整数が使えるなら、やはり論理的な違いはありません）

> abcというキーワードは1-20のどこにいれてもよく

どこに入れてもいいのなら、あらかじめソートして入れてもいいのです。


いずれも、単に考え方を示しているだけで、実装方法を示しているわけではないと、お考えください。

----

> すこし認識に相違があるかもしれません。

といわれて気付いたのですが、たぶん、認識のずれというのは、

> 1案件に20個のキーワードを設定し、それらを検索したいという要件があるとき

というのを、私は「20個のキーワードがあり、そのなかの自由な組み合わせで案件毎に設定する」という意味と捉えたのですが、
Kenjuさんのおっしゃりたかったのは、「不特定のキーワードを、一つの案件につき最大20個まで設定する」という意味だったのですね…。

数カラムならともかく、20カラムも検索対象があるのはあまり好ましくないと思いますので（データベースによっては、カラムを通して検索できるものもあるかもしれませんので、一概には言えませんが）、やはり、ある程度正規化する事を考えた方がいいと思います。

各案件で、キーワードをある程度共通化して使う事が想定されるのであれば、
案件だけのテーブル、キーワードだけのテーブル、案件とキーワードをつなぐテーブル、の三つに分けるのが一番良さそうです。

案件とキーワードをつなぐテーブルは、単なるペアにする方法もありますが、キーワードがある程度固まるのであれば、キーワードの組み合わせのテーブルにする方法もあります：前者は、案件がひとつ増えると、設定されたキーワード分だけロウ数が増えるのに対し、後者はキーワードの実際に使用されている組み合わせ以上にロウ数が増えません。その代わり、管理は前者の方が後者より圧倒的に楽です。

あとは、分かりやすいという点で、文字列を使う方法がいいかもしれません（ただ、これもデータベースによっては不定長の文字列をカラムに設定できないものもありますので、必ずできるとは言えませんが）。
今なら、データベース内を全文検索することもできるようですから、文字列を使うのも案外いいのかもしれません。

----

ところで、上の方のコメントを実はまだ読んでいなかったのですが、

案件というのはバイナリデータとかテキストデータとか、そういう大きなまとまった量のデータなのですか？
ならば、さらにもうひとつ、案件を格納するデータはIDをつけるだけにして、それとは別に案件検索用のテーブルを作った方がいいような気がします。そうすれば、検索用テーブルは多少カラム数を多くしても、クライアント側でSQLを動的に生成すれば、カラム数もあまり問題にはならないですね。

> 検索用テーブルは多少カラム数を多くしても、クライアント側でSQLを動的に生成すれば、カラム数もあまり問題にはならない

と書きましたが、やっぱり問題ありますね。

kuro-yo 様

ありがとうございます。

認識の相違についてはその通りで、
・不特定のキーワードを1案件に最大20個設定し、さらに検索時にも20個設定できる。
・キーワードは不特定なので文字列で格納するしかない。
・順番に指定もないので検索時は設定された全てのキーワードを検索する。

ということでした。最初にまとめておくべきでしたね。

使ってみればある程度のまとまりはできるかもしれませんが、これは想定できません。
３つにする方法はスキル不足で想定ができないのですが管理も複雑になりそうですしうまくいかなそうです。

というあたりです。上のほうはちょっとわかりませんでした。
たぶん、前認識の状態のレスではないかと推測します。
特定のキーワードが20個あるという意味なら仰るとおり順序に意味がでてきます。
その場合なら案件テーブルにはキーワードのIDだけ格納しておけばいいのでもう少し楽でいいですよね。

話をしていてちょっと気になったのですが、何もかもデータベースにやらせようとしていませんか？
まさか、クライアントがコンピューターではない、という事はないですよね？

クライアントはコンピュータですよ。

ポイント頂戴しました。
たいしたお役にも立てませんでしたが、ありがとうございます。

１カラムに収める件は私も考えたのですが、同様に駄目だと判断してました。
改行コードを入れるというのは盲点というか、昔、自分もやったことありました。
年々記憶力の衰えを感じる今日この頃です・・・。

長くなりそうなので、ひとまず、お礼まで。

> クライアントはコンピュータですよ。

いえ、そういう事ではなく、
クエリのたびに人間がSQLをじか打ちしたり、
あるいはそれとほとんど変わらないフロントエンドしか用意してない
（例えば、検索フォームに入った文字列をそのままストアドプロシージャのパラメータに渡すだけ、というような）、
というわけではないですよね？という意味です。

…ただ、昨日書いてしまってから思ったのですが、
データベースの設計だけ受注していて（あるいはプロジェクト内で依頼されてて）、
しかも、クエリのインターフェースも発注者側（依頼者側）で指定されていて、
そのうえ、クエリの仕方について仕様が固定されていて、変更の提案ができない、
という事情がもしもあるのだとすると、
処理の多くをデータベースに依存せざるを得ないのも仕方ないかもしれません…。
私はてっきり、クライアントも含めての設計だと思っていましたので、そのつもりで答えてました。

> あるいはそれとほとんど変わらないフロントエンドしか用意してない
>（例えば、検索フォームに入った文字列をそのままストアドプロシージャのパラメータに渡すだけ、というような）

想定はこれです。
ただ、webシステムの設計なので変えられないわけではありません。
あまりいい方法を思いつかないだけです。

クライアント -> webアプリケーション -> データベースサーバ
という流れなので、webアプリ側でSQL文は適当に書き直せます。
ただ、保守性から複雑なSQLを構築したくなかったり、うまい方法を思いつかなかったりで固定的なSQL文を想定する結果になっています。

まだちゃんとテーブルの構成について書いてませんでした。
とりあえず、テーブルは３つとして説明します。テーブル名、カラム名は適当です。
案件そのものを格納するカラムは、説明に全く不要なので書きません。
SQLはすっかり忘れてうろ覚えなので書きません。

・案件テーブル…案件と検索IDを関連づける。案件IDはユニーク※。
案件ID, 検索ID

・検索テーブル…キーワードIDの集合を検索IDに関連づける。異なる集合には異なる検索IDをつける事。
検索ID, キーワードID

・キーワードテーブル…キーワードとキーワードIDを対応づける。キーワードIDはユニーク※。
キーワードID, キーワード

（※テーブル側で自動でつけさせます。なお行を削除してもIDは再利用しません。）

検索の際は、まずキーワードテーブルから、検索キーワードに対応づけられたキーワードIDを取得します。
文字列比較を行うのはこの時だけです。
次に検索テーブルで、そのキーワードIDを含む検索IDを全て取得します（OR条件）。
最後に、この検索IDを持った案件IDを全てひろえばいいわけです。

AND条件にする場合には、検索テーブルのクエリで、
同一検索IDの行数と取得したキーワードIDの個数とを一致させます。

管理は少し面倒です：案件へのキーワードの登録は、
まず一致するキーワードをキーワードテーブルで検索し、ないものは新たに追加します。
次に、検索テーブルでその全てのキーワードIDが登録されていて、
なおかつ同一検索IDの行数が登録するキーワードIDの個数に一致するものを検索します。
なければ新しい検索IDを決めて、その検索IDで、案件に登録する全てのキーワードIDを検索テーブルに追加します。
（つまり、キーワードIDの集合と検索IDが一対一に対応します）
最後に検索IDを該当案件に設定します。

検索IDの設定がやっかいですが、それは解決方法がいくらでもあると思います。

一見オーバーヘッドが大きいように思えるかもしれませんが、クエリの組み立て方次第です。
あいにく具体例を示す時間はありませんが、私が以前行った仕事（MSSQLServer2000だったと思います）では、
サブクエリの順番を変えただけで、クエリの時間を10倍以上速くした事もあります。
要は、joinするテーブルがサイズが小さくければ速くなります。
上に書いた手順はまさにそうなっていると思いますので、この手順になるようにクエリを組み立てます。

いくらかでもお役にたてば何よりです。

kuro-yo 様

ありがとうございます。
ややこしそうですが検討してみます。

残念なことにキーワードの部分検索を行うように要求されました。

likeでやらざるを得ないのか。どうしようかな。

likeを使うのは構わないのではないですか？
例えば私の例では、キーワードテーブルでlikeで文字列比較してキーワードIDを取得すれば良いのです。