osdir.com

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: "single or null" aggregation function for elastic


Thanks for the hint, Julian.
Unfortunately min / max aggregations work only on numeric types
<https://www.elastic.co/guide/en/elasticsearch/reference/current/number.html>
in elastic.

On Thu, Dec 27, 2018 at 1:28 AM Julian Hyde <jhyde.apache@xxxxxxxxx> wrote:

> case when max(x) = min(x) then max(x) end
>
> Julian
>
> > On Dec 26, 2018, at 16:22, Andrei Sereda <andrei@xxxxxxxxx> wrote:
> >
> > Hello,
> >
> > 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
> >   null).
> >   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
> > <
> https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-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 ?
> >
> > Regards,
> > Andrei.
>