Mathematical Capabilities
Comparison Operator Support
Mathematical Function Support
Trigonometric Function Support
Geometric Function Support
String Function Support
Function | Description |
---|---|
CHAR_LENGTH(str) |
Returns the number of characters in a string |
LENGTH(str) |
Returns the length of a string in bytes |
Pattern-Matching Support
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 Function Support
Usage Notes
The 'date_part'
argument must be enclosed in single quotes. For example, in the following query, the 'DAY'
argument is in single quotes:
SELECT DATEDIFF('DAY', NOW(),plane_issue_date) from flights_2008_7M;
The interval
argument does not require quotes, but the numeric value associated with the interval must be enclosed in single quotes. For example, if setting the interval to one day, the number 1 must be in single quotes:
SELECT INTERVAL '1' DAY FROM flights_2008_7M LIMIT 1;
Supported 'date_part' types:
DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLENNIUM, CENTURY, DECADE, WEEK,
QUARTERDAY]
EXTRACT [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, ISODOW, DOY, EPOCH, QUARTERDAY,
WEEK]
DATEDIFF [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLENNIUM, CENTURY, DECADE,
QUARTERDAY]
Supported interval types:
DATEADD [YEAR, QUARTER, MONTH, DAY, WEEKDAY, HOUR, MINUTE,
SECOND ]
TIMESTAMPADD [YEAR, QUARTER, MONTH, DAY, WEEKDAY, HOUR, MINUTE,
SECOND ]
DATEPART [YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, HOUR, MINUTE,
SECOND ]
Accepted Date, Time, and Timestamp Formats
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.
- On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS.
Aggregate Function Support
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 usingAPPROX_COUNT_DISTINCT
when possible to preserve memory.Set the default error rate using the -hll-precision-bits configuration paramenter.
-
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.
Statistical Function Support
Both double-precision (standard) and single-precision floating point functions are provided. Single-precision functions run faster on GPUs but might cause overflow errors.