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