WINDOW FUNCTION PERCENT_RANK

description

The PERCENT_RANK() is a window function that calculates the percentile rank of a row within a partition or result set.

The following shows the syntax of the PERCENT_RANK() function:

1. PERCENT_RANK() OVER (
2. PARTITION BY partition_expression
3. ORDER BY
4. sort_expression [ASC | DESC]
5. )

The PERCENT_RANK() function returns a number that ranges from zero to one.

For a specified row, PERCENT_RANK() calculates the rank of that row minus one, divided by 1 less than the number of rows in the evaluated partition or query result set:

1. (rank - 1) / (total_rows - 1)

In this formula, rank is the rank of a specified row and total_rows is the number of rows being evaluated.

The PERCENT_RANK() function always returns zero for the first row in a partition or result set. The repeated column values will receive the same PERCENT_RANK() value.

Similar to other window functions, the PARTITION BY clause distributes the rows into partitions and the ORDER BY clause specifies the logical order of rows in each partition. The PERCENT_RANK() function is calculated for each ordered partition independently.

Both PARTITION BY and ORDER BY clauses are optional. However, the PERCENT_RANK() is an order-sensitive function, therefore, you should always use the ORDER BY clause.

example

1. // create table
2. CREATE TABLE test_percent_rank (
3. productLine VARCHAR,
4. orderYear INT,
5. orderValue DOUBLE,
6. percentile_rank DOUBLE
7. ) ENGINE=OLAP
8. DISTRIBUTED BY HASH(`orderYear`) BUCKETS 4
9. PROPERTIES (
10. "replication_allocation" = "tag.location.default: 1"
11. );

13. // insert data into table
14. INSERT INTO test_percent_rank (productLine, orderYear, orderValue, percentile_rank) VALUES
15. ('Motorcycles', 2003, 2440.50, 0.00),
16. ('Trains', 2003, 2770.95, 0.17),
17. ('Trucks and Buses', 2003, 3284.28, 0.33),
18. ('Vintage Cars', 2003, 4080.00, 0.50),
19. ('Planes', 2003, 4825.44, 0.67),
20. ('Ships', 2003, 5072.71, 0.83),
21. ('Classic Cars', 2003, 5571.80, 1.00),
22. ('Motorcycles', 2004, 2598.77, 0.00),
23. ('Vintage Cars', 2004, 2819.28, 0.17),
24. ('Planes', 2004, 2857.35, 0.33),
25. ('Ships', 2004, 4301.15, 0.50),
26. ('Trucks and Buses', 2004, 4615.64, 0.67),
27. ('Trains', 2004, 4646.88, 0.83),
28. ('Classic Cars', 2004, 8124.98, 1.00),
29. ('Ships', 2005, 1603.20, 0.00),
30. ('Motorcycles', 2005, 3774.00, 0.17),
31. ('Planes', 2005, 4018.00, 0.50),
32. ('Vintage Cars', 2005, 5346.50, 0.67),
33. ('Classic Cars', 2005, 5971.35, 0.83),
34. ('Trucks and Buses', 2005, 6295.03, 1.00);

36. // query
37. SELECT
38. productLine,
39. orderYear,
40. orderValue,
41. ROUND(
42. PERCENT_RANK()
43. OVER (
44. PARTITION BY orderYear
45. ORDER BY orderValue
46. ),2) percentile_rank
47. FROM
48. test_percent_rank
49. ORDER BY
50. orderYear;

52. // result
53. +------------------+-----------+------------+-----------------+
54. | productLine      | orderYear | orderValue | percentile_rank |
55. +------------------+-----------+------------+-----------------+
56. | Motorcycles      |      2003 |     2440.5 |               0 |
57. | Trains           |      2003 |    2770.95 |            0.17 |
58. | Trucks and Buses |      2003 |    3284.28 |            0.33 |
59. | Vintage Cars     |      2003 |       4080 |             0.5 |
60. | Planes           |      2003 |    4825.44 |            0.67 |
61. | Ships            |      2003 |    5072.71 |            0.83 |
62. | Classic Cars     |      2003 |     5571.8 |               1 |
63. | Motorcycles      |      2004 |    2598.77 |               0 |
64. | Vintage Cars     |      2004 |    2819.28 |            0.17 |
65. | Planes           |      2004 |    2857.35 |            0.33 |
66. | Ships            |      2004 |    4301.15 |             0.5 |
67. | Trucks and Buses |      2004 |    4615.64 |            0.67 |
68. | Trains           |      2004 |    4646.88 |            0.83 |
69. | Classic Cars     |      2004 |    8124.98 |               1 |
70. | Ships            |      2005 |     1603.2 |               0 |
71. | Motorcycles      |      2005 |       3774 |             0.2 |
72. | Planes           |      2005 |       4018 |             0.4 |
73. | Vintage Cars     |      2005 |     5346.5 |             0.6 |
74. | Classic Cars     |      2005 |    5971.35 |             0.8 |
75. | Trucks and Buses |      2005 |    6295.03 |               1 |
76. +------------------+-----------+------------+-----------------+

keywords

1. WINDOW,FUNCTION,PERCENT_RANK