apache / apache/druid

Add SQL GROUPING, GROUPING_ID functions

Open
#9,410 0 comments 0 reactions 0 assignees View on GitHub
Area - SQL Feature/Change Description
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.