SQL functions¶
SQream DB supports functions from ANSI SQL, as well as others for compatibility.
Summary of functions¶
Scalar functions¶
See more about Scalar functions
Bitwise operations¶
Function | Description |
---|---|
& (bitwise AND) | Bitwise AND |
~ (bitwise NOT) | Bitwise NOT |
| (bitwise OR) | Bitwise OR |
<< (bitwise shift left) | Bitwise shift left |
>> (bitwise shift right) | Bitwise shift right |
XOR (bitwise XOR) | Bitwise XOR |
Conditionals¶
Function | Description |
---|---|
BETWEEN | Value is in [ or not within ] the range |
CASE | Test a conditional expression, and depending on the result, evaluate additional expressions. |
COALESCE | Evaluate first non-NULL expression |
IN | Value is in [ or not within ] a set of values |
ISNULL | Alias for COALESCE with two expressions |
IS_ASCII | Test a TEXT for ASCII-only characters |
IS NULL | Check for NULL [ or non-NULL ] values |
Conversion¶
Function | Description |
---|---|
FROM_UNIXTS, FROM_UNIXTSMS | Converts a UNIX Timestamp to DATE or DATETIME |
TO_HEX | Converts a number to a hexadecimal string representation |
TO_UNIXTS, TO_UNIXTSMS | Converts a DATE or DATETIME to a UNIX Timestamp |
Date and time¶
Function | Description |
---|---|
CURDATE | Special syntax, equivalent to CURRENT_DATE |
CURRENT_DATE | Returns the current date as DATE |
CURRENT_TIMESTAMP | Equivalent to GETDATE |
DATEPART | Extracts a date or time element from a date expression |
DATEADD | Adds an interval to a date expression |
DATEDIFF | Calculates the time difference between two date expressions |
EOMONTH | Calculates the last day of the month of a given date expression |
EXTRACT | ANSI syntax for extracting date or time element from a date expression |
GETDATE | Returns the current timestamp as DATETIME |
SYSDATE | Equivalent to GETDATE |
TRUNC | Truncates a date element down to a specified date or time element |
Numeric¶
See more about Arithmetic operators
Operator | Syntax | Description |
---|---|---|
+ (unary) |
+a |
Converts a string to a numeric value. Identical to a :: double |
+ |
a + b |
Adds two expressions together |
- (unary) |
-a |
Negates a numeric expression |
- |
a - b |
Subtracts b from a |
* |
a * b |
Multiplies a by b |
/ |
a / b |
Divides a by b |
% |
a % b |
Modulu of a by b . See also MOD, % |
Function | Description |
---|---|
ABS | Calculates the absolute value of an argument |
ACOS | Calculates the inverse cosine of an argument |
ASIN | Calculates the inverse sine of an argument |
ATAN | Calculates the inverse tangent of an argument |
ATN2 | Calculates the inverse tangent for a point (y, x) |
CEILING / CEIL | Calculates the next integer for an argument |
COS | Calculates the cosine of an argument |
COT | Calculates the cotangent of an argument |
CRC64 | Calculates a CRC-64 hash of an argument |
DEGREES | Converts a value from radian values to degrees |
EXP | Calcalates the natural exponent for an argument (ex) |
FLOOR | Calculates the largest integer smaller than the argument |
LOG | Calculates the natural log for an argument |
LOG10 | Calculates the 10-based log for an argument |
MOD, % | Calculates the modulu (remainder) of two arguments |
PI | Returns the constant value for π |
POWER | Calculates x to the power of y (xy) |
RADIANS | Converts a value from degree values to radians |
ROUND | Rounds an argument down to the nearest integer, or an arbitrary precision |
SIN | Calculates the sine of an argument |
SQRT | Calculates the square root of an argument (√x) |
SQUARE | Raises an argument to the power of 2 (xy) |
TAN | Calculates the tangent of an argument |
TRUNC | Rounds a number to its integer representation towards 0 |
Strings¶
Function | Description |
---|---|
CHAR_LENGTH | Calculates number of characters in an argument |
CHARINDEX | Calculates the position where a string starts inside another string |
|| (Concatenate) | Concatenates two strings |
ISPREFIXOF | Matches if a string is the prefix of another string |
LEFT | Returns the first number of characters from an argument |
LEN | Calculates the length of a string in characters |
LIKE | Tests if a string argument matches a pattern |
LOWER | Converts an argument to a lower-case equivalent |
LTRIM | Trims whitespaces from the left side of an argument |
OCTET_LENGTH | Calculates the length of a string in bytes |
PATINDEX | Calculates the position where a pattern matches a string |
REGEXP_COUNT | Calculates the number of matches of a regular expression match in an argument |
REGEXP_INSTR | Returns the start position of a regular expression match in an argument |
REGEXP_SUBSTR | Returns a substring of an argument that matches a regular expression |
REPEAT | Repeats a string as many times as specified |
REPLACE | Replaces characters in a string |
REVERSE | Reverses a string argument |
RIGHT | Returns the last number of characters from an argument |
RLIKE | Tests if a string argument matches a regular expression pattern |
RTRIM | Trims whitespace from the right side of an argument |
SUBSTRING | Returns a substring of an argument |
TRIM | Trims whitespaces from an argument |
UPPER | Converts an argument to an upper-case equivalent |
Aggregate functions¶
See more about Aggregate functions
Function | Aliases | Description |
---|---|---|
AVG | Calculates the average of all of the values | |
CORR | Calculates the Pearson correlation coefficient | |
COUNT | Calculates the count of all of the values or only distinct values | |
COVAR_POP | Calculates population covariance of values | |
COVAR_SAMP | Calculates sample covariance of values | |
MAX | Returns maximum value of all values | |
MIN | Returns minimum value of all values | |
SUM | Calculates the sum of all of the values or only distinct values | |
STDDEV_SAMP | stdev , stddev |
Calculates sample standard deviation of values |
STDDEV_POP | stdevp |
Calculates population standard deviation of values |
VAR_SAMP | var , variance |
Calculates sample variance of values |
VAR_POP | varp |
Calculates population variance of values |
Window functions¶
See more about Window functions
Function | Description |
---|---|
LAG | Calculates the value evaluated at the row that is before the current row within the partition |
LEAD | Calculates the value evaluated at the row that is after the current row within the partition |
MAX | Calculates the maximum value |
MIN | Calculates the minimum value |
RANK | Calculates the rank of a row |
ROW_NUMBER | Calculates the row number |
SUM | Calculates the sum of all of the values |
System functions¶
System functions allow you to execute actions in the system, such as aborting a query or get information about system processes.
Function | Description |
---|---|
EXPLAIN | Returns a static query plan for a statement |
SHOW_CONNECTIONS | Returns a list of jobs and statements on the current worker |
SHOW_LOCKS | Returns any existing locks in the database |
SHOW_NODE_INFO | Returns a query plan for an actively running statement with timing information |
SHOW_SERVER_STATUS | Shows running statements across the cluster |
SHOW_VERSION | Returns the version of SQream DB |
STOP_STATEMENT | Stops a query (or statement) if it is currently running |
Workload management functions¶
Function | Description |
---|---|
SUBSCRIBE_SERVICE | Add a SQream DB worker to a service queue |
UNSUBSCRIBE_SERVICE | Remove a SQream DB worker to a service queue |
SHOW_SUBSCRIBED_INSTANCES | Return a list of service queues and workers |