You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2014/11/09 17:21:33 UTC

[jira] [Commented] (CALCITE-370) Support GROUPING SETS in SQL and algebra (AggregateRel)

    [ https://issues.apache.org/jira/browse/CALCITE-370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14203977#comment-14203977 ] 

Julian Hyde commented on CALCITE-370:
-------------------------------------

Started work in https://github.com/julianhyde/incubator-calcite/tree/calcite-370.

My plan is to generalize the Aggregate class to have a list of grouping sets. If you use simple {{GROUP BY x, y}} you will get just one grouping set as if you had written {{GROUP BY GROUPING SETS (x, y)}}. The output row type will include indicator columns, one for each distinct grouping expression (see the {{GROUPING}} function, [https://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#i1007434]).

{code}
public class Aggregate extends SingleRel {
  public final BitSet groupKey;
  public final List<BitSet> groupSets;
  public final GroupingType groupingType;

  enum GroupingType {
    SINGLE, // one grouping set
    ROLLUP, // roll up leading edge: (x, y, z), (x, y), (x), ()
    CUBE, // the full 2^n grouping sets
    OTHER // not one of the above
}
{code}

The row type for {{select k0, k1, sum(c) as a0, sum(d) as a1, sum(e) as a2 from t group k0, k1}} would be {{(k0, g0, k1, g1, a0, a1, a2)}}. Note the indicator columns g0, g1. g0 evaluates {{GROUPING(k0)}}, saying whether this row is a roll up over all g0 values.

Existing rules will have to be changed to only fire if groupingType == SINGLE, and skip over the indicator columns.

> Support GROUPING SETS in SQL and algebra (AggregateRel)
> -------------------------------------------------------
>
>                 Key: CALCITE-370
>                 URL: https://issues.apache.org/jira/browse/CALCITE-370
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>
> Support grouping sets in the relational algebra. This feature would add a new field, groupKeys, to aggregate rel that has a set of bit maps, each of which has a grouping key.
> For example, if 'group by x, y, z' is represented by
> {code}
> AggregateRel(groupSet={0, 1, 2})
> {code}
> then 'group by grouping sets (x, y, z), (y, z), ()' would be represented by
> {code}
> AggregateRel(groupSet={0, 1, 2}, groupSets=[ {0, 1, 2}, {1, 2}, {} ])
> {code}
> A regular group by, or a group by with a singleton grouping set, would have groupSets = null.
> If groupSets is present, the returned row type would include an indicator column for each grouping key.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)