Contents:
- If a row contains a missing or null value, it is not factored into the calculation. If the entire column contains no values, the function returns a null value.
- When used in a
pivot
transform, the function is computed for each instance of the value specified in thegroup
parameter. See Pivot Transform.
Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
percentile(myScores, 25, linear)
Output: Computes the value that is at the 25th percentile across all values in the myScores column.
Syntax and Arguments
percentile(function_col_ref,num_percentile,interpolation_method) [group:group_col_ref] [limit:limit_count]
Argument | Required? | Data Type | Description |
---|---|---|---|
function_col_ref | Y | string | Name of column to which to apply the function |
num_percentile | Y | integer | Integer value between 1-100 of the percentile to compute |
interpolation_method | Y | enum | Method by which to interpolate values between two row values. See below. |
For more information on the group
and limit
parameters, see Pivot Transform.
For more information on syntax standards, see Language Documentation Syntax Notes.
function_col_ref
Name of the column the values of which you want to calculate the percentile. Column must contain Integer or Decimal values.
- Literal values are not supported as inputs.
- Multiple columns and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | String (column reference) | precipitationIn
|
num_percentile
Name of the column the values of which you want to calculate the percentile. Column must contain Integer or Decimal values.
- Literal values are not supported as inputs.
- Multiple columns and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | Integer | 55 |
interpolation_method
Method of interpolation between each discrete value. The list of support methods is the following:
Interpolation method | Description |
---|---|
linear | Percentiles are calculated between two discrete values in a linear fashion. |
exclusive | Excludes 0 and 1 from calculation of percentiles. |
inclusive | Includes 0 and 1 from calculation of percentiles. |
lower | Use the lower value when the computed value falls between two discrete values. |
upper | Use the upper value when the computed value falls between two discrete values. |
midpoint | Use the midpoint value when the computed value falls between two discrete values. |
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | Enum | linear
|
Tip: For additional examples, see Common Tasks.Examples
Example - Percentile functions
MEDIAN
- Calculate the median value from a column of values. See MEDIAN Function.PERCENTILE
- Calculate a specified percentile for a column of values. See PERCENTILE Function.QUARTILE
- Calculate a specified quartile for a column of values. See QUARTILE Function.
Source:
The following table lists each student's height in inches:
Student | Height |
---|---|
1 | 64 |
2 | 65 |
3 | 63 |
4 | 64 |
5 | 62 |
6 | 66 |
7 | 66 |
8 | 65 |
9 | 69 |
10 | 66 |
11 | 73 |
12 | 69 |
13 | 69 |
14 | 61 |
15 | 64 |
16 | 61 |
17 | 71 |
18 | 67 |
19 | 73 |
20 | 66 |
Transformation:
Use the following transformations to calculate the median height in inches, a specified percentile and the first quartile.
Median: This transformation calculates the median value, which corresponds to the 50th percentile.
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | median(heightIn) |
Parameter: New column name | 'medianIn' |
Percentile: This transformation calculates the 68th percentile.
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | percentile(heightIn, 68, linear) |
Parameter: New column name | 'percentile68In' |
Quartile:This transformation calculates the first quartile, which corresponds to the 25th percentile.
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | quartile(heightIn, 1, linear) |
Parameter: New column name | 'percentile25In' |
Results:
Student | Height | percentile25In | percentile68In | medianIn |
---|---|---|---|---|
1 | 64 | 65.75 | 68.92 | 67.5 |
2 | 65 | 65.75 | 68.92 | 67.5 |
3 | 63 | 65.75 | 68.92 | 67.5 |
4 | 64 | 65.75 | 68.92 | 67.5 |
5 | 62 | 65.75 | 68.92 | 67.5 |
6 | 66 | 65.75 | 68.92 | 67.5 |
7 | 66 | 65.75 | 68.92 | 67.5 |
8 | 65 | 65.75 | 68.92 | 67.5 |
9 | 69 | 65.75 | 68.92 | 67.5 |
10 | 66 | 65.75 | 68.92 | 67.5 |
11 | 73 | 65.75 | 68.92 | 67.5 |
12 | 69 | 65.75 | 68.92 | 67.5 |
13 | 69 | 65.75 | 68.92 | 67.5 |
14 | 61 | 65.75 | 68.92 | 67.5 |
15 | 64 | 65.75 | 68.92 | 67.5 |
16 | 61 | 65.75 | 68.92 | 67.5 |
17 | 71 | 65.75 | 68.92 | 67.5 |
18 | 67 | 65.75 | 68.92 | 67.5 |
19 | 73 | 65.75 | 68.92 | 67.5 |
20 | 66 | 65.75 | 68.92 | 67.5 |
This page has no comments.