imay commented on a change in pull request #2293: Support Grouping Sets, Rollup and Cube to extend group by statement URL: https://github.com/apache/incubator-doris/pull/2293#discussion_r350827732
########## File path: docs/documentation/cn/sql-reference/sql-statements/Data Manipulation/GROUP BY.md ########## @@ -0,0 +1,173 @@ +<!-- +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License"); you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, +software distributed under the License is distributed on an +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +KIND, either express or implied. See the License for the +specific language governing permissions and limitations +under the License. +--> + +# GROUP BY + +## description + + GROUP BY `GROUPING SETS` ｜ `CUBE` ｜ `ROLLUP` 是对 GROUP BY 子句的扩展，它能够在一个 GROUP BY 子句中实现多个集合的分组的聚合。其结果等价于将多个相应 GROUP BY 子句进行 UNION 操作。 + + GROUP BY 子句是只含有一个元素的 GROUP BY GROUPING SETS 的特例。 + 例如，GROUPING SETS 语句： + + ``` + SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), (a), (b), ( ) ); + ``` + + 其查询结果等价于： + + ``` + 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 + ``` + + `GROUPING(expr)` 指示一个列是否为聚合列，如果是聚合列为0，否则为1 + + `GROUPING_ID(expr [ , expr [ , ... ] ])` 与GROUPING 类似， GROUPING_ID根据指定的column 顺序，计算出一个列列表的 bitmap 值，每一位为GROUPING的值. GROUPING_ID()函数返回位向量的十进制值。 + +### Syntax + + ``` + SELECT ... + FROM ... + [ ... ] + GROUP BY [ + , ... | + GROUPING SETS [, ...] ( groupSet [ , groupSet [ , ... ] ] ) | + ROLLUP(expr [ , expr [ , ... ] ]) | + expr [ , expr [ , ... ] ] WITH ROLLUP | + CUBE(expr [ , expr [ , ... ] ]) | + expr [ , expr [ , ... ] ] WITH CUBE + ] + [ ... ] + ``` + +### Parameters + + `groupSet` 表示 select list 中的列，别名或者表达式组成的集合 `groupSet ::= { ( expr [ , expr [ , ... ] ] )}` + + `expr` 表示 select list 中的列，别名或者表达式 + +### Note + + doris 支持两种语法，类似PostgreSQL 语法和 类似hive 语法，这两种语法实例如下 + + 类 PostgreSQL 语法： + + ``` + SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), (a), (b), ( ) ); + SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY ROLLUP(a,b,c) + SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY CUBE(a,b,c) + ``` + + 类似hive 语法 Review comment: I think we can support hive syntax in the futrue, not now. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
