[FEATURE] Span() / bin should support auto_date_histogram aggregation

[noCharger]

Is your feature request related to a problem?

Currently, PPL's span() function with fixed intervals doesn't provide the same flexibility as OpenSearch's native auto_date_histogram aggregation. This leads to:

1. Suboptimal bucket distribution for time series analysis (as 1000 fixed)
2. Need for manual tuning of time intervals
3. Potential for too many or too few buckets
4. Inconsistent results between PPL and DSL queries

For example, this PPL query uses a fixed 1-hour interval:

source = custom-big5 
| eval range_bucket = case(
    `metrics.size` < -10, 'range_1', 
    `metrics.size` >= -10 and `metrics.size` < 10, 'range_2',
    `metrics.size` >= 10 and `metrics.size` < 100, 'range_3',
    `metrics.size` >= 100 and `metrics.size` < 1000, 'range_4',
    `metrics.size` >= 1000 and `metrics.size` < 2000, 'range_5',
    `metrics.size` >= 2000, 'range_6'
) 
| stats min(`metrics.tmin`) as tmin, avg(`metrics.size`) as tavg, max(`metrics.size`) as tmax 
    by range_bucket, span(`@timestamp`, 1h) as auto_span

While the equivalent DSL query uses auto_date_histogram for optimal bucket distribution:

{
  "aggs": {
    "tmax": {
      "range": {
        "field": "metrics.size",
        "ranges": [...]
      },
      "aggs": {
        "date": {
          "auto_date_histogram": {
            "field": "@timestamp",
            "buckets": 20
          }
        }
      }
    }
  }
}

What solution would you like?

Implement auto_date_histogram support in PPL with:

New function syntax for auto-bucketing:

source = custom-big5 
| stats count() by auto_span(`@timestamp`, buckets=20)

Key features:

• Dynamic interval selection based on data range
• Target bucket count specification
• Automatic interval rounding to human-readable values
• Consistent bucket count regardless of time range

What alternatives have you considered?

Enhanced span() function with auto mode:

span(`@timestamp`, 'auto', target_buckets=20)

Hybrid approach with min/max intervals:

adaptive_span(`@timestamp`, buckets=20, min_interval='1m', max_interval='1d')

[qianheng-aws]

The testing data:

{"index": {}}
{"@timestamp":"2025-09-18T03:23:47.322"}
{"index": {}}
{"@timestamp":"2025-09-18T03:24:17.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:24:47.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:25:17.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:25:47.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:26:17.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:26:47.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:27:17.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:27:47.323"}
{"index": {}}
{"@timestamp":"2025-09-18T03:28:17.324"}
{"index": {}}
{"@timestamp":"2025-09-18T03:28:47.324"}
{"index": {}}
{"@timestamp":"2025-09-18T03:29:17.324"}
{"index": {}}
{"@timestamp":"2025-09-18T03:29:47.314"}
{"index": {}}
{"@timestamp":"2025-09-18T03:30:17.314"}
{"index": {}}
{"@timestamp":"2025-09-18T03:30:47.314"}
{"index": {}}
{"@timestamp":"2025-09-18T03:31:17.314"}
{"index": {}}
{"@timestamp":"2025-09-18T03:31:47.314"}
{"index": {}}
{"@timestamp":"2025-09-18T03:32:17.315"}
{"index": {}}
{"@timestamp":"2025-09-18T03:32:47.325"}
{"index": {}}
{"@timestamp":"2025-09-18T03:33:17.315"}

The behavior between auto_date_histogram and bins in SPL has gaps:

1. auto_date_histogram starts with the value rounded by one unit, while bins starts with the value rounded by span. For example:

source=test | bin @timestamp bins=3 | stats count() by @timestamp 

auto_date_histogram gets results:

        [
            9,
            "2025-09-18 03:23:00"
        ],
        [
            10,
            "2025-09-18 03:28:00"
        ],
        [
            1,
            "2025-09-18 03:33:00"
        ]

2025-09-18 03:23:00 is rounded by 1min

while bins gets results:

2025/09/18 03:20:00 | 3
2025/09/18 03:25:00 | 10
2025/09/18 03:30:00 | 7

2025/09/18 03:20:00 is rounded by 5min

2. auto_date_histogram always follows the policy that The actual number of buckets returned is always less than or equal to the number of buckets you specify., while bin gets number of buckets exceeding that parameter. For exmaple:

source=test | bin @timestamp bins=10 | stats count() by @timestamp 

auto_date_histogram keep the same results as above, while bins get 11 buckets, which is greater than 10.

3. auto_date_histogram will keep the empty buckets while bins will only show non-empty buckets. For example:

source=test | bin @timestamp bins=1000 | stats count() by @timestamp 

auto_date_histogram gets results:

  [
    1,
    "2025-09-18 03:23:47"
  ],
  [
    0,
    "2025-09-18 03:23:48"
  ],
  ..
  [
     1,
     "2025-09-18 03:33:17"
  ]
]
"total": 571,
"size": 571

while bin gets results:

2025/09/18 03:23:47 | 1
2025/09/18 03:24:17 | 1
...
2025/09/18 03:33:17 | 1

This gap could be addressed by adding an additional FILTER(COUNT() > 0).