Skip to main content

Aggregates and Functions

Stateless Functions​

Stateless Functions work with fields of one specific class/field.

Functions for Numerics​

ExpressionExplanation
max(x,y)Maximum
min(x,y)Minimum
abs(x)Absolute value
sqrt(x)Square root
log(x)Natural logarithm (base e).
exp(x)Euler's number e raised to the power of a value
floor(x)Largest value that is less than or equal to the argument and is equal to a mathematical integer.
ceil(x)Smallest value that is greater than or equal to the argument and is equal to a mathematical integer.
#Shell format

==> select max(1,23)

>_,TIMESTAMP,SYMBOL,TYPE,"MAX (1, 23)"
0,_,,CUSTOM,23

==> select abs(-10)

>_,TIMESTAMP,SYMBOL,TYPE,ABS (-10)
0,_,,CUSTOM,10

==> select float32(10.1123456789)

>_,TIMESTAMP,SYMBOL,TYPE,FLOAT32 (101123456789E-10)
0,_,,CUSTOM,10.112346

Functions for VARCHAR​

Functions for VARCHAR datatype.

ExpressionExplanation
length(s)string length
uppercase(s)uppercase string
lowercase(s)lowercase string
reverse(s)reverse string
indexof(x, y)find y in x, return index
substr(x, start, end)substring
toTimestamp(string, format)parse string into timestamp(ms)
toTimestampNs(string, format)parse string into timestamp(ns)
toTimestamp(string)parse string into timestamp(ms) (use default Timestamp format*)
toTimestampNs(string)parse string into timestamp(ns) (use default Timestamp format*)
#Shell format

==> select length('hello')

>_,TIMESTAMP,SYMBOL,TYPE,LENGTH (hello)
0,_,,CUSTOM,5

==> select uppercase('hello')

>_,TIMESTAMP,SYMBOL,TYPE,UPPERCASE (hello)
0,_,,CUSTOM,HELLO 

==> select lowercase('HeLlo')

>_,TIMESTAMP,SYMBOL,TYPE,LOWERCASE (HeLlo)
0,_,,CUSTOM,hello 

==> select reversed('hello')

>_,TIMESTAMP,SYMBOL,TYPE,REVERSED (hello)
0,_,,CUSTOM,olleh

==> select indexof('h', 'hello')

>_,TIMESTAMP,SYMBOL,TYPE,"INDEXOF (h, hello)"
0,_,,CUSTOM,-1 

==> select substr('Hello, World!', 7, 14)

>_,TIMESTAMP,SYMBOL,TYPE,"SUBSTR (Hello, World!, 7, 14)"
0,_,,CUSTOM,World!
SELECT length(entry.exchangeid) AS length
FROM bittrex
ARRAY JOIN entries IN entry
info

toTimestamp and toTimestampNs are available since 5.6.111+.

info

* Default format for toTimestamp (and toTimestampNs):

[yyyyMMdd][yyyy-MM-dd][yyyy-DDD]['T'[HHmmss][HHmm][HH:mm:ss][HH:mm][.SSSSSSSSS][.SSSSSS][.SSS][.SS][.S]][OOOO][O][z][XXXXX][XXXX]['['VV']']
-- Parse timestamp with default format:
select toTimestamp('2016-10-27T16:36:08.993+02:00:00[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+02:00[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+020000[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+0200[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993GMT+1[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993PST[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+02:00:00')
select toTimestamp('2016-10-27T16:36:08.993Z')
select toTimestamp('2016-10-27T16:36:08.993')
select toTimestampNs('2016-10-27T16:36:08.000993')
select toTimestampNs('2016-10-27T16:36:08.000000993')
select toTimestamp('2016-10-27T16:36:08+0200')
select toTimestamp('2016-10-27T16:36:08')
select toTimestamp('2016-10-01T16:36:08Z')
select toTimestamp('2016-10-27')
select toTimestamp('20161223T163608')
select toTimestamp('20161223T1636')

-- Parse timestamp with specified format:
SELECT toTimestamp('2022-08-27 22:32:02.123', 'yyyy-MM-dd HH:mm:ss.SSS')
SELECT toTimestampNs('2022-08-27 22:32:02.123456789', 'yyyy-MM-dd HH:mm:ss.SSSSSSSSS')
SELECT toTimestamp('2024-08 27 22:32 - 02 123 Europe/Moscow', 'yyyy-MM dd HH:mm - ss SSS v')

Functions for Arrays​

ExpressionExplanation
empty(arr)is array empty
notempty(arr)is array not empty
size(arr)array size
max(arr)array maximum
min(arr)array minimum
mean(arr)array mean
sum(arr)array sum
enumerate(arr)array indices
sort(arr)array sort
indexof(arr, el)find index of element
any(arr)if any element is true
all(arr)if all elements are true
Examples with functions for arrays
SELECT
sum(entries.size) AS SUM
FROM bittrex

SELECT
avg(entries.price) AS AVG
FROM bittrex

SELECT sort(entries.price) FROM bittrex

SELECT size(entries.price) FROM bittrex

SELECT 
ANY(entries.price > 200)
FROM bittrex

SELECT * FROM bitfinex 
WHERE notEmpty(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])

Internal Functions​

ExpressionExplanation
now()returns the current timestamp as a constant at the moment of the query's execution
currentTimeMs()continually returns the current time in milliseconds as the query is being executed, providing a dynamic time value throughout the execution process
streams()returns an array of streams with full schema information
typeOf(expr)returns actual type of expression or object
symbols(stream key)returns an array of symbols in stream
spaces(stream key)returns an array of spaces in stream
stateless_functions()returns an array of stateless functions supported by QQL
stateful_functions()returns an array of stateful functions supported by QQL
Examples with internal functions
-- Select all streams
SELECT s.key
ARRAY JOIN streams() AS s

-- Select all symbols from secruties stream
SELECT s
ARRAY JOIN symbols('securities') AS s

-- Select all stateless functions with arguments and types
SELECT f.id, f.arguments.name, f.arguments.dataType.baseName
ARRAY JOIN STATELESS_FUNCTIONS() as f

-- Count entries grouped by type
select typeof(e), count{}() from KRAKEN 
array join this.entries as e
group by typeof(e)

Stateful Functions​

Keywords​

Time template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER/RESET] OVER [EVERY] TIME(interval[, offset])

Count template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER] OVER COUNT(100)
  • RUNNING - the result is returned for every input message. For example we compute running max, it means, that for each message we receive max for this message and all previous messages in a group.
  • OVER [EVERY] TIME(5m) - computes functions separately for each (5 minutes) interval and delivers updates at the end of every (5 minutes) interval. If we add EVERY, we expect empty result for empty 5m intervals when no messages were published.
  • OVER TIME(1d, 10h) - computes functions separately for each 1 day interval with 10 hours offset and delivers updates daily at 10:00.
  • TRIGGER OVER [EVERY] TIME(5m) - receive function results every 5 minutes and function is computed over the entire stream. If we add EVERY, we expect update for empty 5m intervals when no messages were published.
  • OVER COUNT(100) - compute function separately for each 100 messages group and receive update every 100 messages.
  • TRIGGER OVER COUNT(100) - compute function over the entire stream, but receive updates every 100 messages.
  • RESET - is used to reset function based on provided conditions. For example, reset running calculation for each time period instead of carrying on with the cumulative counting.
caution

OVER TIME produces interval message only at a time when a new message arrives from a source. For example, for OVER TIME (1m) if message for new interval arrives at 10:51:08.458, QQL will produce interval message with timestamp 10:51:00.000 with 8.458 seconds delay.

caution

Offset for OVER TIME(interval[, offset]) is available since 5.6.78.

Functions Syntax​

function{initArg1: value1, initArg2: value2, ...}(argValue1, argValue2)

OR

function{value1, value2, ...}(argValue1, argValue2, ...)

Init arguments could be passed to function like named args and also like position arguments in {...} braces. Init arguments are constant, so you cannot pass here selectors or anything similar. Arguments are passed to function in (...) parentheses as positional arguments.

Examples​

messagemax(field)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampmax
1
2
3
4
5
6
7
8
9
10
11
12
132021-03-19T08:07:41.1044737
messagemax(field) over time(1s)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
timestampfield
2021-03-19T08:07:32623
2021-03-19T08:07:333467
2021-03-19T08:07:342374
2021-03-19T08:07:35347
2021-03-19T08:07:37-347
2021-03-19T08:07:384737
2021-03-19T08:07:39499
2021-03-19T08:07:427
messagemax(field) trigger over count(5)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampfield
1
2
3
4
52021-03-19T08:07:33.1853467
6
7
8
9
102021-03-19T08:07:36.1013467
11
12
132021-03-19T08:07:41.1044737
-- max price for the entire stream
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex

-- max for every hour time period
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (1h)

-- max from all prior messages for each message
SELECT
RUNNING 
max{} (max(entries.price)) AS MAX 
FROM bittrex

-- current and total MAX for every message for the time interval
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (10m)

-- max value from prior 10 messages for each interval
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance
OVER COUNT (10)

-- every 10 messages returns max from all prior messages
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance
TRIGGER OVER COUNT (10)

-- every 10 min returns max from all prior messages
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
TRIGGER OVER TIME (10m)

-- returns max from all prior messages for every message, resets every 30 min but does not return a snapshot
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
RESET OVER TIME (30m)

-- incremental update for every message and a snapshot for every 10 messages
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM binance
OVER COUNT (10)

-- select BID and ASK price FROM L2EntryNew
-- cast entries array to L2EntryNew type
-- return max for every selection every 1 min
-- filter by packageType and symbol
WITH 
  (entries AS array(deltix.timebase.api.messages.universal.L2EntryNew)) AS 'entries',
  entries[side == BID].price AS 'bidPrices',
  entries[side == ASK].price AS 'askPrices'
SELECT
  max{}(max(askPrices)) AS 'highAsk',
  max{}(max(bidPrices)) AS 'highBid',
  min{}(min(askPrices)) AS 'lowAsk',
  min{}(min(bidPrices)) AS 'lowBid'
FROM binance
OVER TIME(1m)
WHERE packageType == PERIODICAL_SNAPSHOT
AND symbol == 'BTC/USDT'

-- one-minute bars based on trades 
WITH 
  entries[THIS IS TradeEntry] AS 'entries'
SELECT
  sum{}(sum(entries.size)) AS 'volume',
  first{}(entries[0].price) AS 'open',
  last{}(entries[-1].price) AS 'close',
  max{}(max(entries.price)) AS 'high',
  min{}(min(entries.price)) AS 'low'
FROM binance
OVER TIME(1m)
WHERE symbol == 'BTC/USDT' AND size(entries) > 0

-- different price indicators 
WITH 
  entries[THIS IS TradeEntry].price AS 'prices'
SELECT
  sma{timePeriod: 1h}(price) AS 'sma',
  (bollinger{timeWindow: 1h}(price) AS 'bollinger').*,
  cma{}(price) AS 'cma'
FROM bitfinex
ARRAY JOIN prices AS 'price'
OVER TIME(10m)
WHERE symbol == 'BTC/USDT' AND size(entries) > 0

-- simple moving average for Prices for 1 min time period returned every 1 hour for each symbol
SELECT
  sma{timePeriod: 1m}(max(entries.price))
FROM bittrex
TRIGGER OVER TIME(1h)
GROUP BY symbol

-- Volume-Weighted Average Price (VWAP)
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
OVER TIME (1m)
WHERE symbol == 'BTCUSD'

-- VWAP cumulative
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
TRIGGER OVER TIME (1m)
WHERE symbol == 'BTCUSD'

-- counts the number of unique FX instruments in the securities stream
SELECT size(collect_unique{}(symbol)) FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX

-- builds L2 order book with 10 levels size for bittrex BTC/USDT and returns snapshots every 10 seconds
SELECT orderbook{maxDepth: 10}(this.packageType, this.entries)
FROM bittrex
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

-- builds L2 order book and returns snapshots in Universal format
WITH
  orderbook{maxDepth: 10}(this.packageType, this.entries) as book
SELECT
  book as entries, 
  PERIODICAL_SNAPSHOT as packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM BITFINEX
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

-- builds L3 order book and returns snapshots in Universal format (L3 is supported since 5.6.95+) for each symbol
WITH
  orderbook{maxDepth: 10, model: 'L3'}(this.packageType, this.entries) as book
SELECT
  book as entries, 
  PERIODICAL_SNAPSHOT as packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM MBO
OVER TIME (10s)
WHERE size(book) > 0
GROUP BY symbol

-- calculate sum of trade quantities for each day, given that the trading day starts at 15:00 UTC
SELECT
  SUM{}(tradeQuantity * -1 if orderEvent:Side == SELL else tradeQuantity) 
FROM "ember-messages"
OVER Time(1d, 15h)
WHERE orderStatus IN (COMPLETELY_FILLED, PARTIALLY_FILLED)
 AND symbol IN ('BTCUSD')
GROUP BY symbol

List of Functions​

IDInit argsArgsReturnsDescription
COUNTINT64counts messages
MAXBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes maximum value
MAXtimePeriod: INT64FLOAT64?FLOAT64?computes maximum over time window with given timePeriod
MAXperiod: INT64FLOAT64?FLOAT64?computes maximum over count window with given period
MINBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes minimum value
MINtimePeriod: INT64FLOAT64?FLOAT64?computes minimum over time window with given timePeriod
MINperiod: INT64FLOAT64?FLOAT64?computes minimum over count window with given period
SUMINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes sum of given values
SUMFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes sum of given values
SUMtimePeriod: INT64DECIMAL64?DECIMAL64?computes sum of values in time window with given time period
SUMperiod: INT32DECIMAL64?DECIMAL64?computes sum of values in count window with given period
AVGINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes avg of given values
AVGFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes avg of given values
SMAtimePeriod: INT64FLOAT64?FLOAT64?computes moving average over time window with given timePeriod
SMAperiod: INT64FLOAT64?FLOAT64?computes moving average over count window with given period
CMAFLOAT64?FLOAT64?computes cumulative moving average
EMAperiod: INT32FLOAT64?FLOAT64?computes exponential moving average with given period
EMAfactor: FLOAT64FLOAT64?FLOAT64?computes exponential moving average with given factor
ADXRperiod: INT64open, high, low, close, volume (FLOAT64)ADXRMessagecomputes Average Directional Movement Rating indicator (read more)
ATRperiod: INT64open, high, low, close, volume (FLOAT64)FLOAT64computes Average True Range (read more)
BOLLINGERpointWindow: INT64 or timeWindow: INT64, factor: FLOAT64FLOAT64BollingerMessagecomputes Bollinger Bands (read more)
KAMAperiod: INT64FLOAT64FLOAT64computes Kaufman's Adaptive Moving Average (read more)
LSMApointWindow: INT64 or timeWindow: INT64, useDateTime: BOOLEANFLOAT64LSMAMessagecomputes Least Squares Moving Average (read more)
MMAperiod: INT64FLOAT64FLOAT64computes Modified Moving Average (read more)
COLLECT_UNIQUEVARCHAR?ARRAYS OF VARCHARScollects and returns an array of unique strings
lastNotNullBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?fills null gaps with previous not null value
windowperiod: INT64 or timePeriod: INT64FLOAT64?ARRAY OF FLOAT64?builds fixed size (if period is set) or time (if timePeriod is set) window and returns it as an array
orderBookmaxDepth: INT32, model: VARCHAR ('L1', 'L2' (default), 'L3')ENUM(PackageType), ARRAY of OBJECT(BaseEntry)ARRAY of OBJECT(BaseEntry)builds order book
statWindowperiod: INT64 or timePeriod: INT64FLOAT64?OBJECT(StatWindowMessage), where StatWindowMessage contains: sum, count, sumOfSquares, sumOfAbs, geometricMean, harmonicMean, firstRawMoment, secondRawMoment, thirdRawMoment, forthRawMoment, variance, standardDeviation, median, min, maxcalculates statistics over fixed size (if period is set) window or time window (if timePeriod is set).