Query language cheat sheet

note

Backticks ` are used for strings and single quotes ' are used for timestamps and characters.

String examples

Manipulate strings

You can use any of the standard Java String operators in your queries, as the following examples show:

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([
    string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
    int_col("Number", [NULL_INT, 2, 1, NULL_INT, 4, 5, 3]),
    string_col("Color", ["red", "blue", "orange", "purple", "yellow", "pink", "blue"]),
    int_col("Code", [12, 14, 11, NULL_INT, 16, 14, NULL_INT]),
])


string_stuff = source.view(formulas=["NewString = `new_string_example_`",
    "ConcatV1 = NewString + Letter",\
    "ConcatV2 = NewString + `Note_backticks!!`",\
    "ConcatV3 = NewString.concat(Letter)",\
    "ConcatV4 = NewString.concat(`Note_backticks!!`)",\
    "StartBool = Letter.startsWith(`A`)",\
    "NoEndBool = !Letter.endsWith(`D`)",\
    "ContainsBool = Letter.contains(`A`)",\
    "NoBool = !Letter.contains(`B`)",\
    "FirstChar = Letter.substring(0,1)",\
    "LengthString = Letter.length()",\
    "CharIndexPos = ConcatV1.charAt(1)",\
    "SubstringEx = ConcatV1.substring(0,1)",\
    "FindIt = NewString.indexOf(`_`)",\
    "FindItMiddle = NewString.indexOf(`_`, FindIt + 1)",\
    "FindLastOf = NewString.lastIndexOf(`_`)",\
    "SplitToArrays = NewString.split(`_`)",\
    "SplitWithMax = NewString.split(`_`, 2)",\
    "SplitIndexPos = NewString.split(`_`)[1]",\
    "LowerCase = Letter.toLowerCase()",\
    "UpperCase = NewString.toUpperCase()",\
    "DoubleToStringv1 = Number + ``",\
    "DoubleToStringv2 = String.valueOf(Number)",\
    "DoubleToStringv3 = Double.toString(Number)",\
    "StringToDoublev1 = Double.valueOf(DoubleToStringv1)"])

source

string_stuff

one_str_match = source.where(filters=["Letter = `A`"]) # match filter
str_set_match = source.where(filters=["Letter in `A`, `B`, `C`"])
case_insensitive = source.where(filters=["Letter icase in `a`, `B`, `c`"])
not_in_example = source.where(filters=["Letter not in `A`, `B`, `C`"]) # see "not"
contains_example = source.where(filters=["Letter.contains(`A`)"])
not_contains_example = source.where(filters=["!Letter.contains(`A`)"])
starts_with_example = source.where(filters=["Letter.startsWith(`A`)"])
ends_with_example = source.where(filters=["Letter.endsWith(`M`)"])

one_str_match

str_set_match

case_insensitive

not_in_example

contains_example

not_contains_example

starts_with_example

ends_with_example

Number examples

caution

Using i and ii is not a good idea in non-static use cases, as calculations based on these variables aren't stable.

from deephaven import new_table
from deephaven.column import string_col, int_col

source = new_table([
    string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
    int_col("Number1", [44, 42, 41, 44, 40, 45, 37]),
    int_col("Number2", [12, 53, 11, 40, 16, 14, 80]),
])

equals_example = source.where(filters=["round(Number1) = 44"])
less_than_example = source.where(filters=["Number1 < 40"])
some_manipulation = source.where(filters=["(Number1 - Number2) / Number1 > 0.05"])
mod_example1 = source.where(filters=["Number1 % 10 = 0"])
mod_example2 = source.where(filters=["Number2 % 2 = 0"])

source

equals_example

less_than_example

some_manipulation

mod_example1

mod_example2

Nulls and NaNs

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([string_col("Letter", ["A", None, "D"]), int_col("Number", [NULL_INT, 2, 3])])

null_example = source.where(filters=["isNull(Letter)"]) # all data types supported
not_null_example = source.where(filters=["!isNull(Letter)"])
nan_example = source.where(filters=["isNaN(Number)"])
not_nan_example = source.where(filters=["!isNaN(Number)"])

source

null_example

not_null_example

nan_example

not_nan_example

Do math

simple_math = source.update_view(formulas=["RandomNumber = Math.random()",\
    "RandomInt100 = new Random().nextInt(100)",\
    "Arithmetic = Number * RandomInt100",\
    "SigNum = signum(RandomNumber - 0.5)",\
    "Signed = SigNum * Arithmetic",\
    "AbsDlrVolume = abs(Signed)",\
    "Power = Math.pow(i, 2)",\
    "Exp = Number * 1E2",\
    "Log = Number * log(2.0)",\
    "Round = round(RandomInt100)",\
    "Floor = floor(RandomInt100)",\
    "Mod = RandomInt100 % 5",\
    "CastInt = (int)AbsDlrVolume",\
    "CastLong = (long)AbsDlrVolume"])

simple_math

Handle arrays

array_examples = source.update_view(formulas=["RowNumber = i",\
    "PrevRowReference = Number_[i-1]",\
    "MultipleRowRef = Number_[i] - Number_[i-1]"])

array_examples

Bin data

bins = source.update_view(formulas=["PriceBin = upperBin(Number, 20)"])

bins

Use ternaries (if-thens)

if_then_example = source\
.update_view(formulas=["SimpleTernary = Number < 2 ? `smaller` : `bigger`",\
    "TwoLayers = Number <= 1 ? `small` : Number < 2 ? `medium` : `large`",\
    "Conj = Number < 3 && (!isNull(Letter) && Letter.contains(`A`)) ? `special` : `boring`",\
    "Disj = Number < 3 || (!isNull(Letter) && Letter.contains(`A`)) ? `fave` : `other`"])

if_then_example

Create and use custom variables

from deephaven import empty_table

a = 7

result = empty_table(5).update(formulas=["Y = a"])

result

Create and use custom functions

See our guides:

• How to write a Python function

from deephaven import new_table
from deephaven.column import int_col

def my_function(a):
   return a * (a + 1)

source = new_table([int_col("A", [1, 2, 3])])
result = source.update(formulas=["X = 2 + 3 * (int)my_function(A)"])

source

result