跳转至

Rollup（汇总聚合 (Rollup)）

Supported in: Batch, Faster

Performs the specified aggregations on the input dataset at different levels of granularity, providing both intermediate and super aggregates.

Transform categories: Aggregate

Declared arguments

  • Aggregations: List of aggregations to perform on the dataset.
    List\>
  • Dataset: Dataset to perform rollup on.
    Table
  • Rollup columns: List of columns to rollup the dataset by when aggregating. If empty, no roll up is applied.
    List\>

Examples

Example 1: Base case

Argument values:

  • Aggregations: [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupBaseCase
  • Rollup columns: [city]

Input:

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

Output:

city max_price
London 900.0
San Francisco 1105.7
null 1105.7

Example 2: Base case

Argument values:

  • Aggregations: [
    alias(
     alias: mean_price,
     expression:
    mean(
     expression: price,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupBaseCase
  • Rollup columns: [city, model]

Input:

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

Output:

city model mean_price
London new phone 873.8333333333334
London forestX 20 723.3666666666667
London null 798.6
San Francisco new phone 1018.65
San Francisco forestX 20 947.0833333333334
San Francisco null 982.8666666666667
null null 890.7333333333335

Example 3: Base case

Argument values:

  • Aggregations: [
    alias(
     alias: max_price,
     expression:
    max(
     expression: plan_prices,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupComplexCase
  • Rollup columns: [model]

Input:

city model plan_prices stores
London new phone [ 900.0, 1080.23, 899.99 ] MegaMart
London new phone [ 850.75, 800.78, 999.99 ] AA
London new phone [ 870.75, 775.0, 804.48 ] ABC Zone
San Francisco new phone [ 910.0, 1030.23, 1100.5 ] Prescos
San Francisco new phone [ 1020.0, 989.99, 1130.0 ] XZY Force
San Francisco new phone [ 1020.0, 1065.25, 1110.99 ] Phone Mart
London forestX 20 [ 738.5, 701.25, 834.0 ] MegaMart
London forestX 20 [ 703.75, 821.0, 712.5 ] AA
London forestX 20 [ 692.0, 787.5, 841.75 ] ABC Zone
San Francisco forestX 20 [ 1003.25, 997.75, 893.5 ] Prescos
San Francisco forestX 20 [ 981.5, 872.25, 1035.0 ] XZY Force
San Francisco forestX 20 [ 928.0, 995.25, 1098.5 ] Phone Mart

Output:

model max_price
new phone [ 1020.0, 1065.25, 1110.99 ]
forestX 20 [ 1003.25, 997.75, 893.5 ]
null [ 1020.0, 1065.25, 1110.99 ]

Example 4: Null case

Argument values:

  • Aggregations: [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupNullCase
  • Rollup columns: [city, model]

Input:

city model price stores
London new phone null MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone null Prescos
San Francisco new phone null XZY Force
San Francisco new phone null Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 null ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 null XZY Force
San Francisco forestX 20 1050.75 Phone Mart

Output:

city model max_price
London new phone 870.75
London forestX 20 750.1
London null 870.75
San Francisco new phone null
San Francisco forestX 20 1050.75
San Francisco null 1050.75
null null 1050.75

Example 5: Edge case

Argument values:

  • Aggregations: [
    alias(
     alias: mean_price,
     expression:
    mean(
     expression: price,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupBaseCase
  • Rollup columns: []

Input:

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

Output:

mean_price
890.7333333333335

Example 6: Edge case

Argument values:

  • Aggregations: [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • Dataset: ri.foundry.main.dataset.rollupEmptyCase
  • Rollup columns: [city, model]

Input:

city model price store

Output:

city model max_price

中文翻译

汇总聚合 (Rollup)

支持：批处理 (Batch)、快速 (Faster)

对输入数据集按不同粒度级别执行指定的聚合操作，同时提供中间聚合和超级聚合结果。

转换类别：聚合 (Aggregate)

声明的参数

  • 聚合 (Aggregations)： 要对数据集执行的聚合列表。
    List\>
  • 数据集 (Dataset)： 要执行汇总聚合的数据集。
    Table
  • 汇总列 (Rollup columns)： 进行聚合时用于分组汇总的列列表。如果为空，则不应用汇总。
    List\>

示例

示例 1：基础情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupBaseCase
  • 汇总列 (Rollup columns)： [city]

输入：

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

输出：

city max_price
London 900.0
San Francisco 1105.7
null 1105.7

示例 2：基础情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: mean_price,
     expression:
    mean(
     expression: price,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupBaseCase
  • 汇总列 (Rollup columns)： [city, model]

输入：

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

输出：

city model mean_price
London new phone 873.8333333333334
London forestX 20 723.3666666666667
London null 798.6
San Francisco new phone 1018.65
San Francisco forestX 20 947.0833333333334
San Francisco null 982.8666666666667
null null 890.7333333333335

示例 3：复杂情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: max_price,
     expression:
    max(
     expression: plan_prices,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupComplexCase
  • 汇总列 (Rollup columns)： [model]

输入：

city model plan_prices stores
London new phone [ 900.0, 1080.23, 899.99 ] MegaMart
London new phone [ 850.75, 800.78, 999.99 ] AA
London new phone [ 870.75, 775.0, 804.48 ] ABC Zone
San Francisco new phone [ 910.0, 1030.23, 1100.5 ] Prescos
San Francisco new phone [ 1020.0, 989.99, 1130.0 ] XZY Force
San Francisco new phone [ 1020.0, 1065.25, 1110.99 ] Phone Mart
London forestX 20 [ 738.5, 701.25, 834.0 ] MegaMart
London forestX 20 [ 703.75, 821.0, 712.5 ] AA
London forestX 20 [ 692.0, 787.5, 841.75 ] ABC Zone
San Francisco forestX 20 [ 1003.25, 997.75, 893.5 ] Prescos
San Francisco forestX 20 [ 981.5, 872.25, 1035.0 ] XZY Force
San Francisco forestX 20 [ 928.0, 995.25, 1098.5 ] Phone Mart

输出：

model max_price
new phone [ 1020.0, 1065.25, 1110.99 ]
forestX 20 [ 1003.25, 997.75, 893.5 ]
null [ 1020.0, 1065.25, 1110.99 ]

示例 4：空值情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupNullCase
  • 汇总列 (Rollup columns)： [city, model]

输入：

city model price stores
London new phone null MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone null Prescos
San Francisco new phone null XZY Force
San Francisco new phone null Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 null ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 null XZY Force
San Francisco forestX 20 1050.75 Phone Mart

输出：

city model max_price
London new phone 870.75
London forestX 20 750.1
London null 870.75
San Francisco new phone null
San Francisco forestX 20 1050.75
San Francisco null 1050.75
null null 1050.75

示例 5：边界情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: mean_price,
     expression:
    mean(
     expression: price,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupBaseCase
  • 汇总列 (Rollup columns)： []

输入：

city model price store
London new phone 900.0 MegaMart
London new phone 850.75 AA
London new phone 870.75 ABC Zone
San Francisco new phone 1000.0 Prescos
San Francisco new phone 950.25 XZY Force
San Francisco new phone 1105.7 Phone Mart
London forestX 20 750.1 MegaMart
London forestX 20 690.0 AA
London forestX 20 730.0 ABC Zone
San Francisco forestX 20 890.4 Prescos
San Francisco forestX 20 900.1 XZY Force
San Francisco forestX 20 1050.75 Phone Mart

输出：

mean_price
890.7333333333335

示例 6：边界情况

参数值：

  • 聚合 (Aggregations)： [
    alias(
     alias: max_price,
     expression:
    max(
     expression: price,
    ),
    )]
  • 数据集 (Dataset)： ri.foundry.main.dataset.rollupEmptyCase
  • 汇总列 (Rollup columns)： [city, model]

输入：

city model price store

输出：

city model max_price