Flink SQL:Queries(Group Aggregation)

Group Aggregation

Batch Streaming

Like most data systems, Apache Flink supports aggregate functi; both built-in and user-defined. User-defined functi must be registered in a catalog before use.
与大多数数据系统一样，Apache Flink支持聚合函数；内置和用户定义。用户定义的函数必须在使用前在目录中注册。

An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the COUT, SUM, AVG (average), MAX (maximum) and MI (minimum) over a set of rows.
聚合函数从多个输入行计算单个结果。例如，聚合计算一组行的COUT、SUM、AVG(平均值)、MAX(最大值)和MI(最小值)。

SELECT COUT(*) FROM Orders

For streaming queries, it is important to understand that Flink runs continuous queries that never terminate. Instead, they update their result table according to the updates on its input tables. For the above query, Flink will output an updated count each time a new row is inserted into the Orders table.
对于流式查询，重要的是要了解Flink运行的是永不终止的持续查询。相反，它们根据输入表的更新更新结果表。对于上面的查询，每次在Orders表中插入新行时，Flink都会输出更新的计数。

Apache Flink supports the standard GROUP BY clause for aggregating data.
Apache Flink支持聚合数据的标准GROUP BY子句。

SELECT COUT(*)
FROM Orders
GROUP BY order_id

For streaming queries, the required state for computing the query result might grow infinitely. State size depends on the number of groups and the number and type of aggregation functi. For example MI/MAX are heavy on state size while COUT is cheap. You can provide a query configuration with an appropriate state time-to-live (TTL) to prevent excessive state size. ote that this might affect the correctness of the query result. See query configuration for details.
对于流式查询，计算查询结果所需的状态可能会无限增长。状态大小取决于分组的数量以及聚合函数的数量和类型。例如，MI/MAX对状态大小影响比较大，而COUT影响较小。您可以为查询配置提供适当的状态生存时间(TTL) ，以防止状态大小过大。请注意，这可能会影响查询结果的正确性。有关详细信息，请参阅查询配置。

Apache Flink provides a set of performance tuning ways for Group Aggregation, see more Performance Tuning.
Apache Flink为分组聚合提供了一组性能调优方法，请参阅更多性能调优。

DISTICT Aggregation

Distinct aggregates remove duplicate values before applying an aggregation function. The following example counts the number of distinct order_ids instead of the total number of rows in the Orders table.
Distinct聚合在应用聚合函数之前会删除重复的值。下面的示例统计不同order_id的数量，而不是Orders表中的总行数。

SELECT COUT(DISTICT order_id) FROM Orders

For streaming queries, the required state for computing the query result might grow infinitely. State size is mostly depends on the number of distinct rows and the time that a group is maintained, short lived group by windows are not a problem. You can provide a query configuration with an appropriate state time-to-live (TTL) to prevent excessive state size. ote that this might affect the correctness of the query result. See query configuration for details.
对于流式查询，计算查询结果所需的状态可能会无限增长。状态大小主要取决于不同行的数量和维护组的时间，短时间的逐窗口分组不是问题。您可以为查询配置提供适当的状态生存时间(TTL)，以防止状态大小过大。请注意，这可能会影响查询结果的正确性。有关详细信息，请参阅查询配置。

GROUPIG SETS

Grouping sets allow for more complex grouping operati than those describable by a standard GROUP BY. Rows are grouped separately by each specified grouping set and aggregates are computed for each group just as for simple GROUP BY clauses.
Grouping sets允许比标准GROUP BY描述的操作更复杂的分组操作。行按每个指定的分组集单独分组，并为每个组计算聚合，就像简单的GROUP BY子句一样。

SELECT supplier_id, rating, COUT(*) AS total
FROM (VALUES(	supplier1	, 	product1	, 4),(	supplier1	, 	product2	, ),(	supplier2	, 	product	, ),(	supplier2	, 	product4	, 4))
AS Products(supplier_id, product_id, rating)
GROUP BY GROUPIG SETS ((supplier_id, rating), (supplier_id), ())

Results:

----------------------------
| supplier_id | rating | total |
----------------------------
|   supplier1 |      4 |     1 |
|   supplier1 | (ULL) |     2 |
|      (ULL) | (ULL) |     4 |
|   supplier1 |       |     1 |
|   supplier2 |       |     1 |
|   supplier2 | (ULL) |     2 |
|   supplier2 |      4 |     1 |
----------------------------

Each sublist of GROUPIG SETS may specify zero or more columns or expressi and is interpreted the same way as though it was used directly in the GROUP BY clause. An empty grouping set means that all rows are aggregated down to a single group, which is output even if no input rows were present.
GROUPIG SETS的每个子列表可以指定零个或多个列或表达式，其解释方式与GROUP BY子句中直接使用的相同。空的分组集意味着所有行都被聚合到一个组中，即使没有输入行也会输出该组。

References to the grouping columns or expressi are replaced by null values in result rows for grouping sets in which those columns do not appear.
对分组列或表达式的引用将被结果行中的空值替换，这些空值用于对不显示这些列的集合进行分组。

For streaming queries, the required state for computing the query result might grow infinitely. State size depends on number of group sets and type of aggregation functi. You can provide a query configuration with an appropriate state time-to-live (TTL) to prevent excessive state size. ote that this might affect the correctness of the query result. See query configuration for details.
对于流式查询，计算查询结果所需的状态可能会无限增长。状态大小取决于分组集的数量和聚合函数的类型。您可以为查询配置提供适当的状态生存时间(TTL)，以防止状态大小过大。请注意，这可能会影响查询结果的正确性。有关详细信息，请参阅查询配置。

ROLLUP

ROLLUP is a shorthand notation for specifying a common type of grouping set. It represents the given list of expressi and all prefixes of the list, including the empty list.
ROLLUP是用于指定通用类型分组集的简写符号。它表示给定的表达式列表和列表的所有前置列，包括空列表。

For example, the following query is equivalent to the one above.
例如，下面的查询等同于上面的查询。

SELECT supplier_id, rating, COUT(*)
FROM (VALUES(	supplier1	, 	product1	, 4),(	supplier1	, 	product2	, ),(	supplier2	, 	product	, ),(	supplier2	, 	product4	, 4))
AS Products(supplier_id, product_id, rating)
GROUP BY ROLLUP (supplier_id, rating)

CUBE

CUBE is a shorthand notation for specifying a common type of grouping set. It represents the given list and all of its possible subsets - the power set.
CUBE是用于指定常用分组集类型的简写符号。它表示给定的列表及其所有可能的子集-幂集。

For example, the following two queries are equivalent.
例如，以下两个查询是等效的。

SELECT supplier_id, rating, product_id, COUT(*)
FROM (VALUES(	supplier1	, 	product1	, 4),(	supplier1	, 	product2	, ),(	supplier2	, 	product	, ),(	supplier2	, 	product4	, 4))
AS Products(supplier_id, product_id, rating)
GROUP BY CUBE (supplier_id, rating, product_id)SELECT supplier_id, rating, product_id, COUT(*)
FROM (VALUES(	supplier1	, 	product1	, 4),(	supplier1	, 	product2	, ),(	supplier2	, 	product	, ),(	supplier2	, 	product4	, 4))
AS Products(supplier_id, product_id, rating)
GROUP BY GROUPIG SET (( supplier_id, product_id, rating ),( supplier_id, product_id         ),( supplier_id,             rating ),( supplier_id                     ),(              product_id, rating ),(              product_id         ),(                          rating ),(                                 )
)

HAVIG

HAVIG eliminates group rows that do not satisfy the condition. HAVIG is different from WHERE: WHERE filters individual rows before the GROUP BY while HAVIG filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column unless it appears within an aggregate function.
HAVIG消除不满足条件的组行。HAVIG与WHERE不同：WHERE在GROUP BY之前过滤单个行，而HAVIG则过滤GROUP B创建的组行。条件中引用的每个列必须明确引用分组列，除非它出现在聚合函数中。

SELECT SUM(amount)
FROM Orders
GROUP BY users
HAVIG SUM(amount) > 50

The presence of HAVIG turns a query into a grouped query even if there is no GROUP BY clause. It is the same as what happens when the query contains aggregate functi but no GROUP BY clause. The query ciders all selected rows to form a single group, and the SELECT list and HAVIG clause can only reference table columns from within aggregate functi. Such a query will emit a single row if the HAVIG condition is true, zero rows if it is not true.
即使没有GROUP BY子句，HAVIG的存在也会将查询转换为分组查询。这与查询包含聚合函数但不包含GROUP BY子句时的情况相同。查询将所有选定行视为一个组，SELECT列表和HAVIG子句只能引用聚合函数中的表列。如果HAVIG条件为真，这样的查询将发出一行，如果不为真，则发出零行。