在oracle中按字符串对数据分组( Oracle9i企业版发布9.2.0.4.0)

Question

我有以下查询：

select referrer, count(distinct ad_id) as Adverts,
       sum(case f when 'Y' then hits else 0 end) as clicks,
       sum(case f when 'N' then hits else 0 end) as views
from advert_view_hits
where ad_id in ({$id_strings})
group by referrer"

这将返回如下数据：

 Referrer  Adverts  Clicks  Views
 Caterer     3       124     74
 Indeed      5       234     136

这很好，但在某些情况下，引用程序已经存储在数据库中，如下所示：

user1@jwrecruitment.co.uk_200890,
user2@jwrecruitment.co.uk_200890

user1@gatewayjobs.co.uk_200890, 
user3@towngate-personnel.co.uk_2

我将如何进行分组的数据，仅仅基于公司的用户电子邮件，已被用作推荐人。

所以数据应该是这样的：

Referrer             Adverts  Clicks  Views
 Caterer               3       124     74
 Indeed                5       234     136
 jwrecruitment.co.uk   8       456     782 
 gatewayjobs.co.uk     9       897     959

这样，像jwrecruitment.co.uk这样的电子邮件的所有数据都会被分组显示。

Answer 1

还可以使用regexp_substr查找@和_之间的字符串，如下所示：

select REGEXP_SUBSTR(referrer,'@([^_]+)',1,1,NULL,1) referrer, 
       count(distinct ad_id) as Adverts,
       sum(case f when 'Y' then hits else 0 end) as clicks,
       sum(case f when 'N' then hits else 0 end) as views
  from advert_view_hits
where ad_id in ({$id_strings})
group by REGEXP_SUBSTR(referrer,'@([^_]+)',1,1,NULL,1)

如果您使用的是旧版本，那么使用SUBSTR和INSTR的组合代替regexp_substr，如下所示：

SUBSTR(referrer, 
       INSTR(referrer, '@') + 1, 
       DECODE(INSTR(referrer, '_', - 1), 
              0, 
              LENGTH(referrer) - INSTR(referrer, '@'), 
              INSTR(referrer, '_', - 1) - INSTR(referrer, '@') - 1)
      )

Answer 2

如果我正确地跟踪了您，您可以使用regexp_replace()：

select 
    regexp_replace(referrer, '^.*@([^_]+).*$', '\1') referrer, 
    count(distinct ad_id) as Adverts,
    sum(case f when 'Y' then hits else 0 end) as clicks,
    sum(case f when 'N' then hits else 0 end) as views
from advert_view_hits
where ad_id in ({$id_strings})
group by regexp_replace(referrer, '^.*@([^_]+).*$', '\1')

regexp匹配包含arobas的引用程序，并在arobas之后和下一个下划线之前捕获该部分。如果引用程序与正则表达式不匹配，则保持不变。

Answer 3

如果我理解正确的话，你可以在@之后把所有的东西都拿出来--你可以用regexp_substr()来完成这个任务。

select regexp_substr(referrer, '[^@]+$') as referrer, count(distinct ad_id) as Adverts,
       sum(case f when 'Y' then hits else 0 end) as clicks,
       sum(case f when 'N' then hits else 0 end) as views
from advert_view_hits
where ad_id in ({$id_strings})
group by regexp_substr(referrer, '[^@]+$') ;

可以将regexp_substr()逻辑替换为：

select substr(referrer, instr(referrer, '@') + 1) as referrer, count(distinct ad_id) as Adverts,
       sum(case f when 'Y' then hits else 0 end) as clicks,
       sum(case f when 'N' then hits else 0 end) as views
from advert_view_hits
where ad_id in ({$id_strings})
group by  substr(referrer, instr(referrer, '@') + 1) ;