在以下查询中,我得到了活跃在" 2017-09-01-01 00:00:00"one_answers" 2017-11-11-31 23:59:59)之间的客户计数"作为CUST_90,想添加另一列以查找" 2017-11-01-01 00:00:00"one_answers" 2017-11-11-31 23:59:59"之间活跃的客户计数(整个时期)。
select custid, count(distinct concat(visit1, visit2)) as cust_90
from test1
where date_time between "2017-09-01 00:00:00" and "2017-11-31 23:59:59"
and custid = '234214124'
group by custid;
样本输出:
CustomerName cust_90 cust_30
David 38 15
想知道我是否可以在上述查询中有一个子查询,以在一个月内找到活跃的客户。任何建议都很棒。
这称为条件聚合,可以使用case
表达式完成。
select custid,
count(distinct concat(visit1, visit2) end) as cust_90,
count(distinct case when to_date(date_time)>='2017-11-01' then concat(visit1, visit2) end) as cust_30
from test1
where date_time >= '2017-09-01' and date_time < '2017-12-01'
and custid = '234214124'
group by custid;