%matches any number of characters, including zero characters.
_matches exactly one character.
), 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.
)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_DISTINCTwhen possible to preserve memory. When data cardinalities permit, OmniSci uses the precise implementation of
APPROX_COUNT_DISTINCT. Set the default error rate using the
)upon the distribution of data. For example:
SAMPLEaggregate function, you can select non-dictionary-encoded strings that are presumed to be unique in a group. For example:
SAMPLEselects a value that might be nondeterministic because of the parallel nature of OmniSci query execution.
udf_difffunction, 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):