Оператор sql case на основе количества вхождений

Этот подход использует внутренний запрос для получения счетчиков, а затем оператор case для интерпретации счетчиков как домена или строки 'Other' в зависимости от ситуации. Протестировал его на некоторых игровых данных, чтобы убедиться, что он работает, но у меня нет мнения о его производительности.

Это кажется немного неудобным, потому что t1 запрашивается дважды; один раз, чтобы получить домен, и снова, чтобы получить количество. Несмотря ни на что, он выполняет свою работу.

Вы можете поменять число 100 на другое число (или переменную), если изменится конкретный порог.

select 
  t2.Opened
, t2.Email
, case when t3.cntDomain > 100 then t3.domain else 'Other' end as domain
from t2
left outer join t1 on t2.Email = t1.email
left outer join (
    select t1.domain, count(1) cntDomain
    from t1
    left outer join t2 on t1.email = t2.email
    group by t1.domain
) as t3 on t1.domain = t3.domain

Изменить

Если вам не нравятся операторы case, этот подход может показаться более элегантным. Внутренний запрос модифицируется оператором having. Теперь, из-за левого соединения, t3.domain будет нулевым в том случае, если количество меньше порогового значения. Добавьте немного ISNULL в оператор select для объединения нулей, и вы получите деньги.

select 
  t2.Opened
, t2.Email
, ISNULL(t3.domain, 'Other')
from t2
left outer join t1 on t2.Email = t1.email
left outer join (
    select t1.domain, count(1) cntDomain
    from t1
    left outer join t2 on t1.email = t2.email
    group by t1.domain
    having count(1) > 100
) as t3 on t1.domain = t3.domain

Ваше здоровье!

Неясно, все ли электронные письма в первой таблице находятся во второй. Если они есть, вы можете сделать:

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;

Ожидайте, что производительность в этой версии будет очень, очень плохой.

Я думаю, что ниже запрос должен решить вашу проблему

       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