[FEATURE] PPL `first`/`last` aggregate function

[ykmr1224]

Add first/last aggregate function for PPL (Calcite)

FIRST Function

Syntax: FIRST(field)
Description: Returns the first value of the specified field in natural document order
Parameters:

• field (required): The field name to get the first value from
  Return Type: Same as input field type (nullable)
  Behavior:
• Returns the first occurrence of the field value in the result set
• Uses natural document order (no sorting applied)
• Returns NULL if no records exist or field is NULL in first record

LAST Function

Syntax: LAST(field)
Description: Returns the last value of the specified field in natural document order
Parameters:

• field (required): The field name to get the last value from
  Return Type: Same as input field type (nullable)
  Behavior:
• Returns the last occurrence of the field value in the result set
• Uses reverse natural document order (equivalent to sorting by _doc DESC)
• Returns NULL if no records exist or field is NULL in last record

Usage Examples

# Basic usage
source=logs | stats first(message), last(status) by host

# Combined with other aggregations
source=metrics | stats first(cpu_usage), last(memory_usage), count(), avg(response_time) by server

# Sequential processing after sorting
source=events | sort timestamp | stats first(event_type), last(event_data) by session_id

# With eventstats (if extended)
source=transactions | eventstats first(amount) by account_id

Comparison with Related Functions

Function	Ordering	Use Case	Parameters
FIRST(field)	Natural document order	Sequential processing	Single field
LAST(field)	Reverse document order	Sequential processing	Single field
EARLIEST(field)	Time-based (ASC)	Time-based analysis	Field + optional time field
LATEST(field)	Time-based (DESC)	Time-based analysis	Field + optional time field
TAKE(field, n)	Natural document order	Multiple record retrieval	Field + count

[ykmr1224]

I checked possibility to reuse earliest/latest implementation for first/last.

I strongly recommend using the same approach as the TAKE function rather than leveraging the existing EARLIEST/LATEST implementation. While EARLIEST/LATEST use Calcite’s ARG_MIN/ARG_MAX with time-based sorting, this approach is unsuitable for FIRST/LAST because it introduces unnecessary sorting overhead when natural document order is all that’s needed. The TAKE function already has full OpenSearch pushdown support using the topHits aggregation, which maintains natural document order without sorting and provides optimal performance through native OpenSearch execution. By implementing FIRST/LAST as topHits with size=1 (similar to TAKE but with a fixed size), we can reuse the existing TopHitsParser infrastructure, achieve minimal data transfer, and maintain consistent architecture patterns. This approach provides the best performance characteristics for sequential record processing while avoiding the semantic mismatch and complexity of conditional aggregation functions like ARG_MIN/ARG_MAX.

[dai-chen]

@ahkcs Can we close this now?

[ahkcs]

@ahkcs Can we close this now?

I think we are good to close this, #4223 merged