SQL Functions¶
SQream DB supports functions from ANSI SQL, as well as others for compatibility.
Summary of functions¶
Built-In Scalar Functions¶
See more about Built-In 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 |
User-Defined Scalar Functions¶
For more information about user-defined scalar functions, see Scalar SQL UDF
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 |
| SUM | Calculates the sum of all of the values |
| RANK | Calculates the rank of a row |
| FIRST_VALUE | Returns the value in the first row of a window |
| LAST_VALUE | Returns the value in the last row of a window |
| NTH_VALUE | Returns the value in a specified (n) row of a window |
| DENSE_RANK | Returns the rank of the current row with no gaps |
| PERCENT_RANK | Returns the relative rank of the current row |
| CUME_DIST | Returns the cumulative distribution of rows |
| NTILE | Returns an integer ranging between 1 and the argument value, dividing the partitions as equally as possible |
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 |