在where子句中添加条件

select t.* except(rn)
from (
    select t.*, rank() over(partition by a_id order by count desc) rn
    from mytable t where count>=2000
) t
where rn = 1

在bigquery中，您可以不使用子查询，通过使用聚合：

select (array_agg(t order by count desc limit 1))[ordinal(1)].*
from t
where count >= 2000
group by timestamp_trunc(timestamp, month);

如果要使用窗口函数，另一种方法是：

select t.*
from (select t.*,
             row_number() over (partition by timestamp_trunc(timestamp, month) order by count desc) as seqnum
      from t
      where count >= 2000
     ) t
where seqnum = 1;