[ESQL] EXACT_ Functions

[BenB196]

Description

Feature Request

Introduce the concept of EXACT_ prefixed functions that operate similar to their "approximate" counterpart functions, but focus on accuracy over speed.

Use-case

Elasticsearch has well documented that functions things like COUNT_DISTINCT are approximate as it is a trade-off for speed. However, as ES|QL is intended for a large number of use-cases, it'd be nice to have the ability to perform the opposite and trade speed for accuracy. This is only partially possible today with the COUNT_DISTINCT function as there is an upper-bound limit on precision. It would be useful to have a short-hand function built into ES|QL like, EXACT_COUNT_DISTINCT that would do this. Accurate counts are possible today in regular DSL, but are somewhat verbose, as they require scripted metrics to be used. Having something similar in ES|QL (while also being "simpler") would be a nice value add.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[nik9000]

Sounds like a good idea. I like the EXACT_ prefix. It's informative and compatible with what we have. More traditional databases have gone with APPROXIMATE_ prefixes for the approximate versions but we didn't do that years and years ago when we built our aggs. Arguably an approximate algorithm is a better default at the scales we're talking about anyway. But that doesn't matter. The EXACT_ prefix gives us a good recipe for when we add exact versions of these functions.

I'm not sure when we will build them, but here's a list of the approximate algorithms I remember:

• EXACT_COUNT_DISTINCT
• EXACT_PERCENTILE
• EXACT_MEDIAN_ABSOLUTE_DEVIATION