「这是我参与11月更文挑战的第2天，活动详情查看：2021最后一次更文挑战」

窗口函数

RowNumber

特点是▁▁▁

分页排序可以使用row_bumber。1、2、3、4、5、6……

#按照金额排序后序号
select o.order_amount, 
row_number() over(order by o.order_amount desc) rn
from yp_dwb.dwb_order_detail o;

#排序后取top10
select * from
(
    select o.order_amount, 
    row_number() over( order by o.order_amount desc) rn
    from yp_dwb.dwb_order_detail o
) num
where num.rn <=10;

#先按照店铺分组，然后按照金额排序
select * from
(
    select o.store_id, o.order_amount, 
    row_number() over(partition by o.store_id order by o.order_amount desc) rn
    from yp_dwb.dwb_order_detail o
) num
where num.rn <=10;

Rank

特点是▁▁▁

学生按成绩排名可以使用。1、2、3、4、5、5、5、8、9。

select o.order_amount, 
row_number() over(order by o.order_amount desc) rn,
rank() over(order by o.order_amount desc) rn_rank
from yp_dwb.dwb_order_detail o;

select * from
(
    select o.order_amount, 
    row_number() over( order by o.order_amount desc) rn,
    rank() over( order by o.order_amount desc) rn_rank
    from yp_dwb.dwb_order_detail o
) num
where num.rn_rank <=10;


select * from
(
    select o.store_id, o.order_amount, 
    row_number() over(partition by o.store_id order by o.order_amount desc) rn,
    rank() over(partition by o.store_id order by o.order_amount desc) rn_rank
    from yp_dwb.dwb_order_detail o
) num
where num.rn_rank <=10;

Dense_RANK

特点是▁▁▁

在Rank的基础上，不会跳数据：1、2、3、4、5、5、5、6、7。

select o.order_amount, 
row_number() over(order by o.order_amount desc) rn,
rank() over(order by o.order_amount desc) rn_rank,
dense_rank() over(order by o.order_amount desc) rn_dense_rank
from yp_dwb.dwb_order_detail o;

select * from
(
    select o.order_amount, 
    row_number() over( order by o.order_amount desc) rn,
    rank() over( order by o.order_amount desc) rn_rank,
    dense_rank() over( order by o.order_amount desc) rn_dense_rank
    from yp_dwb.dwb_order_detail o
) num
where num.rn_dense_rank <=10;


select * from
(
    select o.store_id, o.order_amount, 
    row_number() over(partition by o.store_id order by o.order_amount desc) rn,
    dense_rank() over(partition by o.store_id order by o.order_amount desc) rn_dense_rank
    from yp_dwb.dwb_order_detail o
) num
where num.rn_dense_rank <=10;

Ntile

特点是▁▁▁

分区，序号就是区号。根据入参分成对应数量的区，每个分区相差最多是1。

select o.order_amount, 
row_number() over(order by o.order_amount desc) rn,
rank() over(order by o.order_amount desc) rn_rank,
dense_rank() over(order by o.order_amount desc) rn_dense_rank,
ntile(500) over(order by o.order_amount desc) rn_ntile
from yp_dwb.dwb_order_detail o;

select * from
(
    select o.order_amount, 
    row_number() over( order by o.order_amount desc) rn,
    rank() over( order by o.order_amount desc) rn_rank,
    dense_rank() over( order by o.order_amount desc) rn_dense_rank,
    ntile(500) over( order by o.order_amount desc) rn_ntile
    from yp_dwb.dwb_order_detail o
) num
where num.rn_ntile <=10;


select * from
(
    select o.store_id, o.order_amount, 
    row_number() over(partition by o.store_id order by o.order_amount desc) rn,
    rank() over(partition by o.store_id order by o.order_amount desc) rn_rank,
    dense_rank() over(partition by o.store_id order by o.order_amount desc) rn_dense_rank,
    ntile(500) over(partition by o.store_id order by o.order_amount desc) rn_ntile
    from yp_dwb.dwb_order_detail o
) num
where num.rn_ntile <=10;

窗口函数那些事

窗口函数

RowNumber

Rank

Dense_RANK

Ntile