こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

SQLでどのように書いたら教えてください。

列Aに「aaa.co.jp」「bbb.com」「ccc.ne.jp」「ddd.jp」「eee.it」があり列Bでドメインの判別をしたいと考えています。ドメインは「co」「com」「ne」「jp」「その他」の5種類があり、aaa.co.jp→co、bbb.com→com、ccc.ne.jp→ne、ddd.jp→jp、eee.it→その他となるようにしたい時、どのようなSQLを組めばいいのでしょうか?ややこしいのは、aaa.co.jpの時は「jp」ではなく「co」で、ccc.ne.jpも「jp」ではなく「ne」で判別しますが、ddd.jpの場合は「jp」になります。実際にはもっと膨大な種類のドメインを判別するのですが、考え方や使うべき関数、SQLを教えてもらえないでしょうか?アクセス解析のプログラムなのですが行き詰ってしまいました。 分かりにくい説明ですいません。

投稿日時 - 2006-01-23 23:10:01

QNo.1916854

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

8人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

見にくいです&ログが NULL の場合に対処が必要ですが、
だいたいこんな感じで書けるでしょう。
ドメインの振り分けルールのマスタテーブルが必要

select d, name
from (
select
d, name,
row_number() over (partition by d order by pr desc) rnum
from (
select
data.d, d_type.name, d_type.d_name, d_type.pr
from (
select 'aaa.co.jp' d from dual
union all
select 'ccc.ne.jp' d from dual
union all
select 'ddd.jp' d from dual
union all
select 'bbb.com' d from dual
union all
select 'eee.it' d from dual
union all
select 'localhost' d from dual
union all
select '192.168.0.1' d from dual
) data ,
(
-- priority, rule, name
select 4 pr, '%.co.__' d_name, 'co.xx' name from dual
union all
select 4 pr, '%.ne.__' d_name, 'ne.xx' name from dual
union all
select 3 pr,'%.jp' d_name, 'jp' name from dual
union all
select 3 pr,'%.com' d_name, 'com' name from dual
union all
select 2 pr,'_%._%._%._%' d_name, 'may be ip address' name from dual
union all
select 1 pr,'%.%' d_name, 'host.domain' name from dual
union all
select 0 pr,'_%' d_name, 'host' name from dual
) d_type
where
data.d like d_type.d_name
)
)
where rnum = 1

投稿日時 - 2006-01-24 16:27:32

列Aを見て、5種類のいずれかを判断すれば良いのですよね?

バージョンによって最適な解決策が変わるような気がします。

10Gなら、正規表現が使えるので、それを使った方が良いでしょう。
8i~9iであれば、jakartaのoroを導入すると、正規表現が使えます。
(大げさになってしまうけど・・)

9i以下で、それなりにドメイン(というのかな?)を識別するなら、
次のような判断で良いと思います。

decode
(1,
decode(substrb(A,lengthb(A) - 3,4),'.com',1),'com',
decode(substrb(A,lengthb(A) - 5,lengthb(A) - 5),'.co.',1),'co',
decode(substrb(A,lengthb(A) - 5,lengthb(A) - 5),'.ne.',1),'ne',
decode(substrb(A,lengthb(A) - 2,3),'.jp',1),'jp',
'その他'
)

判断する順番に気を付ければ、良いだけの話ではないですか?

投稿日時 - 2006-01-24 01:29:31

ANo.2

失礼しました。AAAとBBBに設定する値が逆になってました。

一回目のAAAは'%.co.jp'、BBBは'co'
二回目のAAAは'%.com'、BBBは'com'
三回目のAAAは'%.ne.jp'、BBBは'ne'

ここで、残る.or.jpや.ac.jpなどの処遇を考えてね。

四回目のAAAは'%.jp'、BBBは'jp'

ここで、.govなんかも考えてね

五回目のAAAは'%.it'、BBBは'その他'
(残ってしまったもの、すなわちNULLがすべて'その他'では?)


です。

投稿日時 - 2006-01-24 00:41:45

ANo.1

.or.jpや.ac.jpはどうする?


列BがNULLで、かつ列Aに[AAA]が含まれるとき、列Bに[BBB]を設定する、を複数作成して実行ではどうでしょう?

update <テーブル名> set <列B>='BBB'
where (<列B>is null ) and (lower(<列A>) like '%AAA');

一回目のAAAは'co'、BBBは'%.co.jp'
二回目のAAAは'com'、BBBは'%.com'
三回目のAAAは'ne'、BBBは'%.ne.jp'

ここで、残る.or.jpや.ac.jpなどの処遇を考えてね。

四回目のAAAは'jp'、BBBは'%.jp'

ここで、.govなんかも考えてね

五回目のAAAは'その他'、BBBは'%.it'
(残ってしまったもの、すなわちNULLがすべて'その他'では?)

投稿日時 - 2006-01-24 00:28:23

あなたにオススメの質問