WINDOW FUNCTION PERCENT_RANK

description

PERCENT_RANK()是一个窗口函数，用于计算分区或结果集中行的百分位数排名。

下面展示了PERCENT_RANK()函数的语法：

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

PERCENT_RANK()函数返回一个范围从0.0到1.0的小数。

对于指定行，PERCENT_RANK()计算公式如下：

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

在此公式中，rank是指定行的排名，total_rows是正在评估的行数。

对于分区或结果集中的第一行，PERCENT_RANK()函数始终返回零。对于重复的列值，PERCENT_RANK()函数将返回相同的值。

与其他窗口函数类似，PARTITION BY子句将行分配到分区中，并且ORDER BY子句指定每个分区中行的排序逻辑。PERCENT_RANK()函数是针对每个有序分区独立计算的。

PERCENT_RANK()是一个顺序敏感的函数，因此，您应该始终需要使用ORDER BY子句。

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