--- title: rollup --- The deephaven `rollup` method creates a rollup table from a source table given zero or more aggregations and zero or more grouping columns to create a hierarchy from. ## Syntax ``` source.rollup(aggregations) source.rollup(aggregations, includeConstituents) source.rollup(aggregations, groupByColumns...) source.rollup(aggregations, includeConstituents, groupByColumns...) ``` ## Parameters One or more aggregations. The following aggregations are supported: - [`AggAbsSum`](../group-and-aggregate/AggAbsSum.md) - [`AggAvg`](../group-and-aggregate/AggAvg.md) - [`AggCount`](../group-and-aggregate/AggCount.md) - [`AggCountDistinct`](../group-and-aggregate/AggCountDistinct.md) - [`AggCountWhere`](../group-and-aggregate/AggCountWhere.md) - [`AggFirst`](../group-and-aggregate/AggFirst.md) - [`AggFormula`](../group-and-aggregate/AggFormula.md) - [`AggGroup`](../group-and-aggregate/AggGroup.md) - [`AggLast`](../group-and-aggregate/AggLast.md) - [`AggMax`](../group-and-aggregate/AggMax.md) - [`AggMin`](../group-and-aggregate/AggMin.md) - [`AggSortedFirst`](../group-and-aggregate/AggSortedFirst.md) - [`AggSortedLast`](../group-and-aggregate/AggSortedLast.md) - [`AggStd`](../group-and-aggregate/AggStd.md) - [`AggSum`](../group-and-aggregate/AggSum.md) - [`AggUnique`](../group-and-aggregate/AggUnique.md) - [`AggVar`](../group-and-aggregate/AggVar.md) - [`AggWAvg`](../group-and-aggregate/AggWAvg.md) - [`AggWSum`](../group-and-aggregate/AggWSum.md) Whether or not to include constituent rows at the leaf level. The default value is `False`. One or more columns to group on and create hierarchy from. ## Methods ### Instance - `getAggregations` - Get the aggregations performed in the `rollup` operation. - `getGroupByColumns` - Get the `groupByColumns` used for the `rollup` operation. - `getLeafNodeType` - Get the [`NodeType`](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.NodeType.html) at the leaf level. - `getNodeDefinition(nodeType)` - Get the [`TableDefinition`](/core/javadoc/io/deephaven/engine/table/TableDefinition.html) that should be exposed to node table consumers. - `includesConstituents` - Returns a boolean indicating whether or not the constituent rows at the lowest level are included. - `makeNodeOperationsRecorder(nodeType)` - Get a [`recorder`](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.NodeOperationsRecorder.html) for per-node operations to apply during snapshots of the requested [`NodeType`](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.NodeType.html). - `translateAggregatedNodeOperationsForConstituentNodes(aggregatedNodeOperationsToTranslate)` - Translate node operations for aggregated nodes to the closest equivalent for a constituent node. - `withFilter(filter)` - Create a new rollup table that will apply a filter to the Group By or Constituent columns of the rollup table. - `withNodeOperations(nodeOperations...)` - Create a new rollup table that will apply the [`recorded`](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.NodeOperationsRecorder.html) operations to nodes when gathering snapshots. - `withUpdateView(columns...)` - Create a new rollup table that applies a set of `updateView` operations to the `groupByColumns` of the rollup table. - `withNodeOperations(nodeOperations...)` - Create a new rollup table that applies the [`recorded`](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.NodeOperationsRecorder.html) operations to nodes when gathering snapshots. ## Returns A rollup table. ## Examples The following example creates a rollup table from a source table of insurance expense data. The aggregated average of the `bmi` and `expenses` columns are calculated, then the table is rolled up by the `region` and `age` column. ```groovy order=insurance,insuranceRollup import static io.deephaven.csv.CsvTools.readCsv import io.deephaven.api.agg.spec.AggSpec import io.deephaven.api.agg.Aggregation insurance = readCsv("https://media.githubusercontent.com/media/deephaven/examples/main/Insurance/csv/insurance.csv") aggList = [Aggregation.of(AggSpec.avg(), "bmi", "expenses")] insuranceRollup = insurance.rollup(aggList, false, "region", "age") ``` Similar to the previous example, this example creates a rollup table from a source table of insurance expense data. However, this time we are filtering on the source table before applying the rollup using `withFilter`. Both group and constituent columns can be used in the filter, while aggregation columns cannot. ```groovy order=insurance,insuranceRollup import static io.deephaven.csv.CsvTools.readCsv import io.deephaven.api.agg.spec.AggSpec import io.deephaven.api.agg.Aggregation import io.deephaven.api.filter.Filter insurance = readCsv("https://media.githubusercontent.com/media/deephaven/examples/main/Insurance/csv/insurance.csv") aggList = [Aggregation.of(AggSpec.avg(), "bmi", "expenses")] filterList = List.of("age > 30") insuranceRollup = insurance.rollup(aggList, false, "region", "age") .withFilter(Filter.from(filterList)) ``` The following example creates a rollup table from real-time source data. The source data updates 10,000 times per second. The `result` rollup table can be expanded by the `N` column to show unique values of `M` for each `N`. The aggregated average and sum are calculated for the `Value` and `Weight`, respectively. ```groovy ticking-table order=null import io.deephaven.api.agg.spec.AggSpec import io.deephaven.api.agg.Aggregation aggList = [Aggregation.of(AggSpec.avg(), "Value"), Aggregation.of(AggSpec.sum(), "Weight")] rows = emptyTable(1_000_000).updateView("Group = i", "N = i % 347", "M = i % 29") changes = timeTable("PT00:00:00.0001").view("Group = i % 1_000_000", "LastModified = Timestamp", "Value = (i * Math.sin(i)) % 6977", "Weight = (i * Math.sin(i)) % 7151").lastBy("Group") source = rows.join(changes, "Group") result = source.rollup(aggList, false, "N", "M") ``` ![The above `result` rollup table](../../../assets/how-to/rollup-table-realtime.gif) ## Formula Aggregations in Rollups When a rollup includes a formula aggregation, care should be taken with the function being applied. On each tick, the formula is evaluated for every changed row in the output table. Since the aggregated rows include numerous source rows, the input vectors for a formula aggregation can become very large — encompassing the entire source table at the root level. If the formula is inefficient when handling large input vectors, it may negatively impact the rollup's performance. By default, the formula aggregation operates on a group of all the values as they appeared in the source table. In this example, the `Value` column contains the same vector that is used as input to the formula: ```groovy order=simpleSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 10, 10, 20, 20, 30, 30)) simpleSum = source.rollup(List.of(AggGroup("Value"), AggFormula("Sum = sum(Value)")), "Key") ``` To calculate the sum for the root row, every row in the source table is read. The Deephaven engine provides detailed update information for rows in the table (i.e., which rows are added, removed, modified, or shifted). Even though a vector contains many values, it is contained within a single row; therefore, the Deephaven engine does not provide detailed update information for a vector. Every time the table ticks, the formula is completely re-evaluated. ### Formula Reaggregation Formula reaggregation can be used to limit the size of input vectors while evaluating changes to a rollup. When writing your query, be mindful of the requirement that your formula must be applicable to each level of the rollup and produce the same output type. ```groovy order=reaggregatedSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 10, 10, 20, 20, 30, 30)) reaggregatedSum = source.updateView("Sum=Value").rollup(List.of(AggFormula("Sum = sum(Sum)").asReaggregating()), "Key") ``` `reaggregatedSum` and `simpleSum` produce the same results but operate differently. `simpleSum` reads the source table twice: first to calculate individual sums and then to compute the overall total. In contrast, `reaggregatedSum` reads the source table only once to gather the individual sums and then uses those intermediate sums to get the total. If a new row with the key `Delta` is added to the source, `simpleSum` will read all eight rows again to recalculate the sums. However, `reaggregatedSum` will only recalculate the sum for `Delta` and then read the intermediate sums for `Alpha`, `Bravo`, `Charlie`, and `Delta`, not all rows. As the number of keys and the size of the data grow, this difference can significantly impact performance. In the previous example, the `Sum` column evaluated the [`sum(IntVector)`](https://docs.deephaven.io/core/javadoc/io/deephaven/function/Numeric.html#sum(io.deephaven.vector.IntVector)) function at the first level of the rollup and produced a `long`. Since the original table contains an `int` column, the lowest-level rollup provides an `IntVector` to `sum`, while subsequent levels use a `LongVector`. Similarly, the original table has a column called `Value`, but after aggregation, the result is labeled as `Sum`. To resolve this discrepancy, the `updateView` method is used before the rollup to rename the `Value` column to `Sum`. If the rename was omitted and the original data was used directly, it would lead to inconsistent results across different rollup levels. If we ran the same example without the rename: ```groovy syntax source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 10, 10, 20, 20, 30, 30)) reaggregatedSum = source.rollup(List.of(AggFormula("Sum = sum(Value)").asReaggregating()), "Key") ``` We instead get an Exception message indicating that the formula cannot be applied properly, because the `Value` column does not exist in the second level of the rollup: ```text Error running script: io.deephaven.engine.table.impl.select.FormulaCompilationException: Formula compilation error for: sum(Value) ... Full expression : sum(Value) Expression having trouble : Exception type : io.deephaven.engine.table.impl.lang.QueryLanguageParser$ParserResolutionFailure Exception message : Cannot find variable or class Value ``` ### Formula Depth and Keys Formula aggregations may include the constant `__FORMULA_DEPTH__` or `__FORMULA_KEYS__` columns. The `__FORMULA_DEPTH__` column is the depth of the formula aggregation in the rollup tree. The root node of the rollup has a depth of 0, the next level is 1, and so on. The `__FORMULA_KEYS__` column is an [`ObjectVector`](https://docs.deephaven.io/core/javadoc/io/deephaven/vector/ObjectVector.html) containing the keys of the rows at the current level of the rollup. The following formulas demonstrate the values of depth and keys: ```groovy order=depthAndKeys,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Charlie", "Charlie", "Charlie"), stringCol("Key2", "Apple", "Banana", "Banana", "Coconut", "Coconut", "Coconut", "Dragonfruit"), intCol("Value", 10, 20, 15, 20, 15, 30, 35)) depthAndKeys = source.rollup(List.of(AggFormula("Depth = __FORMULA_DEPTH__"), AggFormula("Keys = __FORMULA_KEYS__")), "Key", "Key2") ``` These variables can be used to implement distinct aggregations at each level of the rollup. For example: ```groovy order=firstThenSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 10, 10, 20, 20, 30, 30)) firstThenSum = source.rollup(List.of(AggFormula("Value = __FORMULA_DEPTH__ == 0 ? sum(Value) : first(Value)")), "Key") ``` In this case, for each value of `Key`, the aggregation returns the first value. For the root level, the aggregation returns the sum of all values. When combined with a reaggregating formula, even more interesting semantics are possible. For example, rather than summing all of the values, we can sum the values from the prior level: ```groovy order=firstThenSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 10, 10, 20, 20, 30, 30)) firstThenSum = source.rollup(List.of(AggFormula("Value = __FORMULA_DEPTH__ == 0 ? sum(Value) : first(Value)").asReaggregating()), "Key") ``` Another simple example of reaggregation is a capped sum. In this example, the sums below the root level are capped at 40: ```groovy order=cappedSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"), intCol("Value", 10, 20, 15, 20, 15, 25, 35)) cappedSum = source.rollup(List.of(AggFormula("Value = __FORMULA_DEPTH__ == 0 ? sum(Value) : min(sum(Value), 40)").asReaggregating()), "Key") ``` In this example, the `__FORMULA_KEYS__` column is similarly used to cap at the `Key` column (using `__FORMULA_DEPTH__ == 1` would be equivalent in this case): ```groovy order=cappedSum,source source = newTable( stringCol("Key", "Alpha", "Alpha", "Alpha", "Bravo", "Charlie", "Charlie", "Charlie"), stringCol("Key2", "Apple", "Banana", "Banana", "Coconut", "Coconut", "Coconut", "Dragonfruit"), intCol("Value", 10, 20, 15, 20, 15, 30, 35)) cappedSum = source.rollup(List.of(AggFormula("Value = __FORMULA_KEYS__.get(__FORMULA_KEYS__.size() - 1) == `Key` ? min(sum(Value), 40) : sum(Value)").asReaggregating()), "Key", "Key2") ``` ## Related documentation - [`AggAvg`](../group-and-aggregate/AggAvg.md) - [`AggSum`](../group-and-aggregate/AggSum.md) - [`emptyTable`](./emptyTable.md) - [`join`](../join/join.md) - [`timeTable`](./timeTable.md) - [`treeTable`](./tree.md) - [Pydoc](/core/pydoc/code/deephaven.table.html#deephaven.table.Table.rollup) - [Javadoc](/core/javadoc/io/deephaven/engine/table/hierarchical/RollupTable.html)