GROUP BY

description

GROUP BY GROUPING SETS ｜ CUBE ｜ ROLLUP is an extension to GROUP BY clause. This syntax lets you define multiple groupings in the same query. GROUPING SETS produce a single result set that is equivalent to a UNION ALL of differently grouped rows For example GROUPING SETS clause:

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

This statement is equivalent to:

  1. SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
  2. UNION
  3. SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
  4. UNION
  5. SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
  6. UNION
  7. SELECT null, null, SUM( c ) FROM tab1

GROUPING(expr) indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set.

GROUPING_ID(expr [ , expr [ , ... ] ]) describes which of a list of expressions are grouped in a row produced by a GROUP BY query. The GROUPING_ID function simply returns the decimal equivalent of the binary value formed as a result of the concatenation of the values returned by the GROUPING functions.

Syntax

  1. SELECT ...
  2. FROM ...
  3. [ ... ]
  4. GROUP BY [
  5.     , ... |
  6.     GROUPING SETS [, ...] (  groupSet [ , groupSet [ , ... ] ] ) |
  7.     ROLLUP(expr  [ , expr [ , ... ] ]) |
  8.     expr  [ , expr [ , ... ] ] WITH ROLLUP |
  9.     CUBE(expr  [ , expr [ , ... ] ]) |
  10.     expr  [ , expr [ , ... ] ] WITH CUBE
  11.     ]
  12. [ ... ]

Parameters

groupSet is a set of expression or column or it’s alias appearing in the query block’s SELECT list. groupSet ::= { ( expr [ , expr [ , ... ] ] )}

expr is expression or column or it’s alias appearing in the query block’s SELECT list.

Note

doris supports PostgreSQL like syntax, for example:

  1. SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), (a), (b), ( ) );
  2. SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY ROLLUP(a,b,c)
  3. SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY CUBE(a,b,c)

ROLLUP(a,b,c) is equivalent to GROUPING SETS as follows:

  1. GROUPING SETS (
  2. (a,b,c),
  3. ( a, b ),
  4. ( a),
  5. ( )
  6. )

CUBE ( a, b, c ) is equivalent to GROUPING SETS as follows:

  1. GROUPING SETS (
  2. ( a, b, c ),
  3. ( a, b ),
  4. ( a,    c ),
  5. ( a       ),
  6. (    b, c ),
  7. (    b    ),
  8. (       c ),
  9. (         )
  10. )

example

This is a simple example

  1. > SELECT * FROM t;
  2. +------+------+------+
  3. | k1   | k2   | k3   |
  4. +------+------+------+
  5. | a    | A    |    1 |
  6. | a    | A    |    2 |
  7. | a    | B    |    1 |
  8. | a    | B    |    3 |
  9. | b    | A    |    1 |
  10. | b    | A    |    4 |
  11. | b    | B    |    1 |
  12. | b    | B    |    5 |
  13. +------+------+------+
  14. 8 rows in set (0.01 sec)
  16. > SELECT k1, k2, SUM(k3) FROM t GROUP BY GROUPING SETS ( (k1, k2), (k2), (k1), ( ) );
  17. +------+------+-----------+
  18. | k1   | k2   | sum(`k3`) |
  19. +------+------+-----------+
  20. | b    | B    |         6 |
  21. | a    | B    |         4 |
  22. | a    | A    |         3 |
  23. | b    | A    |         5 |
  24. | NULL | B    |        10 |
  25. | NULL | A    |         8 |
  26. | a    | NULL |         7 |
  27. | b    | NULL |        11 |
  28. | NULL | NULL |        18 |
  29. +------+------+-----------+
  30. 9 rows in set (0.06 sec)
  32. > SELECT k1, k2, GROUPING_ID(k1,k2), SUM(k3) FROM t GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ());
  33. +------+------+---------------+----------------+
  34. | k1   | k2   | grouping_id(k1,k2) | sum(`k3`) |
  35. +------+------+---------------+----------------+
  36. | a    | A    |             0 |              3 |
  37. | a    | B    |             0 |              4 |
  38. | a    | NULL |             1 |              7 |
  39. | b    | A    |             0 |              5 |
  40. | b    | B    |             0 |              6 |
  41. | b    | NULL |             1 |             11 |
  42. | NULL | A    |             2 |              8 |
  43. | NULL | B    |             2 |             10 |
  44. | NULL | NULL |             3 |             18 |
  45. +------+------+---------------+----------------+
  46. 9 rows in set (0.02 sec)

keyword

GROUP, GROUPING, GROUPING_ID, GROUPING_SETS, GROUPING SETS, CUBE, ROLLUP