Covered data sources: presto, impala, hive, spark, greenplum, oracle, postgresql, redshift, sqlserver, tidb, mysql, db2, mongodb, maxcompute
| Function Name | Description | Parameter Info | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| abs(N) | Absolute value operation. For example, abs(-1) returns the numeric type: 1 | Numeric argument, optional type: NUMBER | NUMBER | |
| ceil(N) | Gets the smallest integer value that is greater than or equal to the specified number | Numeric argument, optional type: NUMBER | NUMBER | |
| floor(N) | Gets the smallest integer that is less than or equal to the specified number | Numeric argument, optional type: NUMBER | NUMBER | |
| median(N) | Gets the median of the specified list of numbers | Numeric argument, optional type: NUMBER | NUMBER | impala hive spark sqlserver tidb mysql |
| mod(DIVIDEND,DIVISOR) | Modulus operation, arguments can be decimals. For example, mod(12.3, 5) returns the numeric type: 2.3 | 1.Dividend, optional type: NUMBER; 2.Divisor, optional type: NUMBER | NUMBER | |
| growth_rate(CURRENT,PREVIOUS) | Growth rate | 1.New value, optional type: NUMBER; 2.Previous value, optional type: NUMBER |
NUMBER | |
| percentile(N,PERCENT) | Calculates percentile | 1.Numeric argument, optional type: NUMBER; 2.Internal parameter, optional type: NUMBER |
NUMBER | impala hive spark sqlserver tidb mysql mongodb maxcompute |
| power(BASE,EXPONENT) | Power operation. For example, power(2,3) returns the numeric type: 8 | 1.Base, optional type: NUMBER; 2.Exponent, optional type: NUMBER |
NUMBER | |
| round(N,LITERAL_PRECISION) | Rounds to the specified number of digits | 1.Numeric argument, optional type: NUMBER; 2.Specified precision, optional type: NUMBER |
NUMBER | |
| sign(N) | Sign function. For example, sign(x), if x>0, then return 1; if x=0, then return 0; if x<0, then return -1 | Numeric argument, optional type: NUMBER | NUMBER | |
| sqrt(N) | Square root operation | Numeric argument, optional type: NUMBER | NUMBER | |
| cbrt(N) | Cubic root operation | Numeric argument, optional type: NUMBER | NUMBER | mysql oracle sqlserver db2 impala tidb mongodb |
| acos(N) | Returns the arccosine of N in radians, N must be between [-1, 1], for example: acos(0.5) = 1.0471975511966 | Numeric argument, optional type: NUMBER | NUMBER | |
| asin(N) | Returns the arcsine of N in radians, N must be between [-1, 1], for example: asin(0.5) = 0.523598775598299 | Numeric argument, optional type: NUMBER | NUMBER | |
| atan(N) | Returns the arctangent of N in radians, for example: atan(1) = 0.785398163397448 | Numeric argument, optional type: NUMBER | NUMBER | |
| atan2(ARG1, ARG2) | Returns the arctan of arg1/arg2 in radians, for example: atan2(2, 2) = 0.785398163397448 | Numeric arguments, optional type: NUMBER | NUMBER | hive |
| cos(N) | Returns the cosine of N, N is in radians, for example: cos(pi()/3) = 0.5 | Numeric argument, optional type: NUMBER | NUMBER | |
| cot(N) | Returns the cotangent of N, N is in radians, N cannot be 0, for example: cot(pi()/4) = 1 | Numeric argument, optional type: NUMBER | NUMBER | |
| sin(N) | Returns the sine of N, N is in radians, for example: sin(pi()/6) = 0.5 | Numeric argument, optional type: NUMBER | NUMBER | |
| tan(N) | Returns the tangent of N, N is in radians, for example: tan(pi()/4) = 1 | Numeric argument, optional type: NUMBER | NUMBER | |
| cosh(N) | Returns the hyperbolic cosine of N, for example: cosh(1) = 1.5430806348152437 | Numeric argument, optional type: NUMBER | NUMBER | postgres greenplum mysql sqlserver hive tidb mongodb redshift |
| sinh(N) | Returns the hyperbolic sine of N, for example: sinh(1) = 0.8686709614860095 | Numeric argument, optional type: NUMBER | NUMBER | postgres greenplum mysql sqlserver hive tidb presto mongodb redshift |
| tanh(N) | Returns the hyperbolic tangent of N, for example: tanh(1) = 0.7615941559557649 | Numeric argument, optional type: NUMBER | NUMBER | postgres greenplum mysql sqlserver hive tidb mongodb redshift |
| degrees(N) | Converts radians to degrees, N is in radians | Numeric argument, optional type: NUMBER | NUMBER | oracle |
| radians(N) | Converts degrees to radians, N is in degrees | Numeric argument, optional type: NUMBER | NUMBER | oracle |
| pi() | Constant π, equals 3.14159265358979 | NUMBER | orace db2 |
|
| exp(N) | The exponential function (e to the power of N), e is the base of the natural logarithm, exp(1) = 2.171828182845904 | Numeric argument, optional type: NUMBER | NUMBER | |
| ln(N) | Returns the natural logarithm of N, natural logarithm has a constant base e(2.71828182845904), N must be greater than 0, for example: ln(1) = 0 | Numeric argument, optional type: NUMBER | NUMBER | |
| log(BASE, N) | Returns the logarithm of N with BASE as the base, BASE must be greater than 0 and not equal to 1, N must be greater than 0, for example: log(3, 9) = 2 | BASE, optional type: NUMBER, N, optional type: NUMBER |
NUMBER | db2 redshift |
| log2(N) | Returns the base-2 logarithm of N, N must be greater than 0, for example: log2(4) = 2 | Numeric argument, optional type: NUMBER | NUMBER | db2 redshift |
| log10(N) | Returns the base-10 logarithm of N, N must be greater than 0, for example: log10(100) = 2 | Numeric argument, optional type: NUMBER | NUMBER | |
| rand() | Random value in the range 0.0 <= N < 1.0 | NUMBER | ||
| greatest(ARG1, ARG2) | Returns the largest number in the list | ARG1: Numeric argument, optional type: NUMBER, ARG2: Numeric argument, optional type: NUMBER |
NUMBER | |
| least(ARG1, ARG2) | Returns the smallest number in the list | ARG1: Numeric argument, optional type: NUMBER, ARG2: Numeric argument, optional type: NUMBER |
NUMBER |
| Function Name | Description | Parameter Info | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| concat(S1,S2) | Concatenates two arguments as text. For example, concat('abc', 123) returns the string type: 'abc123' | 1.First text argument, optional type: STRING; 2.Second text argument, optional type: STRING |
STRING | |
| initcap(S) | Formats the specified string by capitalizing the first letter of each word and making the rest lowercase | Text argument, optional type: STRING | STRING | tidb mongodb |
| length(S) | Calculates the length of the string | Text argument, optional type: STRING | NUMBER | |
| lower(S) | Modifies all characters of the specified string to lowercase | Text argument, optional type: STRING | STRING | |
| position(S,PATTERN) | Gets the position where the substring appears in the specified string for the first time | 1.Text argument, optional type: STRING; 2.Internal argument, optional type: STRING |
NUMBER | |
| to_string(ARG) | Converts the input field or value to text | Parameter in computation, optional type: ANY | STRING | |
| trim(S) | Trims whitespace from the beginning and end of the string | Text argument, optional type: STRING | STRING | |
| replace(s,s1,s2) | Replaces string. Usage: replace(s, s1, s2), s2 replaces all occurrences of s1 in s. | 1.Text argument, optional type: STRING2; 2.First text argument, optional type: STRING; 3.Second text argument, optional type: STRING |
STRING | |
| substring(S,START,LENGTH) | Extracts a substring. Usage: substring(s, start, length), start begins from 1. | 1.Text argument, optional type: STRING; 2.Specified start, optional type: NUMBER; 3.Specified length, optional type: NUMBER |
STRING | |
| upper(S) | Modifies all characters of the specified string to uppercase | Text argument, optional type: STRING | STRING | |
| unlike(S,LITERAL_PATTERN) | Specifies fields do not contain specific text | 1.Text argument, optional type: STRING; 2.Specified pattern, optional type: STRING |
BOOL | |
| like(S,LITERAL_PATTERN) | Specifies fields contain specific text | 1.Text argument, optional type: STRING; 2.Specified pattern, optional type: STRING |
BOOL |
| Function Name | Description | Parameter Info | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| row_number() | Window function, returns the row count in the current window, counting starts from 1 and increases one by one, numbers do not repeat, such as 1, 2, 3, 4. Usage: row_number() OVER( [ PARTITION BY expr1 ] ORDER BY expr2 [ DESC ] ) | NUMBER | impala hive skqrk oracle tidb mysql mongodb |
|
| rank() | Window function, returns the rank in the current window (with gaps), counting starts from 1, equal rankings share the same number and subsequent ranks jump, such as 1, 2, 2, 4. Usage: rank() OVER( [ PARTITION BY expr1 ] ORDER BY expr2 [ DESC ] ) | NUMBER | impala hive skqrk tidb mysql mongodb |
|
| dense_rank() | Window function, returns the dense rank in the current window (without gaps), counting starts from 1, equal rankings share the same number and subsequent ranks do not jump, such as 1, 2, 2, 3. Usage: dense_rank() OVER( [ PARTITION BY expr1 ] ORDER BY expr2 [ DESC ] ) | NUMBER | impala hive skqrk tidb mysql mongodb |
|
| lag(ARG,I) | Window function, obtains the arg data of the record specified i distance ahead of the current record | 1.Parameter in computation, optional type: ANY; 2.Integer parameter, optional type: NUMBER |
ANY | impala hive skqrk tidb mysql mongodb |
| lead(ARG,I) | Window function, obtains the arg data of the record specified i distance behind the current record | 1.Parameter in computation, optional type: ANY; 2.Integer parameter, optional type: NUMBER |
ANY | impala hive skqrk tidb mysql mongodb |
| Function Name | Description | Parameter Info | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| add_day(T,I) | Returns the date after the number of days specified by the numeric field from the start date, if the numeric field is negative, then returns the date before the specified number of days. For example, add_day('2018-02-02',3), returns the date type: 2018-02-05 | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_hour(T,I) | Returns the time after the number of hours specified by the numeric field from the start time, if the numeric field is negative, then returns the time before the specified number of hours. For example, add_hour('2018-02-02 08:30:00.000',3), returns the date type: 2018-02-02 11:30:00.000 | 1.Time type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_minute(T,I) | Returns the time after the number of minutes specified by the numeric field from the start time, if the numeric field is negative, then returns the time before the specified number of minutes. For example, add_minute('2018-02-02 08:30:00.000',3), returns the date type: 2018-02-02 08:33:00.000 | 1.Time type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_month(T,I) | Returns the date after the number of months specified by the numeric field from the start date, if the numeric field is negative, then returns the date before the specified number of months. For example, add_month('2018-02-02',3), returns the date type: 2018-05-02 | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_quarter(T,I) | Returns the date after the number of quarters specified by the numeric field from the start date, if the numeric field is negative, then returns the date before the specified number of quarters. For example, add_quarter('2018-02-02',3), returns the date type: 2018-05-02 | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_week(T,I) | Returns the date after the number of weeks specified by the numeric field from the start date, if the numeric field is negative, then returns the date before the specified number of weeks. For example, add_week('2018-02-02',3), returns the date type: 2018-05-02 | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_year(T,I) | Returns the date after the number of years specified by the numeric field from the start date, if the numeric field is negative, then returns the date before the specified number of years. For example, add_year('2018-02-02',3), returns the date type: 2021-02-02 | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | |
| add_year_keep_woy(T,I) | Similar to add_year, but adjusts the date to keep the week of the year unchanged | 1.Date type argument, optional type: DATE; 2.Integer type argument, optional type: NUMBER |
DATE | mysql tidb sqlserver redshift spark hive impala presto db2 mongodb maxcompute |
| diff_in_day(START,END) | Compares the difference in days between two dates. For example, diff_in_day('2015-02-03', '2018-02-03') returns the integer type: 1095 | 1.Specified start, optional type: DATE; 2.Specified end, optional type: DATE |
NUMBER | |
| diff_in_hour(START,END) | Compares the difference in hours between two times. For example, diff_in_hour('2015-03-24 16:32:32', '2018-02-02 12:34:43') returns the integer type: 25100 | 1.Specified start, optional type: DATE; 2.Specified end, optional type: DATE |
NUMBER | |
| diff_in_minute(START,END) | Compares the difference in minutes between two times. For example, diff_in_minute('2015-03-24 16:32:32', '2018-02-02 12:34:43') returns the integer type: 1506002 | 1.Specified start, optional type: DATE; 2.Specified end, optional type: DATE |
NUMBER | |
| diff_in_month(START,END) | Compares the difference in months between two dates. For example, diff_in_month('2015-02-03', '2018-02-03') returns the integer type: 36 | 1.Specified start, optional type |