Functions and Operators

Functions accept expression arguments. Depending if function accepts one or more data types the arguments are named as follows:

arg: { 'expr' | type-group'_expr' | type-name }

where:

  • expr - refers to all possible SpectX data type expressions
  • type-group - refers to data type groups (such as numeric)
  • type-name - refers to a particular SpectX data type

Aggregate functions

Aggregate functions compute result from a collection of values. The collection is determined by grouping clause in a statement and can be one of the following:

  1. group by all non-aggregated fields in select clause will yield computing aggregations for all unique values of combinations of grouped fields:

    SELECT a, b, count(c) FROM view GROUP BY a, b
    
  2. group by some of the non-aggregated fields in select clause will yield computing aggregations for all unique values of combinations of grouped fields. The values of non-aggregated fields in resultset are undetermined (i.e the ordering of these fields are not determined):

    SELECT a, b, count(c) FROM view GROUP BY a
    
  3. when GROUP BY is omitted the resultset will contain one computed aggregation for the whole selected dataset (which may be restricted by WHERE clause). The values of non-aggregated fields in resultset are undetermined:

    SELECT count(*) FROM view
    
Name Description
AVG(numeric_expr)
Computes arithmetic mean (average) of not NULL numeric
values returned by numeric_expr.
COUNT(*)
Computes count of all rows in result set.
COUNT(expr)
Computes count of not NULL values returned by expr.
CORR(numeric_expr1, numeric_expr2)
Computes Pearson correlation coefficient between
not NULL numeric values returned by numeric_expr1
and numeric_expr2.
FIRST
Returns first non null value in group
LAST
Returns last non null value in group
MAX(numeric_expr)
Computes maximum value of not NULL numeric values
returned by numeric_expr.
MIN(numeric_expr)
Computes minimum value of not NULL numeric values
returned by numeric_expr.
SUM(numeric_expr)
Computes sum of not NULL numeric_expr field values.
When numeric_expr is of LONG type, then returns DOUBLE.
SUM(numeric_expr, …)
Computes sum of numeric_expr arguments.
SUM(array)
Computes sum of numeric array members.
SUM_LONG(long)
Returns sum of not NULL LONG values.
Returned value is LONG.
STDDEV_SAMP(numeric_expr)
Computes the estimate of standard deviation for population from sample of
not NULL numeric values.
STDDEV(numeric_expr)
STDDEV is alias for STDDEV_SAMP
STDDEV_POP(numeric_expr)
Computes the population standard deviation from not
NULL numeric values.
VAR_SAMP(numeric_expr)
Computes the estimate of the population variance from sample of not NULL
numeric values.
VARIANCE(numeric_expr)
VARIANCE is alias for VAR_SAMP.
VAR_POP(numeric_expr)
Computes the population variation of not NULL numeric
values.

Example: Aggregate functions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
dual(1000)
 .select(i % 5 as group_f, i * 0.11 as value_f)
 .select(
   group_f,
   MIN(value_f) as min, 
   MAX(value_f) as max, 
   SUM(value_f) as sum, 
   COUNT(*) as cnt1, 
   COUNT(value_f > 2.0) as cntConditional,
   AVG(value_f) as avg,
   CORR(value_f, value_f+1) as corr,
   STDDEV(value_f) as stddev, // same as STDDEV_SAMP
   STDDEV_POP(value_f) as stddev_pop,
   VARIANCE(value_f) as variance, // same as VAR_SAMP
   VAR_POP(value_f) as var_pop
 )
 .group(group_f)

Arithmetic operators

Name Description Example
+ Addition
SELECT 5 + 3;
Returns: 8

SELECT 1.5 + (3 - 1);
Returns: 3.5
- Subtraction
SELECT 100 - 3;
Returns: 97

SELECT 24 - (3.5 * 2)
Returns: 17.0
* Multiplication
SELECT 4 * 3;
Returns: 12
/ Division
SELECT 5 / 2;
Returns: 2

SELECT 7.0 / 2;
Returns: 3.5
% Modulo
SELECT 5 % 2
Returns: 1

SELECT 5.2 % 2.1;
Returns: 1.0

Result type of any arithmetic operation depends on both arguments type bit-length and if it is floating point:

  • result type is maximum bit-length of either of the arguments base type
  • If either of arguments are of floating point type then result type is also floating point type
Argument 1 type Argument 2 type Result type
INT INT INT
INT LONG LONG
INT FLOAT FLOAT
LONG FLOAT FLOAT
FLOAT FLOAT FLOAT
FLOAT DOUBLE DOUBLE

Bitwise operators

Name
Description
Example
numeric_expr1 & numeric_expr2
BITWISE_AND(numeric_expr1,*numeric_expr2*
Bitwise AND between numeric expressions
SELECT (3 + 1) & 1;
Returns: 0
numeric_expr1 | numeric_expr2
BITWISE_OR(numeric_expr1,*numeric_expr2*)
Bitwise OR between numeric expressions
SELECT 9 | 240;
Returns: 249
numeric_expr1 ^ numeric_expr2
BITWISE_XOR(numeric_expr1,*numeric_expr2*)
Bitwise XOR between numeric expressions
SELECT 27 ^ 12;
Returns: 23
numeric_expr1 << numeric_expr2
LEFT_SHIFT(numeric_expr1,*numeric_expr2*)
Bitwise shift left numeric_expr1 by
number of bits numeric_expr2
SELECT 1 << 2;
Returns: 4
numeric_expr1 >> numeric_expr2
RIGHT_SHIFT(numeric_expr1,*numeric_expr2*)
Bitwise shift right numeric_expr1 by
number of bits numeric_expr2
SELECT (-1 * 8) >> 2;
Returns: -2
numeric_expr1 >>> numeric_expr2
ZERO_FILL_RIGHT_SHIFT(numeric_expr1,
numeric_expr2)

Unsigned bitwise shift right
numeric_expr1 by number of bits of
numeric_expr2 (shifts zero into
leftmost position)
SELECT (-1 * 8) >>> 2;
Returns: 1073741822
~ numeric_expr
NOT(numeric_expr)
Inverts the bits of expr
SELECT ~2;
Returns: -3
BIT_COUNT(numeric expr)

Returns count of bits set to 1 of
numeric_expr
SELECT BIT_COUNT(3);
Returns: 31

Casting functions

Name
Description
Example
BOOLEAN(string)



BOOLEAN(numeric_expr)

Returns TRUE on following string values:
T, t, Y, y, 1, YES, yes, TRUE, true. Returns
FALSE on any other string value.

numeric_expr value 0 is converted to
false, other values to true
BOOLEAN(‘T’)
Returns: true


BOOLEAN(0)
Returns: false
BYTES(string)


BYTES(numeric_array)

Converts string argument to BYTE
data type

Converts numeric_array values to BYTE
data type
BYTES(‘abc10’)
Returns: [97,98,99,49,48]

BYTES([1,2,49])
Returns: [1,2,49]
DOUBLE(numeric_expr)


DOUBLE(string)

Converts numeric_expr to DOUBLE
Returns NULL if numeric_expr is NULL

Converts string argument to DOUBLE
Returns NULL if numeric_expr is NULL
SELECT DOUBLE(300*2);
Returns: 600.0

SELECT DOUBLE(‘-04’);
Returns: -4.0
FLOAT(numeric_expr)


FLOAT(string)

Converts numeric_expr to FLOAT
Returns NULL if numeric_expr is NULL

Converts string argument to FLOAT
Returns NULL if numeric_expr is NULL
SELECT FLOAT(300*2);
Returns: 600.0

SELECT FLOAT(‘600.0’);
Returns: 600.0
GEOPOINT | GEO(ipaddr)




Converts IPADDR argument to GEOPOINT




SELECT GEO(8.8.8.8)
Returns: 37.751,-97.822

SELECT GEO(IPADDR(‘2001:7d0:8432:a480:58dc:46e:1eea:dddd’))
Returns: 59.4339,24.7281
GEOPOINT | GEO(double1,
double2)

Converts geographical coordinates
double1 latitude, double2 longitude
to GEOPOINT data type
SELECT GEO(59.3984, 24.6563)
Returns: 59.3984,24.6563

INTEGER | INT(numeric_expr)


INTEGER | INT(string)


INTEGER | INT(timestamp)


INTEGER | INT(timestamp_nano)

Converts numeric_expr to INTEGER


Converts string argument to INTEGER


Converts timestamp argument to INTEGER
i.e Unix timestamp

Converts timestamp_nano argument
to INTEGER i.e Unix timestamp
SELECT INTEGER(0x0ffffffl);
Returns: 16777215

SELECT INTEGER(‘-04’);
Returns: -4

SELECT INTEGER(NOW());
Returns: 1512120599

SELECT INTEGER(SYS_NANO_TIME());
Returns: 1512121622
IPADDR(numeric_expr)


IPADDR(hi_64_bit_num,
low_64_bit_num)

IPADDR(string)

Converts numeric_expr arg to IPv4-mapped
Ipv6 address

Converts 128-bit argument to IPv6 address


Converts string arg to IPADDR

SELECT IPADDR(189459673)
Returns: 11.74.236.217

SELECT IPADDR(1,1)
Returns: ::1:0:0:0:1

SELECT IPADDR(‘192.168.0.1’)
Returns: 192.168.0.1
IPSOCKET(IPADDR,
port)

IPSOCKET(string)

Converts IPADDR and numeric port arguments
to IPSOCKET

Converts string ip address and port
to IPSOCKET
SELECT IPSOCKET(IPADDR(‘192.168.0.1’),443)
Returns: 192.168.0.1:443

SELECT IPSOCKET(‘[2a00:1450:4010:c05::69]:443’)
Returns: [2a00:1450:4010:c05::69]:443
IPNET(IPADDR,
mask)

IPNET(string)

Converts IPADDR and numeric mask
arguments to IPNET

Converts string ip address to IPNET

SELECT IPNET(IPADDR(-1062731773), 24)
Returns: 192.168.0.3/24

SELECT IPNET(‘2a00:1450:4010:c05::69/8’)
Returns: 2a00:1450:4010:c05::69/8
LONG(numeric_expr)


LONG(string)

Converts numeric_expr to LONG


Converts string argument to LONG

SELECT LONG(3 + 1);
Returns: 4

SELECT LONG(‘04’);
Returns: 4
MACADDR(numeric_expr)


MACADDR(string)

Converts numeric_expr to MAC_ADDR


Converts string argument to MAC_ADDR

SELECT MACADDR(3 + 1)
Returns: 00-00-00-00-00-04

SELECT MACADDR(‘00-00-00-00-00-04’)
Returns: 00-00-00-00-00-04
STRING(expr)

Converts expr returning any type to
string
SELECT STRING(10.10.10.10:99)
Returns: 10.10.10.10:99
TIMESTAMP(integer)


TIMESTAMP(numeric_expr)


TIMESTAMP(year, month,
day)

TIMESTAMP(year, month, day
hour)

TIMESTAMP(year, month, day
hour, min)

TIMESTAMP(year, month, day
hour, min, sec)

TIMESTAMP(year, month, day
hour, min, sec, millis)

TIMESTAMP(string)





Converts integer seconds to TIMESTAMP


Converts numeric_expr milliseconds
to TIMESTAMP

Converts date given by numeric
arguments to TIMESTAMP

Converts date and time given by numeric
arguments to TIMESTAMP

Converts date and time given by numeric
arguments to TIMESTAMP

Converts date and time given by numeric
arguments to TIMESTAMP

Converts date and time given by numeric
arguments to TIMESTAMP

Converts following format string args to
TIMESTAMP:
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd HH:mm:ss Z
yyyy-MM-dd HH:mm:ss.SSS
yyyy-MM-dd HH:mm:ss.SSS Z
SELECT TIMESTAMP(1512126612)
Returns: 2017-12-01 13:10:12.000 +0200

SELECT TIMESTAMP(1512126612001)
Returns: 2017-12-01 13:10:12.001 +0200

SELECT TIMESTAMP(2017,03,22)
Returns: 2017-03-22 00:00:00.000 +0200

SELECT TIMESTAMP(2017,03,22,15)
Returns: 2017-03-22 15:00:00.000 +0200

SELECT TIMESTAMP(2017,03,22,15,01)
Returns: 2017-03-22 15:01:00.000 +0200

SELECT TIMESTAMP(2017,03,22,15,01,44)
Returns: 2017-03-22 15:01:44.000 +0200

SELECT TIMESTAMP(2017,03,22,15,01,44,200))
Returns: 2017-03-22 15:01:44.200 +0200

SELECT TIMESTAMP(‘2017-12-01 13:10:12’)
Returns: 2017-12-01 13:10:12.000 +0200




TIMESTAMP_NANO | TNANO

TNANO(numeric_expr)



TNANO(year, month,
day)


TNANO(year, month,
day, hour)


TNANO(year, month,
day, hour, min)


TNANO(year, month,
day, hour, min,
sec)

TNANO(year, month,
day, hour, min,
sec, millis)

TNANO(string)







Converts numeric_expr nanoseconds
to TIMESTAMP_NANO


Converts date given by numeric arguments
to TIMESTAMP_NANO


Converts date and time given by numeric
arguments to TIMESTAMP_NANO


Converts date and time given by numeric
arguments to TIMESTAMP_NANO


Converts date and time given by numeric
arguments to TIMESTAMP_NANO


Converts date and time given by numeric
arguments to TIMESTAMP_NANO


Converts following format string args to
TIMESTAMP:
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd HH:mm:ss Z
yyyy-MM-dd HH:mm:ss.SSS
yyyy-MM-dd HH:mm:ss.SSS Z


SELECT TNANO(1512126612001000555l)
Returns:
2017-12-01 13:10:12.001000555 +0200

SELECT TNANO(2017,03,22)
Returns:
2017-03-22 03:00:00.000000000 +0200

SELECT TNANO(2017,03,22,15)
Returns:
2017-03-22 15:00:00.000000000 +0200

SELECT TNANO(2017,03,22,15,38)
Returns:
2017-03-22 15:38:00.000000000 +0200

SELECT TNANO(2017,03,22,15,38,42)
Returns:
2017-03-22 15:38:42.000000000 +0200

SELECT TNANO(2017,03,22,15,38,42,777)
Returns:
2017-03-22 15:38:42.777000000 +0200

SELECT TNANO(‘2017-12-01 13:10:12.456’)
Returns:
2017-12-01 13:10:12.456000000 +0200



TO_NULL(expr)

Returns NULL value

SELECT TO_NULL(3)
Returns: NULL
VARIANT(arg)

Converts primitive type arg to VARIANT
data type
SELECT VARIANT(3)
Returns: variant = 3 (VARIANT:INTEGER)
VARIANT_ARRAY(arg)

Converts ARRAY or primitive type arg
to VARIANT_ARRAY
SELECT VARIANT_ARRAY([1,2,3])
Returns: variant_array = [1,2,3] (VARIANT_ARRAY)
VARIANT_OBJECT(arg)




Converts TUPLE, VARIANT or primitive type
arg to VARIANT_OBJECT



SELECT VARIANT_OBJECT({a:1, b:”foo”}), VARIANT_OBJECT(42);
Returns:
variant_object[a] = 1 // (VARIANT:INTEGER)
variant_object[b] = ‘foo’ // (VARIANT:STRING)
variant_object_2[value] = 42 // (VARIANT:INTEGER)

Comparison operators

Comparison functions return boolean (true or false), based on the following types of comparisons:

  • A comparison of two expressions
  • A comparison of an expression to a specific criteria (such as being NULL)
Name
Description
Example
expr1 = expr2
EQUAL(expr1, expr2)
Returns true if the expressions are equal
SELECT 1 = 1
Returns: true
expr1 <> expr2
expr1 != expr2
NOT_EQUAL(expr1, expr2)
Returns true if the expressions are not equal
SELECT 1 != 1
Returns: false

expr1 > expr2
GREATER(expr1, expr2)
Returns true if expr1 is greater than expr2
SELECT 10 > 8
Returns: true
expr1 < expr2
LESS(expr1, expr2)
Returns true if expr1 is less than expr2
SELECT 10 < 8
Returns: true
expr1 >= expr2
GREATER_OR_EQUAL(expr1,
expr2)
Returns true if expr1 is greater than or equal to expr2
SELECT 10 >= 8
Returns: true

expr1 <= expr2
LESS_OR_EQUAL(expr1,
expr2)
Returns true if expr1 is less than or equal to expr2
SELECT 8 <= 10
Returns: true

expr IS [NOT] NULL
IS_NULL(expr)
IS_NOT_NULL(expr)
Returns true if expr1 is [NOT] NULL
SELECT 10 IS NOT NULL
Returns: true

expr1 IN array



Returns true if expr1 is present in array
SELECT ‘a’ IN (‘a’,’b’,’c’);
Returns: true
SELECT 192.168.3.44 IN 192.168.0.0/8
Returns: true
expr1 NOT IN
array


Returns true if expr1 is not present in array
SELECT ‘o’ NOT IN (‘a’,’b’,’c’);
Returns: true
SELECT 192.168.3.44 NOT IN 192.168.0.0/8
Returns: false

Conditional expressions

Name
Description
CASE
WHEN boolean_expr THEN true_expr
[…]
[ ELSE else_expr ]
END
Returns true_expr if boolean_expr evaluates to true.
Otherwise returns else_expr if specified.



CASE [expr1]
WHEN expr2 THEN true_expr
[…]
[ ELSE else_xpr ]
END
Simplified version of the general form above.Returns true_expr
when expr2 equals to expr1. Otherwise returns else_expr
if specified. Note that first value match evaluating to true is
returned.

IF(boolean_expr,expr)

Represents single if choice block. Returns expr when
boolean_expr evaluates to true. Otherwise returns NULL.
IF(boolean_expr,true_expr,else_expr)

Represents single if else choice block. Returns true_expr when
boolean_expr evaluates to true. Otherwise returns else_expr.

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*
 Calculate daily count on requests from Baltic and Nordic regions
*/

$region(ip) =
   CASE
     WHEN cc($ip) IN ('EE','LV','LT') THEN 'Baltic'
     WHEN cc($ip) IN ('DK','NO','SE','FI') THEN 'Nordic'
     ELSE 'Other'
   END
;

SELECT
   t[day] as date,                  //truncate timestamp to day
   cc(ip) as ipCC,                  //built-in function cc returns geoip country code
   $region(ip) as clientRegion,
   COUNT(*) as reqCnt
FROM
   dual(36900000, 1000000)
GROUP BY date, clientRegion
HAVING
   clientRegion != 'Other'
ORDER BY reqCnt DESC

Cryptographic functions

MD5(string)
Computes MD5 hash value of string.

Example 2:

1
SELECT MD5('SpectX');
SHA1(string)
Computes SHA1 hash value of string.

Example 3:

1
SELECT SHA1('SpectX');
BCRYPT(string1, hash)
Returns true when plaintext password string1 matches previously computed bcrypt hash hash.

Example 4:

1
2
// Returns true
SELECT bcrypt('mysecret', '$2a$08$LTvRV2427XV.i6.7pB3GROGHqA9P9h2Lox2/1Ed9rEdBtiBsBlzGu');
BCRYPTSALT | SALT(hash_str)
Extracts and returns salt from hash_str bcrypt hash value in base16 encoding.

Example 5:

1
2
// Returns: 355c535f8eb8f595c093c03dac3e484d
SELECT SALT('$2a$08$LTvRV2427XV.i6.7pB3GROGHqA9P9h2Lox2/1Ed9rEdBtiBsBlzGu');
ROT13(string)
Replaces every letter in argument string with the 13’th letter after it, in the alphabet. https://en.wikipedia.org/wiki/ROT13

Example 59:

1
2
// returns: Jul qvq gur puvpxra pebff gur ebnq?
select ROT13('Why did the chicken cross the road?');

Date and Time functions

Name
Description
Example
DAY(timestamp)

Returns integer representing day
of month from timestamp
SELECT DAY(T(‘2017-01-22 15:00:00’))
Returns: 22
DAY_OF_WEEK(timestamp)

Returns integer representing 1 - 7 day
of week from timestamp
SELECT DAY_OF_WEEK(T(‘2017-01-22 15:00:00’))
Returns: 7
DAY_OF_YEAR(timestamp)

Returns integer representing day 1 - 365
or 366 in leap of year from timestamp
SELECT DAY_OF_YEAR(T(‘2017-01-22 15:00:00’))
Returns: 22
HOUR(timestamp)

Returns integer representing 0 - 23 hour
of day from timestamp
SELECT HOUR(T(‘2016-09-23 17:58:00’))
Returns: 17
MINUTE(timestamp)

Returns integer representing 0 - 59
minute of hour from timestamp
SELECT MINUTE(T(‘2016-09-23 17:58:00’))
Returns: 58
MONTH(timestamp)

Returns integer representing 1 - 12
month of year from timestamp
SELECT MONTH(T(‘2016-09-23 17:58:00’))
Returns: 9
NOW()


Returns the query execution start
timestamp (can be set by query.now
SELECT NOW()
Returns: 2016-05-25 20:03:35.734 +0300

NANO_NOW()


Returns the query execution start
timestamp with nanosecond precision

SELECT NANO_NOW()
Returns:
2016-05-25 20:03:35.734000000 +0300
SECOND(timestamp)

Returns integer representing 0 - 59
second of minute from timestamp
SELECT SECOND(T(‘2016-09-23 17:58:32’))
Returns: 32
STR_SEC_TO_TIME
(string seconds)
Converts string seconds to TIMESTAMP

SELECT STR_SEC_TO_TIME(“300000000”)
Returns: 1979-07-05 08:20:00
STR_SEC_TO_TIME_NANO
(string seconds)
Converts string seconds to
TIMESTAMP_NANO
SELECT STR_SEC_TO_TIME_NANO(“300000000”)
Returns: 1979-07-05 08:20:00.000000000
STR_TO_TIME(string);
T(string)





Converts string to timestamp.
Argument string must be in one of the
following format:
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd HH:mm:ss Z
yyyy-MM-dd HH:mm:ss.SSS
yyyy-MM-dd HH:mm:ss.SSS Z
SELECT T(‘2016-09-23 17:58:00’)
Returns: 2016-09-23 17:58:00.000 +0300





SYS_TIME()



Returns current timestamp from system
clock


SELECT SYS_TIME()
Returns: 2017-01-10 15:49:25.750 +0200


SYS_NANO_TIME()



Returns current timestamp from system
clock with nanosecond precision


SELECT SYS_NANO_TIME()
Returns:
2017-01-10 15:49:25.749828657 +0200

TIME_ADD(timestamp,
numeric_expr)


Adds numeric_expr of milliseconds
to timestamp and returns resulting
TIMESTAMP value

//add one second to a timestamp
SELECT TIME_ADD(T(‘2016-09-23 17:58:00’),
1000);
Returns: 2016-09-23 17:58:01.000 +0300
TIME_SUB(timestamp,
numeric_expr)


Subtracts numeric_expr milliseconds
from timestamp and returns resulting
timestamp value

//subtract one second from a timestamp
SELECT TIME_SUB(T(‘2016-09-23 17:58:00’),
1000);
Returns: 2016-09-23 17:57:59.000 +0300
TIME_SUBMOD(timestamp,
numeric_expr)



Subtracts timestamp modulo
numeric_expr from timestamp.
Effectively performing truncating
timestamp to the precision expressed
by numeric_expr in milliseconds
//truncate timestamp to hour precision
SELECT TIME_SUBMOD(T(‘2016-09-23 17:58:00’),
1000*60*60);
Returns: 2016-09-23 17:00:00.000 +0300

TIME_TO_STR(timestamp,
format)

Converts timestamp to string
according to format (see format string
SELECT TIME_TO_STR(NOW(),’yyyy-MM-dd’)
Returns: 2016-11-30

TO_STR_SEC(timestamp)

Converts timestamp to string of
floating point seconds
SELECT TO_STR_SEC(NOW())
Returns: “1528894757.697”
YEAR(timestamp)

Returns integer representing year from
timestamp
SELECT YEAR(T(‘2017-01-22 15:00:00’))
Returns: 2017

Timestamp values can take operator for adding, subtracting or truncation:

'['
   [ {+ | - | % } ] [integer]
   { ms | millis | sec | s | min | m | hour | h | day | d | week | w }
']'

The default operator (when not specified) is % - i.e truncation.

Example 6:

1
2
//add one minute to a timestamp
SELECT T('2016-09-23 17:58:00.000')[+1 min]

Example 7:

1
2
//truncate timestamp to hour precision
SELECT T('2016-09-23 17:58:00.000')[1 hour];

Example 8:

1
2
3
4
//select count of ip addresses per 5 minute interval
SELECT t[5 min] as period, count(ip) as ipCount
FROM dual(1000000)
GROUP BY period;

Example 9:

1
2
3
4
//select records from last 24 hours from a view:

@[/user/examples/views/my_webserver_access_logs.sx]
 .filter(timestamp > now()[-24 hour] and timestamp < now());

Example 10:

1
2
3
4
5
//select records from arbitrary period from apache webserver access log
//note that T is shorthand alias for function STR_TO_TIME

@[/user/examples/views/my_webserver_access_logs.sx]
 .filter(timestamp<STR_TO_TIME('2016-03-15 00:00:00') and timestamp>T('2016-03-13 00:00:00'));

Logical operators

Logical operators AND, OR, NOT supporting NULL arguments. Right and left arguments can be exchanged without affecting the result.

a b a AND b a OR b
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL NULL NULL NULL
a NOT a
TRUE FALSE
FALSE TRUE
NULL NULL

Mathematical functions

Name
Description
Example
ABS(numeric_expr)
Returns absolute value of
numeric_expr
SELECT abs(-10)
Returns: 10
ADD(numeric_expr1,
numeric_expr2)
Adds numeric_expr1 to numeric_expr2



SELECT add(5, 3)
Returns: 8
SELECT add(1.5, (3 - 1))
Returns: 3.5
ACOS(numeric_expr)


Computes arc cosine of numeric expr.
The returned angle is in the range 0.0
through pi
SELECT acos(0.5)
Returns: 1.0471975511965979

ASIN(numeric_expr)


Computes arc sine of numeric_expr.
The returned angle is in the range
-pi/2 through pi/2
SELECT asin(0.5)
Returns: 0.5235987755982989

ATAN(numeric_expr)


Computes arc tangent of numeric_expr.
The returned angle is in range
-p/2 through pi/2
SELECT atan(0.5)
Returns: 0.4636476090008061

ATAN2(numeric_expr1,
numeric_expr2)

Computes the angle theta from the
conversion of rectangular coordinates
x,y to polar coordinates(r, theta)
SELECT atan2(0.5, 0.5)
Returns: 0.7853981633974483

CBRT(numeric_expr)


Computes the cube root of numeric_expr


SELECT cbrt(0.27)
Returns: 0.6463304070095651

CEIL(numeric_expr)




Computes the smallest (closest to
negative infinity) double value that is
greater than or equal to the
numeric_expr and is equal to a
mathematical integer.
SELECT ceil(0.7)
Returns: 1.0



COS(numeric_expr)

Computes trigonometric cosine of an
angle numeric_expr (in radians).
SELECT cos(0.5)
Returns: 0.8775825618903728
COSH(numeric_expr)

Computes hyperbolic cosine of an angle
numeric_expr.
SELECT cosh(0.5)
Returns: 1.1276259652063807
DEGREES(numeric_expr)


Converts numeric expr angle (of radians)
to an approximately equivalent angle
of degrees
SELECT degrees(1.571)
Returns: 90.01166961505233

DIVIDE(numeric_expr1,
numeric_expr2)
Divides numeric_expr1 by
numeric_expr2


SELECT divide(5, 2)
Returns: 2
SELECT divide(7.0, 2)
Returns: 3.5
E()

Returns Euler’s number

SELECT E()
Returns: 2.718281828459045
EXP(numeric_expr)

Computes Euler’s number e raised to
the power of numeric_expr
SELECT exp(2)
Returns: 7.38905609893065
EXPM1(numeric_expr)

Returns e numeric_expr -1

SELECT expm1(2)
Returns: 6.38905609893065
FLOOR(numeric_expr)



Computes the largest (closest to
positive infinity) DOUBLE value,
less than or equal to the numeric expr
and is equal to a mathematical integer.
SELECT floor(2.71)
Returns: 2.0


FLOORDIV(integer,
numeric_expr)


FLOORDIV(
numeric_expr1,
numeric_expr2)
Computes INTEGER value floor
of integer argument division by
numeric_expr argument.

Computes LONG value floor
of numeric_expr1 argument division by
numeric_expr2 argument.
SELECT floordiv(5, 2.0)
Returns: 2


SELECT floordiv(22.4, 2.0)
Returns: 11

FLOORMOD(integer,
numeric_expr)

FLOORMOD(
numeric_expr1,
numeric_expr2)
Computes INTEGER floor modulus of
integer and numeric_expr arguments

Computes LONG floor modulus of
arguments

SELECT floormod(5, 2.71)
Returns: 1

SELECT floormod(5.0, 2.71)
Returns: 1

GETEXPONENT(double)


GETEXPONENT(float)

Computes INTEGER unbiased exponent used
in the representation of double arg

Computes INTEGER unbiased exponent used
in the representation of float arg
SELECT getexponent(28.71d)
Returns: 4

SELECT getexponent(28.71f)
Returns: 4
HYPOT(numeric_expr1,
numeric_expr2)

Returns sqrt(x 2 +y2)
without intermediate overflow or
underflow
SELECT hypot(3.0, 4l)
Returns: 5.0

IEEEREMAINDER(
numeric_expr1,
numeric_expr2)
Returns DOUBLE value of the remainder
operation on numeric arguments as
prescribed by the IEEE 754 standard
SELECT IEEEREMAINDER(7.389f, 5)
Returns: 2.3889999389648438

LOG(numeric_expr)

Computes the natural logarithm (base e)
of numeric_expr
SELECT log(7.389)
Returns: 1.9999924078065106
LOG1P(numeric_expr)

Computes the natural logarithm (base e)
of the sum of the numeric_expr and 1
SELECT log1p(7.389)
Returns: 2.1269213238641576
LOG10(numeric_expr)

Computes base 10 logarithm of
numeric_expr
SELECT log10(7.389)
Returns: 0.8685856665587657
MODULO(numeric_expr1,
numeric_expr2)
Computes remainder (modulo) of division
numeric_expr1 by numeric_expr2


SELECT modulo(5, 2)
Returns: 1
SELECT modulo(5.2, 2.1)
Returns: 1.0
MULTIPLY(
numeric_expr1,
numeric_expr2)
Multiplies numeric_expr1 by
numeric_expr2

SELECT multiply(4, 3)
Returns: 12

NEXTAFTER(float,
numeric_expr)


NEXTAFTER(double,
numeric_expr)

Returns the FLOAT number adjacent to the
first argument in the direction of the
second argument.

Returns the DOUBLE number adjacent to
the first argument in the direction of
the second argument.
SELECT nextafter(3.33f, 2)
Returns: 3.3299997


SELECT nextafter(3.33d, 2)
Returns: 3.3299999999999996

NEXTDOWN(float)



NEXTDOWN(numeric_expr)


Returns the FLOAT number adjacent to
float argument in the direction of
negative infinity.

Returns the DOUBLE number adjacent to
numeric_expr argument in the direction
of negative infinity.
SELECT nextdown(3.33f)
Returns: 3.3299997


SELECT nextdown(3l)
Returns: 2.9999999999999996

NEXTUP(float)



NEXTUP(numeric_expr)


Returns the FLOAT number adjacent to
float argument in the direction of
positive infinity.

Returns the DOUBLE number adjacent to
numeric_expr argument in the direction
of positive infinity.
SELECT nextup(3.33f)
Returns: 3.3300002


SELECT nextup(3)
Returns: 3.0000000000000004

PI()

Returns DOUBLE value of pi

SELECT pi()
Returns: 3.141592653589793
POWER(numeric_expr1,
numeric_expr2)
Computes the value of numeric_expr1
raised to the power of numeric_expr2
SELECT power(2, 3.5)
Returns: 11.313708498984761
RADIANS(numeric_expr)


Converts numeric_expr angle of degrees
to an approximately equivalent angle of
radians
SELECT radians(90)
Returns: 1.5707963267948966

RANDOM()

Returns positive double value greater
than or equal to 0.0 and less than 1.0
SELECT random()
Returns: 0.38474518862412765
RINT(numeric_expr)


Returns the DOUBLE value that is closest
in value to the numeric_expr argument
and is equal to a mathematical integer.
SELECT rint(12.436)
Returns: 12.0

ROUND(numeric_expr)

Computes closest mathematical integer
to numeric_expr with ties rounding up.
SELECT round(12.436)
Returns: 12.0
ROUND(numeric_expr
decimalplaces)

Computes closest mathematical integer
to numeric_expr with decimalplaces
number of decimals
SELECT round(12.436, 2)
Returns: 12.44

SCALB(float f,
scale_factor)




SCALB(numeric_expr,
scale_factor)



Returns FLOAT f×2scale_factor
rounded as if performed by a single
correctly rounded floating-point
multiply to a member of the double value
set

Returns DOUBLE d×2scale_factor
rounded as if performed by a single
correctly rounded floating-point
multiply to a member of the double value
set
SELECT scalb(0.524f, 6)
Returns: 33.536




SELECT scalb(0.524d, 6)
Returns: 33.536



SIGNUM(float)




SIGNUM(numeric_expr)



Returns FLOAT result of signum function
of argument; 0 if the argument is 0,
1.0 if the argument is greater than 0
-1.0 if the argument is less than 0

Returns DOUBLE result of signum function
of argument; 0 if the argument is 0,
1.0 if the argument is greater than 0
-1.0 if the argument is less than 0
SELECT signum(28.71f)
Returns: 1.0



SELECT signum(100)
Returns: 1.0


SIN(numeric_expr)

Computes trigonometric sine of angle
numeric_expr (in radians).
SELECT sin(0.524)
Returns: 0.5003474302699141
SINH(numeric_expr)

Computes hyperbolic sine of
numeric_expr
SELECT sinh(0.524)
Returns: 0.5483110094354913
SQRT(numeric_expr)

Computes positive square root of
numeric_expr
SELECT sqrt(9.781)
Returns: 3.127459032505462
SUBMOD(numeric_expr1,
numeric_expr2)
Subtracts numeric_expr1 modulo
numeric_expr2 from numeric_expr1
SELECT submod(9.2, 4)
Returns: 8
SUBTRACT(
numeric_expr1,
numeric_expr2)

Subtracts numeric_expr2 from
numeric_expr1


SELECT subtract(100, 3)
Returns: 97
SELECT subtract(24, (3.5 * 2))
Returns: 17.0
TAN(numeric_expr)

Computes trigonometric tangent of angle
numeric_expr (in radians)
SELECT tan(1.524)
Returns: 21.353597524589244
TANH(numeric_expr)

Computes hyperbolic tangent of
numeric_expr
SELECT tanh(1.524)
Returns: 0.9093922044597188
TOHEXSTRING(arg)

Converts INT or LONG argument to
hexadecimal string
SELECT TOHEXSTRING(1000)
Returns: 3e8
ULP(float)


ULP(numeric_expr)

Returns FLOAT size of an ulp of the
argument

Returns DOUBLE size of an ulp of the
argument
SELECT ulp(1.524f)
Returns: 1.1920929E-7

SELECT ulp(1l)
Returns: 2.220446049250313E-16

Network functions

Name Description Example
ASN(ip_expr)

Returns ASN number of IPADDR, IPSOCKET
or IPNET argument
SELECT ASN(IPADDR(‘213.3.222.5’))
Returns: 3303
ASN_NAME(ip_expr)

Returns ASN Name of IPADDR, IPSOCKET
or IPNET argument
SELECT ASN_NAME(IPSOCKET(‘[2a00:1450:4010:c05::69]:443’))
Returns: AS15169 Google LLC
CC(ip_expr)

Returns 2-letter ISO 3166 country code
of IPADDR, IPSOCKET or IPNET argument
SELECT CC(IPNET(‘2a00:1450:4010:c05::69/22’))
Returns: FI
DNS_LOOKUP(ipaddr)


DNS_LOOKUP(domain)

Returns result of reverse DNS lookup
of ipv4 or ipv6 address

Returns result of DNS lookup for
domain
SELECT DNS_LOOKUP(IPADDR(‘85.222.234.14’))
Returns: uvn-234-14.ams01.zonevs.eu

SELECT DNS_LOOKUP(‘www.spectx.com’)
Returns: 85.222.234.14
DSLICE(domain, level)


Returns substring of domain containing
labels up to specified level

SELECT DSLICE(‘www.spectx.com’,2)
Returns: spectx.com

IPLOC(ip_expr)



Returns country code, city name and
geographical coordinates (latitude
and longitude ) of IPADDR, IPNET
or IPSOCKET argument.
SELECT IPLOC(IPADDR(‘84.50.125.243:443’))
Returns:
{cc=”EE” city=”Tallinn”
longitude=24.7281 latitude=54.4339}
IPIN(ip_expr, ipnet)



Returns true or false if IPADDR or
IPSOCKET are within the range of
ipnet

SELECT IPIN(IPADDR(‘200.100.32.45’), IPNET(‘200.100.32.0/8’));
Returns: true
SELECT IPIN(IPADDR(‘2a00:1450:4010:c0d::63’),IPNET(‘2a00:1450::/32’))
Returns: true
IS_IPV4(ip_expr)

Returns true if IPADDR, IPNET or
IPSOCKET argument contains IPv4 address
SELECT IS_IPV4(IPSOCKET(‘200.100.32.45:99’))
Returns: true
IS_IPV6(ip_expr)

Returns true if IPADDR, IPNET or
IPSOCKET argument contains IPv6 address
SELECT IS_IPV6(IPADDR(‘2a00:1450:4010:c05::69’))
Returns: true
MANUF(macaddr)

Returns manufacturer of macaddr

SELECT MANUF(6c-40-08-98-54-7e)
Returns: Apple
MASK(ipnet)

Returns netmask from IPNET argument

SELECT MASK(IPNET(‘2a00:1450:4010:c05::69/20’))
Returns: 20
PARSEURI(uri)









Returns array of URI elements parsed
from string argument uri








SELECT PARSEURI(“https://docs.spectx.com:
443/pages/spectx.html#sx-install”)
Returns:
parseuri[scheme] = https
parseuri[user] =
parseuri[host] = docs.spectx.com
parseuri[port] = 443
parseuri[path] = /pages/spectx.html
parseuri[query] =
parseuri[fragment] = sx-install
PORT(ipsocket)

Returns port of IPSOCKET

SELECT PORT(IPSOCKET(‘200.100.32.45:99’))
Returns: 99

Row Functions

HISTORY(fieldname, integer)
Selects integer count previous fieldname values into an array.

HISTORY function is highly useful in building filters based on previous values

Example 42:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
/*
  extract events greater than from the average of previous 2 events
*/

dual(10)                                            // generate 10 event records
 .select(history(i,3) as i_hist, *)                 // select 3 last values of i into i_hist array
 .select(i-i_hist[1] as i_delta,                    // compute delta between current and last i values as i_delta
        (i_hist[0]+i_hist[1])/2 as i_hist_avg,      // and average of last two record i values as i_hist_avg
         i)
 .filter(i_delta > 0 AND i_hist_avg > 5)            //filter out events based on i_delta and i_hist_avg
 .select(i);
KEEP(fieldname)
Returns last non-NULL value of fieldname.

KEEP comes handy when dealing with data where some data element occurs only once for a sequence of records. For instance events from a day are separated with date

1
2
3
4
5
6
Mon Dec 1 2016 12:00:00
event 1
event 2
...
Tue Dec 2 2016 12:00:00
...

OSX daily.out is a good example of such log:

/examples/data/daily.out
Sat Apr  2 14:08:34 EEST 2016

Removing old temporary files:

Cleaning out old system announcements:

Removing stale files from /var/rwho:

Removing scratch fax files

Disk status:
Filesystem                          Size   Used  Avail Capacity   iused   ifree %iused  Mounted on
/dev/disk1                         465Gi  430Gi   35Gi    93% 112711012 9135296   93%   /

Network interface status:
Name  Mtu   Network       Address            Ipkts Ierrs    Opkts Oerrs  Coll
lo0   16384 <Link#1>                           890     0      890     0     0
lo0   16384 localhost   ::1                    890     -      890     -     -
lo0   16384 127           localhost            890     -      890     -     -
lo0   16384 localhost   fe80:1::1              890     -      890     -     -

Local system status:
14:08  up 2 mins, 2 users, load averages: 3.79 1.26 0.49

-- End of daily output --

Sun Apr  3 10:31:10 EEST 2016

Removing old temporary files:

Cleaning out old system announcements:

As it can be seen events are grouped by date, starting with date timestamp and ending with constant string -- End of daily output --. The events are separated by double newlines.

Obviously when time field is separated from event data record it is not possible to perform time related queries. We can use KEEP function to attach time field to all the events belonging to that date

Example 11:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$pattern = <<<END
  EOL?                                          // timestamp can be preceded with empty row
                                                // our record can contain either
  TIMESTAMP('EEE MMM d hh:mm:ss Z yyyy')?:time  // timestamp or
  DATA?:message                                 // multiline message (DATA matches also newlines)
  ('\n\n')                                      // record is terminated by double newline
END;

@src = PARSE(
    src:'sx:/user/examples/data/daily.out',
    pattern:$pattern
);

@src
 .select(keep(time) as evTime, message)

Will prepend each line in log with timestamp:

evTime message
2016-04-03 02:08:34.000 +0300 Removing old temporary files:
2016-04-03 02:08:34.000 +0300 Cleaning out old system announcements:
2016-04-03 02:08:34.000 +0300 Removing stale files from /var/rwho:
2016-04-03 02:08:34.000 +0300 Removing scratch fax files
2016-04-03 02:08:34.000 +0300 Disk status: Filesystem Size Used Avail Capacity iused ifree …
2016-04-03 02:08:34.000 +0300 Network interface status: Name Mtu Network Address lo0 16384 …
2016-04-03 02:08:34.000 +0300 Local system status: 14:08 up 2 mins, 2 users, load averages: 3.79 …
2016-04-03 02:08:34.000 +0300 – End of daily output –
2016-04-03 22:31:10.000 +0300 Removing old temporary files:
2016-04-03 22:31:10.000 +0300 Cleaning out old system announcements:

PREV(fieldname)
Returns previous value of fieldname.

Example 12:

1
2
dual(5)
 .select(i, prev(i) as i_prev);

Returns:

i i_prev
0 0
1 0
2 1
3 2
4 3

ROWID()
Returns rowid of current row (tuple) in resultset.

Example 13:

1
2
dual(5)
 .select(rowid(), i, s);

Returns:

rowid i s
1 0 0ho0
2 1 1ho1
3 2 2ho2
4 3 3ho3
5 4 4ho4

String functions

BASE16_DECODE | UNHEX(string)
Returns base16 decoded string.

Example 14:

1
dual.select(BASE16_DECODE('52656420666f78206a756d7073206f7665722062726f776e20646f67'));
BASE16_ENCODE | HEX(string)
Returns base16 encoded string.

Example 15:

1
dual.select(BASE16_ENCODE('Red fox jumps over brown dog'));
BASE64_DECODE | UNBASE64 | BASE64DECODE(str)
Returns base64 decoded string str.

Example 16:

1
dual.select(BASE64_DECODE('UmVkIGZveCBqdW1wcyBvdmVyIGJyb3duIGRvZw=='));
BASE64_ENCODE | BASE64 | BASE64ENCODE(string)
Returns base64 encoded string.

Example 17:

1
dual.select(BASE64_ENCODE('Red fox jumps over brown dog'));

CONCAT(expr, [,…]);

operator ||

operator +
Concatenates expr arguments and returns resulting string. Max number of arguments is 128. Alternatively you can use operators || or + for the same purpose.

Example 18:

1
2
3
4
5
dual.select(
    CONCAT('my ', '20 ', '$cents') as concat1,      //use CONCAT function for concatenating strings
    'my ' || '20 ' || '$cents' as concat2,          //use concatenation operators:
    'give ' + 'me ' + 'some ' + ' money' as concat3
    );
string1 CONTAINS string2
Returns true if string1 contains string2. Otherwise returns false. Note that comparison is case sensitive.

Example 19:

1
2
3
4
dual.select(
    'hello world' contains 'hello' as trueContain,
    'hello world' contains 'hellO' as falseContain
    );
ENDS(string1, string2)
Returns true if string1 ends with string2. Otherwise returns false. Note that comparison is case sensitive.

Example 20:

1
2
3
4
dual.select(
    ENDS('Hello World!', 'ld!') as trueEnd,
    ENDS('Hello World!', 'orld') as falseEnd
    );
ESCAPE(string)
Prepends (escapes) following characters in string with backslash: 0x8 (backspace), 0x9 (horizontal tab), 0xA (line feed), 0xC (form feed), 0xD (carriage return), 0x22 (double quote), 0x27 (single quote) and 0x5C (backslash).

Example 21:

1
2
//     Returns: \t\"\'
dual.select(ESCAPE('	"\''));
ESCAPE_JAVA(string)
Escapes the characters in a String using Java String rules. Deals correctly with quotes and control-chars (tab, backslash, cr, ff, etc.) So a tab becomes the characters ‘' and ‘t’.

Example:

1
2
input string: He didn't say, "Stop!"
output string: He didn't say, \"Stop!\"
ESCAPE_OCTAL(string)
Translates argument string characters into backslash escaped octal Unicode values.
ESCAPE_OCTAL(bytes)
Translates argument bytes into backslash escaped octal Unicode values.

Example 60:

1
2
3
select ESCAPE_OCTAL('abc123') as e1,        // returns \141\142\143\061\062\063
       ESCAPE_OCTAL(BYTES('abc123')) as e2  // returns \141\142\143\061\062\063
;
JSON(string)
Parses string argument containing Json and returns TUPLE structure with extracted VARIANT type members

Example:

1
SELECT JSON('{"name":"john", "id":1}');
INDEXOF(str, substr, startPos)
Returns the index within str of the first occurrence of the specified substr, starting at startPos. If startPos is negative the search is done backwards from the startPos. If not found then -1 is returned.

Example 61:

1
2
3
4
5
dual
 .select(str:"a/b/cc/dddd")
 .select(firstOccurence:INDEXOF(str, '/', 0)               //first occurrence of '/' is at position 1
         ,lastOccurence:INDEXOF(str, '/', 0-STRLEN(str))   //last occurrence  of '/' is at position 6
);
LDIST(string1, string2)
Computes Levenshtein distance between string1 and string2
string LIKE pattern | STR_LIKE(string, pattern)
Returns true if pattern matches string. If pattern does not contain percent signs then LIKE acts as = operator. A percent sign (%) matches any sequence of zero or more characters. Note that matching is case sensitive.

Example 22:

1
2
//Returns: false
dual.select('aHoi' LIKE '%ho%');
LOWER(string)
Converts string to lowercase and returns resulting string.

Example 23:

1
2
//Returns: hello world
dual.select(LOWER('HeLlo WorlD'));
MATCHES(string, regexp)
Returns true if regular expression regexp matches string. Otherwise returns false.

Example 24:

1
2
//Returns: true
dual.select(MATCHES('hello world', '[a-z ]*'));
PARSE(pattern, string)

Extracts data according to pattern from string data using SpectX pattern matching language. Can return three type of results:

  1. in case pattern does not contain any exported matchers then it returns boolean, indicating success or failure of parsing.
  2. in case pattern contains only one exported matcher then it returns matched as single value
  3. in case pattern contains more than one exported matcher then it returns matched values as tuple

Example 25:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$p_boolean = <<<END
  'id=' INT
  '&client=' INT
  '&s=' LD
  EOF
END;

$p_single_val = <<<END
  'id=' INT:id
  '&client=' INT
  '&s=' LD
  EOF
END;

$p_tuple = <<<END
  'id=' INT:id
  '&client=' INT:client
  '&s=' LD:message
  EOF
END;

$str = 'id=444&client=12&s=hello world';

dual.select(
    PARSE($p_boolean, $str) as ret_boolean,         // case 1: PARSE returns matching success as boolean
    PARSE($p_single_val, $str) as ret_single_val,   // case 2: PARSE returns matched as single value
    PARSE($p_tuple, $str) as ret_tuple              // case 3: PARSE returns matched values as tuple
    );
PRINTF(format, args…)
Returns a formatted string using format string (based on java.util.Formatter class) and arguments.

Example 52:

1
2
3
4
dual(20)
 .select(i,s,d)
 .select(PRINTF("Result: %010x string: %s double: %2.2e", i, s, d*10000))
;
PUNCT(string)
Returns punctuation characters contained in string.
PUNCT(string, count, withSpace)
Returns first count punctuation characters contained in string. Boolean withSpace includes space character (ASCII 0x20 hex) in search, and is printed out as underscore (ASCII 0x5F hex).

Example 53:

1
2
3
4
5
6
dual
 .select(a:" !\"#$%&’()*+,-./:;<=>?@[]^_`{|}~|")
 .select(PUNCT(a),          //filter out all punctuation characters of string a
         PUNCT(a, 5, true)  //filter out first 5 punctuation and space characters in string a
 )
;
STR_REGEXP_LIKE(string, regexp)
Returns true if string matches regular expression regexp.

Example 26:

1
2
// Returns: true
dual.select(STR_REGEXP_LIKE('0ho0','[0-9ho]*'));
REPLACE(string, target_str, replace_str)
Replaces each substring of string that matches the target_str with the replace_str.

Example 27:

1
2
3
4
dual.select(
    REPLACE('hello world', 'world', 'space'),   //returns: hello space
    REPLACE('hello world', 'dude', 'space')     //returns: hello world
    );
REPLACEALL(string, regex_str, replace_str)
Replaces each substring of string that matches the regular expression regex_str with the replace_str.

Example 54:

1
2
3
dual
 .select(REPLACEALL("aaa bbb ccc", "[a]", "x"))   //replace all occurrences of character 'a' with 'x'
;
SH_ENTROPY(string)
Computes Shannon entropy of string
SPLIT(string, regex)
Splits string around matches of given regular expression regexp and returns result as ARRAY of strings.

Example 28:

1
dual.select(SPLIT('p1;p2;p3', ';'));
STRLEN(string)
Returns length of string.

Example 29:

1
dual.select(STRLEN('hello world')); //Returns: 11
STARTS(string1, string2)
Returns true if string1 starts with string2. Otherwise returns false. Note that comparison is case sensitive.

Example 30:

1
2
3
4
dual.select(
    STARTS('Hello World!', 'Hell'),     //Returns: true
    STARTS('Hello World!', 'hell')      //Returns: false
    );
SUBSTR(str, startPos)
Takes substring of str from position startPos to the end of str. If startPos is negative, then substring is taken from position relative to the end of string.

Example 31:

1
2
3
4
dual.select(
    SUBSTR('hello world', 6),   //extract 'world' from 'hello world'
    SUBSTR('hello world', -2)   //select two last characters from 'hello world'
);
SUBSTR(str, startPos, endPos)
Takes substring of str from position startPos to position endPos. Position is counted from 0 (i.e first element is at position 0).

Example 32:

1
dual.select(SUBSTR('my 20 $cents', 3,5));   //extract middle word from 'my 20 $cents'
TRIM(string)
Removes leading and trailing whitespaces from string.

Example 33:

1
dual.select('_' || TRIM('  hello world	     ') || '_');    //returns _hello world_
UNESCAPE | DEESCAPE(string)
Removes escaping from string.

Example 34:

1
dual.select(UNESCAPE("\t\"\'"));    // Returns: 	"'
URLDECODE(string)
Returns urldecoded (also known as percent encoding/decoding) string.
URLENCODE(string)
Returns urlencoded string
HTMLUNESCAPE(string)
Unescapes HTML string
UPPER(string)
Converts string to uppercase.

Example 35:

1
dual.select(UPPER('HeLlo WorlD'));  // Returns: HELLO WORLD

Functions on Composite Data

VARIANT_FIELD_SELECT(variant, fieldname)
Selects fieldname from VARIANT_OBJECT or VARIANT_ARRAY variant.

Example 50:

1
2
3
4
5
6
7
8
dual(1,4).select(tuple, array)            //generate 4 records with a tuple and an array
 .select(VARIANT_OBJECT(tuple) as varObj, //convert tuple to VARIANT_OBJECT
         VARIANT_ARRAY(array) as varArr)  //convert array to VARIANT_ARRAY
 .select(varObj[i],                       //select i member directly and
         VARIANT_FIELD_SELECT(varObj,'i'),//using VARIANT_FIELD_SELECT function
         varArr[0],                       //select first element directly and
         VARIANT_FIELD_SELECT(varArr,'0'))//using VARIANT_FIELD_SELECT function
;
TUPLE_FIELD_SELECT(tuple, fieldname)
Selects fieldname from tuple. The function is complementary to accessing tuple elements directly.

Example 36:

1
2
3
dual(5)
 .select(tuple as myTuple)
 .select(TUPLE_FIELD_SELECT(myTuple,'ip') as ipAddr);

Returns:

ipAddr
0.0.0.0
0.0.0.1
0.0.0.2
0.0.0.3
0.0.0.4
ARRAY_AVG(array)
Computes arithmetic mean of numeric array members.

Example 51:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
VALUES([                    //generate 2 records with numeric arrays
  {arr: [1, 2, 3, 4]},
  {arr: [7, 6, 5, 10, TO_NULL(1),8]}
])
 .select(aavg:ARRAY_AVG(arr)        //compute average of members of the array
        ,asum:ARRAY_SUM(arr)        //compute sum of array members
        ,acnt:ARRAY_COUNT(arr)      //compute count of non NULL value members
        ,alen:ARRAY_LEN(arr)        //compute the count of array members
        ,amax:ARRAY_MAX(arr)        //compute max value of array members
        ,amin:ARRAY_MIN(arr)        //compute min value of array members
        ,asort:ARRAY_SORT(arr)      //return array with members sorted in ascending order
        ,astr:ARRAY_JOIN(arr,';')   //return string with array elements separated by ;
);

Returns:

aavg asum acnt alen amax amin asort astr
2.5 10.0 4 4 4 1 [1, 2, 3, 4] 1;2;3;4
7.2 36.0 5 6 10 5 [5, 6, 7, 8, 10, null] 7;6;5;10;NULL;8
ARRAY_COUNT(array)
Computes count of non NULL members of array.

See Example 51.

ARRAY_MAX(array)
Returns max value of numeric array members.

See Example 51.

ARRAY_MIN(array)
Returns min value of numeric array members.

See Example 51.

ARRAY_JOIN(array, str)
Returns string with array elements separated by str.

See Example 51.

ARRAY_SORT(array)
Returns copy of array with members sorted in ascending order.

See Example 51.

ARRAY_SUM(array)
Computes sum of numeric array members.

See Example 51.

ARRAY_IDX(array, expr)
Returns position of first member in array which is equal to expr

Example 37:

1
2
3
dual(4,1)
 .select(array as arr)				//select array of ip addresses from dual
 .select(ARRAY_IDX(arr, 0.0.0.6));	//get index of 0.0.0.6 element in array
ARRAY_SELECT(array, integer)
Returns element from array at position integer. The function is complementary to accessing array elements directly.

Example 38:

1
2
3
dual(5)
 .select(array as myArray)
 .select(ARRAY_SELECT(myArray,0) as ipAddr);

Returns:

ipAddr
NULL
0.0.0.1
0.0.0.2
0.0.0.3
0.0.0.4
ARRAY_SELECT(array, integer1, integer2)
Returns elements of array from position integer1 to integer2.
ARRAY_LEN(array)
Returns the number of elements in array.

Example 39:

1
2
3
dual(5)
 .select(array as myArray)
 .select(ARRAY_LEN(myArray))

Returns:

ARRAY_LEN_1
0
1
2
3
4
ARRAY_REMOVE_NULLS(array)
Removes elements with NULL values from array.

Example 40:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$pattern = <<<END
 ARRAY {
  INT*:i ';'
 }*:myArray
END;

$arrayStr = "0;1;2;3;;5;";

dual
 .select(PARSE($pattern, $arrayStr) as nullArray)	//PARSE produces array containing 4'th element with NULL value
 .select(ARRAY_REMOVE_NULLS(nullArray) as nullFreeArray);
ARRAY_AGG(expr)
Collects expr values into an array.

Example 58: Collect request attributes from client ip-addresses to an array:

1
2
3
4
5
6
7
8
$pattern = $[/user/examples/patterns/apache_access.sxp];
@list    = LIST('sx:/user/examples/data/apache_access.log.sx.gz');
@stream  = PARSE(pattern:$pattern, src:@list);

@stream
 .select(clientIp, ARRAY_AGG({timestamp, uri, response, agent}))
 .group(@1)
;
UNNEST(array)
Expand an array to a set of rows. This is the opposite of ARRAY_AGG function.

Example 41:

1
2
3
dual(10)
 .select(myArray:[l, l+1])
 .select(unnest(myArray));

Geospatial functions

DISTANCE(geopoint1, geopoint2)
Computes geographical distance between two geographical coordinates (expressed in GEOPOINT) in kilometers, using spherical law of cosines formula.

Example 44:

1
SELECT DISTANCE(GEO(200.100.32.45),GEO(37.279452,-121.886943));
BEARING(geopoint1, geopoint2)
Computes bearing in degrees from geopoint1 to geopoint2.

Example 45:

1
SELECT BEARING(GEO(200.100.32.45),GEO(37.279452,-121.886943));
GEO_POINT_IN(geopoint, area)
Returns true if geopoint argument is located within given area - specified as an array of sequentially connected geopoints representing closed polygon. Last geopoint is assumed to be connected with the first one.

Example 46:

1
2
3
4
5
6
7
8
9
dual
 .select(GEO(59.396654, 24.658450))
 .select(GEO_POINT_IN(geo,
    [GEO(59.3968915, 24.6578264),
     GEO(59.3963126, 24.6578264),
     GEO(59.3962798, 24.6590495),
     GEO(59.3968806, 24.6590924)]
     )
 );
GEO_ROUND(location, edge_len_km)

Returns center point of square with given length of edges in kilometers, containing geopoint location on spherically modeled Earth.

The function can be used for aggregations and joins based on geographical area.

Example 47:

1
2
3
4
5
6
7
dual(10)
 .select( IPV4(INT(random()*1000000000)) as ip)  //generate 10 random ipv4 addresses
 .select(GEO(ip) as ipLocation)                  //compute geopoint location of the ip-address
 .select(GEO_ROUND(ipLocation, 100))             //compute geo_round of 100km squares
;

//press Map to see visualized aggregation
LATITUDE(geopoint)
Returns latitude coordinate from GEOPOINT argument.
LONGITUDE(geopoint)
Returns longitude coordinate from GEOPOINT argument.

Example 57:

1
2
3
4
5
dual
 .select(gp:GEO(84.50.125.243))
 .select(LATITUDE(gp)
        ,LONGITUDE(gp)
 );

Other Functions

COLUMN(string)
Selects column by name string. This function can be used in user defined functions manipulating record stream.

Example 55:

1
2
3
4
5
6
7
@@concat(f1::STRING, f2) =
 select($f1 + STRING(COLUMN($f2)))
;

dual(10)
 .@@concat('Time: ', 't')
;
CONFIG()
Returns names and values of all currently set query configuration parameters
CONFIG(param_name)
Returns value of query configuration parameter param_name.
ES_QUERY(default_field, query_string)
Executes query_string in Elasticsearch. default_field is used for query terms if no prefix fields are specified in the query_string. Use _all to refer to all index fields or a particular field name.

Example 62: Retrieve from Elasticsearch index apache records where any of its fields contains words “login” or “logout”.

@src = ES(
    uri: "http://127.0.0.1:9200"
    ,index: "apache"
);

@src
 .filter(ES_QUERY(_all, "login OR logout"));

/* or alternatively using SQL style:
SELECT * FROM @src WHERE ES_QUERY(_all, "login OR logout");
*/
OBJECT_DESC(arg)
Returns string with names and types of keys in VARIANT_OBJECT argument.

Example 49: Suppose we have a web application logging incoming request headers in Json format. We define several known headers explicitly for extraction, the unknown ones will be captured in the VARIANT_OBJECT named ‘others’. If we wanted to know what unknown headers are appearing we could do it as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
$pattern = <<<EOP
JSON{                          //known headers:
 STRING:Host,
 STRING:Referer,
 STRING:'Accept-Language',
 STRING:'User-Agent',
 STRING:Connection,
 STRING:Accept
}(greedy='others'):headers    //unknown headers will be captured in variant_object named 'others'
EOL
EOP;

@src = PARSE(src:'sx:/user/examples/patterns/json-headers.sxp.data', pattern:$pattern);

@src
 .filter(SIZE(headers[others]) > 0 )             //select only records where unknown headers are present
 .select(OBJECT_DESC(headers[others]), count(*)) //count the names of unknown headers
 .group(@1)
 .sort(@2 desc)
;
FLAG(country_code)
Returns unicode flag symbol of ISO 3166 country_code
GZIP(str)
Returns gzip compressed str
GUNZIP(bytes)
Returns gzip uncompressed bytes
HASH64(object)
Computes 64-bit hash value of object. Note that the algorithm is not cryptographically secure.
FETCH(uri)
Fetch content as string from specified uri.

Example 58:

1
2
3
4
/* select the content of example script into string field 'script'.
   NB! This works only when examples have been extracted to /user
*/
SELECT FETCH("sx:/user/examples/sorting.sx") as script;
SLEEP(integer)
Causes the currently executing thread to sleep (temporarily cease execution) for integer milliseconds.
SIZE(arg)
Returns the number of elements in ARRAY, TUPLE, VARIANT_ARRAY or VARIANT_OBJECT argument.

Example 48:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
$jsonstr = <<<EOSTR
{
	"int":1,
	"array":[1,2,3],
	"obj":{"alpha":"a", "dora":["foo",3,null], "gamma":2.0},
	"other":"stuff"
}
EOSTR;

$createVariantObject = PARSE("DATA JSON:variant_object DATA EOS", $jsonstr);
$createVariantArray = PARSE("JSON_ARRAY:variant_array EOS", '[1,4,"aa", 2.45]');

dual
 .select([1,2] as array,
         {i:1, s:"str",d:2.5} as tuple,
         $createVariantObject,
         $createVariantArray)
 .select(SIZE(array) as array_size,
         SIZE(tuple) as tuple_size,
         SIZE(variant_object) as variant_object_size,
         SIZE(variant_array) as variant_array_size
);
SIZEOF(arg)
Returns arg size in bytes.

Example 56:

1
2
3
4
5
6
7
8
9
dual(20)
 .select(
    intSize:SIZEOF(i)
   ,floatSize:SIZEOF(f)
   ,doubleSize:SIZEOF(d)
   ,timeSize:SIZEOF(t)
   ,stringSize:SIZEOF(s)
   ,arraySize:SIZEOF(array)
 );
TYPE(expr)
Returns string identifying data type of the expr argument.

Example 43:

1
dual.select(i, type(i), ips, type(ips));