SQL functions and operators

Comparison Functions and Operators

Less than

datatype < datatype

Returns true if the first datatype is less than the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses < in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.number < 1000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses < in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.number < 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

Greater than

datatype > datatype

Returns true if the first datatype is greater than the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses > in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.number > 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses > in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.number > 1000;
 COUNT(UInt8(1))
-----------------
               0
(1 row)

Less than or equal to

datatype <= datatype

Returns true if the first datatype is less than or equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses <= in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.number <= 1000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses <= in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.number <= 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

Greater than or equal to

datatype >= datatype

Returns true if the first datatype is greater than or equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses >= in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.number >= 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses >= in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.number >= 1000;
 COUNT(UInt8(1))
-----------------
               0
(1 row)

Equal

datatype = datatype

Returns true if the first datatype is equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses = in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.status = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses = in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.status = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

Not equal

datatype != datatype

Returns true if the first datatype is not equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses != in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.status != 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses <> in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.status <> 'completed';
 COUNT(UInt8(1))
-----------------
            6654
(1 row)

BETWEEN

datatype BETWEEN datatype AND datatype

Returns true if the first datatype is between the second and third.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses BETWEEN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE orders.number BETWEEN(orders.status) 1 AND 100000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses BETWEEN in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE o.number BETWEEN 1 AND 100000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

Mathematical Functions and Operators

TRUNC

TRUNC(numeric)

Truncates a number to an integer (towards zero).

The example below uses TRUNC in a projection within a Cube query rewrite:

SELECT TRUNC(orders.number) FROM orders LIMIT 1;
 trunc(orders.number)
----------------------
               504690
(1 row)

The example below uses TRUNC in post-processing with an outer query:

SELECT TRUNC(orders.number)
FROM (SELECT * FROM orders) o
LIMIT 1;
 trunc(o.number)
----------------------
               504690
(1 row)

String Functions and Operators

Concatenation

string || string

Concatenates the first string with the second.

The example below uses || in a selection within a Cube query rewrite:

SELECT COUNT(*)
FROM orders
WHERE status = 'comple' || 'ted';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses || in post-processing with an outer query:

SELECT (status || status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 o.status || o.status
--------------------------------
 processingprocessing
(1 rows)

BIT_LENGTH

BIT_LENGTH(string)

Returns number of bits in the string (8 times the OCTET_LENGTH).

The example below uses BIT_LENGTH in post-processing with an outer query:

SELECT BIT_LENGTH(status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 bitlength(orders.status)
--------------------------
                       80
(1 row)

CHAR_LENGTH

CHAR_LENGTH(string)
CHARACTER_LENGTH(string)

Returns the number of characters in the string.

The example below uses CHAR_LENGTH in post-processing with an outer query:

SELECT CHAR_LENGTH(status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 characterlength(orders.status)
--------------------------------
                             10
(1 row)

LOWER

LOWER(string)

Converts string to all lower case.

The example below uses LOWER in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE LOWER(orders.status) = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses LOWER in post-processing with an outer query:

SELECT LOWER(orders.status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 lower(o.status)
----------------------
 processing
(1 row)

OCTET_LENGTH

OCTET_LENGTH(string)

Returns number of bytes in the string.

The example below uses OCTET_LENGTH in post-processing with an outer query:

SELECT OCTET_LENGTH(status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 octetlength(orders.status)
----------------------------
                         10
(1 row)

SUBSTRING

SUBSTRING (string [ FROM start integer ] [ FOR count integer ])

Extracts the substring of string starting at the start character (if specified), and stopping after count characters (if specified). Either start or count must be specified.

The example below uses SUBSTRING in a projection within a Cube query rewrite:

SELECT SUBSTRING(orders.status, 1, 3) FROM orders LIMIT 1;
 substr(orders.status,Int64(1),Int64(3))
-----------------------------------------
 com
(1 row)

The example below uses SUBSTRING in post-processing with an outer query:

SELECT SUBSTRING(orders.status, 1, 3)
FROM (SELECT * FROM orders) o
LIMIT 1;
 substr(o.status,Int64(1),Int64(3))
-----------------------------------------
 com
(1 row)

TRIM

TRIM ([ LEADING | TRAILING | BOTH ] [ characters text ] FROM string)

Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.

The example below uses TRIM in post-processing with an outer query:

SELECT TRIM(LEADING 'p' FROM orders.status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 ltrim(o.status,Utf8("p"))
--------------------------------
 rocessing
(1 row)
 
cube=>

UPPER

UPPER(string)

Converts string to all upper case.

The example below uses UPPER in post-processing with an outer query:

SELECT UPPER(orders.status)
FROM (SELECT * FROM orders) o
LIMIT 1;
 upper(o.status)
----------------------
 PROCESSING
(1 row)

STARTS_WITH

starts_with (string, prefix)

Returns true if string starts with prefix.

The example below uses STARTS_WITH in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE STARTS_WITH(orders.status, 'complete');
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses STARTS_WITH in post-processing with an outer query:

SELECT STARTS_WITH(orders.status, 'complete')
FROM (SELECT * FROM orders) o
LIMIT 1;
 startswith(o.status,Utf8("complete"))
--------------------------------------------
 f
(1 row)

Pattern Matching

LIKE

string LIKE pattern

Returns true if string matches the supplied pattern.

The example below uses LIKE in a selection within a Cube query rewrite:

SELECT COUNT(*)
FROM orders
WHERE orders.status LIKE 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

REGEXP_SUBSTR

REGEXP_SUBSTR(string, pattern)

Returns the substring of string that matches a POSIX regular expression pattern, or null if there is no match.

The example below uses REGEXP_SUBSTR in post-processing with an outer query:

SELECT REGEXP_SUBSTR(orders.status, '\w+')
FROM (SELECT * FROM orders) o
LIMIT 1;

Aggregation Functions

COUNT

COUNT(*)
COUNT(col)

Counts the number of rows. If * is specified, the count returned is the total number of rows.

1. COUNT can only be used to query measures of type count inside Cube query rewrites.

2. If * is specified, Cube will query the first measure of type count found in the cube

The example below uses COUNT in a projection within a Cube query rewrite:

SELECT COUNT(*) FROM orders;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses COUNT with a measure of type count in a projection within a Cube query rewrite:

SELECT COUNT(orders.count) FROM orders;
 COUNT(orders.count)
---------------------
               10000
(1 row)

MEASURE

MEASURE(measure)

MEASURE is only supported inside Cube query rewrites. It is a special function that can be only be used with cube measures.

The example below uses MEASURE in a projection within a Cube query rewrite:

SELECT MEASURE(orders.count) FROM orders;
 measure(orders.count)
-----------------------
                 10000
(1 row)

SUM

SUM(col)

Computes the sum of the non-null input values.

1. SUM can only be used to query measures of type sum inside Cube query rewrites.

The example below uses SUM with a measure of type sum in a projection within a Cube query rewrite:

SELECT SUM(number) FROM orders;
 SUM(orders.number)
--------------------
             504690
(1 row)

AVG

AVG(col)

Computes the average (arithmetic mean) of all the non-null input values.

1. AVG can only be used to query measures of type avg inside Cube query rewrites.

The example below uses AVG with a measure of type avg in a projection within a Cube query rewrite:

SELECT AVG(avgNumber) FROM orders;
 AVG(orders.avgNumber)
-----------------------
                50.469
(1 row)

MIN

MIN(col)

Computes the minimum of the non-null input values. Available for any numeric, string or date/time value.

1. MIN can only be used to query measures of type min inside Cube query rewrites.

The example below uses MIN with a measure of type min in a projection within a Cube query rewrite:

SELECT MIN(min_number) FROM orders;
 MIN(orders.min_number)
-----------------------
                     1
(1 row)

MAX

MAX(col)

Computes the maximum of the non-null input values. Available for any numeric, string or date/time value.

1. MAX can only be used to query measures of type max inside Cube query rewrites.

The example below uses MAX with a measure of type max in a projection within a Cube query rewrite:

SELECT MAX(max_number) FROM orders;
 MAX(orders.max_number)
-----------------------
                   100
(1 row)

Date/Time Functions

TO_TIMESTAMP

TO_TIMESTAMP(string, string)

Converts a string to a timestamp according to the given format.

Valid format patterns can be found here (opens in a new tab).

The example below uses TO_TIMESTAMP in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE TO_TIMESTAMP('2020-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') < orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7945
(1 row)

LOCALTIMESTAMP

LOCALTIMESTAMP()

Returns the current date and time.

The example below uses LOCALTIMESTAMP in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE LOCALTIMESTAMP() > orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7693
(1 row)

NOW

NOW()

Returns the current date and time with time zone.

The example below uses NOW in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM orders) o
WHERE NOW() > orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7693
(1 row)

EXTRACT

EXTRACT(field FROM source)

The EXTRACT function retrieves subfields such as year or hour from date/time values.

source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well). field is an identifier or string that selects what field to extract from source. The EXTRACT function returns values of type numeric.

The example below uses EXTRACT in a projection within a Cube query rewrite:

SELECT EXTRACT(YEAR FROM orders.createdAt)
FROM orders
LIMIT 1;
 datepart(Utf8("year"),orders.createdAt)
-----------------------------------------
                                    2019
(1 row)

DATE_TRUNC

DATE_TRUNC(field, source)

The DATE_TRUNC function is conceptually similar to the trunc function for numbers.

source is a value expression of type timestamp, timestamp with time zone, or interval. field selects to which precision to truncate the input value. The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).

The example below uses DATE_TRUNC in a projection within a Cube query rewrite:

SELECT DATE_TRUNC('month', orders.createdAt)
FROM orders
LIMIT 1;
 datetrunc(Utf8("month"),orders.createdAt)
-------------------------------------------
 2019-01-01 00:00:00.000000
(1 row)

Conditional Expressions

CASE

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

The CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

The example below uses CASE in post-processing with an outer query:

SELECT
  CASE
    WHEN char_length(status) > 5 THEN 'big'
    ELSE 'small'
  END status,
  SUM(number) as total_value
from (
  SELECT status, number FROM orders
) orders
GROUP BY 1;
 status | total_value
--------+------------
 big    |     504690
(1 row)

COALESCE

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null.

Null is returned only if all arguments are null. It is used to substitute a default value for null values when data is retrieved for display.

The example below uses COALESCE in post-processing with an outer query:

SELECT
  COUNT(*),
  COALESCE(status, 'unknown') AS state
FROM (SELECT * FROM orders) o
GROUP BY 2;
 COUNT(UInt8(1)) |   state
-----------------+------------
            3354 | processing
            3346 | completed
            3300 | shipped
(3 rows)

NULLIF

NULLIF(value1, value2)

The NULLIF function returns a null value if value1 equals value2, otherwise it returns value1.

The example below uses NULLIF in post-processing with an outer query:

SELECT NULLIF(orders.status, 'completed'), COUNT(*)
FROM (SELECT * FROM orders) o
GROUP BY 1;
 nullif(orders.status,Utf8("completed")) | COUNT(UInt8(1))
-----------------------------------------+-----------------
 processing                              |            3354
                                         |            3346
 shipped                                 |            3300
(3 rows)

Row and Array comparisons

IN

expression IN (value [, ...])

The IN operator allows specifying multiple values for comparison in a WHERE clause, and returns true if the provided expression matches any of the values.

The example below uses IN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE status IN ('shipped', 'completed');
 COUNT(UInt8(1))
-----------------
            6646
(1 row)

NOT IN

expression NOT IN (value [, ...])

The NOT IN operator is the reverse of the IN operator; it returns true if the provided expression matches none of the values.

The example below uses IN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM orders WHERE status NOT IN ('shipped', 'completed');
 COUNT(UInt8(1))
-----------------
            3354
(1 row)