Spark SQL - PERCENT_RANK Window Function

About PERCENT_RANK function

PERCENT_RANK in Spark returns the percentile of rows within a window partition.  

PERCENT_RANK without partition

The following sample SQL uses PERCENT_RANK function without PARTITION BY clause:

SELECT StudentScore.*, PERCENT_RANK() OVER (ORDER BY Score) AS Percentile FROM VALUES 
(101,56),
(102,78),
(103,70),
(104,93),
(105,95),
(106,95),
(107,75),
(108,81),
(109,66),
(110,73)
AS StudentScore(Student,Score)

Result:

+-------+-----+------------------+
|Student|Score|        Percentile|
+-------+-----+------------------+
|    101|   56|               0.0|
|    109|   66|0.1111111111111111|
|    103|   70|0.2222222222222222|
|    110|   73|0.3333333333333333|
|    107|   75|0.4444444444444444|
|    102|   78|0.5555555555555556|
|    108|   81|0.6666666666666666|
|    104|   93|0.7777777777777778|
|    105|   95|0.8888888888888888|
|    106|   95|0.8888888888888888|
+-------+-----+------------------+

PERCENT_RANK with partition

The following sample SQL returns a rank number for each records in each window (defined by PARTITION BY):

SELECT StudentScore.*, PERCENT_RANK() OVER (PARTITION BY Class ORDER BY Score) AS Percentile FROM VALUES 
(1,101,56),
(1,102,78),
(1,103,70),
(1,104,93),
(1,105,95),
(2,106,95),
(2,107,75),
(2,108,81),
(2,109,66),
(2,110,73)
AS StudentScore(Class,Student,Score)

Result:

+-----+-------+-----+----------+
|Class|Student|Score|Percentile|
+-----+-------+-----+----------+
|    1|    101|   56|       0.0|
|    1|    103|   70|      0.25|
|    1|    102|   78|       0.5|
|    1|    104|   93|      0.75|
|    1|    105|   95|       1.0|
|    2|    109|   66|       0.0|
|    2|    110|   73|      0.25|
|    2|    107|   75|       0.5|
|    2|    108|   81|      0.75|
|    2|    106|   95|       1.0|
+-----+-------+-----+----------+

Records are allocated to windows based on Class column and the rank is computed based on column Score.  

Example table

The virtual table/data frame is cited from SQL - Construct Table using Literals.