Categories: Window

PERCENT_RANK

Returns the relative rank of the current row in the partition based on the ORDER BY clause. The displayed percentage ranges from 0.0 to 1.0.

Syntax

PERCENT_RANK() OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression]) → double

• partition_expression: An optional expression that groups rows into partitions.
• order_expression: An optional expression that specifies the order of the rows within each partition.

Examples

SELECT "Category", 
  "Descript", 
  "DayOfWeek",
  PERCENT_RANK() 
    OVER (
      PARTITION BY "Category" 
      ORDER BY "DayOfWeek")
FROM Samples."samples.dremio.com"."SF_incidents2016.json"

-- Category, Descript, DayOfWeek, EXPR$3
-- ARSON, ARSON, Friday, 0.0 
-- ARSON, ARSON, Monday, 0.1368421052631579

Usage Notes

The example uses Dremio’s Samples Data Lake, which includes sample datasets that you can practice with. You must manually add this data lake. To add in Dremio Cloud, see Add Dremio’s Samples Data Lake to Your Project. Guidance to add in Dremio Software is in development.