扫码加入数据分析学习群

-- 查询每个用户订单金额最高的前三个订单

select

*,

row_number() over(partition by user_no order by amount desc) as 订单金额排名1,

rank() over(partition by user_no order by amount desc) as 订单金额排名2,

dense_rank() over(partition by user_no order by amount desc) as 订单金额排名3

from order_tab;

#当over中指定了排序，但是没有指定滑动窗口范围时，默认是基于值计算，计算当前分区内的第一行排序字段对应值到当前行排序字段对应值范围内的记录

select

*,

dense_rank() over(partition by user_no order by amount desc) as 订单金额排名

from order_tab

where dense_rank() over(partition by user_no order by amount desc)<=3;#报错：where子句中不能使用开窗函数

select *

from

(select

*,

dense_rank() over(partition by user_no order by amount desc) as 订单金额排名

from order_tab) as t

where 订单金额排名<=3;