opensearch-project / opensearch-project/sql
[META] PPL aggregation function enhancement
Open
@dai-chen is already working on this.
Since Sep 3, 2025.
enhancement
Meta
PPL
v3.3.0
- Dominant language
- Java
- Stars
- 176
- Forks
- 229
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 43
Description
Is your feature request related to a problem?
Yes. PPL already provides many aggregation functions in stats and eventstats command, but the existing implementations have functional gaps and meanwhile several important functions are missing. These gaps limit common log-analytics workflows and prevent full parity with SPL. This issue proposes a phased enhancements as outlined in the scope table below.
What solution would you like?
For each item below, either (1) close gaps in the existing implementation or (2) add the missing function.
| Category | Priority | Function | Description |
|---|---|---|---|
| Aggregate functions | P0 | count() | Returns the number of occurrences where the field contains any value (is not empty). You can also count a specific value using eval inside count, e.g., count(eval(field_name="value")). |
| Aggregate functions | P0 | sum() | Returns the sum of the values in the field specified. |
| Aggregate functions | P0 | avg() | Returns the average of the values in the field specified. |
| Aggregate functions | P0 | distinct_count() | Returns the count of distinct values in the field specified. |
| Aggregate functions | P0 | max() | Returns the maximum value in the field. If values are non-numeric, uses lexicographical ordering; processes as numbers when possible, otherwise as strings. |
| Aggregate functions | P0 | percentile() | Returns the N-th percentile of the numeric field. Valid percent values are integers from 1 to 99. Additional percentile functions include upperperc<percentile>(<value>) and exactperc<percentile>(<value>). |
| Time functions | P0 | earliest() | Returns the chronologically earliest (oldest) seen occurrence of a value in a field. |
| Time functions | P0 | latest() | Returns the chronologically latest (most recent) seen occurrence of a value in a field. |
| Multivalue stats and chart functions | P0 | values() | Returns the list of all distinct values in a field as a multivalue entry. The order of the values is lexicographical. |
| Time functions | P1 | per_second() | Returns the values in a field or eval expression for each second. |
| Event order functions | P1 | first() | Returns the first seen value in a field. In general, the first seen value is the most recent instance relative to the input order into the stats command. |
| Event order functions | P1 | last() | Returns the last seen value in a field. In general, the last seen value is the oldest instance relative to the input order into the stats command. |
| Multivalue stats and chart functions | P1 | list() | Returns a list of up to 100 values in a field as a multivalue entry. The order reflects input event order. |
| Aggregate functions | P1 | min() | Returns the minimum value in the field. If values are non-numeric, uses lexicographical ordering. |
| Aggregate functions | P1 | median() | Returns the middle-most value of the field. |
| Aggregate functions | P1 | estdc() | Returns the estimated count of distinct values. |
| Aggregate functions | P1 | estdc_error() | Returns the theoretical error of the estimated distinct count: abs(estimate - real) / real. |
| Aggregate functions | P1 | exactperc() | Returns an exact percentile of the numeric field. Provides exact value but can be very resource-expensive for high cardinality fields. |
| Aggregate functions | P1 | mean() | Returns the arithmetic mean of the field values. |
| Aggregate functions | P1 | mode() | Returns the most frequent value in the field. |
| Aggregate functions | P1 | range() | If numeric, returns the difference between the maximum and minimum values. |
| Aggregate functions | P1 | stdev() | Returns the sample standard deviation of the field values. |
| Aggregate functions | P1 | stdevp() | Returns the population standard deviation of the field values. |
| Aggregate functions | P1 | sumsq() | Returns the sum of squares of the field values. |
| Aggregate functions | P1 | upperperc() | Returns an approximate percentile; for more than 1000 values, gives the approximate upper bound for the requested percentile; otherwise returns the same percentile as perc. |
| Aggregate functions | P1 | var() | Returns the sample variance of the field values. |
| Aggregate functions | P1 | varp() | Returns the population variance of the field values. |
| Time functions | P1 | earliest_time() | Returns the UNIX time of the earliest (oldest) occurrence of the field value; used with earliest, latest, and latest_time to compute rates. |
| Time functions | P1 | latest_time() | Returns the UNIX time of the latest (most recent) occurrence; used with earliest, earliest_time, and latest to compute rates. |
| Time functions | P1 | per_day() | Returns the values in a field or eval expression for each day. |
| Time functions | P1 | per_hour() | Returns the values in a field or eval expression for each hour. |
| Time functions | P1 | per_minute() | Returns the values in a field or eval expression for each minute. |
| Time functions | P1 | rate() | Returns the per-second rate change of the field value: (latest - earliest) / (latest_time - earliest_time). Requires numeric earliest/latest and different times. |
| Time functions | P1 | rate_avg() | Returns the average rates for the time series of an accumulating counter metric. |
| Time functions | P1 | rate_sum() | Returns the summed rates for the time series of an accumulating counter metric. |
What alternatives have you considered?
N/A
Do you have any additional context?
N/A
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.