PERCENT_RANK
更新时间：2025-10-16
描述
PERCENT_RANK() 是一个窗口函数，用于计算分区或结果集中行的相对排名，返回值范围从 0.0 到 1.0。对于给定的行，其计算公式为：(rank - 1) / (total_rows - 1)，其中 rank 是当前行的排名，total_rows 是分区中的总行数。
语法
SQL
1PERCENT_RANK()返回值
返回 DOUBLE 类型的数值，范围从 0.0 到 1.0：
- 对于分区内的第一行，始终返回 0
- 对于分区内的最后一行，始终返回 1
- 对于相同的值，返回相同的百分比排名
举例
SQL
1CREATE TABLE test_percent_rank (
2 productLine VARCHAR,
3 orderYear INT,
4 orderValue DOUBLE,
5 percentile_rank DOUBLE
6) ENGINE=OLAP
7DISTRIBUTED BY HASH(`orderYear`) BUCKETS 4
8PROPERTIES (
9"replication_allocation" = "tag.location.default: 1"
10);
SQL
1INSERT INTO test_percent_rank (productLine, orderYear, orderValue, percentile_rank) VALUES
2('Motorcycles', 2003, 2440.50, 0.00),
3('Trains', 2003, 2770.95, 0.17),
4('Trucks and Buses', 2003, 3284.28, 0.33),
5('Vintage Cars', 2003, 4080.00, 0.50),
6('Planes', 2003, 4825.44, 0.67),
7('Ships', 2003, 5072.71, 0.83),
8('Classic Cars', 2003, 5571.80, 1.00),
9('Motorcycles', 2004, 2598.77, 0.00),
10('Vintage Cars', 2004, 2819.28, 0.17),
11('Planes', 2004, 2857.35, 0.33),
12('Ships', 2004, 4301.15, 0.50),
13('Trucks and Buses', 2004, 4615.64, 0.67),
14('Trains', 2004, 4646.88, 0.83),
15('Classic Cars', 2004, 8124.98, 1.00),
16('Ships', 2005, 1603.20, 0.00),
17('Motorcycles', 2005, 3774.00, 0.17),
18('Planes', 2005, 4018.00, 0.50),
19('Vintage Cars', 2005, 5346.50, 0.67),
20('Classic Cars', 2005, 5971.35, 0.83),
21('Trucks and Buses', 2005, 6295.03, 1.00);
SQL
1SELECT
2 productLine,
3 orderYear,
4 orderValue,
5 ROUND(
6 PERCENT_RANK()
7 OVER (
8 PARTITION BY orderYear
9 ORDER BY orderValue
10 ),2) percentile_rank
11FROM
12 test_percent_rank
13ORDER BY
14 orderYear;
Text
1+------------------+-----------+------------+-----------------+
2| productLine | orderYear | orderValue | percentile_rank |
3+------------------+-----------+------------+-----------------+
4| Motorcycles | 2003 | 2440.5 | 0 |
5| Trains | 2003 | 2770.95 | 0.17 |
6| Trucks and Buses | 2003 | 3284.28 | 0.33 |
7| Vintage Cars | 2003 | 4080 | 0.5 |
8| Planes | 2003 | 4825.44 | 0.67 |
9| Ships | 2003 | 5072.71 | 0.83 |
10| Classic Cars | 2003 | 5571.8 | 1 |
11| Motorcycles | 2004 | 2598.77 | 0 |
12| Vintage Cars | 2004 | 2819.28 | 0.17 |
13| Planes | 2004 | 2857.35 | 0.33 |
14| Ships | 2004 | 4301.15 | 0.5 |
15| Trucks and Buses | 2004 | 4615.64 | 0.67 |
16| Trains | 2004 | 4646.88 | 0.83 |
17| Classic Cars | 2004 | 8124.98 | 1 |
18| Ships | 2005 | 1603.2 | 0 |
19| Motorcycles | 2005 | 3774 | 0.2 |
20| Planes | 2005 | 4018 | 0.4 |
21| Vintage Cars | 2005 | 5346.5 | 0.6 |
22| Classic Cars | 2005 | 5971.35 | 0.8 |
23| Trucks and Buses | 2005 | 6295.03 | 1 |
24+------------------+-----------+------------+-----------------+