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

質問

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

回答No.4

MZ-80B

見にくいです＆ログが 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

回答No.3

k_o_r_o_c_h_a_n

列Ａを見て、５種類のいずれかを判断すれば良いのですよね？

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

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',
'その他'
)

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

回答No.2

bin-chan

失礼しました。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がすべて'その他'では？)

です。

回答No.1

bin-chan

.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がすべて'その他'では？)