useful feature.

But I think the grammar of Hive may be a reference, it is a bit different from 
your case:
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )


ref: 
https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup




------------------ ???????? ------------------
??????: "??????"<yangz...@gmail.com>;
????????: 2019??10??24??(??????) ????11:30
??????: "dev"<dev@doris.apache.org>;

????: [Proposal] Enhanced Aggregation, Support Grouping Sets, Rollup andCube to 
extend group by statement



The CUBE, ROLLUP, and GROUPING SETS  extensions to SQL make querying and
reporting easier and faster. CUBE, ROLLUP, and grouping sets produce a
single result set that is equivalent to a UNION ALL of differently grouped
rows.

The three GROUPING functions  make aggregate calculations more efficient,
thereby enhancing database performance, and scalability.

GROUPING SETS clause

GROUPING SETS syntax lets you define multiple groupings in the same
query. GROUP
BY computes all the groupings specified and combines them with UNION ALL.
For example, consider the following statement:

SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), (a),
(b), ( ) );

This statement is equivalent to:

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
UNION
SELECT null, null, SUM( c ) FROM tab1

This is an example of real query??

mysql> SELECT * FROM t;
+------+------+------+
| k1   | k2   | k3   |
+------+------+------+
| a    | A    |    1 |
| a    | A    |    2 |
| a    | B    |    1 |
| a    | B    |    3 |
| b    | A    |    1 |
| b    | A    |    4 |
| b    | B    |    1 |
| b    | B    |    5 |
+------+------+------+
8 rows in set (0.01 sec)

mysql> SELECT k1, k2, SUM(k3) FROM t GROUP BY GROUPING SETS ( (k1,
k2), (k2), (k1), ( ) );
+------+------+-----------+
| k1   | k2   | sum(`k3`) |
+------+------+-----------+
| b    | B    |         6 |
| a    | B    |         4 |
| a    | A    |         3 |
| b    | A    |         5 |
| NULL | B    |        10 |
| NULL | A    |         8 |
| a    | NULL |         7 |
| b    | NULL |        11 |
| NULL | NULL |        18 |
+------+------+-----------+
9 rows in set (0.06 sec)

GROUPING SETS Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]

groupSet ::= { ( expr  [ , expr [ , ... ] ] )}

<expr>
Expression??column name.


*ROLLUP clause*

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals
across a specified group of dimensions. It also calculates a grand total.
ROLLUP is a simple extension to the GROUP BY clause, so its syntax is
extremely easy to use. The ROLLUP extension is highly efficient, adding
minimal overhead to a query.

ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is:

ROLLUP ( e1, e2, e3, ... )

This statement is equivalent to GROUPING SETS as followed??

GROUPING SETS (
( e1, e2, e3, ... ),
...
( e1, e2 ),
( e1 ),
( )
)

 ROLLUP Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( expr  [ , expr [ , ... ] ] )
[ ... ]

<expr>
Expression??column name.

*CUBE clause*


Like ROLLUP CUBE generates all the subtotals that could be calculated for a
data cube with the specified dimensions.

CUBE ( e1, e2, e3, ... )

e.g. CUBE ( a, b, c ) is equivalent to GROUPING SETS as followed:

GROUPING SETS (
( a, b, c ),
( a, b ),
( a,    c ),
( a       ),
(    b, c ),
(    b    ),
(       c ),
(         )
)

CUBE Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY CUBE ( expr  [ , expr [ , ... ] ] )
[ ... ]

<expr>
Expression??column name.

GROUPING_ID() Function

GROUPING_ID describes which of a list of expressions are grouped in a row
produced by a GROUP BY query. GROUPING_ID () returns an integer bitmap
whose lowest N bits may be lit. A lit bit indicates the corresponding
argument is not a grouping column for the given output row. The
lowest-order bit corresponds to argument N, and the N-1th lowest-order
bit corresponds
to argument 1. If the column is a grouping column the bit is 0 else is 1.

For example??

mysql> select * from t;
+------+------+------+
| k1   | k2   | k3   |
+------+------+------+
| a    | A    |    1 |
| a    | A    |    2 |
| a    | B    |    1 |
| a    | B    |    3 |
| b    | A    |    1 |
| b    | A    |    4 |
| b    | B    |    1 |
| b    | B    |    5 |
+------+------+------+

grouping sets result??

mysql> SELECT k1, k2, GROUPING_ID(), SUM(k3) FROM t GROUP BY GROUPING
SETS ( (k1, k2), (k2), (k1), ( ) );
+------+------+---------------+-----------+
| k1   | k2   | grouping_id() | sum(`k3`) |
+------+------+---------------+-----------+
| a    | A    |             0 |         3 |
| a    | B    |             0 |         4 |
| a    | NULL |             1 |         7 |
| b    | A    |             0 |         5 |
| b    | B    |             0 |         6 |
| b    | NULL |             1 |        11 |
| NULL | A    |             2 |         8 |
| NULL | B    |             2 |        10 |
| NULL | NULL |             3 |        18 |
+------+------+---------------+-----------+
9 rows in set (0.02 sec)


*Solution*

For GROUPING SET is equivalent to the UNION of GROUP BY . So we can expand
input rows, and run an GROUP BY on these rows??

For example??

SELECT a, b FROM src GROUP BY a, b GROUPING SETS ((a, b), (a), (b), ());

Data in table src :

1, 2
3, 4

Base on GROUPING SETS , we can expend the input to??

1, 2       (GROUPING_ID: a, b -> 00 -> 0)
1, null    (GROUPING_ID: a, null -> 01 -> 1)
null, 2    (GROUPING_ID: null, b -> 10 -> 2)
null, null (GROUPING_ID: null, null -> 11 -> 3)

3, 4       (GROUPING_ID: a, b -> 00 -> 0)
3, null    (GROUPING_ID: a, null -> 01 -> 1)
null, 4    (GROUPING_ID: null, b -> 10 -> 2)
null, null (GROUPING_ID: null, null -> 11 -> 3)

And then use those row as input, then GROUP BY a, b, GROUPING_ID

Reply via email to