Aggregate 表

创建表时，您可以定义聚合键并为值列指定聚合函数。当多行数据具有相同的聚合键时，值列中的值将被聚合。此外，您可以单独定义排序键。如果查询中的过滤条件包含排序键，StarRocks 可以快速过滤数据，从而提高查询效率。

在数据分析和聚合场景中，聚合表可以减少需要处理的数据量，从而提高查询效率。

使用场景

聚合表非常适合数据统计和分析场景。以下是一些示例

• 帮助网站或应用程序提供商分析其用户在特定网站或应用程序上花费的流量和时间以及对网站或应用程序的总访问次数。

• 帮助广告代理商分析他们为客户提供的广告的总点击次数、总观看次数和消费统计信息。

• 帮助电子商务公司分析其年度交易数据，以识别各个季度或月份中畅销的地理区域。

上述场景中的数据查询和摄取具有以下特征

• 大多数查询都是聚合查询，例如 SUM、MAX 和 MIN。
• 不需要检索原始详细数据。
• 历史数据不会频繁更新。只会附加新数据。

原理

从数据摄取到数据查询的阶段，聚合表中的数据会多次聚合，如下所示

1. 在数据摄取阶段，当数据分批加载到聚合表中时，每批数据形成一个版本。在一个版本中，具有相同聚合键的数据将被聚合。

2. 在后台 Compaction 阶段，当在数据摄取时生成的多个数据版本的文件定期 Compaction 成一个大文件时，StarRocks 会聚合该大文件中具有相同聚合键的数据。

3. 在数据查询阶段，StarRocks 会聚合所有数据版本中具有相同聚合键的数据，然后返回查询结果。

聚合操作有助于减少需要处理的数据量，从而加快查询速度。

假设您有一个使用聚合表的表，并且想要将以下四个原始记录加载到该表中。

日期	国家	PV
2020.05.01	CHN	1
2020.05.01	CHN	2
2020.05.01	USA	3
2020.05.01	USA	4

StarRocks 在数据摄取时将四个原始记录聚合为以下两个记录。

日期	国家	PV
2020.05.01	CHN	3
2020.05.01	USA	7

创建表

假设您要分析来自不同城市的用户对不同网页的访问次数。在此示例中，创建一个名为 example_db.aggregate_tbl 的表，将 site_id、date 和 city_code 定义为聚合键，将 pv 定义为值列，并为 pv 列指定 SUM 函数。

创建表的语句如下

CREATE TABLE aggregate_tbl (
    site_id LARGEINT NOT NULL COMMENT "id of site",
    date DATE NOT NULL COMMENT "time of event",
    city_code VARCHAR(20) COMMENT "city_code of user",
    pv BIGINT SUM DEFAULT "0" COMMENT "total page views"
)
AGGREGATE KEY(site_id, date, city_code)
DISTRIBUTED BY HASH(site_id);

注意

• 创建表时，必须使用 DISTRIBUTED BY HASH 子句指定分桶列。有关详细信息，请参阅分桶。
• 从 v2.5.7 开始，StarRocks 可以在您创建表或添加分区时自动设置 bucket 数量 (BUCKETS)。您不再需要手动设置 bucket 数量。有关详细信息，请参阅设置 bucket 数量。

通用聚合函数状态

Beta 功能

Beta 功能使用建议

从 v3.4.0 开始，StarRocks 支持通用聚合函数状态。

在数据分析和摘要中，聚合表减少了查询期间处理的数据量，从而提高了查询性能。对于大型数据集，聚合表非常有效，因为它们在查询之前按维度汇总数据。它们也是 StarRocks 中增量聚合函数计算的重要方法。但是，在早期版本中，支持仅限于内置函数，例如 SUM、MAX、MIN、REPLACE、HLL_UNION、PERCENTILE_UNION 和 BITMAP_UNION，而从理论上讲，所有内置聚合函数都可以在聚合表中使用。为了解决此限制，引入了通用聚合状态以支持所有内置函数状态的存储。

存储通用聚合状态

您可以通过指定函数名称和输入参数类型来唯一标识聚合函数，从而在聚合表中定义通用聚合状态。列类型将自动推断为聚合函数的中间状态类型。

定义

col_name agg_func_name(parameter1_type, [parameter2_type], ...)

• col_name: 列名。
• agg_func_name: 需要存储其中间状态的聚合函数的名称。
• parameter_type: 聚合函数的输入参数类型。可以使用参数类型唯一标识该函数。

注意

• 仅支持至少有一个参数的 StarRocks 内置函数。不支持 Java 和 Python UDAF。
• 为了稳定性和可扩展性，聚合状态列类型始终为 Nullable（count 函数除外），并且无法修改。
• 定义多参数函数不需要参数值，因为可以推断类型并且参数值不参与计算。
• 不支持复杂的参数，如 ORDER BY 和 DISTINCT。
• 对特定内置函数（如 GROUP_CONCAT、WINDOW_FUNNEL 和 APPROX_TOP_K）的支持仍在开发中。它们将在未来的版本中得到支持。有关详细信息，请参阅FunctionSet.java#UNSUPPORTED_AGG_STATE_FUNCTIONS。

示例

CREATE TABLE test_create_agg_table (
  dt VARCHAR(10),
  -- Define generic aggregate state storage.
  hll_sketch_agg ds_hll_count_distinct(varchar),
  avg_agg avg(bigint),
  array_agg_agg array_agg(int),
  min_by_agg min_by(varchar, bigint)
)
AGGREGATE KEY(dt)
PARTITION BY (dt) 
DISTRIBUTED BY HASH(dt) BUCKETS 4;

组合器函数

通用聚合状态使用组合器函数来封装中间状态计算和流程。

_state 组合器函数

_state 函数将输入参数转换为中间状态类型。

定义

agg_intermediate_type {agg_func_name}_state(input_col1, [input_col2], ...)

• agg_func_name: 需要将输入参数转换为中间状态类型的聚合函数的名称。
• input_col1/col2: 聚合函数的输入列。
• agg_intermediate_type: _state 函数的返回类型，即聚合函数的中间状态类型。

注意

_state 是一个标量函数。您不需要为输入参数状态的计算定义聚合列。

示例

CREATE TABLE t1 (
  id BIGINT NOT NULL,
  province VARCHAR(64),
  age SMALLINT,
  dt VARCHAR(10) NOT NULL 
)
DUPLICATE KEY(id)
PARTITION BY (dt)
DISTRIBUTED BY HASH(id) BUCKETS 4;

INSERT INTO t1 SELECT generate_series, generate_series, generate_series % 10, "2024-07-24" FROM table(generate_series(1, 100));

-- Transfer the data in t1 with _state combinator function, and insert it into the Aggregate table.
INSERT INTO test_create_agg_table
SELECT
    dt,
    ds_hll_count_distinct_state(id),
    avg_state(id),
    array_agg_state(id),
    min_by_state(province, id)
FROM t1;

_union 组合器函数

_union 函数将多个中间状态列合并为单个状态。

定义

-- Union multiple aggregate intermediate states.
agg_intermediate_type {agg_func_name}_union(input_col)

• agg_func_name: 聚合函数的名称。
• input_col: 聚合函数的输入列。输入列类型是聚合函数的中间状态类型。您可以使用 _state 函数获取它。
• agg_intermediate_type: _union 函数的返回类型，即聚合函数的中间状态类型。

注意

_union 是一个聚合函数。它返回中间状态类型，而不是函数的最终结果类型。

示例

-- Case 1: Union the intermediate states of the Aggregate table.
SELECT 
    dt,
    ds_hll_count_distinct_union(hll_sketch_agg),
    avg_union(avg_agg),
    array_agg_union(array_agg_agg),
    min_by_union(min_by_agg)
FROM test_create_agg_table
GROUP BY dt
LIMIT 1;

-- Case 2: Union the intermediate states input by the _state combinator function.
SELECT 
    dt,
    ds_hll_count_distinct_union(ds_hll_count_distinct_state(id)),
    avg_union(avg_state(id)),
    array_agg_union(array_agg_state(id)),
    min_by_union(min_by_state(province, id))
FROM t1
GROUP BY dt
LIMIT 1;

_merge 组合器函数

_merge 组合器函数将聚合函数封装为通用聚合函数，以计算多个中间状态的最终聚合结果。

定义

-- Merge multiple aggregate intermediate states.
agg_result_type {agg_func_name}_merge(input_col)

• agg_func_name: 聚合函数的名称。
• input_col: 聚合函数的输入列。输入列类型是聚合函数的中间状态类型。您可以使用 _state 函数获取它。
• agg_intermediate_type: _merge 函数的返回类型，即聚合函数的最终聚合结果。

示例

-- Case 1: Merge the intermediate states of the Aggregate table to obtain the final aggregation result.
SELECT 
    dt,
    ds_hll_count_distinct_merge(hll_sketch_agg),
    avg_merge(avg_agg),
    array_agg_merge(array_agg_agg),
    min_by_merge(min_by_agg)
FROM test_create_agg_table
GROUP BY dt
LIMIT 1;

-- Case 2:  Merge the intermediate states input by the _state combinator function to obtain the final aggregation result.
SELECT 
    dt,
    ds_hll_count_distinct_merge(ds_hll_count_distinct_state(id)),
    avg_merge(avg_state(id)),
    array_agg_merge(array_agg_state(id)),
    min_by_merge(min_by_state(province, id))
FROM t1
GROUP BY dt
LIMIT 1;

在物化视图中使用通用聚合状态

通用聚合状态可用于同步和异步物化视图，以通过聚合状态的汇总来加速查询性能。

同步物化视图中的通用聚合状态

示例

-- Create a synchronous materialized view test_mv1 to store aggregate states.
CREATE MATERIALIZED VIEW test_mv1 
AS
SELECT 
    dt,
    -- Original aggregate functions.
    min(id) AS min_id,
    max(id) AS max_id,
    sum(id) AS sum_id,
    bitmap_union(to_bitmap(id)) AS bitmap_union_id,
    hll_union(hll_hash(id)) AS hll_union_id,
    percentile_union(percentile_hash(id)) AS percentile_union_id,
    -- Generic aggregate state functions.
    ds_hll_count_distinct_union(ds_hll_count_distinct_state(id)) AS hll_id,
    avg_union(avg_state(id)) AS avg_id,
    array_agg_union(array_agg_state(id)) AS array_agg_id,
    min_by_union(min_by_state(province, id)) AS min_by_province_id
FROM t1
GROUP BY dt;

-- Wait until rollup creation finishes.
show alter table rollup;

-- Direct queries against the aggregate function will be transparently accelerated by test_mv1.
SELECT 
    dt,
    min(id),
    max(id),
    sum(id),
    bitmap_union_count(to_bitmap(id)), -- count(distinct id)
    hll_union_agg(hll_hash(id)), -- approx_count_distinct(id)
    percentile_approx(id, 0.5),
    ds_hll_count_distinct(id),
    avg(id),
    array_agg(id),
    min_by(province, id)
FROM t1
WHERE dt >= '2024-01-01'
GROUP BY dt;

-- Direct queries against the aggregate function and the rollup will also be transparently accelerated by test_mv1.
SELECT 
    min(id),
    max(id),
    sum(id),
    bitmap_union_count(to_bitmap(id)), -- count(distinct id)
    hll_union_agg(hll_hash(id)), -- approx_count_distinct(id)
    percentile_approx(id, 0.5),
    ds_hll_count_distinct(id),
    avg(id),
    array_agg(id),
    min_by(province, id)
FROM t1
WHERE dt >= '2024-01-01';

DROP MATERIALIZED VIEW test_mv1;

异步物化视图中的通用聚合状态

示例

-- Create an asynchronous materialized view test_mv2 to store aggregate states.
CREATE MATERIALIZED VIEW test_mv2
PARTITION BY (dt)
DISTRIBUTED BY RANDOM
AS
SELECT 
    dt,
    -- Original aggregate functions.
    min(id) AS min_id,
    max(id) AS max_id,
    sum(id) AS sum_id,
    bitmap_union(to_bitmap(id)) AS bitmap_union_id,
    hll_union(hll_hash(id)) AS hll_union_id,
    percentile_union(percentile_hash(id)) AS percentile_union_id,
    -- Generic aggregate state functions.
    ds_hll_count_distinct_union(ds_hll_count_distinct_state(id)) AS hll_id,
    avg_union(avg_state(id)) AS avg_id,
    array_agg_union(array_agg_state(id)) AS array_agg_id,
    min_by_union(min_by_state(province, id)) AS min_by_province_id
FROM t1
GROUP BY dt;

-- Refresh the materialized view.
REFRESH MATERIALIZED VIEW test_mv2 WITH SYNC MODE;

-- Direct queries against the aggregate function will be transparently accelerated by test_mv2.
SELECT 
    dt,
    min(id),
    max(id),
    sum(id),
    bitmap_union_count(to_bitmap(id)), -- count(distinct id)
    hll_union_agg(hll_hash(id)), -- approx_count_distinct(id)
    percentile_approx(id, 0.5),
    ds_hll_count_distinct(id),
    avg(id),
    array_agg(id),
    min_by(province, id)
FROM t1
WHERE dt >= '2024-01-01'
GROUP BY dt;

SELECT 
    min(id),
    max(id),
    sum(id),
    bitmap_union_count(to_bitmap(id)), -- count(distinct id)
    hll_union_agg(hll_hash(id)), -- approx_count_distinct(id)
    percentile_approx(id, 0.5),
    ds_hll_count_distinct(id),
    avg(id),
    array_agg(id),
    min_by(province, id)
FROM t1
WHERE dt >= '2024-01-01';

使用说明

• 聚合键:

  • 在 CREATE TABLE 语句中，聚合键必须在其他列之前定义。

  • 可以使用 AGGREGATE KEY 显式定义聚合键。AGGREGATE KEY 必须包含除值列之外的所有列，否则表将无法创建。

    如果未使用 AGGREGATE KEY 显式定义聚合键，则默认情况下，除值列之外的所有列都被视为聚合键。

  • 聚合键具有唯一性约束。

• 值列：通过在列名后指定聚合函数，将列定义为值列。此列通常保存需要聚合的数据。

• 聚合函数：用于值列的聚合函数。有关聚合表支持的聚合函数，请参阅CREATE TABLE。

• 排序键

  • 从 v3.3.0 开始，排序键与聚合表中聚合键分离。聚合表支持使用 ORDER BY 指定排序键，使用 AGGREGATE KEY 指定聚合键。排序键和聚合键中的列需要相同，但列的顺序不需要相同。

  • 运行查询时，排序键列会在多个数据版本聚合之前进行过滤，而值列则在多个数据版本聚合之后进行过滤。因此，我们建议您识别常用作过滤条件的列，并将这些列定义为排序键。这样，数据过滤可以在多个数据版本聚合之前开始，以提高查询性能。

• 创建表时，只能在表的键列上创建 Bitmap 索引或 Bloom Filter 索引。

下一步做什么

创建表后，您可以使用各种数据摄取方法将数据加载到 StarRocks 中。有关 StarRocks 支持的数据摄取方法的信息，请参阅加载选项。

注意：将数据加载到使用聚合表的表中时，只能更新表的所有列。例如，当您更新上述 example_db.aggregate_tbl 表时，您必须更新其所有列，即 site_id、date、city_code 和 pv。