SQL按标识符Cust_ID计数行:我需要在2个条件下计数行.Teradata

Question

目标：统计同时收到电子邮件1和电子邮件2的客户数量。

数据：

Cust Id  Email 
Tom      Email 1
Ben      Email 1
Tom      Email 2
Tom      Email 2
Jason    Email 3
Ben      Email 2

我试过了：

Select count(cust_id)
from WORK_TBLS_LOADS.aw_fpq_ajm_current
Where Email = 'Email 2' 
and Email = 'Email 3'
Select count(cust_id), touch
from WORK_TBLS_LOADS.aw_fpq_ajm_current
having count(email)
group by 1
Where email = 'Email 1' 
and email = 'Email 2'
Select cust_id, count(email)
from WORK_TBLS_LOADS.aw_fpq_ajm_current
Where email = 'Email 2' 
and email = 'Email 3'
group by 1

预期结果：应将Tom和Ben计算为总共5个

count
5

我知道我错过了一些简单的东西，非常感谢你的帮助！

Answer 1

您现有的查询不起作用，因为电子邮件的值不能同时为同一行中的"电子邮件1"one_answers"电子邮件2"。您正在寻找的是关系划分，可以使用条件聚合来实现。但根据GROUP BY，这将导致2(客户收到这些电子邮件组合(。基于窗口聚合的相同逻辑返回行数：

with cte as 
( select t.*
-- flag rows matching a single mail
,max(case when email = 'Email 1' then 1 else 0 end) over (partition by cust_id) as flag_1
,max(case when email = 'Email 2' then 1 else 0 end) over (partition by cust_id) as flag_2
,max(case when email = 'Email 3' then 1 else 0 end) over (partition by cust_id) as flag_3
from WORK_TBLS_LOADS.aw_fpq_ajm_current AS t
-- filter emails to be matched
where email in ('Email 1','Email 2','Email 3')
-- filter for customers with combinations of emails
qualify flag_1 + flag_2 = 2
or flag_2 + flag_3 = 2
or flag_1 + flag_3 = 2
)
select count(*)
from cte
;

编辑，根据您的评论如果我想要一个不同的客户数量，我会：：select count(distinct cust_id(吗

是的，这是可行的，但与传统的GROUP BY方法相比，它的开销更大。相同的逻辑，但在CTE:中为每个匹配的客户返回一行

with cte as 
( select cust_id
,max(case when email = 'Email 1' then 1 else 0 end) as flag_1
,max(case when email = 'Email 2' then 1 else 0 end) as flag_2
from WORK_TBLS_LOADS.aw_fpq_ajm_current AS t
where email in ('Email 1','Email 2')
group by 1
having flag_1 + flag_2 = 2
)
select count(*)
from cte
;

Answer 2

你似乎想数电子邮件，而不是客户：

select count(*)
from t
where exists (select 1 from t t2 where t2.custid = t.custid and email = 'Email 1') and
exists (select 1 from t t2 where t2.custid = t.custid and email = 'Email 2') ;

Answer 3

你说你想计数客户，但似乎你真的想以某种方式计数行。毕竟，也许条件聚合就是你想要的。类似的东西

with cte as
-- conditional aggregation by Cust_ID 
( select Cust_ID,
,sum(case when email = 'Email 1' then 1 else 0 end) as email_1_count
,sum(case when email = 'Email 2' then 1 else 0 end) as email_2_count
,sum(case when email = 'Email 3' then 1 else 0 end) as email_3_count
from WORK_TBLS_LOADS.aw_fpq_ajm_current
group by Cust_ID
)
-- summarize to just total counts
select count(*) as cust_count, sum(email_1_count+email_2_count) as row_count
from cte
where email_1_count > 0 and email_2_count > 0
;

Answer 4

我不知道一般情况。通过附加更多的union all查询，您可以很容易地添加到其中。

如果您的"；匹配集"；有任何重叠。这个想法主要是一种简单的方法，可以将单个查询的结果与总数相加的假设相结合。很难确定你的大图要求是什么。

with data as (
select count(*) as c
from WORK_TBLS_LOADS.aw_fpq_ajm_current
where Email in ('Email 1' ,'Email 2')
group by Cust_Id
having count(distinct Email) = 2 -- because there are two emails in this match
union all
select count(*) as c
from WORK_TBLS_LOADS.aw_fpq_ajm_current
where Email in ('Email 2' ,'Email 3')
group by Cust_Id
having count(distinct Email) = 2
)
select sum(c) from data

Answer 5

这可以同时获得客户数量和电子邮件数量。这样做的方法是——我们只保留那些共享这两个电子邮件ID的客户。然后我们聚合。

select count(distinct id) as num_customer, 
count(*) as num_emails
from t a
where exists (select 1 from t b
where b.id=a.id
and email in ('Email 1', 'Email 2') 
group by id
having count(distinct email)=2);

演示