MYSQL的执行顺序如下所示：
from
on
join
where
group by
having
select
distinct
union
order by
可以看到，select是在where后执行的，也就是说，select下的窗口函数也是在where之后执行的，所以如果涉及窗口函数的筛选，要在往外一层进行查询。
下面是一个例子，注意注释内容。
SQL172 10月的新户客单价和获客成本
1 select round(avg(amt),1) as avg_amount, 2 round(avg(youhui_price),1) as avg_cost 3 from 4 ( 5 select uid, avg(total_amount) as amt, 6 (sum(price*cnt)-avg(total_amount)) as youhui_price 7 from( 8 9 select t1.uid, 10 t1.order_id, 11 t1.event_time, 12 t1.total_amount, 13 t2.price, 14 t2.cnt 15 from 16 ( 17 18 select uid, order_id, date(event_time) as event_time, total_amount, 19 row_number() over(partition by uid order by event_time) as rk 20 from tb_order_overall 21 # 注意条件筛选要在row_number所在的外层，因为执行顺序是where->row_number, 22 # 如果在下一行筛选，当用户A存在0901和1001的两个订单时，筛选后1001还会被视为新用户首单，但实际上首单是0901的订单 23 24 # where date_format(event_time, '%Y%m')=202110 25 # and status=1 26 ) t1 27 right join 28 tb_order_detail t2 29 on t1.order_id=t2.order_id 30 where rk=1 31 and date_format(event_time, '%Y%m')=202110 32 ) tmp1 33 group by 1 34 ) tmp2