Sql case case, основанный на количестве случаев

У меня есть таблица (t1) с адресами электронной почты, пользователями и доменными именами:

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

И еще одна таблица (t2) о том, было ли отправлено электронное сообщение на адрес:

Opened Email
 0 [removed_email]
 1 [removed_email]
 0 [removed_email]
 ... ...

Я хотел бы присоединиться к 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 [removed_email] domain.com
 1 [removed_email] email.net
 0 [removed_email] other 
 ... ...

но я не могу понять соединение (я предполагаю, что это будет левое соединение для создания "другого" значения для редко встречающихся значений) и case case, необходимых для присоединения к значению, если оно встречается более 100x, а если не значение ' Другие'.

4 ответа

Этот подход использует внутренний запрос для получения счетчиков, а затем оператор 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

редактировать

Если вам не нравятся заявления о случаях, этот подход может чувствовать себя более элегантно. Внутренний запрос модифицирован, having выражение. Теперь из-за левого соединения t3.domain будет null, если счетчик меньше порога. Добавьте немного 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


select *
from table2 t2
inner join
(
 SELECT domain, count(1) cntDomain
 from table1
 group by domain
 having count(1) > 100
) t1 on t2.email = t1.email

licensed under cc by-sa 3.0 with attribution.