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
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.