Built-in query language variables

Deephaven provides three built-in query language variables that refer to rows in a table:

• i is a 32-bit int row position.
• ii is a 64-bit long row position.
• k is a 64-bit long row key. A row key is the engine's internal address for a row, not its position.

Combined with the underscore operator, i and ii also read values from the previous and next rows.

On refreshing tables, the engine restricts where you can use these variables. Refreshing table restrictions lists the safe cases, and Alternatives for refreshing tables shows what to use instead.

Use the variables in a query

The following code block creates a table with emptyTable and shows how to use i, ii, and k in a query:

An int holds values only up to 2,147,483,647, so a formula that uses i throws an error on any row position past that limit. Use ii unless you need to pass an int to another function.

In source, row keys happen to equal row positions, so all three columns match. A filter shows the difference: where keeps each row's key from source, but positions start again at 0.

Read values from other rows

To read values from other rows, combine i or ii with the underscore operator. Column_ is the column array variable for the column Column. Column_[ii-1] reads the value of Column in the previous row, and Column_[ii+1] reads it in the next row. An offset before the first row or after the last row returns null.

The following code block uses update to add the previous and next values of RowPosition64Bit to the source table:

Refreshing table restrictions

The engine checks every formula that the following operations run on a refreshing table:

• update
• select
• view
• updateView
• lazyUpdate

If the formula uses i, ii, k, or a column array variable such as Column_ in a way the table below marks unsafe, the operation throws an IllegalArgumentException.

The engine does not check filters passed to where. In a where filter on a refreshing table, use column arrays only as constant offsets. Don't rely on i, ii, or k there either, because their results go stale as rows change.

The following table shows which usages are safe on static, append-only, add-only, and blink tables:

A flat add-only table is an add-only table whose row keys match its row positions (0, 1, 2, and so on). The engine treats it as append-only.

Unsafe usage throws an error like this:

Alternatives for refreshing tables

Constant offsets such as Column_[ii-1] already work on refreshing tables when written in the exact form the engine recognizes. For other positional access on a refreshing table, such as the previous value within each group or a whole-column Column_ reference, use one of these approaches:

• updateBy computes row-based windows within each group and updates only the windows that a change affects.
• The group, update, and ungroup pattern works on any table, but on a refreshing table it causes tick expansion. With tick expansion, a change to one input row reports every row in its group as modified.
• An as-of join works when a column such as a timestamp or sequence number reliably orders the rows.

Which approach is fastest depends on your data, so benchmark each one before you choose.

If you want to work with each group as its own table, you can also partition the table first and apply any of these approaches to each partition.

The examples in this section use a keyed input table as the refreshing source. You can modify and remove rows in a keyed input table, so it is neither append-only nor add-only. The engine therefore rejects formulas that use i, ii, or k on it in any of the operations listed under Refreshing table restrictions.

Each example computes PrevValue, the previous Value in the same Group.

Because refreshingSource is neither append-only nor blink, a formula that uses ii on it throws an error:

Update by

updateBy computes row-based windows within each group and updates them incrementally as rows are added, modified, or removed. It does not need an ordering column, and it avoids the tick expansion of group, update, and ungroup.

The following example uses RollingFormula with a reverse window of 2 and a forward window of -1. Together, these settings define a window that contains only the previous row in the group:

The first row of each group has no previous row, so its window is empty and first returns null.

Group, update, and ungroup

Group the data, perform the update operation within each group, then ungroup. Because the formula works on each group's array, it references values within the group without relying on absolute row positions.

After groupBy, each Value cell holds the array of that group's values. The formula drops the last element with subVector and prepends NULL_INT, so each element of PrevValue is the previous element of Value. ungroup then expands every array column back into rows, including Value and PrevValue. The first row of each group gets a null PrevValue.

Because ungroup expands each group in place, result lists the rows group by group rather than in the source table's order.

As-of joins

If a column such as a timestamp or sequence number reliably orders the rows, use an as-of join on that column instead of row position.

The aj matches Group exactly. Timestamp > Timestamp then picks the row in the same group with the largest Timestamp that is less than the current row's. That row's Value becomes PrevValue, the previous Value in the group. The first row of each group has no earlier row, so its PrevValue is null.

Partition the table

Use this approach if your data is already a partitioned table, or if you want each group as its own table. In the following example, partitionBy creates one constituent table, or partition, for each Group value.

Partitioning does not lift the refreshing table restrictions, because the engine checks each partition as its own table:

• i and ii work in a partition that is static, append-only, or blink.
• In any other partition, use updateBy, the group, update, and ungroup pattern, or an as-of join, as the following example does.

In the example:

• transform applies an aj to each partition. Each partition holds a single Group value, so the aj matches only on Timestamp.
• The prevValue closure opens an execution context because the table operations inside it need one, and transform doesn't pass the console's execution context to the closure.
• merge combines the results into one table.

Related documentation

• Table types
• Table update model
• Execution context
• Built-in constants
• Built-in functions
• Query string overview
• Filters
• Formulas
• Work with arrays
• Group and ungroup data
• Rolling aggregations
• Inexact, time-series, and range joins
• Input tables
• Partitioned tables
• Special variables and constants
• aj
• concat
• emptyTable
• first
• groupBy
• InputTable
• lazyUpdate
• merge
• partitionBy
• RollingFormula
• select
• transform
• ungroup
• update
• updateBy
• updateView
• view
• where
• ExecutionContext Javadoc
• KeyedArrayBackedInputTable Javadoc
• SafeCloseable Javadoc