Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL: Allow MIN() and MAX() aggregate functions to operate also on arguments of type keyword #35639

Closed
MagnusAnde opened this issue Nov 16, 2018 · 2 comments
Closed

SQL: Allow MIN() and MAX() aggregate functions to operate also on arguments of type keyword #35639

MagnusAnde opened this issue Nov 16, 2018 · 2 comments
Assignees
@matriv
Labels
:Analytics/SQL SQL querying >feature

Comments

@MagnusAnde
Copy link

MagnusAnde commented Nov 16, 2018 •
edited by costin
Loading

Allow MIN() and MAX() aggregate functions to operate
also on arguments of type keyword (and eventually also text) apart from the numeric and date.

This would simplify queries using GROUP BY returning columns of type keyword (and eventually text) that is not part of the group by statement. This would also harmonize with the expected behavior from persons used to sql in for example Sql Server.

select max(fieldOfTypeKeyword) from someIndex group by otherFieldOfTypeKeyword

This would return the last value in alphabetic order for the field fieldOfTypeKeyword in the group defined by the group by statement. The min() function would do the inverse.
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search-aggs

@costin
Copy link
Member

costin commented Nov 16, 2018

added minor formatting to your post

Thanks for the suggestion. This would essentially translate MAX and MIN into a more generic FIRST and LAST aggregate function (as it expands beyond numeric types and thus their meaning is slightly different).

We could support this through a terms agg with a size of 1.

@matriv matriv self-assigned this Jan 7, 2019
matriv added a commit to matriv/elasticsearch that referenced this issue Jan 28, 2019
@matriv
SQL: Implement FIRST/LAST aggregate functions
1be7ba1 
FIRST and LAST can be used with one argument and work similarly to MIN
and MAX but they are implemented using a Top Hits aggregation and
therefore can also operate on keyword fields. When a second argument is
provided then they return the first/last value of the first arg when its
values are ordered ascending/descending (respectively) by the values of
the second argument. Currently because of the usage of a Top Hits
aggregation FIRST and LAST cannot be used in the HAVING clause of a
GROUP BY query to filter on the results of the aggregation.

Closes: elastic#35639
@matriv matriv mentioned this issue Jan 28, 2019
Merged
@matriv matriv changed the title Allow MIN() and MAX() aggregate functions to operate also on arguments of type keyword SQL: Allow MIN() and MAX() aggregate functions to operate also on arguments of type keyword Jan 28, 2019
@matriv matriv mentioned this issue Jan 28, 2019
Closed
matriv added a commit that referenced this issue Jan 31, 2019
@matriv
SQL: Implement FIRST/LAST aggregate functions (#37936)
4710a74 
FIRST and LAST can be used with one argument and work similarly to MIN
and MAX but they are implemented using a Top Hits aggregation and
therefore can also operate on keyword fields. When a second argument is
provided then they return the first/last value of the first arg when its
values are ordered ascending/descending (respectively) by the values of
the second argument. Currently because of the usage of a Top Hits
aggregation FIRST and LAST cannot be used in the HAVING clause of a
GROUP BY query to filter on the results of the aggregation.

Closes: #35639
matriv added a commit that referenced this issue Jan 31, 2019
@matriv
SQL: Implement FIRST/LAST aggregate functions (#37936)
0b794d4 
FIRST and LAST can be used with one argument and work similarly to MIN
and MAX but they are implemented using a Top Hits aggregation and
therefore can also operate on keyword fields. When a second argument is
provided then they return the first/last value of the first arg when its
values are ordered ascending/descending (respectively) by the values of
the second argument. Currently because of the usage of a Top Hits
aggregation FIRST and LAST cannot be used in the HAVING clause of a
GROUP BY query to filter on the results of the aggregation.

Closes: #35639
@astefan astefan mentioned this issue Apr 15, 2019
Closed
4 tasks
@stfaun stfaun mentioned this issue Sep 23, 2020
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
:Analytics/SQL SQL querying >feature
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@costin @matriv @elasticmachine @MagnusAnde