SQLでのデータの範囲検索について
SQLでのデータの範囲検索について
SQLの質問ですいません。場違いでしたら申し訳ありません。
いつもありがとうございます。
今回は、データの範囲呼び出しについて質問があります。
データベースはMySQLです。
範囲検索は、通常は上記のようにBETWEENを指定しますが、
現在つまっているのは、範囲に対してあいまい検索も同時に行うということです。
BETWEEN LIKE '1%' AND などということは仕様で無理みたいなので、
どうしようか考えあぐねています。
また、扱う値は0~Zまでで、例えば1001や200Zなど複合的な値を持っているので、
正規表現のregexpを使う必要もあるかなとは感じているのですが、
やはり範囲に対してあいまい検索するのは、
SQLでは難しいでしょうか。
アドバイス等頂ければ幸いです。
いつもありがとうございます。
今回は、データの範囲呼び出しについて質問があります。
データベースはMySQLです。
範囲検索は、通常は上記のようにBETWEENを指定しますが、
現在つまっているのは、範囲に対してあいまい検索も同時に行うということです。
BETWEEN LIKE '1%' AND などということは仕様で無理みたいなので、
どうしようか考えあぐねています。
また、扱う値は0~Zまでで、例えば1001や200Zなど複合的な値を持っているので、
正規表現のregexpを使う必要もあるかなとは感じているのですが、
やはり範囲に対してあいまい検索するのは、
SQLでは難しいでしょうか。
アドバイス等頂ければ幸いです。
- softya(ソフト屋)
- 副管理人
- 記事: 11677
- 登録日時: 14年前
- 住所: 東海地方
- 連絡を取る:
Re: SQLでのデータの範囲検索について
SQLは本業じゃないですが、こんな変則的な方法はありでしょうか?
「任意の基数の文字列を整数値に変換するユーザー定義関数 - babydaemons’s blog」
http://babydaemons.hatenablog.com/entry ... _SQLServer
MySQLじゃないですが。
「任意の基数の文字列を整数値に変換するユーザー定義関数 - babydaemons’s blog」
http://babydaemons.hatenablog.com/entry ... _SQLServer
MySQLじゃないですが。
by softya(ソフト屋) 方針:私は仕組み・考え方を理解して欲しいので直接的なコードを回答することはまれですので、すぐコードがほしい方はその旨をご明記下さい。私以外の方と交代したいと思います(代わりの方がいる保証は出来かねます)。
Re: SQLでのデータの範囲検索について
ありがとうございます。softya(ソフト屋) さんが書きました:SQLは本業じゃないですが、こんな変則的な方法はありでしょうか?
「任意の基数の文字列を整数値に変換するユーザー定義関数 - babydaemons’s blog」
http://babydaemons.hatenablog.com/entry ... _SQLServer
MySQLじゃないですが。
なるほど、こうやって全て数値化するという考えですね。
月曜になりますが、試してみます
MySQLにしても、ファンクションはあるので(・ω・)ノ
Re: SQLでのデータの範囲検索について
お悩みになっているのは
・1001や200Zなどの文字列に対して betweenをどう使うか
・あいまい検索も同時に行いたい
の2つですよね?
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 '%高さ%';
もしかして、こういうことではなかったですか??(汗)
ひょっとしたら私だけ理解できてないのかもしれない・・・・
・1001や200Zなどの文字列に対して betweenをどう使うか
・あいまい検索も同時に行いたい
の2つですよね?
つまり、この場合 hoge が 1001や200Z の値を収めた varchar で宣言してあったとすると海Sea さんが書きました: 扱う値は0~Zまでで、例えば1001や200Zなど複合的な値を持っているので
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 '%高さ%';
もしかして、こういうことではなかったですか??(汗)
ひょっとしたら私だけ理解できてないのかもしれない・・・・
Re: SQLでのデータの範囲検索について
例を挙げると、以下のデータが入ってるとします。
下のSQLを実行すると、検索結果のレコードが抽出されます。
foge, name
---------------------
'1001', 'やくそう'
'100Z', '毒消し草'
'101L', '聖水'
'1201', 'ひのきのぼう'
'200A', 'こんぼう'
'30C1', '銅の剣'
---------------------
select hoge
from test
where hoge between '1000' and '4000'
and hoge like '_2%';
検索結果
---------------------
'1201', 'ひのきのぼう'
---------------------
まぁ、こういう質問だったなら、なんですけどね・・・
下のSQLを実行すると、検索結果のレコードが抽出されます。
foge, name
---------------------
'1001', 'やくそう'
'100Z', '毒消し草'
'101L', '聖水'
'1201', 'ひのきのぼう'
'200A', 'こんぼう'
'30C1', '銅の剣'
---------------------
select hoge
from test
where hoge between '1000' and '4000'
and hoge like '_2%';
検索結果
---------------------
'1201', 'ひのきのぼう'
---------------------
まぁ、こういう質問だったなら、なんですけどね・・・
Re: SQLでのデータの範囲検索について
あいまい検索のことを思い出したので、サブクエリーのことも書いておくことにしました。
行単位にコピペで済ませた結果がこれです。申し訳ないです。
念のため、サブクエリーの例も載せときますね。
サブクエリーを使えば複数のあいまい検索を特定の範囲のレコードに対して行えます。
大抵のものはSQLだけで解決できると思います。
ま・・・求めているものがこういうものだったのかすら定かではないんですけどね・・・・
select * の間違いですね・・・sleep さんが書きました: select hoge
行単位にコピペで済ませた結果がこれです。申し訳ないです。
念のため、サブクエリーの例も載せときますね。
サブクエリーを使えば複数のあいまい検索を特定の範囲のレコードに対して行えます。
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%
---------------------
ま・・・求めているものがこういうものだったのかすら定かではないんですけどね・・・・
Re: SQLでのデータの範囲検索について
sleep さんが書きました:あいまい検索のことを思い出したので、サブクエリーのことも書いておくことにしました。
select * の間違いですね・・・sleep さんが書きました: select hoge
行単位にコピペで済ませた結果がこれです。申し訳ないです。
念のため、サブクエリーの例も載せときますね。
サブクエリーを使えば複数のあいまい検索を特定の範囲のレコードに対して行えます。
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';
大抵のものはSQLだけで解決できると思います。検索結果 --------------------- '1001', 'やくそう' # %1 '100Z', '毒消し草' # %Z '1201', 'ひのきのぼう' # %1 '200A', 'こんぼう' # 2% ---------------------
ま・・・求めているものがこういうものだったのかすら定かではないんですけどね・・・・
ご回答ありがとうございます。
求めているものは、質問にある通り、
between そのものに対して、あいまい検索をできるか、
とういうことです。
ですので、例にあげてくださった
サブクエリーで可能な範疇ということは、わかりました。
ただ、こちらも説明が足りない部分があったため、
補足させて頂きますと、
データは4桁あるけれども、3桁でそのデータを呼び出すということです。
つまり、
100から200を選択すると、
1000から200Zが呼び出すということです。
値はユーザーがその時その時で自由に選択できます。
が、書いていて、sleepさんの考え方でも、
できる気がしてきました。
ちょっと、where句が冗長になりそうですが、
月曜日にまたやってみます。
ただ、気になってるのは
100Zから100Zと同等の値を選択すると
該当データが出てこなかったりもしたので、
そこらへんはやはり、softyaさんが提示してくれた、
考え方も必要かもしれないと思っています。
Re: SQLでのデータの範囲検索について
> データは4桁あるけれども、3桁でそのデータを呼び出すということです。
> つまり、
>
> 100から200を選択すると、
> 1000から200Zが呼び出すということです。
> 値はユーザーがその時その時で自由に選択できます。
手元にMySQLがないので確認してないですが、こういう事では?
select hoge from test where substring(hoge, 1, 3) between '100' and '200';
> つまり、
>
> 100から200を選択すると、
> 1000から200Zが呼び出すということです。
> 値はユーザーがその時その時で自由に選択できます。
手元にMySQLがないので確認してないですが、こういう事では?
select hoge from test where substring(hoge, 1, 3) between '100' and '200';
Re: SQLでのデータの範囲検索について
あー、そんな感じしますね。たいちう さんが書きました: 手元に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', '%'));
Re: SQLでのデータの範囲検索について
たいちろうさん、Sleepさん、Softyaさん
ありがとうございます!
そして、うおおおって、感じになってます。
SQLで関数使う意識が完全になかったのですが・・・
たいちろうさんの、これで、できました。
悩んでた自分の力の無さが悔しいorz
このように、betweenに、
文字列含んでても、ちゃんと呼び出すことができす。
あと、sleepさんの擬似的にLikeなどの、知識もためになりました。
ご回答頂いた皆様、ありがとうございます。
SQLはもっと使い方を勉強しないと、
データを本格的に操作するときに、どうでも良いことで悩んでしまいますね。
精進します(^^;
ありがとうございます!
そして、うおおおって、感じになってます。
SQLで関数使う意識が完全になかったのですが・・・
たいちろうさんの、これで、できました。
悩んでた自分の力の無さが悔しいorz
このように、betweenに、
文字列含んでても、ちゃんと呼び出すことができす。
あと、sleepさんの擬似的にLikeなどの、知識もためになりました。
ご回答頂いた皆様、ありがとうございます。
SQLはもっと使い方を勉強しないと、
データを本格的に操作するときに、どうでも良いことで悩んでしまいますね。
精進します(^^;
Re: SQLでのデータの範囲検索について
解決後ですが一応……。
量や要求速度に依るのですが,関数を使うと,その関数が利用している列のインデックスは使われなくなります。
このため,対象の表の行数が多い場合,関数を使わないで済むような構造にした方が,速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので,使われないのは勿体ないとは思います。
量や要求速度に依るのですが,関数を使うと,その関数が利用している列のインデックスは使われなくなります。
このため,対象の表の行数が多い場合,関数を使わないで済むような構造にした方が,速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので,使われないのは勿体ないとは思います。
と,「複合的な値」と書かれていますが,これが例えば前3桁の分類と最後1桁の枝番のように,複数の要素に分解できることを意味しているのであれば,海Sea さんが書きました:また、扱う値は0~Zまでで、例えば1001や200Zなど複合的な値を持っているので、
- 各要素を別々の列としておく
- 複合インデックスを張る
オフトピック
今時,例えば10000件程度であれば,シーケンシャルアクセスでも十分な速度が出ること多いと思いますが,RDBMSの速度がボトルネックになることはよくあるので……。
Re: SQLでのデータの範囲検索について
なんで最初から関数使わなかったんだとYuO さんが書きました:解決後ですが一応……。
量や要求速度に依るのですが,関数を使うと,その関数が利用している列のインデックスは使われなくなります。
このため,対象の表の行数が多い場合,関数を使わないで済むような構造にした方が,速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので,使われないのは勿体ないとは思います。
と,「複合的な値」と書かれていますが,これが例えば前3桁の分類と最後1桁の枝番のように,複数の要素に分解できることを意味しているのであれば,海Sea さんが書きました:また、扱う値は0~Zまでで、例えば1001や200Zなど複合的な値を持っているので、という変更を行うことで,検索を関数を含む比較から要素単位の単純な比較に変更でき,結果としてインデックスが効くようになります。
- 各要素を別々の列としておく
- 複合インデックスを張る
オフトピック今時,例えば10000件程度であれば,シーケンシャルアクセスでも十分な速度が出ること多いと思いますが,RDBMSの速度がボトルネックになることはよくあるので……。
自分に対して思ってたんですが、
Yuoさんのおっしゃる事情もあって、
何年かくらいSQLの関数ってつかってなかったんですが、
そういうことをすっかり忘れてましたね(^^;)
そもそも、あんまりSQLを大々的に組む機会も無く・・・
あと、データの値は、マスタのテーブルに四つの列で別々に入っており、
トランザクションテーブルには、1つの列に4桁として持っています。
そのため、Yuoさんのご指摘する、各要素を別々の列としておくということは、
現状では厳しい状態で、ひとまず関数を使用して、様子を見ます・・・orz
Re: SQLでのデータの範囲検索について
YuOさんが助言くださっているこちらは現状でも実践可能ですからね。
逆に、最高値側はfogeの下1桁は ~Zまでの存在するレコードが全て抜き出されてくることが期待されているわけです。
つまり、SQLにbindして投げる前から こうなることは分かっている話なので、SQLへbindする前に
最低値の下1桁には 0
最高値の下1桁には Z
をそれぞれ最初から付加しておいて渡してあげれば良いだけだということになります。
そうすればインデックスを効かせた betweenが現状でも使用可能となります。
仮に以下の あいまい検索可能なbetween 構文があったとします。 以下のような指定ができるとしたら 最低値側はfogeの下1桁は 0~の存在するレコードが全て抜き出されてくることが期待されているわけです。YuO さんが書きました: 対象の表の行数が多い場合,関数を使わないで済むような構造にした方が,速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので,使われないのは勿体ないとは思います。
逆に、最高値側はfogeの下1桁は ~Zまでの存在するレコードが全て抜き出されてくることが期待されているわけです。
つまり、SQLにbindして投げる前から こうなることは分かっている話なので、SQLへbindする前に
最低値の下1桁には 0
最高値の下1桁には Z
をそれぞれ最初から付加しておいて渡してあげれば良いだけだということになります。
そうすればインデックスを効かせた betweenが現状でも使用可能となります。
Re: SQLでのデータの範囲検索について
bind前にコード側で0やZを別で付加してしまうと、sleep さんが書きました:YuOさんが助言くださっているこちらは現状でも実践可能ですからね。仮に以下の あいまい検索可能なbetween 構文があったとします。 以下のような指定ができるとしたら 最低値側はfogeの下1桁は 0~の存在するレコードが全て抜き出されてくることが期待されているわけです。YuO さんが書きました: 対象の表の行数が多い場合,関数を使わないで済むような構造にした方が,速度的に有利になる場合があります。
BETWEENはインデックスが効く演算なので,使われないのは勿体ないとは思います。
逆に、最高値側はfogeの下1桁は ~Zまでの存在するレコードが全て抜き出されてくることが期待されているわけです。
つまり、SQLにbindして投げる前から こうなることは分かっている話なので、SQLへbindする前に
最低値の下1桁には 0
最高値の下1桁には Z
をそれぞれ最初から付加しておいて渡してあげれば良いだけだということになります。
そうすればインデックスを効かせた betweenが現状でも使用可能となります。
可能性は非常に少ないですが、
もしもデータの持ち方に変更があったときに面倒なこともあるため、
そこは悩みどころだったんです。実際問題。
それをやると、似たようなSQLを使用してる場合も、
全ての画面に対して修正がかかる可能性もあるのが、ちょっと微妙でして(^^;)
SQLの中だけでのことだったら、SQLのみを修正すれば良いので。
本当に細かいことですし、
そこまで気にする必要もないかもしれませんが、
個人的にはできるだけ回避したいやり方だと、思った次第です。
ただ、実際はStringでSQLを持っています。
なのでコード側といっても、極端な差は無いので、
あとでリファクタリングする時に、Sleepさんの仰る方法に変更がかかるかもしれません。