Functions and Operators

Functions and Operators dinther Wed, 01/22/2020 - 14:52

MonetDB provides the following groups of built-in SQL functions and operators.

Logical functions and operators

Logical functions and operators dinther Wed, 01/22/2020 - 15:18

SQL uses a three-valued logic system with true, false, and null which represents “missing”.

Logical operators

The basic logical operators are available: AND, OR, NOT. The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. Observe the following truth tables:

 a  b a AND b a OR b
true true true true
true false false true
false true false true
false false false false
true null null true
null true null true
false null false null
null false false null
null null null null

 

 

 

 

 

 

 

 

 a NOT a a IS NULL a IS NOT NULL
true false false true
false true false true
null null true false

 

 

 

 

Logical functions

Function Return type Description Example Result
"all"(a boolean, b boolean, c boolean) boolean if c is null then true
else if a is false then false
else if (b is true or c is true) then null
else true
"all"(true, false, true) null
"and"(a boolean, b boolean) boolean a AND b "and"(true, false) false
"any"(a boolean, b boolean, c boolean) boolean if c is null then false
else if a is true then true
else if (b is true or c is true) then null
else false
"any"(true, false, true) true
"not"(a boolean) boolean NOT a "not"(true) false
"or"(a boolean, b boolean) boolean a OR b "or"(true, false) true
"xor"(a boolean, b boolean) boolean a OR b, but NOT, a AND b "xor"(true, true) false

 

Comparison functions and operators

Comparison functions and operators dinther Thu, 02/06/2020 - 14:00

Comparison operators, predicates and functions are available for all built-in data types.

Comparison operators

All comparison operators are binary operators that return values of type boolean. The usual comparison operators are available:

Operator Description Example Result
< less than 2 < 5 true
> greater than 2 > 5 false
<= less than or equal to 2 <= 5 true
>= greater than or equal to 2 >= 5 false
= equal 2 = 5 false
<> not equal 2 <> 5 true


 


 


 

 

Comparison predicates

The usual comparison predicates are available:

Predicate Description Example Result
val BETWEEN lb AND ub is val between lb and ub.
equivalent to: val >= lb AND val <= ub
'db' between 'abc' and 'db' true
val NOT BETWEEN lb AND ub is val not between lb and ub.
equivalent to: val < lb OR val > ub
'db' not between 'abc' and 'db' false
val BETWEEN SYMMETRIC lb AND ub is val between lb and ub after sorting lb and ub 'db' between symmetric 'abc' and 'db' true
val NOT BETWEEN SYMMETRIC lb AND ub is val not between lb and ub after sorting lb and ub 'db' not between symmetric 'abc' and 'db' false
val IS NULL is val NULL 'db' is null false
val IS NOT NULL is val not NULL 'db' is not null true


 


 


 


 

 

Comparison functions

Function Return type Description Example Result
"<"(arg1, arg2) boolean is arg1 less than arg2 "<"('aa', 'ab') true
">"(arg1, arg2) boolean is arg1 greater than arg2 ">"('aa', 'ab') false
"<="(arg1, arg2) boolean is arg1 less than or equal to arg2 "<="('aa', 'ab') true
">="(arg1, arg2) boolean is arg1 greater than or equal to arg2 ">="('aa', 'ab') false
"="(arg1, arg2) boolean is arg1 equal to arg2 "="('aa', 'ab') false
"<>"(arg1, arg2) boolean is arg1 not equal to arg2 "<>"('aa', 'ab') true
"between"(arg_1 any, arg_2 any, arg_3 any, boolean, boolean, boolean, boolean, boolean) boolean is arg1 between arg2 and arg3 "between"('ab', 'aa', 'ac', false, false, false, false, false) true
coalesce(arg1, arg2, ...) same as arg1 or arg2 returns the first non-NULL value in the list, or NULL if all args are NULL. At least two parameters must be passed. coalesce(null, 'ac', 'dc') 'ac'
ifthenelse(boolean arg1, arg2, arg3) same as arg2 or arg3 when arg1 expression evaluates to true then return arg2 else arg3 ifthenelse(('a' = 'b'), 1, 2) 2
isnull(arg1) boolean is arg1 null isnull('aa') false
nullif(arg1, arg2) same as arg1 returns NULL if arg1 = arg2 is true, otherwise returns arg1.
equivalent to: CASE WHEN arg1 = arg2 THEN NULL ELSE arg1 END
nullif('ams', 'ams') null
not_ilike(s, pat) boolean match pcre pattern pat on string s case insensitive. when matched return false else true. not_ilike('abc', '_B%') false
not_ilike(s, pat, escp) boolean match pcre pattern pat on string s case insensitive using escape character escp to escape wildcards. when matched return false else true. not_ilike('a_bc', '_#_B_', '#') false
not_like(s, pat) boolean match pcre pattern pat on string s. when matched return false else true. not_like('abc', '_b%') false
not_like(s, pat, escp) boolean match pcre pattern pat on string s using escape character escp to escape wildcards. when matched return false else true. not_like('a_bc', '_#_b_', '#') false

 

Cast / Conversion functions and operators

Cast / Conversion functions and operators dinther Wed, 07/15/2020 - 19:45

Casting and conversion functions are available for all built-in system data types.

Casting functions

Function Return type Description Example Result
CAST(col_expr AS data type) same as requested data type converts the value of the col_expr into the specified data type cast(123 as varchar(10)) '123'
CONVERT(col_expr , data type) same as requested data type converts the value of the col_expr into the specified data type convert(123, decimal(10,3)) 123.000

Some more examples:

select cast(true as smallint);
select cast(42 as int);
select cast(123.45 as real);
select cast('123.45' as double precision);
select cast(23.45 as decimal(5,2));    -- precision of 5 digits of which 2 decimal digits

select cast('2020-07-29' as date);
select cast('17:44:59' as time);
select cast('17:44:59.123456' as time);
select cast('2020-07-29 17:44:59' as timestamp);
select cast('2020-07-29T17:44:59' as timestamp);
select cast('2020-07-29 17:44:59.123456' as timestamp);
select cast('17:44:59.321+01:30' as timetz);
select cast('2020-07-29 17:44:59.321+01:30' as timestamptz);
select cast('1234' as interval month);
select cast('86400.123' as interval second);

select cast('a4cd' as blob);    -- use 2 hex digits per byte, so 'a4cd' is 2 bytes long.
select cast('abcde' as clob);
select cast('192.168.1.5/24' as inet);
select cast(r'{"a":[1,2,4]}' as json);
select cast('https://www.monetdb.org/Home' as url);
select cast('e31960fb-dc8b-452d-ab30-b342723e756a' as uuid);

-- or using convert instead of cast:
select convert('a4cd' , blob);
select convert('abcde' , clob);
select convert('192.168.1.5/24' , inet);
select convert(r'{"a":[1,2,4]}' , json);
select convert('https://www.monetdb.org/Home' , url);
select convert('e31960fb-dc8b-452d-ab30-b342723e756a' , uuid);

Casting operators for character string literals

Operator Description Example
x'hexadecimals' convert string value (pairs of hexadecimals) to binary string (blob) x'abcd'
blob 'hexadecimals' convert string value (pairs of hexadecimals) to blob type blob 'abcd'
inet 'valid inet address string' convert string value to inet type inet '192.168.1.5/24'
json 'valid json string' convert string value to json type json '{"a":[1,2,4]}'
url 'valid url string' convert string value to url type url 'https://www.monetdb.org/Home'
uuid 'valid uuid string' convert string value to uuid type uuid 'e31960fb-dc8b-452d-ab30-b342723e756a'
E'string with backslash escapes' convert backslash escapes (such as: \f \t \n \b \u etc) in string as specific (control) characters e'A\fB\tC\n\\Z'
R'any string' NO conversion of backslash escapes is done. Treat string characters as provided, so raw r'A\fB\tC\n\\Z'

 

Mathematical functions and operators

Mathematical functions and operators dinther Thu, 01/23/2020 - 14:48

These apply to MonetDB numeric SQL types: tinyint, smallint, int, bigint, hugeint, decimal, double, float and real.
Some of these functions and operators also work on SQL types: interval month and interval second.

Mathematical operators

Operator Description Example Result
+ addition 2 + 3 5
- subtraction 2 - 3 -1
* multiplication 2 * 3 6
/ division (integer division truncates the result) 5 / 2 2
/ division (on decimals or floating point) 5.0 / 2 2.5
% modulo (remainder) 5 % 4 1
& bitwise AND 91 & 15 11
| bitwise OR 32 | 3 35
^ bitwise XOR 17 ^ 5 20
~ bitwise NOT ~1 -2
<< bitwise shift left 1 << 4 16
>> bitwise shift right 8 >> 2 2


 


 


 


 


 


 

 

Mathematical functions

Function Return type Description Example Result
abs(x) same as input absolute value of a number abs(-17.4) 17.4
bit_and(x, y) int or decimal bitwise AND bit_and(91, 15) 11
bit_not(x) int or decimal bitwise NOT bit_not(1) -2
bit_or(x, y) int or decimal bitwise OR bit_or(32, 3) 35
bit_xor(x, y) int or decimal bitwise XOR bit_xor(17, 5) 20
cbrt(x) double cube root cbrt(2.0) 1.2599210498948734
ceil(x) double nearest integer greater than or equal to argument
(same as ceiling(x))
ceil(-42.8) -42
ceiling(x) double nearest integer greater than or equal to argument
(same as ceil(x))
ceiling(-95.3) -95
exp(x) double exponential exp(1) 2.718281828459045
floor(x) real or double nearest integer less than or equal to argument floor(-42.8) -43
get_value_for(schema_nm, seq_nm) bigint get current value of a sequence get_value_for('dwh2', 'seq_9201') 1234
greatest(x, y) same as input greatest (highest) value of x and y
(same as sql_max(x,y))
greatest(1.2, 5) 5
least(x, y) same as input least (lowest) value of x and y
(same as sql_min(x,y))
least(1.2, 5) 1.2
left_shift(x, i) same as first arg bitwise shift left i bits left_shift(1, 4) 16
ln(x) double natural logarithm ln(2.0) 0.6931471805599453
log(x) double natural logarithm, same as ln(x) log(2.0) 0.6931471805599453
log(b, x) double logarithm to base b.
Note: before Oct2020 release (11.39.5) the arguments were switched, so log(x, b).
log(2, 64.0) 6.0
log10(x) double base 10 logarithm log10(100.0) 2.0
log2(x) double base 2 logarithm log2(64.0) 6.0
mod(x, y) same as input modulo (remainder) mod(5.0, 2.1) 0.8
next_value_for(schema_nm, seq_nm) bigint get current value of a sequence and increment it next_value_for('dwh2', 'seq_9201') 1234
power(x, y) double x raised to the power of y power(2, 5) 32
rand() int random integer value between 0 and 2147483648 rand() 917632440
rand(seed) int random integer value with seed set rand(-5) 1345532277
right_shift(x, i) same as first arg bitwise shift right i bits right_shift(16, 2) 4
round(x, d) same as first arg round x to d decimal places round(42.4382, 2) 42.44
scale_down(x, y) same as first arg ??? divide x by y and round to integer scale_down(100, 3) 33
scale_up(x, y) same as first arg ??? multiply x by y scale_up(13, 3) 39
sign(x) tinyint sign of the argument: -1 when x is negative, 0 when x is zero, 1 when x is positive sign(-8.4) -1
sql_add(x, y) same as input x plus y sql_add(2, 3.4) 5.4
sql_div(x, y) same as input x divided by y sql_div(3.4, 2) 1.7
sql_max(x, y) same as input greatest (highest) value of x and y
(same as greatest(x,y))
sql_max(1.2, sql_max(3.3, 5)) 5
sql_min(x, y) same as input least (lowest) value of x and y
(same as least(x,y))
sql_min(1.2, sql_min(3.3, 5)) 1.2
sql_mul(x, y) same as input x multplied by y sql_mul(1.2, 5) 6.0
sql_neg(x) same as input x times -1 sql_neg(-2.5) 2.5
sql_sub(x, y) same as input subtract y from x sql_sub(5, 7) -2
sqrt(x) double square root sqrt(2.0) 1.4142135623730951
sys.alpha(pdec double, pradius double) double compute alpha 'expansion' of theta for a given declination (used by SkyServer) sys.alpha(5.0, 1.2) 1.2045844792437546
sys.fuse(smallint, smallint) integer combine the bits of two integers into a larger integer type sys.fuse(2, 6) 518
sys.ms_round(x double, prc int, trunc int) double round to prc decimal places and ?? truncate to trunc decimal places sys.ms_round(1.2359, 2, 0) 1.24
sys.ms_trunc(num double, prc int) double truncate to prc decimal places sys.ms_trunc(1.2359, 2) 1.23

 

Trigonometric and Hyperbolic functions

All these functions (except pi()) take argument(s) of type double.

Function Return type Description Example Result
acos(x) double arc cosine of angle x in radians acos(0.54) 1.0003592173949747
asin(x) double arc sine of angle x in radians asin(1.0) 1.5707963267948966
atan(x) double arc tangent of angle x in radians atan(1.0) 0.7853981633974483
atan(x, y) double arc tangent of angle x/y in radians atan(1.0, 2.0) 0.4636476090008061
cos(x) double cosine of angle x in radians cos(12.2) 0.9336336440746373
cosh(x) double hyperbolic cosine of angle x in radians cosh(3.0) 10.067661995777765
cot(x) double cotangent of angle x in radians cot(16.0) 3.326323195635449
degrees(rad) double convert radians angle value to degrees: r*180/pi()
Note: before Oct2020 release (11.39.5) you need to include the sys schema prefix, so sys.degrees(rad).
degrees(0.5) 28.64788975654116
pi() double π constant pi() 3.141592653589793
radians(degr) double convert degrees angle value to radians: d*pi()/180
Note: before Oct2020 release (11.39.5) you need to include the sys schema prefix, so sys.radians(degr).
radians(45) 0.7853981633974483
sin(x) double sine of angle x in radians sin(1.4) 0.9854497299884601
sinh(x) double hyperbolic sine of angle x in radians sinh(1.4) 1.9043015014515339
tan(x) double tangent of angle x in radians tan(1.4) 5.797883715482887
tanh(x) double hyperbolic tangent of angle x in radians tanh(1.4) 0.8853516482022625


 


 


 


 


 


 


 

 

String functions and operators

String functions and operators dinther Wed, 01/29/2020 - 18:51

These apply to MonetDB SQL character string types: char, varchar, clob and subtypes: json and url
In MonetDB all character strings are UTF-8 encoded.

Character string operators

Operator Description Example Result
|| concatenate two strings. Returns null when one of the strings is null 'Monet' || 'DB' MonetDB
|| concatenation of a string with a non-string input 'Value: ' || 42 Value: 42


 


 

Character string functions

Function Return type Description Example Result
ascii(s) int Unicode code point of the first character of the string s ascii('€') 8364
char_length(s) int number of UTF-8 characters in UTF-8 string s char_length('2€') 2
character_length((s) int number of UTF-8 characters in UTF-8 string s character_length(('2€') 2
charindex(substr, s) int start position of substr in string s or 0 when not found charindex('gh', 'highigh') 3
charindex(substr, s, i) int start position of substr in string s starting at offset i
or 0 when not found
charindex('gh', 'highigh', 4) 6
concat(s1, s2) same as input concatenate strings s1 and s2.
returns null when either s1 or s2 is null
concat('Front ', 242) Front 242
difference(s1, s2) int converts two strings s1 and s2 to their soundex codes and reports the number of matching code positions. Since soundex codes have four characters, the result ranges from zero to four, with zero being no match and four being an exact match. difference('MonetDB', 'DB3') 4
editdistance(s1, s2) int compute the 'distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string editdistance('MonetDB', 'DB3') 6
editdistance2(s1, s2) int compute the 'distance2' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string editdistance2('MonetDB', 'DB3') 6
greatest(s1, s2) same as input alphabetically ordered last string value of strings s1 and s2. (same as sql_max(s1, s2)) greatest('ab', 'ac') ac
"index"(s, boolean) int index values of internal string dictionary table. Note: the function name index needs to be surrounded by double quotes. select name, "index"(name, true) nm_idx from sys.schemas order by name 8 for sys, 24 for tmp, 40 for json, etc
insert(s1, int pos, int nr, s2) same as input arg 4 insert string s2 into string s1 after position pos replacing nr characters in string s1. when pos is negative it counts from the end of the string. nr must be >= 0. insert('abcdef', 2, 1, 'XY') abXYdef
lcase(s) same as input convert string s to lower case lcase('MonetDB') monetdb
least(s1, s2) same as input alphabetically ordered first string value of strings s1 and s2. (same as sql_min(s1, s2)) least('ab', 'ac') ab
left(s, int n) same as input arg 1 return first n characters in the string left('MonetDB', 3) Mon
length(s) int count number of UTF-8 characters in UTF-8 string s length('2€') 2
levenshtein(s1, s2) int compute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string levenshtein('MonetDB', 'DB3') 6
levenshtein(s1, s2, int ??, int ??, int ??) int compute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n + ?? (largest distance) where n is the length of largest string levenshtein('MonetDB', 'DB3', 4, 2, 1) 8
locate(s1, s2) int locate string s1 in s2, when found return start position else 0 locate('DB', 'MonetDB') 6
locate(s1, s2, int pos) int locate string s1 in s2 starting from position pos, when found return start position else 0 locate('DB', 'DBMonetDB', 3) 8
lower(s) same as input convert string s to lower case lower('MonetDB') monetdb
lpad(s, int len) same as input arg 1 fill up the string to length len by prepending spaces. If the string is already longer than length then it is truncated (on the right). lpad('abc', 6)    abc
lpad(s1, int len, s2) same as input arg 1 fill up the string s1 to length len by prepending the characters s2. If the string s1 is already longer than length then it is truncated (on the right). lpad('abc', 6, 'DB') DBDabc
ltrim(s) varchar remove leading whitespace characters from string s ltrim(E' \t\fab \tdf ') ab df
ltrim(s1, s2) varchar remove leading s2 strings from string s1 ltrim('aabcdaa', 'a') bcdaa
sys.md5(s) clob(32) calculates the MD5 hash of string, returning the result as 32 character hex number sys.md5('abc') 900150983cd24fb0d6963f7d28e17f72
sys.ms_stuff(varchar(32) s1, int pos, int nr, varchar(32) s2) varchar(32) insert string s2 into string s1 at position pos replacing nr characters in string s1. If pos <= 0 or > length of s1 then an empty string is returned. nr may be negative. sys.ms_stuff('abcdef', 2, 1, 'XY') aXYcdef
octet_length(s) int number of bytes in UTF-8 string s octet_length('2€') 4
patindex(pattern, s) int search for pattern in string s, when found return the position of the last matched character, when not found return 0 patindex('%bc%', 'abcd') 3
qgramnormalize(s) same as input normalisation of a string s. Converts letters to uppercase and removes all characters not in a..zA..Z0..9 or space qgramnormalize('Prize €50!') PRIZE 50
repeat(s, nr) same as input arg 1 repeat string s the specified nr of times repeat('kiss,', 3) kiss,kiss,kiss,
replace(s, srch, repl) same as input arg 1 replace occurrences of string srch in string s by string repl replace('abc', 'b', 'X') aXc
sys.reverse(s) clob return reversed string sys.reverse('MonetDB') BDtenoM
right(s, int n) same as input arg 1 return last n characters in the string right('MonetDB', 4) etDB
rpad(s, int) same as input arg 1 fill up the string to length len by appending spaces. If the string is already longer than length then it is truncated (on the right). rpad('abc', 6) || 'End' abc   End
rpad(s1, int len, s2) same as input arg 1 fill up the string s1 to length len by appending the characters s2. If the string s1 is already longer than length then it is truncated (on the right). rpad('abc', 6, 'DB') abcDBD
rtrim(s) varchar remove trailing whitespace characters from string s rtrim(E'ab df \t\n') ab df
rtrim(s1, s2) varchar remove trailing s2 strings from string s1 rtrim('aabcdaa', 'a') aabcd
similarity(s1, s2) double computes the similarity between string s1 and s2 and returns a number between 0 (no similarity) and 1 (equal). similarity('MonetDB', 'DB3') 0.4
soundex(s) varchar(4) computes the Soundex code from string s using a phonetic algorithm soundex('MonetDB') M533
space(nr) clob(nr) generates a string with nr spaces select 's' || space(4) || 'e' s    e
splitpart(s, sep, n) same as input arg 1 extract substring from string s by spliting on separator string sep the field before nth occurrence. n must be greater than zero splitpart('a|a|bc|cd', '|', 3) bc
sql_max(s1, s2) same as input alphabetically ordered last string value of strings s1 and s2 sql_max('ab', 'ac') ac
sql_min(s1, s2) same as input alphabetically ordered first string value of strings s1 and s2 sql_min('ab', 'ac') ab
strings(s) varchar convert a column of type char(n) or varchar(n) or clob or clob(n) to type varchar(m) where m is the maximum of the string lengths of all values in the column select name, length(name) as name_len, strings(name) as nm_varchar8 from sys.schemas the result column nm_varchar8
will be of type varchar(8)
while name is defined
as varchar(1024)
substr(s, startpos) same as input arg 1 extract substring starting from position startpos substr('abCde', 3) Cde
substr(s, startpos, count) clob(count) extract substring starting from position startpos for count characters substr('abCde', 2, 3) bCd
substring(s, startpos) same as input arg 1 extract substring starting from position startpos substring('abcde', 3) cde
substring(s, startpos, count) clob(count) extract substring starting from position startpos for count characters substring('abcde', 2, 2) bc
trim(s) varchar remove leading and trailing whitespace characters from string s trim(E' \t\fab df \t\n') ab df
trim(s1, s2) varchar remove leading and trailing s2 strings from string s1 trim('aabcdaa', 'a') bcd
"truncate"(s, n_chars) same as input arg 1 truncate the string s to a maximum of n_chars characters. Note: the function name truncate need to be surrounded by double quotes. "truncate"('Truncate', 4) Trun
ucase(s) same as input convert string s to upper case ucase('MonetDB') MONETDB
upper(s) same as input convert string s to upper case upper('MonetDB') MONETDB

 

Date/Time functions and operators

Date/Time functions and operators dinther Thu, 02/06/2020 - 16:30

These apply to MonetDB SQL temporal types: date, time, time with time zone, timestamp and timestamp with time zone .
Some functions apply to MonetDB SQL type: second interval and month interval .

Date/Time operators

Operator Description Example Result
  + add seconds to a date or time or timestamp date '2020-09-28' + (interval '1' second * (7 * 24 * 60 * 60)) date '2020-10-05'
  - subtract seconds from a date or time date '2020-09-28' - (interval '1' second * (7 * 24 * 60 * 60)) date '2020-09-21'
  - subtract months from a date or timestamp date '2020-09-28' - month_interval '7' date '2020-02-28'
  - subtract two dates date '2020-09-28' - date '2020-02-28' interval '7 months'
  - subtract two times time '14:35:45' - time '02:12:24' interval '12 hours 23 minutes 31 seconds'

 

Date/Time functions

NOTE: Some examples and results in below table are UNDER CONSTRUCTION

Function Return type Description Example Result
century(date) int extract century from a date or timestamp or timestamptz century(date '2020-03-22') 21
curdate() date get current date curdate() date '2020-03-22'
current_date() date get current date current_date() date '2020-03-22'
current_time() timetz get current time with time zone current_time() timetz '13:16:57.734639+01:00'
current_timestamp() timestamptz get current timestamp (date and time and fractions) with time zone current_timestamp() timestamptz '2020-03-22 13:16:57.734639+01:00'
curtime() timetz get current time with time zone current_time() timetz '13:16:57.734639+01:00'
sys.date_to_str(date, format_str) clob convert date value to a string using format_str sys.date_to_str(date '2020-03-22', '%D %d.%m.%Y'); 03/22/20 22.03.2020
date_trunc(field_str, timestamp) timestamp  Truncate a timestamp to granularity specified bij field_str. Allowed values for field_str are "millennium", "century", "decade", "year", "quarter", "month", "week", "day", "hour", "minute", "second", "milliseconds", "microseconds". select date_trunc('month', timestamp '2020-03-22 13:16:57.734639'); timestamp '2020-03-01 00:00:00.000000'
"day"(dt_or_ts) int extract month day nr (1..31) of a date or timestamp or timestamptz.
same as: dayofmonth(date)
"day"(date '2020-03-22') 22
"day"(sec_interval) bigint compute nr of days and truncate it to an integer "day"(interval '3.23' second * (24 * 60 * 60)) 3
dayofmonth(dt_or_ts) int extract month day nr (1..31) of a date or timestamp or timestamptz.
same as: "day"(date)
dayofmonth(date '2020-03-22') 22
dayofweek(dt_or_ts) int extract week day nr (1..7) of a date or timestamp or timestamptz dayofweek(date '2020-03-22') 7
dayofyear(dt_or_ts) int extract year day nr (1..366) of a date or timestamp or timestamptz dayofyear(date '2020-03-22') 82
decade(dt_or_ts) int extract decade nr of a date or timestamp or timestamptz decade(date '2027-03-22') 202
sys.epoch(int seconds) timestamptz add number of seconds to timestamp '1970-01-01 00:00:00.0' sys.epoch(1000) timestamptz '1970-01-01 02:00:01+02:00'
sys.epoch(bigint milliseconds) timestamptz add number of milliseconds to timestamp '1970-01-01 00:00:00.0' sys.epoch(1234567890) timestamptz '2009-02-14 01:31:30+02:00'
sys.epoch(timestamp) int convert timestamp (or timestamptz) to nr of seconds since '1970-01-01 00:00:00.0' sys.epoch(timestamp '2009-02-13 23:31:30.0') 1234567890
extract(field from dt_value) int extract a specific field from a date or time or timestamp.
supported keywords for field are: CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, MINUTE and SECOND.
extract(doy from date '2027-03-22') 82
"hour"(tm_or_ts) int extract hour nr (0..23) of a time or timetz or timestamp or timestamptz or sec_interval "hour"(timetz '15:35:02.002345+01:00') 15
local_timezone() sec_interval get local time zone as represented in seconds local_timezone() 3600
localtime() time get current local time without time zone localtime() time '13:16:57.734639'
localtimestamp() timestamp get current local timestamp (date and time and fractions) without time zone localtimestamp() timestamp '2020-03-22 13:16:57.734639'
"minute"(tm_or_ts) int extract minutes (0..59) of a time or timetz or timestamp or timestamptz or sec_interval "minute"(timetz '15:35:02.002345+01:00') 35
"month"(dt_or_ts) int extract month nr (1..12) of a date or timestamp or timestamptz or month_interval "month"(date '2020-07-22') 7
now() timestamptz get current timestamp (date and time and fractions) with time zone now() timestamptz '2020-03-22 13:16:57.734639+01:00'
quarter(dt_or_ts) int extract quarter nr (1..4) of a date or timestamp or timestamptz quarter(date '2020-07-22') 3
"second"(sec_interval) int extract seconds (0 .. 59) of a sec_interval "second"(interval '24' second) 24
"second"(tm_or_ts) decimal(9,6) extract seconds (0.000 .. 59.999999) of a time or timetz or timestamp or timestamptz "second"(timetz '15:35:02.002345+01:00') 2.002345
sql_add(dt_or_ts, month_interval) same as arg 1 add an integer month interval value to a date or timestamp or timestamptz sql_add(date '', interval month '')  
sql_add(dt_or_tm, sec_interval) same as arg 1 add a decimal seconds interval value to a date or time or timetz or timestamp or timestamptz sql_add(date '', interval second '')  
sql_sub(date, date) int subtract a date from a date returning nr of seconds between the two dates sql_sub(date '', date '')  
sql_sub(dt_or_ts, month_interval) same as arg 1 subtract an integer month interval value from a date or timestamp or timestamptz sql_sub(date '', interval month '')  
sql_sub(dt_or_tm, sec_interval) same as arg 1 subtract a decimal seconds interval value from a date or time or timetz or timestamp or timestamptz sql_sub(date '', interval second '')  
sql_sub(time, time) sec_interval subtract a time from a time returning nr of seconds interval between the two times sql_sub(time '', time '')  
sql_sub(timestamp, timestamp) sec_interval subtract a timestamp from a timestamp returning nr of seconds interval between the two timestamps sql_sub(timestamp '', timestamp '')  
sql_sub(timestamptz, timestamptz) sec_interval subtract a timestamptz from a timestamptz returning nr of seconds interval between the two timestamptzs sql_sub(timestamptz '', timestamptz '')  
sql_sub(timetz, timetz) sec_interval subtract a timetz from a timetz returning nr of seconds interval between the two timetzs sql_sub(timetz '', timetz '')  
sys.str_to_date(s, format_str) date parse string s using format_str and construct a date value sys.str_to_date('', '')  
sys.str_to_time(s, format_str) time parse string s using format_str and construct a time value sys.str_to_time('', '')  
sys.str_to_timestamp(s, format_str) timestamp parse string s using format_str and construct a timestamp value sys.str_to_timestamp('', '')  
sys.time_to_str(time, format_str) clob convert time value to a string using format_str sys.time_to_str(time '', '')  
sys.timestamp_to_str(timestamp, format_str) clob convert timestamp value to a string using format_str sys.timestamp_to_str(timestamp '', '')  
week(dt_or_ts) int extract week nr (1 .. 53) of a date or timestamp or timestamptz.
same as: weekofyear(date)
week(date '2020-03-22') 12
weekofyear(dt_or_ts) int extract week nr (1 .. 53) of a date or timestamp or timestamptz.
same as: week(date)
weekofyear(date '2020-03-22') 12
"year"(dt_or_ts) int extract year nr of a date or timestamp or timestamptz or month_interval "year"(date '2020-03-22') 2020

 

Date/Time pseudo columns

Pseudo column Description Example Result
CURRENT_DATE get current date select current_date date '2020-02-02'
CURRENT_TIME get current time (with time zone) select current_time timetz '15:35:02.073522+01:00'
CURRENT_TIMESTAMP get current timestamp (with time zone) select current_timestamp timestamptz '2020-02-02 15:35:02.002345+01:00'
CURRENT_TIMEZONE get current time zone as seconds interval select current_timezone 3600.000
NOW same as: CURRENT_TIMESTAMP select now timestamptz '2020-02-02 15:35:02.002345+01:00'
LOCALTIME get current local time without time zone select localtime time '15:35:02.073522'
LOCALTIMESTAMP get current local timestamp without time zone select localtimestamp timestamp '2020-02-02 15:35:02.073522'

See also: Date/Time Pseudo Columns

 

Binary string functions

Binary string functions dinther Thu, 04/23/2020 - 18:37

These apply to MonetDB SQL binary string type: blob
To use binary string data in scripts you can use the hex cast notation: x'0012FF' to represent 3 bytes: 0, 18 (16+2) and 255.
The blob cast notation: blob'0012FF' is also allowed.

Binary string functios

Function Return type Description Example Result
length(blob) int number of bytes in binary string blob length(x'0012FF') 3
octet_length(blob) int number of bytes in binary string blob octet_length(x'0012FF') 3

 

In MonetDB binary string data is stored as hex character strings, using two hex characters per byte. Therefore some of the character string functions can also be applied to binary string data, for example

SELECT x'1a2b3c4d5e';
SELECT blob '1a2b3c4d5e';

CREATE TABLE test_blob(b blob NOT NULL, b52 blob(52));
INSERT INTO test_blob(b,b52) VALUES (x'123456', blob'78901234');
INSERT INTO test_blob(b,b52) VALUES ('123456abcdef', '78901234abcdef'); -- implicit conversion from char to blob is done

SELECT length(b) as byte_length, octet_length(b) as octet_length, b from test_blob where b = x'123456';
-- returns: 3 3 123456
SELECT length(b) as byte_length, char_length(b) as char_length, b from test_blob where b = x'123456';
-- returns: 3 6 123456

-- example of how to extract part of a binary string
SELECT b52, substring(b52, 3, 4) as part_as_Clob from test_blob;
SELECT b52, cast(substring(b52, 3, 4) as blob) as part_as_Blob from test_blob;

-- example of how to concatenate two binary strings
SELECT b||b52 as b_concat_result_Clob from test_blob;
SELECT convert(b||b52 , blob) as b_concat_result_Blob from test_blob;

-- comparisons are allowed, except for LIKE and ILIKE pattern matches on blobs
SELECT * from test_blob where b < b52;
SELECT * from test_blob where b like '1234%'; -- NOT allowed, generates an ERROR
SELECT * from test_blob where substring(b,0,9) like '1234%'; -- This is allowed as substring() returns a Clob

 

Inet functions and operators

Inet functions and operators dinther Thu, 07/02/2020 - 21:50

These apply to MonetDB SQL type: inet (IPv4 Network Address).

An IPv4 Network Address string has a specific syntax, see also IPv4 address.

Inet operators

Operator Description Example Result
< is less than inet '192.168.1.5' < inet '192.168.1.6' true
<= is less than or equal inet '192.168.1.5' <= inet '192.168.1.5' true
= equals inet '192.168.1.5' = inet '192.168.1.5' true
> is greater than inet '192.168.1.5' > inet '192.168.1.4' true
>= is greater than or equal inet '192.168.1.5' >= inet '192.168.1.5' true
<> is not equal inet '192.168.1.5' <> inet '192.168.1.4' true
<< is contained by inet '192.168.1.5' << inet '192.168.1/24' true
<<= is contained by or equals inet '192.168.1/24' <<= inet '192.168.1/24' true
>> contains inet '192.168.1/24' >> inet '192.168.1.5' true
>>= contains or equals inet '192.168.1/24' >>= inet '192.168.1/24' true


 


 


 


 


 

 

Inet functions

Note: You must include the sys. prefix for all these inet functions (see examples) in order to work properly from other schemas than sys.

Function Return type Description Example Result
sys.abbrev(inet) clob abbreviated display format as text sys.abbrev(inet '10.1.0.0/16') 10.1/16
sys.broadcast(inet) inet broadcast address for network sys.broadcast(inet '192.168.1.5/24') 192.168.1.255/24
sys.host(inet) clob extract IP address as text sys.host(inet '192.168.1.5/24') 192.168.1.5
sys.hostmask(inet) inet construct host mask for network sys.hostmask(inet '192.168.23.20/30') 0.0.0.3
sys.left_shift(inet, inet) boolean is contained by sys.left_shift(inet '192.168.1/24', inet '192.168.1.5') true
sys.left_shift_assign(inet, inet) boolean is contained by or equals sys.left_shift_assign(inet '192.168.1/24', inet '192.168.1/24') true
sys.masklen(inet) int extract netmask length sys.masklen(inet '192.168.1.5/24') 24
sys.netmask(inet) inet construct netmask for network sys.netmask(inet '192.168.1.5/24') 255.255.255.0
sys.network(inet) inet extract network part of address sys.network(inet '192.168.1.5/24') 192.168.1.0/24
sys.right_shift(inet, inet) boolean contains sys.right_shift(inet '192.168.1/24', inet '192.168.1.5') true
sys.right_shift_assign(inet, inet) boolean contains or equals sys.right_shift_assign(inet '192.168.1/24', inet '192.168.1/24') true
sys.setmasklen(inet, int) inet set netmask length for inet value sys.setmasklen(inet '192.168.1.5/24', 16) 192.168.1.5/16
sys.text(inet) clob extract IP address and netmask length as text sys.text(inet '192.168.1.5') 192.168.1.5/32

 

JSON functions

JSON functions dinther Wed, 02/19/2020 - 18:35

These apply to MonetDB SQL type: JSON (JavaScript Object Notation).

Note: All JSON functions are located in the json schema. You must include the json. prefix for all these functions in order to work properly.

Function Return type Description Example Result
json.filter(js, indx) json extracts a indexed component from js. The index starts at 0 for the first element. json.filter(json '["a", 4]', 1) [4]
json.filter(js, pathexpr) json extracts the compgonent from js that satisfied the pathexpr string. The supported JSON path expression syntax is documented at JSONPath json.filter(json '{"price":9}', '$..price') [9]
json."integer"(js) bigint or int or smallint or tinyint turn a number or singleton array value or singleton object element of js into an integer. Returns null if it fails. json."integer"(json '{"n":4}') 4
json.isarray(str) boolean checks the string str for JSON array compliance json.isarray('[1,7]') true
json.isarray(js) boolean checks the JSON value js for JSON array compliance json.isarray(json '[1,7]') true
json.isobject(str) boolean checks the string str for JSON object compliance json.isobject('{"n":4}') true
json.isobject(js) boolean checks the JSON value js for JSON object compliance json.isobject(json '{"n":4}') true
json.isvalid(str) boolean checks the string str for JSON syntax compliance json.isvalid('{"a":[1]}') true
json.isvalid(js) boolean checks the JSON value js for JSON validity json.isvalid(json '{"a":[1,2]}') true
json.keyarray(js) json returns an arraylist of key tags for the top-level components of js. Note js must represent a JSON object else an error (Object expected) is reported json.keyarray(json '{"id":2, "nm":"X"}') ["id","nm"]
json.length(js) int returns the number of top-level components of js json.length(json '{"a":[1]}') 1
json.number(js) double turn a number or singleton array value or singleton object tag of js into a double. Returns null if it fails. json.number(json '{"n":4}') 4.0
json.text(js) clob glue together the values in js separated by space character json.text(json '[1,2,3,4]') 1 2 3 4
json.text(js, Separator str) clob glue together the values in js separated by Separator string json.text(json '[1,2,3,4]', 'x') 1x2x3x4
json.valuearray(js) json returns an arraylist of value tags for the top-level components of js. Note js must represent a JSON object else an error (Object expected) is reported json.valuearray(json '{"a":1, "b":2}') [1,2]

 

Aggregate functions returning a concatenated CLOB string which is a valid json arrary

Function Return type Description Example Result
json.tojsonarray(double) clob convert numeric values in the column/group into a json array string json.tojsonarray(i) [ "1", "2", "3", "4" ]
json.tojsonarray(clob) clob convert string values in the column/group into a json array string json.tojsonarray(c) [ "one", "two", "tree", "four" ]

See also: AggregateFunctions

 

URL functions

URL functions dinther Thu, 03/26/2020 - 18:33

These functions apply to MonetDB SQL type: URL (Uniform Resource Locator).

A URL string has a specific syntax, see URL syntax for valid URL string patterns.

For the examples below we use following url_example string:
'https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=asc#example' to create the shown results.

Note: You must include the sys. prefix for all these URL functions (see examples) in order to work properly from other schemas than sys.

Function Return type Description Example Result
sys.getanchor(url) clob extract the anchor part (after the #) from the url, may return null if no achor exists sys.getanchor(url_example) example
sys.getbasename(url) clob extract the base name part (after the / excluding the extension) from the path of the url, may return null sys.getbasename(url_example) Abc
sys.getcontext(url) clob extract the path context part of the url sys.getcontext(url_example) /Doc/Abc.html
sys.getdomain(url) clob extract the top-level domain part of the host part of the url, may return null sys.getdomain(url_example) org
sys.getextension(url) clob extract the file extension part of the url, may return null sys.getextension(url_example) html
sys.getfile(url) clob extract the last file part of the path of the url, may return null sys.getfile(url_example) Abc.html
sys.gethost(url) clob extract the host part of the url, may return null sys.gethost(url_example) www.monetdb.org
sys.getport(url) clob extract the port number part of the url, may return null sys.getport(url_example) 458
sys.getprotocol(url) clob extract the protocol or scheme part of the url sys.getprotocol(url_example) https
sys.getquery(url) clob extract the query part (after the ?) of the url, may return null sys.getquery(url_example) lang=nl&sort=asc
sys.getroboturl(url) clob extract the location of the robot control file of the url sys.getroboturl(url_example) https://me@www.monetdb.org:458/robots.txt
sys.getuser(url) clob extract the user part of the url, may return null sys.getuser(url_example) me
sys.isaurl(string) boolean checks whether a character string complies to the url syntax format sys.isaurl(url_example) true
sys.newurl(scheme, authority, path) url create a new url from three strings: scheme, authority or host and path sys.newurl('https', 'usr@www.a.com:123', 'docs/index.html') https://usr@www.a.com:123/docs/index.html
sys.newurl(scheme, host, int port, path) url create a new url from three strings and a port number as third argument sys.newurl('https', 'www.a.com', 5567, 'docs/index.html') https://www.a.com:5567/docs/index.html

 

UUID functions

UUID functions dinther Wed, 07/15/2020 - 15:20

These apply to MonetDB SQL type: UUID (Universally Unique IDentifiers).

Note: You must include the sys. prefix for these UUID functions (see examples) in order to work properly from other schemas than sys.

Function Return type Description Example Result
sys.isauuid(string) boolean tests if the given string represents a valid uuid value and returns a boolean value true or false, or null when the input is null sys.isauuid('e31960fb-dc8b-452d-ab30-b342723e7565') true
sys.uuid() uuid generates and returns a new random uuid sys.uuid() 65950c76-a2f6-4543-660a-b849cf5f2453

 

To convert a string column or expression or literal (which represents a valid uuid) to a uuid type you can use a cast() or convert() function. Examples:

select  cast('26d7a80b-7538-4682-a49a-9d0f9676b765' as uuid)  as uuid_val;
select convert('83886744-d558-4e41-a361-a40b2765455b', uuid)  as uuid_val;

To convert a quoted string literal which represents a valid uuid to a uuid type you can also use the uuid prefix. Example:

select uuid'AC6E4E8C-81B5-41B5-82DE-9C837C23B40A'  as uuid_val;

 

Aggregate functions

Aggregate functions dinther Wed, 04/15/2020 - 20:06

Aggregate functions operate on a column or column expression of a table or group (as created via GROUP BY clause).

For the examples below we use a simple table t(i int, c varchar(8)) with contents:
INSERT into t(i,c) VALUES
(1, 'one'),
(2, 'two'),
(3, 'tree'),
(4, 'four'),
(NULL, NULL);

to create the shown Results.

Standard aggregate functions

Function Return type Description Example Result
avg(numeric_expr) double compute the average of the non-NULL integer/float/decimal/month_interval values in the column or group avg(i) 2.5
count(*) bigint count the number of rows in the column or group including those who may have NULL values count(*) 5
count(col_expr) bigint count the number of non-NULL values in the column or group count(i) 4
count(distinct col_expr) bigint count the number of distinct non-NULL values in the column or group count(distinct i%2) 2
max(col_expr) type of col_expr return the maximum non-NULL value in the column or group max(c) two
min(col_expr) type of col_expr return the minimum non-NULL value in the column or group min(c) four
prod(numeric_expr) bigint or hugeint or double compute the product of the non-NULL numeric values in the column or group prod(i) 24
prod(distinct numeric_expr) bigint or hugeint or double compute the product of the distinct non-NULL numeric values in the column or group prod(distinct 1 + i%2) 2
sum(integer_expr) bigint or hugeint compute the sum of all non-NULL integer values in the column or group sum(i) 10
sum(distinct integer_expr) bigint or hugeint compute the sum of all distinct non-NULL integer values in the column or group sum(distinct i/2) 3
sum(decimal_expr) decimal compute the sum of all non-NULL decimal values in the column or group sum(cast(i as decimal)) 10.000
sum(float_expr) real or double compute the sum of all non-NULL floating point values in the column or group sum(cast(i as real)) 10.0
sum(month_interval_expr) month_interval compute the sum of all non-NULL interval month values in the column or group sum(cast(i as interval month)) 10
sum(sec_interval_expr) sec_interval compute the sum of all non-NULL interval second values in the column or group sum(cast(i as interval second)) 10.000

 

Aggregate functions returning a concatenated CLOB string

Note: You must include the sys. or json. prefix for these aggregate functions (see examples) in order to work properly.

Function Return type Description Example Result
listagg(col_expr) varchar concatenate non-NULL values in a column or group into one large varchar string with values separated by a comma (the default separator)
Supported from release Jun2020 (11.37.7)
listagg(c) one,two,tree,four
listagg(col_expr, separator_str) varchar concatenate non-NULL values in a column or group into one large varchar string with values separated by separator_str
Supported from release Jun2020 (11.37.7)
listagg(i, ' | ') 1 | 2 | 3 | 4
sys.group_concat(col_expr) clob concatenate non-NULL values in a column or group into one large string with values separated by a comma (the default separator) sys.group_concat(c) one,two,tree,four
sys.group_concat(col_expr, separator_str) clob concatenate non-NULL values in a column or group into one large string with values separated by separator_str sys.group_concat(i, ' | ') 1 | 2 | 3 | 4
json.tojsonarray(clob) clob convert string values in the column or group into a json array string json.tojsonarray(c) [ "one", "two", "tree", "four" ]
json.tojsonarray(double) clob convert numeric values in the column or group into a json array string json.tojsonarray(i) [ "1", "2", "3", "4" ]

 

Aggregate functions for statistics

Note: You must include the sys. prefix for below aggregate functions (see examples) in order to work properly.

Statistic Function Return type Description Example Result
sys.corr(numeric_a, numeric_b) double return the correlation coefficient of the non-NULL values in the numeric (integer/decimal/real/double) column or group numeric_a versus numeric_b sys.corr(i, i+2) 0.7500000000000001
sys.covar_pop(numeric_a, numeric_b) double return the population covariance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group numeric_a versus numeric_b.
Supported from release Jun2020 (11.37.7)
sys.covar_pop(i, i*2) 2.5
sys.covar_samp(numeric_a, numeric_b) double return the sample covariance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group numeric_a versus numeric_b.
Supported from release Jun2020 (11.37.7)
sys.covar_samp(i, i*2) 3.3333333333333335
sys.median(col_expr) type of col_expr return the statistic median of the non-NULL values in the numeric (integer/decimal/real/double) or temporal (date/time/timestamp/interval) column or group sys.median(i) 2
sys.median_avg(numeric_expr) double return the statistic median average of the non-NULL values in the numeric (integer/decimal/real/double) column or group sys.median_avg(i) 2.5
sys.quantile(col_expr, percentile) type of col_expr return the statistic quantile of the non-NULL values in the numeric (integer/decimal/real/double) or temporal (date/time/timestamp/interval) column or group. The percentile argument must be between 0.0 and 1.0 sys.quantile(i, 0.7) 3
sys.quantile_avg(numeric_expr, percentile) double return the statistic quantile average of the non-NULL values in the numeric (integer/decimal/real/double) column or group. The percentile argument must be between 0.0 and 1.0 sys.quantile_avg(i, 0.6) 2.8
sys.stddev_pop(numeric_expr) double return the population standard deviation of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square root of var_pop(). sys.stddev_pop(i) 1.118033988749895
sys.stddev_samp(numeric_expr) double return the sample standard deviation of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square root of var_samp(). sys.stddev_samp(i) 1.2909944487358056
sys.var_pop(numeric_expr) double return the population standard variance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square of stddev_pop(). sys.var_pop(i) 1.25
sys.var_samp(numeric_expr) double return the sample standard variance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square of stddev_samp(). sys.var_samp(i) 1.6666666666666667

For more info see: Statistic Functions

 

You can also create your own aggregate functions, see Create Aggregate function command.

To list the available aggregate functions in your MonetDB server run query:
SELECT DISTINCT schema_id, name, func, mod, language, system FROM sys.functions WHERE type = 3 ORDER BY name;

 

Window functions

Window functions dinther Thu, 06/25/2020 - 20:34

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.
Note that these functions must be invoked using window function syntax, i.e., an OVER clause is required.
The built-in window functions are listed below.

Built-in window functions

Function Return type Description Available since
cume_dist(value any, boolean, boolean) double cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows Apr2019 (11.33.3)
dense_rank(value any, boolean, boolean) int rank of the current row without gaps
diff(value any) boolean is the current row value different from other partition rows
diff(boolean, value any) boolean is the current row value different from other partition rows
first_value(value any) any returns value evaluated at the row that is the first row of the window frame Apr2019 (11.33.3)
lag(value any, [offset int, [default any,]] boolean, boolean) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null Apr2019 (11.33.3)
 
last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame Apr2019 (11.33.3)
lead(value any, [offset int, [default any,]] boolean, boolean) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null Apr2019 (11.33.3)
nth_value(value any, nth int) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row Apr2019 (11.33.3)
ntile(value any, num_buckects int, boolean, boolean) int integer ranging from 1 to the argument value, dividing the partition as equally as possible Apr2019 (11.33.3)
percent_rank(value any, boolean, boolean) double relative rank of the current row: (rank - 1) / (total partition rows - 1) Apr2019 (11.33.3)
rank(value any, boolean, boolean) int rank of the current row with gaps
row_number(value any, boolean, boolean) int number of the current row within its partition, counting from 1

In addition to above functions, some aggregate functions can be used as a window function. Aggregate functions act as window functions only when an OVER clause follows the call; otherwise they act as non-window aggregates and return a single row for the entire set.

When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a “running sum” type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.

Aggregate functions which can be used as window function

Function Return type Available since
avg(numeric_expr) double Apr2019 (11.33.3)
count(col_expr any, distinct boolean) bigint or hugeint Apr2019 (11.33.3)
max(col_expr any) same as type of col_expr Apr2019 (11.33.3)
min(col_expr any) same as type of col_expr Apr2019 (11.33.3)
prod(numeric_expr) bigint or hugeint or double Apr2019 (11.33.3)
sum(numeric_expr) depends on type of numeric_expr Apr2019 (11.33.3)
   
sys.corr(numeric_a, numeric_b) double Jun2020 (11.37.7)
sys.covar_pop(numeric_a, numeric_b) double Jun2020 (11.37.7)
sys.covar_samp(numeric_a, numeric_b) double Jun2020 (11.37.7)
sys.stddev_pop(numeric_expr) double Jun2020 (11.37.7)
sys.stddev_samp(numeric_expr) double Jun2020 (11.37.7)
sys.var_pop(numeric_expr) double Jun2020 (11.37.7)
sys.var_samp(numeric_expr) double Jun2020 (11.37.7)
   
sys.group_concat(col_expr clob) clob Jun2020 (11.37.7)
sys.group_concat(col_expr clob, separator clob) clob Jun2020 (11.37.7)
listagg(col_expr varchar) varchar Jun2020 (11.37.7)
listagg(col_expr varchar, separator varchar) varchar Jun2020 (11.37.7)

See also Window Functions syntax and examples

See also Extended SQL:2011 Window Functions in MonetDB release Apr2019

You can also create your own window functions, see Create Window Function command.

To query the available window functions in your MonetDB server run query:
SELECT DISTINCT schema_id, name, func, mod, language, system FROM sys.functions WHERE type = 6 ORDER BY name;