SQL查询-选择值出现25次以上的结果记录

Question

以下查询正常工作：

select  o.SubscriberKey , o.JobID,  CAST(o.EventDate AS Date) AS 'OpenDate'
FROM _Open o
where o.IsUnique = 1 and (o.EventDate between 'Jun 06 2018' and 'Dec 06 2018')
GROUP BY o.SubscriberKey , o.JobID, o.EventDate

现在我需要修改它，只选择在结果中出现25次或更多次的SubscriberKeys。我想我可以使用Count函数，但我不确定该从哪里开始。

Answer 1

使用窗口函数：

select o.*
from (select o.SubscriberKey, o.JobID, CAST(o.EventDate AS Date) AS OpenDate,
count(*) over (partition by  o.SubscriberKey) as cnt
from _Open o
where o.IsUnique = 1 and (o.EventDate between 'Jun 06 2018' and 'Dec 06 2018')
group by o.SubscriberKey, o.JobID, o.EventDate
) o
where cnt >= 25