Query Cheat Sheet

// access historical data
db.t("Namespace", "Table")

// access intraday data
db.i("Namespace", "Table")

// access intraday data w/o ticking
db.i("Namespace", "Table", isLive)
db.getNamespaces()
db.getTableNames("Namespace")

// get a table with available namespaces and table names
db.getCatalog()

t.where("Condition"…)
    // examples
    t.where("Date > `2018-01-01`")
    t.where("Timestamp <= '2018-01-01T16:00:00 NY'")
    t.where("USym = `AAPL`")
    // filters a list of values
    t.where("USym in `AAPL`, `MSFT`, `GOOG`")
    // case insensitive
    t.where("USym icase in `aapl`, `msft`, `goog`")
    t.where("USym not in `NVDA`, `AMZN`")

t1.whereIn(t2, "Match"…) // filter to rows that match
t1.whereNotIn(t2, "Match"…) // filter to rows that do not match
    // examples
    t1.whereIn(t2, "USym")
    // can match different column names
    t1.whereIn(t2, "USym=UnderlyingSym")

// first number of rows
t.head(100)

// first percentage of rows
t.headPct(0.25)

// first number of rows for each subset
t.headBy(rows, "Column"…)

// last rows
t.tail(rows)
t.tailPct(decimalNum)
t.tailBy(rows, "Column"…)

t.sort("Column"…)
t.sortDescending("Column"…)

// reverses order of entire table
t.reverse()

// restricts sorting to specified columns
t.restrictSortTo("Column"...)
// removes sorting restrictions
t.clearSortingRestrictions()

t.select("ColumnFormula"…)

// evaluates data once and stores it in memory
t.update("ColumnFormula"…)

t.view("ColumnFormula"…)

// evaluates data but doesn't store results in memory
t.updateView("ColumnFormula"…)

// only keep unique values
t.selectDistinct("Column"…)

t.dropColumns("Column"…)
t.renameColumns("ColumnRename"…)
t.moveColumns(index, "Column"…)
t.moveUpColumns("Column"…)

// data in columns other than those specified are grouped into arrays
t.by("Column"…)

// distributes values in specified columns into arrays
// each array value becomes its own row
t.ungroup("Column"…)

tables = t.byExternal("Column"…)
tables.getKeySet() //returns array of keys
table = tables.get("Key")

// The following is required if the table broken down by multiple columns
import com.fishlib.datastructures.util.SmartKey
table = tables.get(new SmartKey("Key"…))

t.firstBy("Column"…)
t.lastBy("Column"…)
t.sumBy("Column"…)
t.avgBy("Column"…)
t.stdBy("Column"…)
t.varBy("Column"…)
t.medianBy("Column"…)
t.minBy("Column"…)
t.maxBy("Column"…)
t.countBy("CountColumn", "Column"…)

t.by(AggCombo(Agg…), "Column"…)
AggFirst("Column"…)
AggLast("Column"…)
AggSum("Column"…)
AggAvg("Column"…)
AggStd("Column"…)
AggVar("Column"…)
AggMedian("Column"…)
AggMin("Column"…)
AggMax("Column"…)
AggCount("Column")

// combined aggregation example
t.by(AggCombo(AggSum("Dollars", "Size"), AggAvg("AvgSize=Size")),"Symbol")

t1.naturalJoin(t2, "Match…", "ColumnToAdd…")
t1.exactJoin(t2, "Match…", "ColumnToAdd…")
t1.join(t2, "Match…", "ColumnToAdd…")
t1.leftJoin(t2, "Match…", "ColumnToAdd…")

// as-of joins matching subsets need to be ordered
t1.aj(t2, "Match…, AjMatch", "ColumnToAdd…")
t1.raj(t2, "Match…, AjMatch", "ColumnToAdd…")

yyyy-mm-ddThh:mm:ss.nanos TZ // date-time
#y#m#w#dT#h#m#s // period
hh:mm:ss.nanos // duration

// constants representing nanoseconds
 threeSeconds = 3 * SECOND
 fiveMinutes = 5 * MINUTE
 twoHours = 2 * HOUR
 sevenDays = 7 * DAY

 currentDateNy()
 lastBusinessDateNy()
 formatDateNy(DateTime) //converts to a date string
 currentTime()

t.formatColumns("ColumnName=Decimal(`.00#`)"…)
   // or Decimal(`\$.00#`) or Decimal(`0.00#`)
   // # means only display non-zero numbers in position
   // 0 means display zeroes in position

t.formatColumns("ColumnName=Color")
t.formatColumnsWhere("ColumnName", "Condition", "Color")
t.formatRowWhere("Condition", "Color")
t.formatColumns("ColumnName=heatmap(Column, minValue, maxValue, minColor, maxColor)")

emptyTable(rows)

// create table with columns specified below
newTable(column…)

// append table rows to one another with same columns
merge(table…)

t.getMeta()