sql case оператор въз основа на броя на срещанията

Имам таблица (t1) с имейл адреси, потребители и имена на домейни:

     email                user         domain
[email protected]        joe123        domain.com
[email protected]         sue234        email.net
      ...                  ...          ...

И друга таблица (t2) за това дали имейл, изпратен до адрес, е отворен:

  Opened             Email
    0            [email protected]
    1            [email protected]
    0            [email protected]
   ...               ...

Бих искал да присъединя t1.domain към t2, но само домейни, които се срещат повече от 100x.

Мога да създам таблица с брой срещания

SELECT domain, count(domain) cntDomain
from table1
group by domain

с резултат като този:

   domain         cntDomain
 domain.com       5000
 email.net        4300
 mybarber.com     67

получената таблица ще изглежда така:

  Opened             Email                 domain
    0            [email protected]         domain.com
    1            [email protected]          email.net
    0            [email protected]       other 
   ...               ...

но не мога да разбера присъединяването (предполагам, че ще бъде ляво присъединяване, за да се създаде стойността „други“ за рядко срещащи се стойности) и инструкцията за случай, необходима за присъединяване към стойността, ако се среща повече от 100x и ако не, стойност от „ друго'.


sql sql-server join case
person screechOwl    schedule 03.11.2014    source източник
comment
имате нужда от having count(*) > 100   -  person crthompson    schedule 04.11.2014

Отговори (3)


arrow_upward
0
arrow_downward

Благодаря ти. Оценявам го.
person kbrimington    schedule 03.11.2014

arrow_upward
0
arrow_downward

Не е ясно дали всички имейли от първата таблица са във втората. Ако са, можете да направите:

select t1.*, t2.domain
from (select t2.*, count(*) over (partition by domain) as cnt
      from table2 t2
     ) t2 join
     table1 t1
     on t1.email = t2.email
where cnt > 100;

Ако не, можем да проверим за домейна в самия имейл адрес:

select t2.*, t1.domain
from table2 t2 left join
     (select t1.domain, count(*) as cnt
      from table1 t1
      group by t1.domain
     ) t1
     on t2.email like '%@' + t1.domain and
        cnt > 100;

Очаквайте производителността на тази версия да бъде наистина, наистина лоша.

person Gordon Linoff    schedule 03.11.2014
comment
Може да искате да направите първото условие за свързване на втората заявка t2.email like '%@' + t1.domain, за да запазите поддомейните разделени. - person Allan; 04.11.2014
comment
@Алан. . . Това има много смисъл. Благодаря ти. Това също засяга неща като gmail.com срещу mail.com. - person Gordon Linoff; 04.11.2014

arrow_upward
0
arrow_downward

Мисля, че заявката по-долу трябва да реши проблема ви

       SELECT t2.opened,
       t2.Email,
       CASE WHEN tempt1.email is NULL THEN 'Other' ELSE tempt1.domain END as domain
       FROM t2 LEFT JOIN (SELECT email,domain
       FROM t1
       group by domain HAVING  count(domain)>100) tempt1 on t2.Email=tempt1.email
person Navneet    schedule 04.11.2014