rank()/dense_rank()函数实现了分区内部排序的功能。
对于数值相同的行,rank()/dense_rank()给出了相同的数字,
row_number函数处理方式则不同,该函数将会给出不同的数字,从而实现数据的大排行
副作用是相同数值的行,将有不同的序号。
语法如下:
row_number() over(partition by ... order by ...)
示例如下:
--nth_value
select id_,
year,week,product,
sale,
rank() over (
partition by product,region,country,year
order by sale
) rank_sale,
dense_rank() over (
partition by product,region,country,year
order by sale
) dense_rank_sale,
row_number() over (
partition by product,region,country,year
order by sale
) as row_number_sale
from sales_fact
where product='product1' and country='country1' and region='region1'
order by product,country,year,week;
结果如下:
ID_ YEAR WEEK PRODUCT SALE RANK_SALE DENSE_RANK_SALE ROW_NUMBER_SALE
---------- ---------- ---------- -------------------- ------------ ---------- --------------- ---------------
1 2000 1 product1 2003.10 1 1 1
301 2000 2 product1 2004.10 2 2 2
601 2000 3 product1 2005.10 3 3 3
901 2000 4 product1 2006.10 4 4 4
1201 2000 5 product1 2007.10 5 5 5
1501 2000 6 product1 2008.10 6 6 7
1801 2000 7 product1 2008.10 6 6 6
2101 2000 8 product1 2010.10 8 7 8
2401 2000 9 product1 2011.10 9 8 9
2701 2000 10 product1 2012.10 10 9 10
绿色部分为sale相同的行,rank(),dense_rank()表现一样,而row_number()则表现不同
红色行中,row_number则是完成重新排序