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
pivottransform, the function is computed for each instance of the value specified in thegroupparameter. 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.