SQLでのデータの範囲検索について

[海Sea]

SQLの質問ですいません。場違いでしたら申し訳ありません。

いつもありがとうございます。
今回は、データの範囲呼び出しについて質問があります。
データベースはMySQLです。

コード:

SELECT 
hoge
FROM 
test
WHERE
BETWEEN hoge 1 AND 3

範囲検索は、通常は上記のようにBETWEENを指定しますが、
現在つまっているのは、範囲に対してあいまい検索も同時に行うということです。
BETWEEN LIKE '1%' AND などということは仕様で無理みたいなので、
どうしようか考えあぐねています。
また、扱う値は0～Zまでで、例えば1001や200Zなど複合的な値を持っているので、
正規表現のregexpを使う必要もあるかなとは感じているのですが、
やはり範囲に対してあいまい検索するのは、
SQLでは難しいでしょうか。
アドバイス等頂ければ幸いです。

[softya（ソフト屋)]

SQLは本業じゃないですが、こんな変則的な方法はありでしょうか？
「任意の基数の文字列を整数値に変換するユーザー定義関数 - babydaemons’s blog」
http://babydaemons.hatenablog.com/entry ... _SQLServer
MySQLじゃないですが。

[海Sea]

SQLは本業じゃないですが、こんな変則的な方法はありでしょうか？
「任意の基数の文字列を整数値に変換するユーザー定義関数 - babydaemons’s blog」
http://babydaemons.hatenablog.com/entry ... _SQLServer
MySQLじゃないですが。

ありがとうございます。
なるほど、こうやって全て数値化するという考えですね。
月曜になりますが、試してみます
MySQLにしても、ファンクションはあるので(・ω・)ノ

[sleep]

お悩みになっているのは
・1001や200Zなどの文字列に対して betweenをどう使うか
・あいまい検索も同時に行いたい
の２つですよね？

扱う値は0～Zまでで、例えば1001や200Zなど複合的な値を持っているので

つまり、この場合 hoge が 1001や200Z の値を収めた varchar で宣言してあったとすると
betweenに指定するとき、シングルクォーテーションで括ってあげれば良いだけです。

select hoge
from test
where hoge between '1A0F' and '2D3Z';

これでこの範囲の hoge のレコードが抜き出せます。
且つ、その範囲内のレコードに対して、更にLIKEで条件を付けることは可能です。
and で条件を繋げるだけです。

select hoge
from test
where hoge between '1001' and '200Z'
and name like '%高さ%';


もしかして、こういうことではなかったですか？？（汗）
ひょっとしたら私だけ理解できてないのかもしれない・・・・

[sleep]

例を挙げると、以下のデータが入ってるとします。
下のSQLを実行すると、検索結果のレコードが抽出されます。

foge, name
---------------------
'1001', 'やくそう'
'100Z', '毒消し草'
'101L', '聖水'
'1201', 'ひのきのぼう'
'200A', 'こんぼう'
'30C1', '銅の剣'
---------------------

select hoge
from test
where hoge between '1000' and '4000'
and hoge like '_2%';

検索結果
---------------------
'1201', 'ひのきのぼう'
---------------------

まぁ、こういう質問だったなら、なんですけどね・・・

[sleep]

あいまい検索のことを思い出したので、サブクエリーのことも書いておくことにしました。

select hoge

select * の間違いですね・・・
行単位にコピペで済ませた結果がこれです。申し訳ないです。


念のため、サブクエリーの例も載せときますね。
サブクエリーを使えば複数のあいまい検索を特定の範囲のレコードに対して行えます。

コード:

select * 
from  (select * from item where id between '1000' and '2Y00') as bw
where  bw.id like '2%' 
or     bw.id like '%1'
or     bw.id like '%Z';

コード:

検索結果
---------------------
'1001', 'やくそう'         # %1
'100Z', '毒消し草'         # %Z
'1201', 'ひのきのぼう'      # %1
'200A', 'こんぼう'         # 2%
---------------------

大抵のものはSQLだけで解決できると思います。
ま・・・求めているものがこういうものだったのかすら定かではないんですけどね・・・・

[海Sea]

あいまい検索のことを思い出したので、サブクエリーのことも書いておくことにしました。

select hoge

select * の間違いですね・・・
行単位にコピペで済ませた結果がこれです。申し訳ないです。


念のため、サブクエリーの例も載せときますね。
サブクエリーを使えば複数のあいまい検索を特定の範囲のレコードに対して行えます。

コード:

select * 
from  (select * from item where id between '1000' and '2Y00') as bw
where  bw.id like '2%' 
or     bw.id like '%1'
or     bw.id like '%Z';

コード:

検索結果
---------------------
'1001', 'やくそう'         # %1
'100Z', '毒消し草'         # %Z
'1201', 'ひのきのぼう'      # %1
'200A', 'こんぼう'         # 2%
---------------------

大抵のものはSQLだけで解決できると思います。
ま・・・求めているものがこういうものだったのかすら定かではないんですけどね・・・・

ご回答ありがとうございます。
求めているものは、質問にある通り、
between そのものに対して、あいまい検索をできるか、
とういうことです。
ですので、例にあげてくださった
サブクエリーで可能な範疇ということは、わかりました。
ただ、こちらも説明が足りない部分があったため、
補足させて頂きますと、

データは4桁あるけれども、3桁でそのデータを呼び出すということです。
つまり、

100から200を選択すると、
1000から200Zが呼び出すということです。
値はユーザーがその時その時で自由に選択できます。

が、書いていて、sleepさんの考え方でも、
できる気がしてきました。
ちょっと、where句が冗長になりそうですが、
月曜日にまたやってみます。

ただ、気になってるのは
100Zから100Zと同等の値を選択すると
該当データが出てこなかったりもしたので、
そこらへんはやはり、softyaさんが提示してくれた、
考え方も必要かもしれないと思っています。

[たいちう]

> データは4桁あるけれども、3桁でそのデータを呼び出すということです。
> つまり、
>
> 100から200を選択すると、
> 1000から200Zが呼び出すということです。
> 値はユーザーがその時その時で自由に選択できます。

手元にMySQLがないので確認してないですが、こういう事では？

select hoge from test where substring(hoge, 1, 3) between '100' and '200';

[sleep]

手元にMySQLがないので確認してないですが、こういう事では？
select hoge from test where substring(hoge, 1, 3) between '100' and '200';

あー、そんな感じしますね。

どうしても Likeが必要な場合、擬似的に あいまい検索のbetweenを作り出すこともできますしね。

コード:

select *
from item
where id >= (select min(id) as a from item where id like CONCAT('101', '%'))
and id <= (select max(id) as a from item where id like CONCAT('200', '%'));

コード:

検索結果
---------------------
'101L', '聖水'
'1201', 'ひのきのぼう'
'200A', 'こんぼう'
---------------------

ここから更に絞り込む場合は、サブクエリになりそうです。

[sleep]

replaceは１文字を複数文字（または０文字）に変換可能ですし

コード:

select replace(id, '1', 'AA') from item;

concatは前後に好きな長さの文字列を追加可能なので

コード:

select concat('_', id, '%') from item;

海Seaさんが求めていたものは、
ひょっとしたらこのあたりの組み合わせで実現できるのかもしれないですね。

[海Sea]

たいちろうさん、Sleepさん、Softyaさん

ありがとうございます！
そして、うおおおって、感じになってます。
SQLで関数使う意識が完全になかったのですが・・・

コード:

select hoge 
from test 
where substring(hoge, 1, 3) between '100' and '200';

たいちろうさんの、これで、できました。
悩んでた自分の力の無さが悔しいorz

コード:

select hoge 
from test 
where substring(hoge, 1, 3) between '10a' and '3ah;

このように、betweenに、
文字列含んでても、ちゃんと呼び出すことができす。

あと、sleepさんの擬似的にLikeなどの、知識もためになりました。
ご回答頂いた皆様、ありがとうございます。
SQLはもっと使い方を勉強しないと、
データを本格的に操作するときに、どうでも良いことで悩んでしまいますね。
精進します（＾＾；

[YuO]

解決後ですが一応……。

量や要求速度に依るのですが，関数を使うと，その関数が利用している列のインデックスは使われなくなります。
このため，対象の表の行数が多い場合，関数を使わないで済むような構造にした方が，速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので，使われないのは勿体ないとは思います。

また、扱う値は0～Zまでで、例えば1001や200Zなど複合的な値を持っているので、

と，「複合的な値」と書かれていますが，これが例えば前3桁の分類と最後1桁の枝番のように，複数の要素に分解できることを意味しているのであれば，

• 各要素を別々の列としておく
• 複合インデックスを張る

という変更を行うことで，検索を関数を含む比較から要素単位の単純な比較に変更でき，結果としてインデックスが効くようになります。

オフトピック

今時，例えば10000件程度であれば，シーケンシャルアクセスでも十分な速度が出ること多いと思いますが，RDBMSの速度がボトルネックになることはよくあるので……。

[海Sea]

解決後ですが一応……。

量や要求速度に依るのですが，関数を使うと，その関数が利用している列のインデックスは使われなくなります。
このため，対象の表の行数が多い場合，関数を使わないで済むような構造にした方が，速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので，使われないのは勿体ないとは思います。

また、扱う値は0～Zまでで、例えば1001や200Zなど複合的な値を持っているので、

と，「複合的な値」と書かれていますが，これが例えば前3桁の分類と最後1桁の枝番のように，複数の要素に分解できることを意味しているのであれば，

• 各要素を別々の列としておく
• 複合インデックスを張る

という変更を行うことで，検索を関数を含む比較から要素単位の単純な比較に変更でき，結果としてインデックスが効くようになります。

オフトピック

今時，例えば10000件程度であれば，シーケンシャルアクセスでも十分な速度が出ること多いと思いますが，RDBMSの速度がボトルネックになることはよくあるので……。

なんで最初から関数使わなかったんだと
自分に対して思ってたんですが、
Yuoさんのおっしゃる事情もあって、
何年かくらいSQLの関数ってつかってなかったんですが、
そういうことをすっかり忘れてましたね（＾＾；）
そもそも、あんまりSQLを大々的に組む機会も無く・・・

あと、データの値は、マスタのテーブルに四つの列で別々に入っており、
トランザクションテーブルには、１つの列に４桁として持っています。
そのため、Yuoさんのご指摘する、各要素を別々の列としておくということは、
現状では厳しい状態で、ひとまず関数を使用して、様子を見ます・・・orz

[sleep]

YuOさんが助言くださっているこちらは現状でも実践可能ですからね。

対象の表の行数が多い場合，関数を使わないで済むような構造にした方が，速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので，使われないのは勿体ないとは思います。

仮に以下の あいまい検索可能なbetween 構文があったとします。

コード:

カラム名 between like '最低値' and '最高値';

以下のような指定ができるとしたら

コード:

foge between like '100_' and '200_';

最低値側はfogeの下１桁は 0～の存在するレコードが全て抜き出されてくることが期待されているわけです。
逆に、最高値側はfogeの下１桁は ～Zまでの存在するレコードが全て抜き出されてくることが期待されているわけです。
つまり、SQLにbindして投げる前から

コード:

foge between like '1000' and '200Z';

こうなることは分かっている話なので、SQLへbindする前に
最低値の下１桁には 0
最高値の下１桁には Z
をそれぞれ最初から付加しておいて渡してあげれば良いだけだということになります。
そうすればインデックスを効かせた betweenが現状でも使用可能となります。

コード:

select hoge
from   test
where foge between  '1000' and '200Z';

[海Sea]

YuOさんが助言くださっているこちらは現状でも実践可能ですからね。

対象の表の行数が多い場合，関数を使わないで済むような構造にした方が，速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので，使われないのは勿体ないとは思います。

仮に以下の あいまい検索可能なbetween 構文があったとします。

コード:

カラム名 between like '最低値' and '最高値';

以下のような指定ができるとしたら

コード:

foge between like '100_' and '200_';

最低値側はfogeの下１桁は 0～の存在するレコードが全て抜き出されてくることが期待されているわけです。
逆に、最高値側はfogeの下１桁は ～Zまでの存在するレコードが全て抜き出されてくることが期待されているわけです。
つまり、SQLにbindして投げる前から

コード:

foge between like '1000' and '200Z';

こうなることは分かっている話なので、SQLへbindする前に
最低値の下１桁には 0
最高値の下１桁には Z
をそれぞれ最初から付加しておいて渡してあげれば良いだけだということになります。
そうすればインデックスを効かせた betweenが現状でも使用可能となります。

コード:

select hoge
from   test
where foge between  '1000' and '200Z';

bind前にコード側で0やZを別で付加してしまうと、
可能性は非常に少ないですが、
もしもデータの持ち方に変更があったときに面倒なこともあるため、
そこは悩みどころだったんです。実際問題。
それをやると、似たようなSQLを使用してる場合も、
全ての画面に対して修正がかかる可能性もあるのが、ちょっと微妙でして（＾＾；）
SQLの中だけでのことだったら、SQLのみを修正すれば良いので。
本当に細かいことですし、
そこまで気にする必要もないかもしれませんが、
個人的にはできるだけ回避したいやり方だと、思った次第です。
ただ、実際はStringでSQLを持っています。
なのでコード側といっても、極端な差は無いので、
あとでリファクタリングする時に、Sleepさんの仰る方法に変更がかかるかもしれません。

[sleep]

なるほど。
事情は色々ありますものね。

例えば、以下のような形で用意しておけば
リテラルとしてパース時に連結されます。
この場合、前の部分だけbindしてやれば良いですね。

コード:

select hoge
from   test
where foge between  '100' | '0' and '200' | 'Z';

多分いくつか方法はあると思うので
パフォーマンス要件と相談になりますね。

[sleep]

・・・と書いたものの
よくよく見るとこれじゃダメですね。ひっかからないものが出そうですね。

コード:

select hoge
from   test
where foge between  '100' | '0' and '200' | 'Z';

[sleep]

いいか、とも思ったんですが・・・
一応、私が何に気付いたか書いておきます。

気づいたのは
mysqlだと、 | じゃなくて || だったということです。
あと、|| が有効になっているかどうかは mysqldの起動時に
--sql-mode="PIPES_AS_CONCAT"
を指定してある場合ということです。

そこまで条件が整えば、確かに下記の記述は可能にはなりますけど・・・

コード:

select hoge
from   test
where  foge between  '100' || '0' and '200' || 'Z';

ちょっとやりすぎかもしれない、と自分で思ってしまった次第です。