MYSQL执行顺序

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