Re: "single or null" aggregation function for elastic
case when max(x) = min(x) then max(x) end
> On Dec 26, 2018, at 16:22, Andrei Sereda <andrei@xxxxxxxxx> wrote:
> I’m looking for a way to simulate “single or null” (on distinct values)
> aggregation function in elastic.
> Example of a query
> -- for multiple distinct values return nullselect date,
> single_value(value) from table group by date
> Some Options
> 1. ANY_VALUE. For multiple values returns one of them (I need null).
> 2. SINGLE_VALUE. For multiple (or empty) values throws exception (I need
> 3. COLLECT / JSON_ARRAYAGG (see below).
> 4. Manually add column COUNT(distinct ...) and post-process the result.
> COLLECT / JSON_ARRAYAGG
> select date, collect(distinct value) from table group by date
> This query might potentially work but the problem with elastic is that it
> doesn’t return all values by default (similar issue to scrolling. see size
> Currently, scrolling is not possible with aggregations.
> Therefore I have to impose usage of DISTINCT / LIMIT with COLLECT :
> -- enforce DISTINCT and LIMITselect date, collect(distinct value limit
> 2) from table group by date
> Do you think it is reasonable to enforce such restriction to make it work
> with elastic ?
> Adding COUNT function
> One can append count(distinct value) aggregation and check if it is equal
> to 1.
> -- use combination of ANY_VALUE and COUNTselect date,
> any_value(value), count(distinct value) from table group by date
> What do you think ?