sql - 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 次或更多的订阅者密钥。我想我可以使用 Count 函数，但我不确定从那里去哪里。

标签： sqlsalesforce

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