按标识符cust\u id对行进行sql计数:我需要使用2个条件对行进行计数天睿资讯

aelbi1ox  于 2021-07-27  发布在  Java
关注(0)|答案(5)|浏览(438)

目标:统计同时收到电子邮件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

预期结果:汤姆和本共5人

count
  5

我知道我错过了一些简单的,帮助是非常感谢!

ippsafx7

ippsafx71#

您现有的查询不起作用,因为email的值不能同时为同一行中的“email 1”和“email 2”。您要寻找的是所谓的关系划分,可以使用条件聚合来实现。但根据分组,这将导致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
;

编辑,根据您的评论,如果我想要一个不同的客户计数,我会::选择计数(不同的客户id)?
是的,这是可行的,但与传统的分组方法相比,它的开销更大。相同的逻辑,但在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
;
2admgd59

2admgd592#

你似乎想统计电子邮件,而不是客户:

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') ;
8ljdwjyq

8ljdwjyq3#

你说你想计算客户数,但似乎你真的想以某种方式计算行数。说到底,条件聚合就是您要寻找的。像这样的

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
;
jucafojl

jucafojl4#

我不知道一般情况。您可以通过添加更多 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
kgqe7b3p

kgqe7b3p5#

这会让你们两个——客户数和电子邮件数。其工作方式是——我们只保留那些共享这两个电子邮件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);

演示

相关问题