Basic Table Operations

This page covers the table operations that appear in almost every query: operations for column manipulation, filtering, sorting, grouping and aggregation, and combining tables. These operations use query strings extensively. The Query Strings chapter, later in this crash course, covers query strings in detail.

Table operations are integral to the Deephaven Query Language (DQL). Besides creating tables, the previous chapters of the crash course used five table operations:

• updateView, which adds columns to or modifies existing columns in a table.
• sumBy, which groups rows by the columns you name and sums every other column within each group.
• renameColumns, which renames columns in a table.
• sort, which sorts a table by the given columns from least to greatest.
• sortDescending, which sorts a table by the given columns from greatest to least.

You can think of these as different transformations applied to the data in a table. The rest of this page builds on them with the basic table operations that make up the backbone of the most common queries.

The operations on this page also work on ticking tables, such as the ones in Create Tables, the same way they work on the static tables used in these examples. Blink tables, ticking tables that keep only the rows added in the current update cycle, are the exception: a few operations on this page behave differently on them or aren't supported.

Many of the code blocks on this page use the following table, t, as the root table. This is a simple table with 100 rows and contains only a Timestamp column.

Basic column manipulation

Add columns

update creates in-memory columns. An in-memory column computes all of its values immediately and stores the results in memory. The following code block adds three columns to t. Most examples in the rest of this section and in the Filter and Sort sections use the result, tUpdated.

• IntRowIndex: 32-bit integer row indices.
• LongRowIndex: 64-bit integer row indices.
• Group: modulo of IntRowIndex and 5.

updateView creates formula columns. A formula column stores only its formula. Deephaven computes each value on the fly when something reads it. Use updateView when calculations are simple or when queries read only small subsets of the data.

The following code block adds two new columns to t.

• IntRowIndex: 32-bit integer row indices.
• Group: modulo of IntRowIndex and 5.

lazyUpdate creates memoized columns. Like a formula column, a memoized column computes values on demand. Unlike a formula column, it caches the results. It computes the result for each unique set of input values only once and returns the cached result for later requests with the same inputs.

The following code block adds two new columns to tUpdated.

• GroupSqrt: square root of Group.
• GroupSquared: square of Group.

Because Group has only five unique values, Deephaven performs only 10 calculations to populate the two new columns.

Select columns

select and view both create tables that contain only the columns you list: existing columns and new columns computed from them. They differ in the kind of column they create, which determines how much memory they allocate and when formulas are evaluated. Performance and memory considerations dictate the best method for a particular use case.

select, like update, creates in-memory columns. The following code block selects two existing columns from tUpdated and adds a new column.

view, like updateView, creates formula columns. The following code block selects two existing columns from tUpdated and adds a new column.

There's a lot to consider when choosing between in-memory, formula, and memoized columns in queries. For guidance, see Choose the right column selection method.

Drop columns

dropColumns removes columns from a table.

You can also use view and select to remove columns by omission. Both tables below drop IntRowIndex and LongRowIndex because the column list leaves them out.

Filter

By condition

where filters rows from a table based on a condition. The following code block keeps only rows in tUpdated where IntRowIndex is an even number.

You can apply multiple filters in a single statement.

A where operation that applies multiple conditions keeps only rows that meet all of them. To keep rows that meet one or more of the conditions, use where with the logical OR operator, ||.

In addition to where, Deephaven offers whereIn and whereNotIn, which filter table data based on another table.

See the filtering guide for more information.

By row position

Methods that filter data by row position keep only a portion of data at the top, middle, or bottom of a table.

• head: Keep the given number of rows from the top.
• headPct: Keep the first fraction of rows, where 0.1 means 10%.
• tail: Keep the given number of rows from the bottom.
• tailPct: Keep the last fraction of rows, where 0.1 means 10%.
• slice: Keep rows from a start position up to, but not including, an end position.
• slicePct: Keep rows between two fractions of the table, such as 0.4 and 0.6.

See the filtering guide for more information.

Sort

You used sort in the previous chapter to order a table by one column. The following code block sorts tUpdated by Group in ascending order.

You can also sort by more than one column.

To sort in descending order, use sortDescending.

To sort multiple columns in different directions, pass sort a list of SortColumn objects built with SortColumn.asc and SortColumn.desc. Both methods take a column name wrapped in ColumnName.of.

See the sorting guide for more information.

Group and aggregate data

Grouping data places rows into groups based on zero or more key columns. Rows that have the same values in the key columns go into the same group. Aggregation calculates summary statistics over a group of data. Grouping and aggregation are key components of data analysis, especially in Deephaven queries. On a blink table, aggregations such as avgBy and aggBy cover every row the table delivers after the aggregation is created, not only the rows in the current update cycle.

The examples in this section use a new t with random X and Y columns plus Group and Letter columns to group by. It replaces the root table t from the top of this page.

Group and ungroup data

groupBy groups table data into array columns. You can group an entire table.

You can also group data by one or more key columns.

ungroup expands array columns back into separate rows. It reverses groupBy, except that rows come back in group order and any key columns come first.

See the grouping and ungrouping guide for more information.

Single aggregations

Single aggregations apply one type of aggregation to a table, either over the whole table or for each group. See the list of single aggregators.

The following code uses avgBy to calculate the average of columns X and Y in the table t. The code passes no key columns, so avgBy averages over the entire table.

Aggregations often operate on groups of data. The following example calculates the average of X and Y for each unique value in Group.

A single aggregation can also use multiple key columns.

Multiple aggregations

To apply multiple aggregations in a single operation, pass one or more of the aggregators into aggBy.

The following code block calculates the average of X and the median of Y, grouped by Group and Letter. It names the resulting columns AvgX and MedianY, respectively.

Cumulative and rolling aggregations

Deephaven also offers a large library of cumulative and rolling calculations. You use them through the updateBy table operation and the io.deephaven.api.updateby package. Unlike avgBy and aggBy, which return one row per group, updateBy keeps every row of the table and adds its results as new columns.

The following code block calculates the cumulative sum of X in t.

In the result of updateBy, each row of SumX holds the running total of X from the first row through that row.

updateBy can also limit these calculations to a window of rows. The following code block calculates the sum of the current row and the previous 9 rows in column X of table t.

updateBy also supports performing these calculations over groups of data. The following code block repeats the cumulative sum and the 10-row rolling sum from the previous examples, this time grouped by Group and Letter.

You can also define windows by time. The following code block calculates a 16-minute rolling average of X, grouped by Group.

Windows can look backward, forward, or both ways. A forward-looking window counts the row ahead of the current row as the first row forward. The following example calculates rolling averages over these windows:

• The current row and the previous 9 minutes.
• The current row and the previous 8 rows.
• The current row, the previous 10 rows, and the next 10 rows.
• The next 8 rows.

See the updateBy user guide to learn more.

Combine tables

Deephaven combines tables in two ways: merging and joining. You can visualize merging as stacking tables vertically, whereas joining is horizontal: it appends columns from matching rows of another table, based on values in key columns.

Each subsection below defines its own tables to demonstrate merging and joining tables in Deephaven.

Merge tables

merge combines any number of tables, as long as they all have the same schema: the same column names and data types. See the merge tables guide for more information.

Join tables

Joining combines two tables based on one or more key columns. Key columns hold the values that both tables share. The table you call the join method on is the left table, and the table you pass as an argument is the right table. The OuterJoinTools methods take the left table first and the right table second.

This page shows three kinds of join: exact joins, relational joins, and time-series joins. The exact and relational joins guide covers the first two in depth. The inexact, time-series, and range joins guide covers time-series joins and range joins.

Consider the following three tables.

The tables t1 and t2 have the common column Letter, and Letter contains matching values in both tables. The following example joins them on Letter with naturalJoin, which keeps every row of the left table and appends the columns of the matching row from the right table. Where the right table has no matching row, the appended columns are null. Exact joins covers naturalJoin in more detail.

Joins can use more than one key column. The tables t2 and t3 have both the Letter and Color columns in common, and they both have matching values. The following code block joins the two tables on both columns. It passes the key columns as one comma-separated string, the format every join example on this page uses.

By default, the join operations on this page append all non-key columns from the right table onto the left table. The optional columnsToAdd argument, also a comma-separated string, specifies which columns to append. The following code block joins t2 and t3 on the Letter column, but only appends the Value column from t3.

t2 and t3 share the Color column, so appending Color from t3 onto t2 results in a name conflict error. To avoid the error, rename the column with renameColumns before the join, or rename it in the columnsToAdd argument.

The following example renames Color in t3 to Color2 in the columnsToAdd argument.

Exact joins

Deephaven has two exact joins: naturalJoin, which you used above, and exactJoin. Like naturalJoin, exactJoin keeps every row of the left table. exactJoin always throws an error if a key value appears more than once in the right table, and naturalJoin does so by default.

Consider the following tables.

tLeft1 and tRight1 both have a Color column with the same data type. In tRight1, Color has no duplicates. Additionally, all colors in tLeft1 have an exact match in tRight1. In cases like this, exactJoin is the most appropriate join operation.

The following tables are like the previous pair, except that tLeft2 contains the color Green, which is not in tRight2.

Here, exactJoin fails because it requires every key in the left table to have a match in the right table. Instead, use naturalJoin, which fills unmatched rows with nulls.

Relational joins

Relational joins are similar to SQL joins: a left-table row can match several right-table rows, and the result has one row for each match. The two outer joins on this page, leftOuterJoin and fullOuterJoin, also keep rows that have no match. Both are experimental, so their API may change.

Consider the following tables.

join keeps only rows whose key values match in both tables.

leftOuterJoin includes all rows from the left table as well as rows from the right table where a match exists. The result contains null values where no match exists.

fullOuterJoin includes all rows from both tables, regardless of whether a match exists. The result contains null values where no match exists.

Time-series (inexact) joins

Time-series (inexact) joins match on zero or more exact key columns plus a final key column that does not need to match exactly. The final key column is usually a timestamp. If the right table has no row that qualifies, the appended columns are null.

Consider the following tables, which contain quotes and trades for two different stocks.

aj matches each row in the left table with the closest row in the right table without going over. To see the most recent quote at or before a trade, use aj.

raj matches each row in the left table with the closest row in the right table without going under. To see the first quote at or after a trade, use raj.

Next, Time and Calendars shows how to work with date-time data and business calendars in these operations.