Functions

VSCode

Malloy has a standard set of functions available at all times. These include scalar functions (e.g. concat), aggregate functions (e.g. stddev), and analytic (or "window") functions (e.g. lag).

These functions are defined in Malloy in order to provide basic typechecking of arguments and return types, as well as some compatibility transformations. Perfect compatibility across all dialects for all functions is not guaranteed, and specific differences are noted on a case-by-case basis.

For quick function lookup, see the alphebetized table of all available functions.

Note: the behavior of functions changed in v0.0.39. For more information, see a description of that change.

Syntax

Built-In Functions

Functions that are "built-in" to Malloy can be called the usual way with the name of the function, an open parenthesis, a comma separated list of Malloy expressions, and a close parenthesis, e.g. concat(upper(first), ' ', upper(last)).

Raw SQL Functions

Functions that are not built in may be called in the same way, but with an ! in between the function name and argument list: sinh!(x). In this case, Malloy assumes the return type of the function is the same as that of the first argument (or number if there are no arguments). When this is not correct, the return type can be specified after the !, e.g. hash!number(username).

Function Documentation

Section
String Functions
Numeric Functions
Interval Functions
Date and Timestamp Functions
Other Functions
Aggregate Functions
Ungrouped Aggregate Functions
Analytic Functions

String Functions

ascii	byte_length	chr	concat	ends_with
length	lower	ltrim	regexp_extract	repeat
replace	repeat	rtrim	starts_with	strpos
substr	trim	unicode	upper

concat

concat(value, ...)

Concatenates multiple values together, casting non-string values to string. The exact behavior of string casting may depend on the dialect.

If no values are given, concat returns the empty string.

Behavior for null depends on dialect: in BigQuery, if any argument is null, the result is null; in DuckDB and Postgres, null is treated as an empty string.

run: empty -> {
  group_by: 
    e1 is concat('foo', 'bar')
    e2 is concat(1, null)
    e4 is concat('Date: ', @2021-01-23)
    e3 is concat()
}

QUERY RESULTS

[
  {
    "e1": "foobar",
    "e2": "1",
    "e4": "Date: 2021-01-23",
    "e3": ""
  }
]

SELECT 
   CONCAT('foo','bar') as "e1",
   CONCAT(1,NULL) as "e2",
   CONCAT('Date: ',DATE '2021-01-23') as "e4",
   '' as "e3"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

lower

lower(value)

Returns a string like value but with all alphabetic characters in lowercase.

run: empty -> {
  group_by: e1 is lower('FOO')
}

QUERY RESULTS

[
  {
    "e1": "foo"
  }
]

SELECT 
   LOWER('FOO') as "e1"
FROM (
    SELECT 1
  ) as base
GROUP BY 1
ORDER BY 1 asc NULLS LAST

upper

upper(value)

Returns a string like value but with all alphabetic characters in uppercase.

run: empty -> {
  group_by: e1 is upper('foo')
}

QUERY RESULTS

[
  {
    "e1": "FOO"
  }
]

SELECT 
   UPPER('foo') as "e1"
FROM (
    SELECT 1
  ) as base
GROUP BY 1
ORDER BY 1 asc NULLS LAST

strpos

strpos(test_string, search_string)

Returns the 1-based position of the first occurrence of search_string in test_string, or 0 if search_string is not found.

run: empty -> {
  group_by: 
    e1 is strpos('abc', 'b')
    e2 is strpos('abc', 'd')
    e3 is strpos('abc', null)
    e4 is strpos(null, 'a')
}

QUERY RESULTS

[
  {
    "e1": 2,
    "e2": 0,
    "e3": null,
    "e4": null
  }
]

SELECT 
   STRPOS('abc','b') as "e1",
   STRPOS('abc','d') as "e2",
   STRPOS('abc',NULL) as "e3",
   STRPOS(NULL,'a') as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

starts_with

starts_with(value, prefix)

Returns true if value begins with prefix and false otherwise. If either value or prefix is null, the result is false (unlike in SQL, where it would be null).

run: empty -> {
  group_by: 
    e1 is starts_with('abc', 'a')
    e2 is starts_with('abc', 'b')
    e3 is starts_with('abc', null)
    e4 is starts_with(null, 'a')
}

QUERY RESULTS

[
  {
    "e1": true,
    "e2": false,
    "e3": false,
    "e4": false
  }
]

SELECT 
   COALESCE(STARTS_WITH('abc', 'a'), false) as "e1",
   COALESCE(STARTS_WITH('abc', 'b'), false) as "e2",
   COALESCE(STARTS_WITH('abc', NULL), false) as "e3",
   COALESCE(STARTS_WITH(NULL, 'a'), false) as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

ends_with

ends_with(value, suffix)

Returns true if value ends with prefix and false otherwise. If either value or suffix is null, the result is false (unlike in SQL, where it would be null).

run: empty -> {
  group_by: 
    e1 is ends_with('abc', 'c')
    e2 is ends_with('abc', 'b')
    e3 is ends_with('abc', null)
    e4 is ends_with(null, 'a')
}

QUERY RESULTS

[
  {
    "e1": true,
    "e2": false,
    "e3": false,
    "e4": false
  }
]

SELECT 
   COALESCE(SUFFIX('abc', 'c'), false) as "e1",
   COALESCE(SUFFIX('abc', 'b'), false) as "e2",
   COALESCE(SUFFIX('abc', NULL), false) as "e3",
   COALESCE(SUFFIX(NULL, 'a'), false) as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

trim

trim(value)
trim(value, trim_characters)

Returns a string with leading and trailing characters in trim_characters (or whitespace, if trim_characters is unspecified) removed.

run: empty -> {
  group_by: 
    e1 is trim('  keep this  ')
    e2 is trim('_ _keep_this_ _', '_ ')
    e3 is trim(' keep everything ', '')
    e4 is trim('null example', null)
    e5 is trim(null, ' _')
}

QUERY RESULTS

[
  {
    "e1": "keep this",
    "e2": "keep_this",
    "e3": " keep everything ",
    "e4": null,
    "e5": null
  }
]

SELECT 
   TRIM('  keep this  ') as "e1",
   TRIM('_ _keep_this_ _','_ ') as "e2",
   TRIM(' keep everything ','') as "e3",
   TRIM('null example',NULL) as "e4",
   TRIM(NULL,' _') as "e5"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5
ORDER BY 1 asc NULLS LAST

ltrim

ltrim(value)
ltrim(value, trim_characters)

Like trim(value, trim_characters) but only removes leading characters.

run: empty -> {
  group_by: 
    e1 is ltrim('  keep this ->  ')
    e2 is ltrim('_ _keep_this -> _ _', '_ ')
    e3 is ltrim(' keep everything ', '')
    e4 is ltrim('null example', null)
    e5 is ltrim(null, ' _')
}

QUERY RESULTS

[
  {
    "e1": "keep this ->  ",
    "e2": "keep_this -> _ _",
    "e3": " keep everything ",
    "e4": null,
    "e5": null
  }
]

SELECT 
   LTRIM('  keep this ->  ') as "e1",
   LTRIM('_ _keep_this -> _ _','_ ') as "e2",
   LTRIM(' keep everything ','') as "e3",
   LTRIM('null example',NULL) as "e4",
   LTRIM(NULL,' _') as "e5"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5
ORDER BY 1 asc NULLS LAST

rtrim

rtrim(value)
rtrim(value, trim_characters)

Like trim(value, trim_characters) but only removes trailing characters.

run: empty -> {
  group_by: 
    e1 is rtrim('  <- keep this  ')
    e2 is rtrim('_ _ <- keep_this _ _', '_ ')
    e3 is rtrim(' keep everything ', '')
    e4 is rtrim('null example', null)
    e5 is rtrim(null, ' _')
}

QUERY RESULTS

[
  {
    "e1": "  <- keep this",
    "e2": "_ _ <- keep_this",
    "e3": " keep everything ",
    "e4": null,
    "e5": null
  }
]

SELECT 
   RTRIM('  <- keep this  ') as "e1",
   RTRIM('_ _ <- keep_this _ _','_ ') as "e2",
   RTRIM(' keep everything ','') as "e3",
   RTRIM('null example',NULL) as "e4",
   RTRIM(NULL,' _') as "e5"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5
ORDER BY 1 asc NULLS LAST

substr

substr(value, position)
substr(value, position, length)

Returns a substring of value starting at the 1-based index position. If length is specified, the returned string will be at most length characters long; otherwise the returned string will extend to the end of value. Negative values of position index the starting value from the end of the string (with -1 for the last character of the string). A position of 0 is equivalent to a position of 1.

run: empty -> {
  group_by: 
    e1 is substr('123456789', 1)
    e2 is substr('123456789', 0)
    e3 is substr('123456789', 5)
    e4 is substr('123456789', -4)
    e5 is substr('123456789', 1, 3)
    e6 is substr('123456789', -5, 3)
    e7 is substr('123456789', null, 1)
    e8 is substr('123456789', 1, null)
    e9 is substr(null, 1, 1)
}

QUERY RESULTS

[
  {
    "e1": "123456789",
    "e2": "123456789",
    "e3": "56789",
    "e4": "6789",
    "e5": "123",
    "e6": "567",
    "e7": null,
    "e8": null,
    "e9": null
  }
]

SELECT 
   SUBSTR('123456789',1) as "e1",
   SUBSTR('123456789',0) as "e2",
   SUBSTR('123456789',5) as "e3",
   SUBSTR('123456789',-4) as "e4",
   SUBSTR('123456789',1,3) as "e5",
   SUBSTR('123456789',-5,3) as "e6",
   SUBSTR('123456789',NULL,1) as "e7",
   SUBSTR('123456789',1,NULL) as "e8",
   SUBSTR(NULL,1,1) as "e9"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 1 asc NULLS LAST

regexp_extract

regexp_extract(value, pattern)

Returns the first substring of value which matches pattern. Returns null if there is no matching substring.

run: empty -> {
  group_by: 
    e1 is regexp_extract('the Malloy language', r'M....y')
    e2 is regexp_extract('the SQL language', r'M....y')
    e3 is regexp_extract('the null language', null)
    e4 is regexp_extract(null, r'nothing')
}

QUERY RESULTS

[
  {
    "e1": "Malloy",
    "e2": "",
    "e3": null,
    "e4": null
  }
]

SELECT 
   REGEXP_EXTRACT('the Malloy language','M....y') as "e1",
   REGEXP_EXTRACT('the SQL language','M....y') as "e2",
   REGEXP_EXTRACT('the null language',NULL) as "e3",
   REGEXP_EXTRACT(NULL,'nothing') as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

replace

replace(value, pattern, replacement)

Returns a copy of value with all occurrences of pattern replaced with replacement.

If pattern is empty, no replacement occurs and value is returned unchanged.

If pattern is a regular expression, parenthesized capturing groups can be included in the replacement string with '\\1' to '\\9'. In BigQuery and DuckDB, the full matched string can be referenced with '\\0'.

run: empty -> {
  group_by: 
    e1 is replace('the SQL language', 'SQL', 'Malloy')
    e2 is replace('the SQL language', r'S.L', 'Malloy')
    e3 is replace('SQL SQL SQL', 'SQL', 'Malloy')
    e4 is replace('SQL SQL SQL', r'S.L', 'Malloy')
    e5 is replace(null, 'SQL', 'Malloy')
    e6 is replace('the null language', null, 'Malloy')
    e7 is replace('the null language', 'SQL', null)
    e8 is replace('the language', '', 'Malloy')
    e9 is replace('axbxc', r'(a).(b).(c)', '\\0 - \\1 - \\2 - \\3')
}

QUERY RESULTS

[
  {
    "e1": "the Malloy language",
    "e2": "the Malloy language",
    "e3": "Malloy Malloy Malloy",
    "e4": "Malloy Malloy Malloy",
    "e5": null,
    "e6": null,
    "e7": null,
    "e8": "the language",
    "e9": "axbxc - a - b - c"
  }
]

SELECT 
   REPLACE('the SQL language','SQL','Malloy') as "e1",
   REGEXP_REPLACE('the SQL language', 'S.L', 'Malloy', 'g') as "e2",
   REPLACE('SQL SQL SQL','SQL','Malloy') as "e3",
   REGEXP_REPLACE('SQL SQL SQL', 'S.L', 'Malloy', 'g') as "e4",
   REPLACE(NULL,'SQL','Malloy') as "e5",
   REPLACE('the null language',NULL,'Malloy') as "e6",
   REPLACE('the null language','SQL',NULL) as "e7",
   REPLACE('the language','','Malloy') as "e8",
   REGEXP_REPLACE('axbxc', '(a).(b).(c)', '\0 - \1 - \2 - \3', 'g') as "e9"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 1 asc NULLS LAST

length

length(value)

Returns the number of characters in value.

run: empty -> {
  group_by: 
    e1 is length('Malloy')
    e2 is length('')
    e3 is length('𝔐𝔞𝔩𝔩𝔬𝔶')
    e4 is length(null)
}

QUERY RESULTS

[
  {
    "e1": 6,
    "e2": 0,
    "e3": 6,
    "e4": null
  }
]

SELECT 
   LENGTH('Malloy') as "e1",
   LENGTH('') as "e2",
   LENGTH('𝔐𝔞𝔩𝔩𝔬𝔶') as "e3",
   LENGTH(NULL) as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

byte_length

byte_length(value)

Returns the number of bytes in the unicode encoding of value.

run: empty -> {
  group_by: 
    e1 is byte_length('Malloy')
    e2 is byte_length('')
    e3 is byte_length('𝔐𝔞𝔩𝔩𝔬𝔶')
    e4 is byte_length(null)
}

QUERY RESULTS

[
  {
    "e1": 6,
    "e2": 0,
    "e3": 24,
    "e4": null
  }
]

SELECT 
   (BIT_LENGTH('Malloy') / 8) as "e1",
   (BIT_LENGTH('') / 8) as "e2",
   (BIT_LENGTH('𝔐𝔞𝔩𝔩𝔬𝔶') / 8) as "e3",
   (BIT_LENGTH(NULL) / 8) as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

chr

chr(value)

Returns a unicode code point value and returns a string containing the character with the matching code point. If value is 0, returns an empty string not a string containing the null character.

run: empty -> {
  group_by: 
    e1 is chr(65)
    e2 is chr(255)
    e3 is chr(0)
    e4 is chr(null)
}

QUERY RESULTS

[
  {
    "e1": "A",
    "e2": "ÿ",
    "e3": "",
    "e4": null
  }
]

SELECT 
   CASE WHEN 65 = 0 THEN '' ELSE CHR(65) END as "e1",
   CASE WHEN 255 = 0 THEN '' ELSE CHR(255) END as "e2",
   CASE WHEN 0 = 0 THEN '' ELSE CHR(0) END as "e3",
   CASE WHEN NULL = 0 THEN '' ELSE CHR(NULL) END as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

Returns an error if value is not a valid unicode code point.

ascii

ascii(value)

Returns the ASCII code point of the first character of value. If value is empty, returns 0.

In BigQuery, ascii errors if the first character of value is not representable in ASCII. In Postgres and DuckDB, ascii returns the Unicode code point of the first character.

run: empty -> {
  group_by: 
    e1 is ascii('Malloy')
    e2 is ascii('')
    e3 is ascii(null)
}

QUERY RESULTS

[
  {
    "e1": 77,
    "e2": 0,
    "e3": null
  }
]

SELECT 
   ASCII('Malloy') as "e1",
   ASCII('') as "e2",
   ASCII(NULL) as "e3"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3
ORDER BY 1 asc NULLS LAST

unicode

unicode(value)

Returns the Unicode code point of the first character of value. If value is empty, returns 0.

run: empty -> {
  group_by: 
    e1 is unicode('Malloy')
    e2 is unicode('𝔐𝔞𝔩𝔩𝔬𝔶')
    e3 is unicode('')
    e4 is unicode(null)
}

QUERY RESULTS

[
  {
    "e1": 77,
    "e2": 120080,
    "e3": 0,
    "e4": null
  }
]

SELECT 
   CASE WHEN 'Malloy' = '' THEN 0 ELSE UNICODE('Malloy') END as "e1",
   CASE WHEN '𝔐𝔞𝔩𝔩𝔬𝔶' = '' THEN 0 ELSE UNICODE('𝔐𝔞𝔩𝔩𝔬𝔶') END as "e2",
   CASE WHEN '' = '' THEN 0 ELSE UNICODE('') END as "e3",
   CASE WHEN NULL = '' THEN 0 ELSE UNICODE(NULL) END as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

repeat

repeat(value, num_repetitions)

Return a string consisting of value repeated num_repetitions times.

Undefined behavior if num_repetitions is negative or a non-integer.

run: empty -> {
  group_by: 
    e1 is repeat('(A)', 3)
    e2 is repeat('(A)', 1)
    e3 is repeat('(A)', 0)
    e4 is repeat('', 3)
    e5 is repeat(null, 1)
    e6 is repeat('(A)', null)
}

QUERY RESULTS

[
  {
    "e1": "(A)(A)(A)",
    "e2": "(A)",
    "e3": "",
    "e4": "",
    "e5": null,
    "e6": null
  }
]

SELECT 
   REPEAT('(A)',3) as "e1",
   REPEAT('(A)',1) as "e2",
   REPEAT('(A)',0) as "e3",
   REPEAT('',3) as "e4",
   REPEAT(NULL,1) as "e5",
   REPEAT('(A)',NULL) as "e6"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5,6
ORDER BY 1 asc NULLS LAST

reverse

reverse(value)

Return a copy of value with the characters in reverse order.

run: empty -> {
  group_by: 
    e1 is reverse('Malloy')
    e2 is reverse('𝔶𝔬𝔩𝔩𝔞𝔐')
    e3 is reverse('racecar')
    e4 is reverse('')
    e5 is reverse(null)
}

QUERY RESULTS

[
  {
    "e1": "yollaM",
    "e2": "𝔐𝔞𝔩𝔩𝔬𝔶",
    "e3": "racecar",
    "e4": "",
    "e5": null
  }
]

SELECT 
   REVERSE('Malloy') as "e1",
   REVERSE('𝔶𝔬𝔩𝔩𝔞𝔐') as "e2",
   REVERSE('racecar') as "e3",
   REVERSE('') as "e4",
   REVERSE(NULL) as "e5"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5
ORDER BY 1 asc NULLS LAST

Numeric Functions

abs	acos	asin	atan	atan2	ceil
cos	div	exp	floor	is_inf	is_nan
log	ln	pi	pow	rand	round
sign	sin	sqrt	tan	trunc

round

round(value)
round(value, precision)

Round value to the nearest integer, or if precision is specified, round to precision decimal places right of the decimal. If precision is negative, round to a precision that many places to the left of the decimal. Halfway cases are rounded away from zero.

run: empty -> {
  group_by: 
    e1 is round(1.49)
    e2 is round(1.51)
    e3 is round(1.5)
    e4 is round(-1.5)
    e5 is round(1.5, 0)
    e6 is round(1.551, 1)
    e7 is round(14.12, -1)
    e8 is round(1.4, null)
    e9 is round(null, 1)
}

QUERY RESULTS

[
  {
    "e1": 1,
    "e2": 2,
    "e3": 2,
    "e4": -2,
    "e5": 2,
    "e6": 1.6,
    "e7": 10,
    "e8": null,
    "e9": null
  }
]

SELECT 
   ROUND(1.49::DOUBLE) as "e1",
   ROUND(1.51::DOUBLE) as "e2",
   ROUND(1.5::DOUBLE) as "e3",
   ROUND(-1.5::DOUBLE) as "e4",
   ROUND(1.5::DOUBLE,0) as "e5",
   ROUND(1.551::DOUBLE,1) as "e6",
   ROUND(14.12::DOUBLE,-1) as "e7",
   ROUND(1.4::DOUBLE,NULL) as "e8",
   ROUND(NULL,1) as "e9"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 1 asc NULLS LAST

trunc

trunc(value)
trunc(value, precision)

Truncate value to an integer, or if precision is specified, truncate after that many decimal places. If precision is negative, truncate to that many decimal places to the left of the decimal point. Similar to round(value, precision) but always rounds toward zero.

run: empty -> {
  group_by: 
    e1 is trunc(1.49)
    e2 is trunc(1.51)
    e3 is trunc(1.5)
    e4 is trunc(-1.5)
    e5 is trunc(1.5, 0)
    e6 is trunc(1.551, 1)
    e7 is trunc(19.999, -1)
    e8 is trunc(1.4, null)
    e9 is trunc(null, 1)
}

QUERY RESULTS

[
  {
    "e1": 1,
    "e2": 1,
    "e3": 1,
    "e4": -1,
    "e5": 1,
    "e6": 1.5,
    "e7": 10,
    "e8": null,
    "e9": null
  }
]

SELECT 
   CASE WHEN 1.49::DOUBLE < 0 THEN CEIL(1.49::DOUBLE) ELSE FLOOR(1.49::DOUBLE) END as "e1",
   CASE WHEN 1.51::DOUBLE < 0 THEN CEIL(1.51::DOUBLE) ELSE FLOOR(1.51::DOUBLE) END as "e2",
   CASE WHEN 1.5::DOUBLE < 0 THEN CEIL(1.5::DOUBLE) ELSE FLOOR(1.5::DOUBLE) END as "e3",
   CASE WHEN -1.5::DOUBLE < 0 THEN CEIL(-1.5::DOUBLE) ELSE FLOOR(-1.5::DOUBLE) END as "e4",
   CASE WHEN 1.5::DOUBLE < 0 THEN CEIL(1.5::DOUBLE * POW(10, 0)) / POW(10, 0) ELSE FLOOR(1.5::DOUBLE * POW(10, 0)) / POW(10, 0) END as "e5",
   CASE WHEN 1.551::DOUBLE < 0 THEN CEIL(1.551::DOUBLE * POW(10, 1)) / POW(10, 1) ELSE FLOOR(1.551::DOUBLE * POW(10, 1)) / POW(10, 1) END as "e6",
   CASE WHEN 19.999::DOUBLE < 0 THEN CEIL(19.999::DOUBLE * POW(10, -1)) / POW(10, -1) ELSE FLOOR(19.999::DOUBLE * POW(10, -1)) / POW(10, -1) END as "e7",
   CASE WHEN 1.4::DOUBLE < 0 THEN CEIL(1.4::DOUBLE * POW(10, NULL)) / POW(10, NULL) ELSE FLOOR(1.4::DOUBLE * POW(10, NULL)) / POW(10, NULL) END as "e8",
   CASE WHEN NULL < 0 THEN CEIL(NULL * POW(10, 1)) / POW(10, 1) ELSE FLOOR(NULL * POW(10, 1)) / POW(10, 1) END as "e9"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 1 asc NULLS LAST

floor

floor(value)

Round value down to the greatest integer not larger than value.

run: empty -> {
  group_by: 
    a is floor(1.0)
    b is floor(1.9)
    c is floor(-1.1)
    d is floor(null)
}

QUERY RESULTS

[
  {
    "a": 1,
    "b": 1,
    "c": -2,
    "d": null
  }
]

SELECT 
   FLOOR(1.0::DOUBLE) as "a",
   FLOOR(1.9::DOUBLE) as "b",
   FLOOR(-1.1::DOUBLE) as "c",
   FLOOR(NULL) as "d"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

ceil

ceil(value)

Round value up to the smallest integer not less than value.

run: empty -> {
  group_by: 
    a is ceil(1.0)
    b is ceil(1.1)
    c is ceil(-1.9)
    d is ceil(null)
}

QUERY RESULTS