Functions

Numeric Functions

• ABSOLUTE(x): Return the absolute value of x
• SQRT(x): Return the square root of x
• BITWISE_AND(x, y): Return a bitwise conjunction of x and y. Equivalent to x & y
• BITWISE_OR(x, y): Return a bitwise disjunction of x and y. Equivalent to x | y

String Functions

• CONCAT(x, y): Return the string obtained by concatenating strings x and y.

Utility Functions

• SEQ_MIN(x, y, z, ...): Return the minimal element among arguments x, y, z, ...
• SEQ_MAX(x, y, z, ...): Return the maximal element among arguments x, y, z, ...

Type Constructors

• CAST(expr AS type): Cast the specified expression as the specified type
• DATE(x): Interpret 'x' as a date. Equivalent to CAST(x AS date)

GIS Functions

• DST(lon1, lat1, lon2, lat2): Compute the orthodromic distance between two long/lat coordinates.
• SPEED(delta, start, end): Given a distance delta, start time, and end time, compute the rate of change in units of delta per hour.

JSON Functions

JSON Functions use a path notation based on SQLite's JSON1 extension. All path strings begin with $. Array subscripts are of the form [x], while object subscripts are of the form .x. For example $[1].foo[3] would treat the referenced object as an array, find the element at index 1, and return index 3 from its foo attribute.

• JSON_EXTRACT(json, path): Extract the element at the specified path from the specified JSON object.
• JSON_ARRAY(x, y, z, ...): Create a JSON array from elements x, y, z, .... This function may have any number of arguments.
• JSON_OBJECT(label1, x, label2, y, label3, z, ...): Create a JSON object: { label1: x, label2: y, label3: z, ...}. This function must have an even number of arguments.
• JSON_ARRAY_LENGTH(x): Find the length of the array in the JSON object x.

Randomness Functions

• RANDOM(): Return a random integer.
• POISSON(mean): Return a poisson-distributed random number with the specified mean.
• GAMMA(k, theta): Return a gamma-distributed random number with the specified k and theta parameters.

Aggregate Functions

Aggregate functions are used specifically in aggregate queries. All aggregate functions may optionally include the modifier DISTINCT preceding the first argument, in which case they will only be evaluated on distinct values of the argument. For example: SELECT SUM(DISTINCT A) FROM R This query will only count each distinct value of A once.

• SUM(x): Total values of x for each row in the group.
• STDDEV(x): Return the standard deviation computed over the values of x for each row in the group.
• MAX(x): Return the maximal value of x for each row in the group.
• MIN(x): Return the minimal value of x for each row in the group.
• COUNT(*): Return the number of rows in the group. If paired with DISTINCT (i.e., COUNT(DISTINCT x)), instead count the number of distinct values of its one argument.
• GROUP_AND(x): Return true if and only if x is true for all rows in the group.
• GROUP_OR(x): Return true if and only if x is true for any row in the group.
• GROUP_BITWISE_AND(x): Compute a bitwise and of x for each row in the group.
• GROUP_BITWISE_OR(x): Compute a bitwise or of x for each row in the group.
• JSON_GROUP_ARRAY(x): Construct a JSON array containing the value of x for each row of the group.
• FIRST(x): Return the value of x on the first row of the group (equivalent to SELECT x FROM ... LIMIT 1). If the input is unsorted, return the value of x from an arbitrary row in the group.