sql - 计算具有... SQL 的行数

Question

我必须计算 UserId 是否被列出一次或多次，如果一个人把 +1 给“新用户”，否则“+1”给“回访用户”。

我有没有做过:

select 
Count(distinct [UserId]) as 'Unique users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'

/* returning */
select
count(Id) as 'Returning users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
group by [UserId]
having count(Id) > 1

/* returning */
select
count(*) as 'New users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
group by [UserId]
having count(*) = 1 

我需要像第一个查询一样计算“返回”和"new"用户查询中的总行数。怎么做？

Answer 1

您可以使用两个级别的聚合来完成此操作。计算 UserId 处的计数水平，然后使用该信息来获得您想要的计数:

select sum(case when cnt > 1 then 1 else 0 end) as ReturningUsers,
       sum(case when cnt = 1 then 1 else 0 end) as NewUsers
from (select UserId, count(*) as cnt
      from [TelemetryData]
      where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
      group by UserId
     ) u