[ES|QL] High accuracy cardinality aggregation

[wchaparro]

Description

Use case: financial services users need to use ES (Kibana) for precise high cardinality counts (millions of accounts) for daily reporting needs. While the current cardinality aggregation works fast and supports very large datasets it is not precise.
It would be very useful if ES|QL supported a precise cardinality calculation via a new aggregation. In the past we have closed requests for this kind of functionality due to our policy that all features that we implement need to work and scale in a distributed environment. ES|QL opens new opportunities for potentially solving this because We can support long running queries via async_search and we have a way to store intermediate results via ES|QL which means the memory risk is much lower.

See elastic/kibana#179934, related: #60149

[elasticsearchmachine]

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