See discussion in #9122, especially these comments:
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).
See discussion in #9122, especially these comments:
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:
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.INSTANCEto 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).