postgreSQLの正規表現

質問

いつもお世話になってますurizakaです。

さて、現在postgreSQL7.1（環境はLinux、言語はJava)を使ってツールを作って
いるのですが、その中でIPアドレスとそのドメインを入力したテーブルm_hostが
あるのですが（以下のような内容のデータが入っています）

ip domein
128.155.35.42 http://www.sample.ne.jp
128.166.34.69 http://www.sando.com/index.html
199.231.12.36 http://paoo.co.jp/search?q=axieosenoe

これらのテーブルから、domeinの「http://」の後で、次の「/」までの部分で、
最後の文字部分がjpのものが何件あるか？といったデータを集計したいの
ですが、これはどのようにすればよいのでしょうか？JavaやVBだったら、
「/」で区切った後に、「.」で区切って最後の配列の要素を…といったプログラム
を組むのですが、なるべくDB側で処理してしまいたいのです。
postgreSQLには正規表現の機能があるそうなので、それを使って結果を集計
できればと思うのですが…

すみませんが、宜しくお願いします。

回答No.2

kusukusu

jp|com|net
3|5|7
↑こんな形じゃ駄目ですか？

SELECT (SELECT COUNT(domain) from table_name where domain~'^http://[^/]*\.jp/') AS jp,
(SELECT COUNT(domain) from table_name where domain~'^http://[^/]*\.com/') AS com,
(SELECT COUNT(domain) from table_name where domain~'^http://[^/]*\.com/') AS net;

お礼

urizakaです。
皆さん返事が遅れてすみません。

さて、今回の件ですが、なるべくSQL一文で済ませてプログラムの方の
メンテナンスを容易に・・・と考えたのですが、どうもこれはプログラム側
で解決した方が良いようです。（登録から時間が経ったデータは検索がし易い
ようにストアドプロシジャーに生成させて、検索専用の別のテーブルに
まとめて収めるとして）
そこで、kusukusuさんのいうようなSQL文をプログラムに自動生成させて、
それで検索を行うことにします。

どうもありがとうございました。

回答No.1

a-kuma

演算子 ~ (チルダ) を正規表現にマッチするかどうかに使います。
「正規表現」については分かりますか？

> domeinの「http://」の後で、次の「/」までの部分で、
> 最後の文字部分がjpのもの

は、正規表現で表すと、'^http://[^/]*\.jp/' になるはずです。

これで引っ掛けて domain をとるのであれば、

select domain where domain ~ '^http://[^/]*\.jp/'

という感じかな。件数だけでよければ count を使えば良いですね。

＃ domein ではなく domain ね　:-)

補足

urizakaです。ご返答ありがとうございます。
domeinではなくdomainですよね、つい間違えてしまいました…
正規表現については、まだあまりよく分かりません。SQL文との組み合わせ
という点では、皆目使い方が検討がつかない状態です。

さて、上記の件ですが、すみません、質問の書き方が悪かったです。
私が集計したいのは

*****ip*****|*****domein**********
--------------------------------------------------
191.35.166.1| http://www.mosuko.com
192.36.155.1| http://www.itirou.co.jp/q=1111
192.35.155.1| http://www.fireman.co.jp/index

とかあった時に、ドメインに.comがついているデータは何件あるか？、
.jpがついているデータは何件あるか、という一覧表です。

例）
****domein****|***count****
----------------------------
.jp | 10
.com | 20
.net | 10

↑このようなデータです。顧客リストDBのメールアドレスのドメインについて
調査するアプリケーションを作っているので…

こういったデータはSQL文で集計できるのでしょうか？あるいはストアドプロシ
ジャーを使うとか…

すみません、宜しくお願いします。