Skip to content

Support "number of centroids" for approx_percentile_cont_with_weight #16990

Description

@crepererum

Is your feature request related to a problem or challenge?

While approx_percentile_cont supports the number of centroids (last parameter that is optional):

### `approx_percentile_cont`
Returns the approximate percentile of input values using the t-digest algorithm.
```sql
approx_percentile_cont(percentile, centroids) WITHIN GROUP (ORDER BY expression)
```
#### Arguments
- **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators.
- **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
- **centroids**: Number of centroids to use in the t-digest algorithm. _Default is 100_. A higher number results in more accurate approximation but requires more memory.
#### Example
```sql
> SELECT approx_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+-----------------------------------------------------------------------+
| approx_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) |
+-----------------------------------------------------------------------+
| 65.0 |
+-----------------------------------------------------------------------+
```

approx_percentile_cont_with_weight does not:

### `approx_percentile_cont_with_weight`
Returns the weighted approximate percentile of input values using the t-digest algorithm.
```sql
approx_percentile_cont_with_weight(weight, percentile) WITHIN GROUP (ORDER BY expression)
```
#### Arguments
- **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators.
- **weight**: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.
- **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
#### Example
```sql
> SELECT approx_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+---------------------------------------------------------------------------------------------+
| approx_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) |
+---------------------------------------------------------------------------------------------+
| 78.5 |
+---------------------------------------------------------------------------------------------+
```

That is somewhat surprising/inconsistent.

Describe the solution you'd like

Add an optional last parameter similar to approx_percentile_cont.

Describe alternatives you've considered

No supporting this.

Additional context

-

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

enhancementNew feature or request

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions