Skip to main content

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.TableTools import newTable, stringCol, intColfrom deephaven.conversion_utils import NULL_INT
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
stringStuff = source.view("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)")
oneStringMatch = source.where("Letter = `A`") # match filter
stringSetMatch = source.where("Letter in `A`, `B`, `C`")caseInsensitive = source.where("Letter icase in `a`, `B`, `c`")notInExample = source.where("Letter not in `A`, `B`, `C`") # see "not"
containsExample = source.where("Letter.contains(`A`)")notContainsExample = source.where("!Letter.contains(`A`)")startsWithExample = source.where("Letter.startsWith(`A`)")endsWithExample = source.where("Letter.endsWith(`M`)")

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.TableTools import newTable, stringCol, intCol
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number1", 44, 42, 41, 44, 40, 45, 37),    intCol("Number2", 12, 53, 11, 40, 16, 14, 80),)equalsExample = source.where("round(Number1) = 44")lessThanExample = source.where("Number1 < 40")someManipulation = source.where("(Number1 - Number2) / Number1 > 0.05")modExample1 = source.where("Number1 % 10 = 0")modExample2 = source.where("Number2 % 2 = 0")

Nulls and NaNs#

from deephaven.TableTools import newTable, intCol, stringColfrom deephaven.conversion_utils import NULL_INT
source = newTable(stringCol("Letter", "A", None, "D"), intCol("Number", NULL_INT, 2, 3))
nullExample = source.where("isNull(Letter)") # all data types supportednotNullExample = source.where("!isNull(Letter)")
nanExample = source.where("isNaN(Number)")notNanExample = source.where("!isNaN(Number)")

Do math#

simpleMath = source.updateView("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")

Handle arrays#

arrayExamples = source.updateView("RowNumber = i",\    "PrevRowReference = Number_[i-1]",\    "MultipleRowRef = Number_[i] - Number_[i-1]")

Bin data#

bins = source.updateView("PriceBin = upperBin(Number, 20)")

Use ternaries (if-thens)#

ifThenexample = source\.updateView("SimpleTernary = Number < 2 ? `smaller` : `bigger`",\    "TwoLayers = Number <= 1 ? `small` : Number < 2 ? `medium` : `large`",\    "Conj = Number < 3 && Letter.contains(`A`) ? `special` : `boring`",\    "Disj = Number < 3 || Letter.contains(`A`) ? `fave` : `other`")

Create and use custom variables#

from deephaven.TableTools import emptyTable
globals()["a"] = 7
result = emptyTable(5).update("Y = a")

Create and use custom functions#

See our guides:

from deephaven.TableTools import newTable, intCol
def myFunction(a):    return a * (a + 1)
source = newTable(intCol("A", 1, 2, 3))
result = source.update("X = 2 + 3 * (int)myFunction(A)")