*** ## description: Functions and Operators (DML) # Functions and Operators ## Basic Mathematical Operators | Operator | Description | | ----------------------------- | ----------------------------------------------- | | `+`*`numeric`* | Returns *`numeric`* | | `–`*`numeric`* | Returns negative value of *`numeric`* | | *`numeric1`* `+` *`numeric2`* | Sum of *`numeric1`* and *`numeric2`* | | *`numeric1`* `–` *`numeric2`* | Difference of *`numeric1`* and *`numeric2`* | | *`numeric1`* `*` *`numeric2`* | Product of *`numeric1`* and *`numeric2`* | | *`numeric1`* `/` *`numeric2`* | Quotient (*`numeric1`* divided by *`numeric2`*) | ### **Mathematical Operator Precedence** 1. Parenthesization 2. Multiplication and division 3. Addition and subtraction ## Comparison Operators | Operator | Description | | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `=` | Equals | | `<>` | Not equals | | `>` | Greater than | | `>=` | Greater than or equal to | | `<` | Less than | | `<=` | Less than or equal to | | `BETWEEN` **`x`** `AND` **`y`** | Is a value within a range | | `NOT BETWEEN` **`x`** `AND` **`y`** | Is a value not within a range | | `IS NULL` | Is a value that is null | | `IS NOT NULL` | Is a value that is not null | | `NULLIF(`**`x`**`,` **`y`**`)` | Compare expressions **x** and **y**. If different, return **x**. If they are the same, return `null`. For example, if a dataset uses ‘NA’ for `null` values, you can use this statement to return `null` using `SELECT NULLIF(field_name,'NA')`. | | `IS TRUE` | True if a value resolves to TRUE. | | `IS NOT TRUE` | True if a value resolves to FALSE. | ## Mathematical Functions | Function | Description | | ------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ABS(`**`x`**`)` | Returns the absolute value of **x** | | `CEIL(`**`x`**`)` | Returns the smallest integer not less than the argument | | `DEGREES(`**`x`**`)` | Converts radians to degrees | | `EXP(`**`x`**`)` | Returns the value of e to the power of **x** | | `FLOOR(`**`x`**`)` | Returns the largest integer not greater than the argument | | `LN(`**`x`**`)` | Returns the natural logarithm of **x** | | `LOG(`**`x`**`)` | Returns the natural logarithm of **x** | | `LOG10(`**`x`**`)` | Returns the base-10 logarithm of the specified float expression **x** | | `MOD(`**`x,y`**`)` | Returns the remainder of int **x** divided by int **y** | | `PI()` | Returns the value of pi | | `POWER(`**`x,y`**`)` | Returns the value of **x** raised to the power of **y** | | `RADIANS(`**`x`**`)` | Converts degrees to radians | | `ROUND(`**`x`**) | Rounds **x** to the nearest integer value, but does not change the data type. For example, the double value 4.1 rounds to the double value 4. | | `ROUND_TO_DIGIT (`**`x,y`**`)` | Rounds **x** to **y** decimal places | | `SIGN(`**`x`**`)` | Returns the sign of **x** as -1, 0, 1 if **x** is negative, zero, or positive | | `SQRT(`**`x`**`)` | Returns the square root of **x**. | | `TRUNCATE(`**`x,y`**`)` | Truncates **x** to **y** decimal places | | `WIDTH_BUCKET(`**`target,lower-boundary,upper-boundary,bucket-count`**`)` |

Define equal-width intervals (buckets) in a range between the lower boundary and the upper boundary, and returns the bucket number to which the target expression is assigned.

Expressions can be constants, column variables, or general expressions.

Example
Create 10 age buckets of equal size, with lower bound 0 and upper bound 100 (\[0,10], \[10,20]... \[90,100]), and classify the

age of a customer accordingly:

SELECT WIDTH\_BUCKET(age, 0, 100, 10) FROM customer;

For example, a customer of age 34 is assigned to bucket 3 (\[30,40]) and the function returns the value 3.

| ## Trigonometric Functions | Function | Description | | ---------------------------- | ------------------------------------------------------------------------------------------------ | | `ACOS(`**`x`**`)` | Returns the arc cosine of **x** | | `ASIN(`**`x`**`)` | Returns the arc sine of **x** | | `ATAN(`**`x`**`)` | Returns the arc tangent of **x** | | `ATAN2(`**`y`**`,`**`x`**`)` | Returns the arc tangent of **(x, y)** in the range (-*π*,*π*]. Equal to `ATAN(y/x)` for `x > 0`. | | `COS(`**`x`**`)` | Returns the cosine of **x** | | `COT(`**`x`**`)` | Returns the cotangent of **x** | | `SIN(`**`x`**`)` | Returns the sine of **x** | | `TAN(`**`x`**`)` | Returns the tangent of **x** | ## Geometric Functions | Function | Description | | ------------------------------------------------------------------------------------ | ---------------------------------------------------------------- | | `DISTANCE_IN_METERS(`**`fromLon`**`,` **`fromLat`**`,` **`toLon`**`,` **`toLat`**`)` | Calculates distance in meters between two WGS84 positions. | | `CONV_4326_900913_X(`**`x`**`)` | Converts WGS84 latitude to WGS84 Web Mercator **x** coordinate. | | `CONV_4326_900913_Y(`**`y`**`)` | Converts WGS84 longitude to WGS84 Web Mercator **y** coordinate. | ## String Functions | Function | Description | | ----------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `BASE64_DECODE(`**`str`**`)` | Decodes a BASE64-encoded string. | | `BASE64_ENCODE(`**`str`**`)` | Encodes a string to a BASE64-encoded string. | | `CHAR_LENGTH(`**`str`**`)` | Returns the number of characters in a string. Only works with unencoded fields (ENCODING set to `none`). | | **`str1`** \|\| **`str2`** \[ \|\| **`str3`**... ] |

Returns the string that results from concatenating the strings specified. Note that numeric, date, timestamp, and time types will be implicitly casted to strings as necessary, so explicit casts of non-string types to string types is not required for inputs to the concatenation operator.

Note that concatenating a variable string with a string literal, i.e. county\_name

| | `ENCODE_TEXT(`**`none_encoded_str`**`)` | Converts a none-encoded string to a transient dictionary-encoded string to allow for operations like group-by on top. When the watchdog is enabled, the number of strings that can be casted using this operator is capped by the value set with the `watchdog-none-encoded-string-translation-limit` flag (1,000,000 by default). | | `HASH(`**`str`**`)` |

Deterministically Hashes a string input to a BIGINT output using a pseudo-random function. Can be useful for bucketing string values or deterministcally coloring by string values for a high-cardinality TEXT column.

Note that currently HASH only accepts TEXT inputs, but in the future may also accept other data types. It should also be noted that NULL values always hash to NULL outputs.

| | `INITCAP(`**`str`**`)` | Returns the string with initial caps after any of the defined delimiter characters, with the remainder of the characters lowercased. Valid delimiter characters are **`!`**, **`?`**, **`@`**, **`"`**, **`^`**, **`#`**, **`$`**, **`&`**, **`~`**, **`_`**, **`,`**, **`.`**, **`:`**, **`;`**, **`+`**, **`-`**, **`*`**, **`%`, `/`**, **`\|`**, **`\`**, **`[`**, **`]`**, **`(`**, **`)`**, **`{`**, **`}`**, **`<`**, **`>`**. | | JAROWINKLER\_SIMILARITY( **str1**, **str2** ) | Computes the Jaro-Winkler similarity score between two input strings. The output will be an integer between 0 and 100, with 0 representing completely dissimilar strings, and 100 representing exactly matching strings. | | `JSON_VALUE(`**`json_str, path`**`)` |

Returns the string of a field given by path instr. Paths start with the $ character, with sub-fields split by . and array members indexed by [], with array indices starting at 0. For example, JSON_VALUE('{"name": "Brenda", "scores": [89, 98, 94]}', '$.scores\[1]') would yield a TEXT return field of '98'.

Note that currentlyLAX parsing mode (any unmatched path returns null rather than errors) is the default, and STRICT parsing mode is not supported.

| | | | | `KEY_FOR_STRING(`**`str`**`)` | Returns the dictionary key of a dictionary-encoded string column. | | `LCASE(`**`str`**`)` | Returns the string in all lower case. Only ASCII character set is currently supported. Same as `LOWER`. | | `LEFT(`**`str, num`**`)` | Returns the left-most number (`num`) of characters in the string (`str`). | | `LENGTH(`**`str`**`)` | Returns the length of a string in bytes. Only works with unencoded fields (ENCODING set to `none`). | | LEVENSHTEIN\_DISTANCE( **str1**, **str2** ) | Computes the edit distance, or number of single-character insertions, deletions, or substitutions, that must be made to make the first string equal the second. It returns an integer greater than or equal to 0, with 0 meaning the strings are equal. The higher the return value, the more the two strings can be thought of as dissimilar. | | `LOWER(`**`str`**`)` | Returns the string in all lower case. Only ASCII character set is currently supported. Same as `LCASE`. | | `LPAD(`**`str`**` ,`` `` `**`len`**`, [`**`lpad_str`**` `` ``]) ` |

Left-pads the string with the string defined in lpad\_str to a total length of len. If the optional lpad\_str is not specified, the space character is used to pad.

If the length of str is greater than len, then characters from the end of str are truncated to the length of len.

Characters are added from lpad\_str successively until the target length len is met. If lpad\_str concatenated with str is not long enough to equal the target len, lpad\_str is repeated, partially if necessary, until the target length is met.

| | `LTRIM(`**`str`**` ,`` `` `**`chars`**`)` | Removes any leading characters specified in `chars` from the string. Alias for `TRIM`. | | `OVERLAY(`**`str`**` PLACING`` `` `**`replacement_str`**` FROM`` `` `**`start`**` `` ``[FOR `**`len`**`])` |

Replaces in str the number of characters defined in len with characters defined in replacement\_str at the location start.

Regardless of the length of replacement\_str, len characters are removed from str unless start + replacement\_str is greater than the length of str, in which case all characters from start to the end of str are replaced.

Ifstart is negative, it specifies the number of characters from the end of str.

| | ` POSITION (`` `` `**`search_str`**` ``IN`` `**`str`**` ``[FROM`` `**`start_position`**`])` |

Returns the position of the first character in search\_str if found in str, optionally starting the search at start\_position.

If search\_str is not found, 0 is returned. If search\_str or str are null, null is returned.

| | `REGEXP_COUNT(`**`str`**` ,`` `` `**`pattern`**` ``[,`` `**`position`**`, [`**`flags`**`]])` |

Returns the number of times that the provided pattern occurs in the search string str.

position specifies the starting position in str for which the search for pattern will start (all matches before position will be ignored. If position is negative, the search will start that many characters from the end of the string str.

Use the following optional flags to control the matching behavior:

c - Case-sensitive matching.
i - Case-insensitive matching.

| | `REGEXP_REPLACE(`**`str`**` ,`` `` `**`pattern`**` ``[,`` `**`new_str`**` ,`` `` `**`position`**` ,`` `` `**`occurrence`**`, [`**`flags`**`]])` |

Replace one or all matches of a substring in string str that matches pattern , which is a regular expression in POSIX regex syntax.

new\_str (optional) is the string that replaces the string matching the pattern. If new\_str is empty or not supplied, all found matches are removed.

The occurrence integer argument (optional) specifies the single match occurrence of the pattern to replace, starting from the beginning of str; 0 (replace all) is the default. Use a negative occurrence argument to signify the nth-to-last occurrence to be replaced.

pattern uses POSIX regular expression syntax.

Use a positive position argument to indicate the number of characters from the beginning of str. Use a negative position argument to indicate the number of characters from the end of str.

Back-references/capture groups can be used to capture and replace specific sub-expressions.

Use the following optional flags to control the matching behavior:

c - Case-sensitive matching.
i - Case-insensitive matching.


If not specified, REGEXP\_REPLACE defaults to case sensitive search.

| | `REGEXP_SUBSTR(`**`str`**` ,`` `` `**`pattern`**` `` ``[,`` `**`position`**` ,`` `` `**`occurrence`**` ,`` `` `**`flags`, `group_num`**`])` |

Search string str for pattern, which is a regular expression in POSIX syntax, and return the matching substring.

Use position to set the character position to begin searching. Use occurrence to specify the occurrence of the pattern to match.

Use a positive position argument to indicate the number of characters from the beginning of str. Use a negative position argument to indicate the number of characters from the end of str.

The occurrence integer argument (optional) specifies the single match occurrence of the pattern to replace, with 0 being mapped to the first (1) occurrence. Use a negative occurrence argument to signify the nth-to-last group in pattern is returned.

Use optional flags to control the matching behavior:

c - Case-sensitive matching.

e - Extract submatches.
i - Case-insensitive matching.

The c and i flags cannot be used together; e can be used with either. If neither c nor i are specified, or if pattern is not provided, REGEXP\_SUBSTR defaults to case-sensitive search.

If the e flag is used, REGEXP\_SUBSTR returns the capture group group\_num of pattern matched in str. If the e flag is used, but no capture groups are provided in pattern, REGEXP\_SUBSTR returns the entire matching pattern, regardless of group\_num. If the e flag is used but no group\_num is provided, a value of 1 for group\_num is assumed, so the first capture group is returned.

| | `REPEAT(`**`str`**` ,`` `` `**`num`**`)` | Repeats the string the number of times defined in `num`. | | `REPLACE(`**`str`**` ,`` `` `**`from_str`**` ,`` `` `**`new_str`**`)` | Replaces all occurrences of substring `from_str` within a string, with a new substring `new_str`. | | `REVERSE(`**`str`**`)` | Reverses the string. | | `RIGHT(`**`str, num`**`)` | Returns the right-most number (`num`) of characters in the string (`str`). | | `RPAD(`**`str`**` ,`` `` `**`len`**` ,`` `` `**`rpad_str`**`)` |

Right-pads the string with the string defined in rpad\_str to a total length of len. If the optional rpad\_str is not specified, the space character is used to pad.

If the length of str is greater than len, then characters from the beginning of str are truncated to the length of len.

Characters are added from rpad\_str successively until the target length len is met. If rpad\_str concatenated with str is not long enough to equal the target len, rpad\_str is repeated, partially if necessary, until the target length is met.

| | `RTRIM(`**`str`**`)` | Removes any trailing spaces from the string. | | `SPLIT_PART(`**`str`**` ,`` `` `**`delim`**` ,`` `` `**`field_num`**`)` | Split the string based on a delimiter `delim` and return the field identified by `field_num`. Fields are numbered from left to right. | | `STRTOK_TO_ARRAY(`**`str`**`, [`**`delim`**`])` |

Tokenizes the string str using optional delimiter(s) delim and returns an array of tokens.

An empty array is returned if no tokens are produced in tokenization. NULL is returned if either parameter is a NULL.

| | `SUBSTR(`**`str`**` ,`` `` `**`start`**`, [`**`len`**`])` | Alias for `SUBSTRING`. | | `SUBSTRING(`**`str FROM`** **`start [ FOR`** **`len`**`])` |

Returns a substring of str starting at index start for len characters.

The start position is 1-based (that is, the first character of str is at index 1, not 0). However, start 0 aliases to start 1.

If start is negative, it is considered to be

| | `TRIM([BOTH \| LEADING \| TRAILING] [`**`trim_str`**` ``FROM`` `**`str`**`])` |

Removes characters defined in trim\_str from the beginning, end, or both of str. If trim\_str is not specified, the space character is the default.

If the trim location is not specified, defined characters are trimmed from both the beginning and end of str.

| | TRY\_CAST( **str** AS type) |

Attempts to cast/convert a string type to any valid numeric, timestamp, date, or time type. If the conversion cannot be performed, null is returned.

Note that TRY\_CAST is not valid for non-string input types.

| | `UCASE(`**`str`**`)` | Returns the string in uppercase format. Only ASCII character set is currently supported. Same as `UPPER`. | | `UPPER(`**`str`**`)` | Returns the string in uppercase format. Only ASCII character set is currently supported. Same as `UCASE`. | | URL\_DECODE( **str** ) | Decode a url-encoded string. This is the inverse of the `URL_ENCODE` function. | | URL\_ENCODE( **str** ) | Url-encode a string. Alphanumeric and the 4 characters: `_-.~` are untranslated. The space character is translated to `+`. All other characters are translated into a 3-character sequence `%XX` where `XX` is the 2-digit hexadecimal ASCII value of the character. | ## Pattern-Matching Functions | Name | Example | Description | | ----------------------------------------------------- | -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **`str`** `LIKE` **`pattern`** | `'ab' LIKE 'ab'` | Returns true if the string matches the pattern (case-sensitive) | | **`str`** `NOT LIKE` **`pattern`** | `'ab' NOT LIKE 'cd'` | Returns true if the string does not match the pattern | | **`str`** `ILIKE` **`pattern`** | `'AB' ILIKE 'ab'` | Returns true if the string matches the pattern (case-insensitive). Supported only when the right side is a string literal; for example, `colors.name ILIKE 'b%` | | **`str`** `REGEXP` **`POSIX pattern`** | `'^[a-z]+r$'` | Lowercase string ending with r | | `REGEXP_LIKE (` **`str`** `,` **`POSIX pattern`** `)` | `'^[hc]at'` | cat or hat | **Usage Notes** The following wildcard characters are supported by `LIKE` and `ILIKE`: * `%` matches any number of characters, including zero characters. * `_` matches exactly one character. ## Date/Time Functions | Function | Description | | ----------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |

CURRENT\_DATE

CURRENT\_DATE()

|

Returns the current date in the GMT time zone.

Example:

SELECT CURRENT\_DATE();

| |

CURRENT\_TIME

CURRENT\_TIME()

|

Returns the current time of day in the GMT time zone.

Example:

SELECT CURRENT\_TIME();

| |

CURRENT\_TIMESTAMP

CURRENT\_TIMESTAMP()

|

Return the current timestamp in the GMT time zone. Same as NOW().

Example:

SELECT CURRENT\_TIMESTAMP();

| | `DATEADD(`**`'date_part'`**`,` **`interval`**`,` **`date`** `\|` **`timestamp`**`)` |

Returns a date after a specified time/date interval has been added.

Example:

SELECT DATEADD('MINUTE', 6000, dep\_timestamp) Arrival\_Estimate FROM flights\_2008\_10k LIMIT 10;

| | `DATEDIFF(`**`'date_part'`**`,` **`date`**`,` **`date`**`)` |

Returns the difference between two dates, calculated to the lowest level of the date\_part you specify. For example, if you set the date\_part as DAY, only the year, month, and day are used to calculate the result. Other fields, such as hour and minute, are ignored.

Example:

SELECT DATEDIFF('YEAR', plane\_issue\_date, now()) Years\_In\_Service FROM flights\_2008\_10k LIMIT 10;

| | `DATEPART(`**`'interval'`**`,` **`date`** `\|` **`timestamp`**`)` |

Returns a specified part of a given date or timestamp as an integer value. Note that 'interval' must be enclosed in single quotes.

Example:

SELECT DATEPART('YEAR', plane\_issue\_date) Year\_Issued FROM flights\_2008\_10k LIMIT 10;

| | `DATE_TRUNC(`**`date_part`**`,` **`timestamp`**`)` |

Truncates the timestamp to the specified date\_part. DATE\_TRUNC(week,...) starts on Monday (ISO), which is different than EXTRACT(dow,...), which starts on Sunday.

Example:

SELECT DATE\_TRUNC(MINUTE, arr\_timestamp) Arrival FROM flights\_2008\_10k LIMIT 10;

| | `EXTRACT(`**`date_part`** `FROM` **`timestamp`**`)` |

Returns the specified date\_part from timestamp.

Example:

SELECT EXTRACT(HOUR FROM arr\_timestamp) Arrival\_Hour FROM flights\_2008\_10k LIMIT 10;

| | `INTERVAL` **`'count'`** **`date_part`** |

Adds or Subtracts count date\_part units from a timestamp. Note that 'count' is enclosed in single quotes.

Example:

SELECT arr\_timestamp + INTERVAL '10' YEAR FROM flights\_2008\_10k LIMIT 10;

| | `NOW()` |

Return the current timestamp in the GMT time zone. Same as CURRENT\_TIMESTAMP().

Example:

NOW();

| | `TIMESTAMPADD(`**`date_part`**`,` **`count`**`,` **`timestamp`** `\|` **`date`**`)` |

Adds an interval of count date\_part to timestamp or date and returns signed date\_part units in the provided timestamp or date form.

Example:

SELECT TIMESTAMPADD(DAY, 14, arr\_timestamp) Fortnight FROM flights\_2008\_10k LIMIT 10;

| | `TIMESTAMPDIFF(`**`date_part`**`,` **`timestamp1`**`,` **`timestamp2`**`)` |

Subtracts timestamp1 from timestamp2 and returns the result in signed date\_part units.

Example:

SELECT TIMESTAMPDIFF(MINUTE, arr\_timestamp, dep\_timestamp) Flight\_Time FROM flights\_2008\_10k LIMIT 10;

| ### Supported Types Supported **date\_part** types: ``` DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, MILLENNIUM, CENTURY, DECADE, WEEK, WEEK_SUNDAY, QUARTERDAY] EXTRACT [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, DOW, ISODOW, DOY, EPOCH, QUARTERDAY, WEEK, WEEK_SUNDAY, DATEEPOCH] DATEDIFF [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, WEEK] ``` Supported **interval** types: ``` DATEADD [DECADE, YEAR, QUARTER, MONTH, WEEK, WEEKDAY, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND] TIMESTAMPADD [YEAR, QUARTER, MONTH, WEEKDAY, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND] DATEPART [YEAR, QUARTER, MONTH, DAYOFYEAR, QUARTERDAY, WEEKDAY, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND] ``` ### Accepted Date, Time, and Timestamp Formats | Datatype | Formats | Examples | | --------- | ---------------- | ------------------------ | | DATE | YYYY-MM-DD | 2013-10-31 | | DATE | MM/DD/YYYY | 10/31/2013 | | DATE | DD-MON-YY | 31-Oct-13 | | DATE | DD/Mon/YYYY | 31/Oct/2013 | | EPOCH | | 1383262225 | | TIME | HH:MM | 23:49 | | TIME | HHMMSS | 234901 | | TIME | HH:MM:SS | 23:49:01 | | TIMESTAMP | DATE TIME | 31-Oct-13 23:49:01 | | TIMESTAMP | DATETTIME | 31-Oct-13T23:49:01 | | TIMESTAMP | DATE:TIME | 11/31/2013:234901 | | TIMESTAMP | DATE TIME ZONE | 31-Oct-13 11:30:25 -0800 | | TIMESTAMP | DATE HH.MM.SS PM | 31-Oct-13 11.30.25pm | | TIMESTAMP | DATE HH:MM:SS PM | 31-Oct-13 11:30:25pm | | TIMESTAMP | | 1383262225 | ### **Usage Notes** * For two-digit years, years 69-99 are assumed to be previous century (for example, 1969), and 0-68 are assumed to be current century (for example, 2016). * For four-digit years, negative years (BC) are not supported. * Hours are expressed in 24-hour format. * When time components are separated by colons, you can write them as one or two digits. * Months are case insensitive. You can spell them out or abbreviate to three characters. * For timestamps, decimal seconds are ignored. Time zone offsets are written as +/-HHMM. * For timestamps, a numeric string is converted to +/- seconds since January 1, 1970. Supported timestamps range from -30610224000 (January 1, 1000) through 29379456000 (December 31, 2900). * On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS. * Linux EPOCH values range from -30610224000 (1/1/1000) through 185542587100800 (1/1/5885487). Complete range in years: +/-5,883,517 around epoch. ## Statistical and Aggregate Functions Both double-precision (standard) and single-precision floating point statistical functions are provided. Single-precision functions run faster on GPUs but might cause overflow errors.
Double-precision FP FunctionSingle-precision FP FunctionDescription
Double-precision FP FunctionSingle-precision FP FunctionDescription
AVG(x)Returns the average value of x
COUNT()Returns the count of the number of rows returned
COUNT(DISTINCT x)Returns the count of distinct values of x
APPROX_COUNT_DISTINCT(x, e)Returns the approximate count of distinct values of x with defined expected error rate e, where e is an integer from 1 to 100. If no value is set for e, the approximate count is calculated using the system-widehll-precision-bits configuration parameter.
APPROX_MEDIAN(x)

Returns the approximate median of x. Two server configuration parameters affect memory usage:

Accuracy of APPROX_MEDIAN depends on the distribution of data; see Usage Notes.

Query steps with this operator will run on CPU.

APPROX_PERCENTILE(x,y)

Returns the approximate quantile of x, where y is the value between 0 and 1.

For example, y=0 returns MIN(x), y=1 returns MAX(x), and y=0.5 returns APPROX_MEDIAN(x).

Query steps with this operator will run on CPU.

MAX(x)Returns the maximum value of x
MIN(x)Returns the minimum value of x
MODE(x)Returns the most common value of x.

Query steps with this operator will run on CPU.
SINGLE_VALUEReturns the input value if there is only one distinct value in the input; otherwise, the query fails.
SUM(x)Returns the sum of the values of x
SAMPLE(x)

Returns one sample value from aggregated column x. For example, the following query returns population grouped by city, along with one value from the state column for each group:

Note: This was previously LAST_SAMPLE, which is now deprecated.

CORRELATION(x, y)CORRELATION_FLOAT(x, y)Alias of CORR. Returns the coefficient of correlation of a set of number pairs.
CORR(x, y)CORR_FLOAT(x, y)Returns the coefficient of correlation of a set of number pairs.
COUNT_IF(conditional_expr)Returns the number of rows satisfying the given condition_expr.
COVAR_POP(x, y)COVAR_POP_FLOAT(x, y)Returns the population covariance of a set of number pairs.
COVAR_SAMP(x, y)COVAR_SAMP_FLOAT(x, y)Returns the sample covariance of a set of number pairs.
STDDEV(x)STDDEV_FLOAT(x)Alias of STDDEV_SAMP. Returns sample standard deviation of the value.
STDDEV_POP(x)STDDEV_POP_FLOAT(x)Returns the population standard the standard deviation of the value.
STDDEV_SAMP(x)STDDEV_SAMP_FLOAT(x)Returns the sample standard deviation of the value.
SUM_IF(conditional_expr)Returns the sum of all expression values satisfying the given condition_expr.
VARIANCE(x)VARIANCE_FLOAT(x)Alias of VAR_SAMP. Returns the sample variance of the value.
VAR_POP(x)VAR_POP_FLOAT(x)Returns the population variance sample variance of the value.
VAR_SAMP(x)VAR_SAMP_FLOAT(x)Returns the sample variance of the value.
### **Usage Notes** * `COUNT(DISTINCT` **`x`**`)`, especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinalities. To avoid this large overhead, use APPROX\_COUNT\_DISTINCT. * `APPROX_COUNT_DISTINCT(`**`x`**`,` **`e`**`)` gives an approximate count of the value **x**, based on an expected error rate defined in **e**. The error rate is an integer value from 1 to 100. The lower the value of **e**, the higher the precision, and the higher the memory cost. Select a value for **e** based on the level of precision required. On large tables with large cardinalities, consider using `APPROX_COUNT_DISTINCT` when possible to preserve memory. When data cardinalities permit, OmniSci uses the precise implementation of `COUNT(DISTINCT` **`x`**`)` for `APPROX_COUNT_DISTINCT`. Set the default error rate using the `-hll-precision-bits` configuration parameter. * The accuracy of `APPROX_MEDIAN (`**`x`**`)` upon the distribution of data. For example: * For 100,000,000 integers (1, 2, 3, ... 100M) in random order, APPROX\_MEDIAN can provide a highly accurate answer 5+ significant digits. * For 100,000,001 integers, where 50,000,000 have value of 0 and 50,000,001 have value of 1, APPROX\_MEDIAN returns a value close to 0.5, even though the median is 1. * Currently, OmniSci does not support grouping by non-dictionary-encoded strings. However, with the `SAMPLE` aggregate function, you can select non-dictionary-encoded strings that are presumed to be unique in a group. For example: ``` SELECT user_name, SAMPLE(user_decription) FROM tweets GROUP BY user_name; ``` If the aggregated column (user\_description in the example above) is not unique within a group, `SAMPLE` selects a value that might be nondeterministic because of the parallel nature of OmniSci query execution. ## Miscellaneous Functions | Function | Description | | ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `SAMPLE_RATIO(`**`x`**`)` |

Returns a Boolean value, with the probability of True being returned for a row equal to the input argument. The input argument is a numeric value between 0.0 and 1.0. Negative input values (return False), input values greater than 1.0 returns True, and null input values return False.

The result of the function is deterministic per row; that is, all calls of the operator for a given row return the same result. The sample ratio is probabilistic, but is generally within a thousandth of a percentile of the actual range when the underlying dataset is millions of records or larger.

The following example filters approximately 50% of the rows from t and returns a count that is approximately half the number of rows in t:

SELECT COUNT(\*) FROM t WHERE SAMPLE\_RATIO(0.5)

| ## User-Defined Functions You can create your own C++ functions and use them in your SQL queries. * User-defined Functions (UDFs) require clang++ version 9. You can verify the version installed using the command `clang++ --version`. * UDFs currently allow any authenticated user to register and execute a runtime function. By default, runtime UDFs are globally disabled but can be enabled with the runtime flag `enable-runtime-udf`. 1. Create your function and save it in a .cpp file; for example, /var/lib/omnisci/udf\_myFunction.cpp. 2. Add the UDF configuration flag to omnisci.conf. For example: ``` udf = "/var/lib/omnisci/udf_myFunction.cpp" ``` 3. Use your function in a SQL query. For example: ``` SELECT udf_myFunction FROM myTable ``` ### Sample User-Defined Function This function, udf\_diff.cpp, returns the difference of two values from a table. ```clike #include #if defined(__CUDA_ARCH__) && defined(__CUDACC__) && defined(__clang__) #define DEVICE __device__ #define NEVER_INLINE #define ALWAYS_INLINE #else #define DEVICE #define NEVER_INLINE __attribute__((noinline)) #define ALWAYS_INLINE __attribute__((always_inline)) #endif #define EXTENSION_NOINLINE extern "C" NEVER_INLINE DEVICE EXTENSION_NOINLINE int32_t udf_diff(const int32_t x, const int32_t y) { return x - y; } ``` #### **Code Commentary** Include the standard integer library, which supports the following datatypes: * bool * int8\_t (cstdint), char * int16\_t (cstdint), short * int32\_t (cstdint), int * int64\_t (cstdint), size\_t * float * double * void ```cpp #include ``` The next four lines are boilerplate code that allows OmniSci to determine whether the server is running with GPUs. OmniSci chooses whether it should compile the function inline to achieve the best possible performance. ```clike #include #if defined(__CUDA_ARCH__) && defined(__CUDACC__) && defined(__clang__) #define DEVICE __device__ #define NEVER_INLINE #define ALWAYS_INLINE #else #define DEVICE #define NEVER_INLINE __attribute__((noinline)) #define ALWAYS_INLINE __attribute__((always_inline)) #endif #define EXTENSION_NOINLINE extern "C" NEVER_INLINE DEVICE ``` The next line is the actual user-defined function, which returns the difference between INTEGER values x and y. ```cpp EXTENSION_NOINLINE int32_t udf_diff(const int32_t x, const int32_t y) { return x - y; } ``` To run the `udf_diff` function, add this line to your /var/lib/omnisci/omnisci.conf file (in this example, the .cpp file is stored at /var/lib/omnisci/udf\_diff.cpp): ```cpp udf = "/var/lib/omnisci/udf_diff.cpp" ``` Restart the OmniSci server. Use your command from an OmniSci SQL client to query, for example, a table named myTable that contains the INTEGER columns `myInt1` and `myInt2`. ```sql SELECT udf_diff(myInt1, myInt2) FROM myTable LIMIT 1; ``` OmniSci returns the difference as an INTEGER value.