Add SQL GROUPING, GROUPING_ID functions
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
See discussion in https://github.com/apache/druid/pull/9122, especially these comments:
- https://github.com/apache/druid/pull/9122#issuecomment-589828549
- https://github.com/apache/druid/pull/9122#issuecomment-590652648
It would be good to include GROUPING and GROUPING_ID functions like those documented here: https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-id-transact-sql?view=sql-server-ver15.
The SQL standard allows them anywhere an aggregation function is allowed:
- SELECT clause, as long as they are not as an input to an aggregation function
- HAVING clause
- ORDER BY clause
I think we'd want to expose these as an expression function that is usable in postaggregators. Then havingFilters and limitSpecs could refer to them. This will require some new context being made available to postaggregators that is not currently available (perhaps a virtual field?).
Btw, adding these plus joins on subqueries would allow Calcite's `AggregateExpandDistinctAggregatesRule.INSTANCE` to work properly. It uses those two features to plan queries with exact count distincts on multiple columns (something we currently don't support in Druid SQL, and would be great to support).
Contributor guide
Research direction
Read the linked PR #9122 discussion and the SQL Server GROUPING_ID documentation first. Then trace Druid's postaggregators, havingFilters, and limitSpecs to understand how GROUPING and GROUPING_ID could be exposed and what aggregation context is missing. Done means supporting the functions in the stated SELECT, HAVING, and ORDER BY cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- backend-api-design, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100