[
https://issues.apache.org/jira/browse/DRILL-3962?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18028853#comment-18028853
]
ASF GitHub Bot commented on DRILL-3962:
---------------------------------------
cgivre opened a new pull request, #3026:
URL: https://github.com/apache/drill/pull/3026
# [DRILL-3962](https://issues.apache.org/jira/browse/DRILL-3962): Add
Support For ROLLUP, CUBE, GROUPING SETS, GROUPING, GROUPING_ID, GROUP_ID
## Description
In recent versions of SQL, there are several aggregates which create
different summaries of the aggregate data.
Specifically, this PR adds support for:
* `GROUPING SETS`
* `ROLLUP`
* `CUBE`
* `GROUPING()`
* `GROUPING_ID()`
* `GROUP_ID()`
## Documentation
### GROUPING SETS:
`GROUPING SETS` allows you to define multiple independent groupings within a
single `GROUP BY` query. Instead of writing separate queries for each
aggregation, you can specify different column combinations to produce multiple
summary results at once. It gives you fine-grained control over which subtotals
are calculated.
```sql
SELECT department_id, position_title, SUM(salary) AS total_salary
FROM cp.`employee.json`
GROUP BY GROUPING SETS ((department_id), (position_title), ())
```
### ROLLUP
`ROLLUP` generates hierarchical aggregations that move from detailed data to
higher-level summaries. For example, a `ROLLUP` on (year, month, day) produces
totals by day, month, year, and an overall grand total. It’s ideal for creating
reports with cumulative subtotals.
```sql
SELECT position_title, education_level, AVG(salary) AS avg_salary
FROM cp.`employee.json`
GROUP BY ROLLUP (position_title, education_level);
```
### CUBE
`CUBE` creates aggregations for all possible combinations of the specified
columns. It’s useful for multidimensional analysis, such as computing totals
across every dimension and their intersections. This produces the most
comprehensive summary of the data.
```sql
SELECT position_title,
education_level,
AVG(salary) AS avg_salary
FROM cp.`employee.json`
GROUP BY CUBE (position_title, education_level);
```
## Testing
Created additional unit tests.
> Add support of ROLLUP, CUBE, GROUPING SETS, GROUPING, GROUPING_ID, GROUP_ID
> support
> -----------------------------------------------------------------------------------
>
> Key: DRILL-3962
> URL: https://issues.apache.org/jira/browse/DRILL-3962
> Project: Apache Drill
> Issue Type: New Feature
> Reporter: Jinfeng Ni
> Assignee: Charles Givre
> Priority: Major
>
> These functions are important for BI analytical workload. Currently, Calcite
> supports those functions, but neither the planning or execution in Drill
> supports those functions.
> DRILL-3802 blocks those functions in Drill planning. But we should provide
> the support for those functions in both planning and execution of Drill.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
