Function List
Supported Data Sources: presto, impala, hive, spark, greenplum, oracle, postgresql, redshift, sqlserver, tidb, mysql, db2, mongodb, maxcompute
Numeric Functions
| Function Name | Description | Parameter Description | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| abs(N) | Absolute value operation. For example: abs(-1), returns the number type: 1 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| ceil(N) | Get the smallest integer value greater than or equal to the specified value | Numeric type parameter, optional types: NUMBER | NUMBER | |
| floor(N) | Get the largest integer value less than or equal to the specified value | Numeric type parameter, optional types: NUMBER | NUMBER | |
| median(N) | Get the median of the specified numeric column | Numeric type parameter, optional types: NUMBER | NUMBER | impala hive spark sqlserver tidb mysql |
| mod(DIVIDEND,DIVISOR) | Modulo operation, parameters can be decimals. For example: mod(12.3, 5), returns the number type: 2.3 | 1. Dividend, optional types: NUMBER; 2. Divisor, optional types: NUMBER | NUMBER | |
| growth_rate(CURRENT,PREVIOUS) | Growth rate | 1. New value, optional types: NUMBER; 2. Previous value, optional types: NUMBER | NUMBER | |
| percentile(N,PERCENT) | Calculate the percentile | 1. Numeric type parameter, optional types: NUMBER; 2. Internal parameter, optional types: NUMBER | NUMBER | impala hive spark sqlserver tidb mysql mongodb maxcompute |
| power(BASE,EXPONENT) | Power operation. For example: power(2,3), returns the number type: 8 | 1. Base, optional types: NUMBER; 2. Exponent, optional types: NUMBER | NUMBER | |
| round(N,LITERAL_PRECISION) | Round to the specified number of digits | 1. Numeric type parameter, optional types: NUMBER; 2. Specified precision, optional types: NUMBER | NUMBER | |
| sign(N) | Sign function. For example: sign(x), if x>0, returns 1; if x=0, returns 0; if x<0, returns -1 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| sqrt(N) | Square root operation | Numeric type parameter, optional types: NUMBER | NUMBER | |
| cbrt(N) | Cube root operation | Numeric type parameter, optional types: NUMBER | NUMBER | mysql oracle sqlserver db2 impala tidb mongodb |
| acos(N) | Returns the arccosine of N, in radians, N must be a value between [-1, 1], for example: acos(0.5) = 1.0471975511966 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| asin(N) | Returns the arcsine of N, in radians, N must be a value between [-1, 1], for example: asin(0.5) = 0.523598775598299 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| atan(N) | Returns the arctangent of N, in radians, for example: atan(1) = 0.785398163397448 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| atan2(ARG1, ARG2) | Returns the arctangent of arg1/arg2, in radians, for example: atan2(2, 2) = 0.785398163397448 | Numeric type parameter, optional types: NUMBER | NUMBER | hive |
| cos(N) | Returns the cosine of N, N is in radians, for example: cos(pi()/3) = 0.5 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| cot(N) | Returns the cotangent of N, N is in radians, N cannot be 0, for example: cot(pi()/4) = 1 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| sin(N) | Returns the sine of N, N is in radians, for example: sin(pi()/6) = 0.5 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| tan(N) | Returns the tangent of N, N is in radians, for example: tan(pi()/4) = 1 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| cosh(N) | Returns the hyperbolic cosine of N, for example: cosh(1) = 1.5430806348152437 | Numeric type parameter, optional types: 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 type parameter, optional types: 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 type parameter, optional types: NUMBER | NUMBER | postgres greenplum mysql sqlserver hive tidb mongodb redshift |
| degrees(N) | Converts radians to degrees, N is in radians | Numeric type parameter, optional types: NUMBER | NUMBER | oracle |
| radians(N) | Converts degrees to radians, N is in degrees | Numeric type parameter, optional types: NUMBER | NUMBER | oracle |
| pi() | π constant, equal to 3.14159265358979 | NUMBER | orace db2 | |
| exp(N) | e raised to the power of N, e is the base of the natural logarithm, exp(1) = 2.171828182845904 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| ln(N) | Returns the natural logarithm of N, the natural logarithm is based on the constant e(2.71828182845904), N must be greater than 0, for example: ln(1) = 0 | Numeric type parameter, optional types: 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 types: NUMBER N, optional types: NUMBER | NUMBER | db2 redshift |
| log2(N) | Returns the logarithm of N with 2 as the base, N must be greater than 0, for example: log2(4) = 2 | Numeric type parameter, optional types: NUMBER | NUMBER | db2 redshift |
| log10(N) | Returns the logarithm of N with 10 as the base, N must be greater than 0, for example: log10(100) = 2 | Numeric type parameter, optional types: NUMBER | NUMBER | |
| rand() | Random value in the range 0.0 <= N < 1.0 | NUMBER | ||
| greatest(ARG1, ARG2) | Returns the largest value in the list | ARG1: Numeric type parameter, optional types: NUMBER ARG2: Numeric type parameter, optional types: NUMBER | NUMBER | |
| least(ARG1, ARG2) | Returns the smallest value in the list | ARG1: Numeric type parameter, optional types: NUMBER ARG2: Numeric type parameter, optional types: NUMBER | NUMBER |
String Functions
| Function Name | Description | Parameter Description | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| concat(S1,S2,S3...) | Concatenate multiple parameters as text together. For example: concat('abc', 123), returns the string type: 'abc123' | Text type parameter, optional types: STRING | STRING | |
| initcap(S) | Format the specified string, capitalize the first letter of each word in the string, and convert other letters to lowercase | Text type parameter, optional types: STRING | STRING | tidb mongodb |
| length(S) | Calculate the length of the string | Text type parameter, optional types: STRING | NUMBER | |
| lower(S) | Convert all characters of the specified string to lowercase | Text type parameter, optional types: STRING | STRING | |
| position(S,PATTERN) | Get the position of the first occurrence of the substring in the specified string | 1. Text type parameter, optional types: STRING; 2. Internal parameter, optional types: STRING | NUMBER | |
| to_string(ARG) | Convert the input field or value to text | Parameter involved in the calculation, optional types: ANY | STRING | |
| trim(S) | Remove spaces at the beginning and end of the string | Text type parameter, optional types: STRING | STRING | |
| replace(s,s1,s2) | Replace string. Usage replace(s, s1, s2), s2 replaces all occurrences of s1 in s. | 1. Text type parameter, optional types: STRING2; 2. First text parameter, optional types: STRING; 3. Second text parameter, optional types: STRING | STRING | |
| substring(S,START,LENGTH) | Extract substring. Usage substring(s, start, length), start is 1-based. | 1. Text type parameter, optional types: STRING; 2. Specified start, optional types: NUMBER; 3. Specified length, optional types: NUMBER | STRING | |
| upper(S) | Convert all characters of the specified string to uppercase | Text type parameter, optional types: STRING | STRING | |
| unlike(S,LITERAL_PATTERN) | The specified field does not contain specific text | 1. Text type parameter, optional types: STRING; 2. Specified pattern, optional types: STRING | BOOL | |
| like(S,LITERAL_PATTERN) | The specified field contains specific text | 1. Text type parameter, optional types: STRING; 2. Specified pattern, optional types: STRING | BOOL | |
| TRIM(s) | Remove spaces at the beginning and end of the specified string. Example: TRIM({usertype}) TRIM(' X ')) | STRING type | Returns the string type | Supported data sources include: Athena, Clickhouse, Dameng, Db2, Postgresql, Greenplum, Spark, Hologres, Impala, Kylin, Maxcompute, Mysql, Mongodb, Oracle, Presto, Redshift, Saphana, Sqlserver, Tidb, Vertica |
| ltrim(s1, s2) | This function searches for content in the s1 string starting from the beginning that matches the s2 string, stops searching when the content does not match the s2 string, and deletes the found content. Example ltrim('abcbabca','ab'), returns the result as cbabca. | Parameter 1: string type. Parameter 2: string type. | Returns the string type. | Supported data sources: Athena, Clickhouse, Dameng, Db2, Postgresql, Greenplum, Spark, Hologres, Impala, Kylin, Maxcompute, Mysql, Mongodb, Oracle, Presto, Redshift, Saphana, Sqlserver, Tidb, Vertica |
| rtrim(s1, s2) | This function searches for content in the s1 string starting from the end that matches the s2 string, stops searching when the content does not match the s2 string, and deletes the found content. Example: rtrim('abadcdabab','ab') returns the value as abadcd. | Parameter 1: string type. Parameter 2: string type. | Returns the string type. | Supported data sources: Athena, Clickhouse, Dameng, Db2, Postgresql, Greenplum, Spark, Hologres, Impala, Kylin, Maxcompute, Mysql, Mongodb, Oracle, Presto, Redshift, Saphana, Sqlserver, Tidb, Vertica |
| split(s, delimiter, n) | This function splits the string s by the delimiter and returns the nth (starting from 1) string after splitting. For example, split('abc,abc,abcdef',',',3), returns the string abcdef. | Parameter 1: string type. Parameter 2: string type. Parameter 3: int type. | Returns the string type. | Supported data sources: Athena, Clickhouse, Doris, Postgresql, Greenplum, Spark, Hive, Hologres, Impala, Maxcompute, Mysql, Mongodb, Phoenix, Presto, Redshift, Tidb, Vertica |
| regexp_extract(s, regexp, index) | This function splits the string s according to the rules of the regular expression regexp and returns the character specified by index | Parameter 1: string type. Parameter 2: string type. Parameter 3: int type. | Returns the string type. | Supported data sources:Athena, Doris, Postgresql, Spark, Hive, Hologres, Impala, Presto |
| regexp_match(s, regexp) | Determines whether the string s matches the regular expression regexp pattern. Returns TRUE if the match is successful, and FALSE if the match fails. | Parameter 1: string type. Parameter 2: string type. | Returns the bool type. | Supported data sources: Athena, Clickhouse, Dameng, Db2, Doris, Postgresql, Greenplum, Spark, Hive, Hologres, Impala, Maxcompute, Mysql, Mongodb, Oracle, Presto, Redshift, Saphana, Tidb, Vertica |
| regexp_replace(s, regexp, replacement) | Replaces the characters in the string s that match the regular expression regexp with the specified string replacement. | Parameter 1: string type. Parameter 2: string type. Parameter 3: string type. | Returns the string type. | Supported data sources:Athena, Clickhouse, Dameng, Db2, Doris, Postgresql, Greenplum, Spark, Hive, Hologres, Impala, Maxcompute, Mysql, Oracle, Phoenix, Presto, Redshift, Vertica |
Window Functions
| Function Name | Description | Parameter Description | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| row_number() | Window function, returns the row number of the current window, counting from 1 and incrementing sequentially without repetition, 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 of the current window (maintaining gaps), counting from 1, with the same rank numbers for ties, and subsequent ranks skipping, 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 rank of the current window (without maintaining gaps), counting from 1, with the same rank numbers for ties, and subsequent ranks not skipping, 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, retrieves the arg data of the record that is i distance before the current record | 1. Parameter involved in the calculation, optional type: ANY; 2. Integer type parameter, optional type: NUMBER | ANY | impala hive skqrk tidb mysql mongodb |
| lead(ARG,I) | Window function, retrieves the arg data of the record that is i distance after the current record | 1. Parameter involved in the calculation, optional type: ANY; 2. Integer type parameter, optional type: NUMBER | ANY | impala hive skqrk tidb mysql mongodb |
Date Functions
| Function Name | Description | Parameter Description | 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, or the date before the number of days if the numeric field is negative. For example: add_day('2018-02-02',3), returns date type: 2018-02-05 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the time before the number of hours if the numeric field is negative. For example: add_hour('2018-02-02 08:30:00.000',3), returns date type: 2018-02-02 11:30:00.000 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the time before the number of minutes if the numeric field is negative. For example: add_minute('2018-02-02 08:30:00.000',3), returns date type: 2018-02-02 08:33:00.000 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the date before the number of months if the numeric field is negative. For example: add_month('2018-02-02',3), returns date type: 2018-05-02 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the date before the number of quarters if the numeric field is negative. For example: add_quarter('2018-02-02',3), returns date type: 2018-05-02 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the date before the number of weeks if the numeric field is negative. For example: add_week('2018-02-02',3), returns date type: 2018-05-02 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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, or the date before the number of years if the numeric field is negative. For example: add_year('2018-02-02',3), returns date type: 2021-02-02 | 1. Date type parameter, optional type: DATE; 2. Integer type parameter, 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 parameter, optional type: DATE; 2. Integer type parameter, 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 integer type: 1095 | 1. Specify start, optional type: DATE; 2. Specify 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 integer type: 25100 | 1. Specify start, optional type: DATE; 2. Specify 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 integer type: 1506002 | 1. Specify start, optional type: DATE; 2. Specify 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 integer type: 36 | 1. Specify start, optional type: DATE; 2. Specify end, optional type: DATE | NUMBER | |
| diff_in_year(START,END) | Compares the difference in years between two times. For example: diff_in_year('2015-02-03', '2018-02-03'), returns integer type: 3 | 1. Specify start, optional type: DATE; 2. Specify end, optional type: DATE | NUMBER | |
| end_of_month(T) | Gets the start time of the last day of the month for the time field | Date type parameter, optional type: DATE | DATE | |
| extract_day(T) | Gets the day part of the parameter, indicating the day of the month. For example: extract_day('2018-02-02'), returns integer type: 2 | Date type parameter, optional type: DATE | NUMBER | |
| extract_dow(T) | Gets the day part of the parameter, indicating the day of the week. For example: extract_dow('2018-02-02'), returns integer type: 5 | Date type parameter, optional type: DATE | NUMBER | |
| extract_doy(T) | Gets the day part of the parameter, indicating the day of the year. For example: extract_doy('2018-02-02'), returns integer type: 33 | Date type parameter, optional type: DATE | NUMBER | |
| extract_hour(T) | Gets the hour part of the parameter, indicating the current hour. For example: extract_hour('2018-02-03 13:23:21'), returns integer type: 13 | Date type parameter, optional type: DATE | NUMBER | |
| extract_minute(T) | Gets the minute part of the parameter, indicating the current minute. For example: extract_minute('2018-02-03 13:23:21'), returns integer type: 02 | Date type parameter, optional type: DATE | NUMBER | |
| extract_month(T) | Gets the month part of the parameter. For example: extract_month('2018-02-02'), returns integer type: 2 | Date type parameter, optional type: DATE | NUMBER | |
| extract_quarter(T) | Gets the quarter part of the parameter. For example: extract_quarter('2018-02-02'), returns integer type: 1 | Date type parameter, optional type: DATE | NUMBER | |
| extract_second(T) | Gets the second part of the parameter, indicating the current second. For example: extract_second('2018-02-03 13:23:21'), returns integer type: 03 | Date type parameter, optional type: DATE | NUMBER | |
| extract_week(T) | Gets the week part of the parameter, indicating the week of the year. For example: extract_week('2018-02-02'), returns integer type: 4 | Date type parameter, optional type: DATE | NUMBER | |
| extract_year(T) | Gets the year part of the parameter. For example: extract_year('2018-02-02'), returns integer type: 2018 | Date type parameter, optional type: DATE | NUMBER | |
| month_weekday_count(YEAR,MONTH) | Gets the number of weekdays in the specified month. For example: month_weekday_count(2018, 2), returns integer type: 20 | 1. Year, optional type: NUMBER; 2. Month, optional type: NUMBER | NUMBER | mysql tidb sqlserver redshift oracle impala hive spark db2 mongodb maxcompute |
| now() | Returns the current time, date type | No parameters | DATE | |
| parse_time(S,LITERAL_PATTERN) | Parses the s parameter into a date type using the literal_pattern | 1. Text type parameter, optional type: STRING; 2. Specify pattern, optional type: STRING | DATE | sqlserver |
| time_in_range(T,S) | Determines whether the date is within the specified range, optional ranges include: 'Current Year', 'Current Year-to-Date', 'Current Quarter', 'Current Quarter-to-Date', 'Current Month', 'Current Month-to-Date', 'Current Week', 'Current Week-to-Date', 'Today', 'Current Hour', 'Yesterday', 'Recent 7 days', 'Recent 30 days', 'Recent 90 days', 'All Available Date', 'All Invalid Date', 'All' | 1. Date type parameter, optional type: DATE; 2. Text type parameter, optional type: STRING | BOOL | |
| today() | Returns the current date, date type | No parameters | DATE | |
| trunc_day(T) | Truncates the time or date column to the day. For example: trunc_day('2018-02-02 12:30:59'), returns date type: '2018-02-02' | Date type parameter, optional type: DATE | DATE | |
| trunc_hour(T) | Truncates the time column to the hour, all finer granularity is set to 0. For example: trunc_hour('2018-02-02 12:23:32'), returns date type: '2018-02-02 12:00:00' | Date type parameter, optional type: DATE | DATE | |
| trunc_minute(T) | Truncates the time column to the minute, all finer granularity is set to 0. For example: trunc_minute('2018-02-02 12:23:32'), returns date type: '2018-02-02 12:23:00' | Date type parameter, optional type: DATE | DATE | |
| trunc_month(T) | Truncates the time or date column to the month, the date is set to the 1st. For example: trunc_month('2018-02-02 12:23:32'), returns date type: '2018-02-01' | Date type parameter, optional type: DATE | DATE | |
| trunc_millisecond(T) | Truncates the time column to the millisecond, all finer granularity is set to 0. For example: trunc_millisecond('2018-02-02 12:23:32.123'), returns date type: '2018-02-02 12:23:32.123' | Date type parameter, optional type: DATE | DATE | |
| trunc_quarter(T) | Truncates the time or date column to the quarter, the month is set to the first month of the quarter, the date is set to the 1st. For example: trunc_quarter('2018-05-02 12:23:32'), returns date type: '2018-04-01' | Date type parameter, optional type: DATE | DATE | |
| trunc_second(T) | Truncates the time column to the second, all finer granularity is set to 0. For example: trunc_second('2018-02-02 12:23:32.123'), returns date type: '2018-02-02 12:23:32.000' | Date type parameter, optional type: DATE | DATE | |
| trunc_week(T) | Truncates the time or date column to the week, the month and date are set to the first day of the week. For example: trunc_week('2018-02-02'), returns date type: '2018-01-29' | Date type parameter, optional type: DATE | DATE | |
| trunc_year(T) | Truncates the time or date column to the year, the month and date are set to January 1st. For example: trunc_year('2018-02-02 12:23:32'), returns date type: '2018-01-01' | Date type parameter, optional type: DATE | DATE |
Aggregate Functions
| Function Name | Description | Parameter Description | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| avg(N) | Get the average value of the specified numeric column | Numeric type parameter, optional type: NUMBER | NUMBER | |
| count(ARG) | Get the number of values in the specified column | Parameter to be calculated, optional type: ANY | NUMBER | |
| distinct(ARG) | Query unique values (deduplication) in the column | Parameter to be calculated, optional type: ANY | ANY | |
| distinct_count(ARG) | Get the number of non-duplicate results in the specified column | Parameter to be calculated, optional type: ANY | NUMBER | |
| max(ARG) | Get the maximum value of the specified numeric column | Parameter to be calculated, optional type: ANY | ANY | |
| max_by(ARG, COMPARE) | Get the value of the arg column corresponding to the row with the maximum value in the compare column, if there are multiple maximum values, take one randomly | 1. Parameter to be calculated, optional type: ANY; 2. Comparison value, optional type: ANY | ANY | impala hive spark redshift sqlserver tidb mysql db2 mongodb maxcompute |
| min(ARG) | Get the minimum value of the specified numeric column | Parameter to be calculated, optional type: ANY | ANY | |
| min_by(ARG, COMPARE) | Get the value of the arg column corresponding to the row with the minimum value in the compare column, if there are multiple minimum values, take one randomly | 1. Parameter to be calculated, optional type: ANY; 2. Comparison value, optional type: ANY | ANY | impala hive spark redshift sqlserver tidb mysql db2 mongodb maxcompute |
| sum(N) | Get the sum of the specified numeric column | Numeric type parameter, optional type: NUMBER | NUMBER | |
| corr(ARG1, ARG2) | Return the correlation coefficient of two numeric columns, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], corr(arg1, arg2) = 1 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver impala tidb mongodb redshift |
| covar_pop(ARG1, ARG2) | Return the population covariance of two numeric columns, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_pop(arg1, arg2) = 138058.47222222222 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver impala tidb mongodb redshift |
| covar_samp(ARG1, ARG2) | Return the sample covariance of two numeric columns, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_samp(arg1, arg2) = 165670.16666666666 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver impala tidb mongodb redshift |
| regr_avgx(ARG1, ARG2) | Return the average of the independent variable (sum(ARG2)/N), e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)], regr_avgx(arg1, arg2) = 3.5 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_avgy(ARG1, ARG2) | Return the average of the dependent variable (sum(ARG1)/N), e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)], regr_avgy(arg1, arg2) = 3.5 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_count(ARG1, ARG2) | Number of input rows where both expressions are not null, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1), (null, 7), (6, null)], regr_count(arg1, arg2) = 6 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_intercept(ARG1, ARG2) | Intercept of the linear equation of the least squares fit determined by the (arg2, arg1) pairs, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)], regr_intercept(arg1, arg2) = 665.666666666666 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb mongodb redshift |
| regr_r2(ARG1, ARG2) | Square of the correlation coefficient, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_r2(arg1, arg2) = 0.06323185011709602 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_slope(ARG1, ARG2) | Slope of the linear equation of the least squares fit determined by the (arg2, arg1) pairs, e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_slope(arg1, arg2) = -0.42857142857142855 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb mongodb redshift |
| regr_sxx(ARG1, ARG2) | sum(arg2^2) - sum(arg2)^2/N (sum of squares of the independent variable), e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxx(arg1, arg2) = 17.5 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_sxy(ARG1, ARG2) | sum(arg2 * arg1) - sum(arg2) * sum(arg1)/N (sum of products of the independent variable and the dependent variable), e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxy(arg1, arg2) = -7.5 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| regr_syy(ARG1, ARG2) | sum(arg1^2) - sum(arg1)^2/N (sum of squares of the dependent variable), e.g., (arg1, arg2) = [(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_syy(arg1, arg2) = 50.833333333333336 | 1. ARG1, optional type: NUMBER 2. ARG2, optional type: NUMBER | NUMBER | mysql sqlserver hive(less than 2.2) spark impala tidb presto mongodb redshift |
| stddev_pop(N) | Return the population standard deviation of the numeric column, e.g., n = [(1000),(1001),(1003),(1004),(1005),(1000)], stddev_pop(n) = 1.9507833184532709 | Numeric type parameter, optional type: NUMBER | NUMBER | tidb |
| stddev_samp(N) | Return the sample standard deviation of the numeric column, e.g., n = [(1000),(1001),(1003),(1004),(1005),(1000)], stddev_samp(n) = 2.1369760566432809 | Numeric type parameter, optional type: NUMBER | NUMBER | tidb |
| var_pop(N) | Return the population variance of the numeric column, e.g., n = [(1000),(1001),(1003),(1004),(1005),(1000)], stddev_samp(n) = 2.1369760566432809 | Numeric type parameter, optional type: NUMBER | NUMBER | tidb mongodb |
| var_samp(N) | Return the sample variance (square of the sample standard deviation) of the numeric column, e.g., n = [(1000),(1001),(1003),(1004),(1005),(1000)], var_samp(n) = 4.5666666666666667 | Numeric type parameter, optional type: NUMBER | NUMBER | tidb mongodb |
| string_agg(S, DELIMITER) | Concatenate the field S into a string, separated by DELIMITER, where oracle must include the within group clause | 1. S: Field to be concatenated, optional type: ANY 2. DELIMITER: Separator, optional type: STRING | STRING | mysql hive sparksql impala presto tidb mongodb maxcompute |
Other Functions
| Function Name | Description | Parameter Description | Return Type | Unsupported Data Sources |
|---|---|---|---|---|
| between(ARG,START,END) | Determines if arg is within [start,end] | 1. Parameter to be calculated, optional type: ANY; 2. Specified start, optional type: ANY; 3. Specified end, optional type: ANY | BOOL | |
| between_ie(ARG,START,END) | Determines if arg is within (start,end] | 1. Parameter to be calculated, optional type: ANY; 2. Specified start, optional type: ANY; 3. Specified end, optional type: ANY | BOOL | |
| group(ARG) | Group statistics | Parameter to be calculated, optional type: ANY | ANY | |
| if(B,TRUE,FALSE) | Conditional branch judgment, the types of the last two parameters must be the same. For example: if({score}>=60, 'Pass', 'Fail') | 1. Condition, boolean type parameter, optional type: BOOL; 2. Value when condition is true, optional type: ANY; 3. Value when condition is false, optional type: ANY | ANY | |
| in(ARG,ARR) | Whether the value of the field is in the list | 1. Parameter to be calculated, optional type: ANY; 2. Array type parameter, optional type: ARRAY | BOOL | |
| isnotnull(ARG) | Determines if the specified field is not null | Parameter to be calculated, optional type: ANY | BOOL | |
| isnull(ARG) | Determines if the specified field is null | Parameter to be calculated, optional type: ANY | BOOL | |
| first(ARG) | Gets the value of the first record in the specified column | Parameter to be calculated, optional type: ANY | ANY | redshift sqlserver tidb mysql db2 mongodb maxcompute |
| jsonget(JSON,KEY) | Gets the specified key of JSON | 1. JSON type parameter, optional type: JSON; 2. Specified key, optional type: STRING | STRING | impala hive spark oracle db2 mongodb maxcompute |
| last(ARG) | Gets the value of the last record in the specified column | Parameter to be calculated, optional type: ANY | ANY | redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_collect(ARG) | Aggregation function, collects the parameters of arg into an array | Parameter to be calculated, optional type: ANY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_collect_flatten(ARR) | Aggregation function, collects and flattens the parameters of arr into an array | Array type parameter, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_count(ARR) | Gets the length of the array | Array type parameter, optional type: ARRAY | NUMBER | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_except(ARR1,ARR2) | Calculates the difference set of two arrays | 1. First array, optional type: ARRAY; 2. Second array, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_intersect(ARR1,ARR2) | Calculates the intersection of two arrays | 1. First array, optional type: ARRAY; 2. Second array, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| list_union(ARR1,ARR2) | Calculates the union of two arrays | 1. First array, optional type: ARRAY; 2. Second array, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| notin(ARG,ARR) | Whether the value of the field is not in the list | 1. Parameter to be calculated, optional type: ANY; 2. Array type parameter, optional type: ARRAY | BOOL | |
| nth(ARG,I) | Gets the i-th value | 1. Parameter to be calculated, optional type: ANY; 2. Integer type parameter, optional type: NUMBER | ANY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| set_collect(ARG) | Aggregation function, collects the parameters of arg into a deduplicated array | Parameter to be calculated, optional type: ANY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql mongodb maxcompute |
| set_collect_flatten(ARR) | Aggregation function, collects and flattens the parameters of arr into a deduplicated array | Array type parameter, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| set_union(ARR1,ARR2) | Calculates the deduplicated union of two arrays | 1. First array, optional type: ARRAY; 2. Second array, optional type: ARRAY | ARRAY | impala hive spark oracle redshift sqlserver tidb mysql db2 mongodb maxcompute |
| case when condition then result1 [else result2] end | This function outputs based on the result of the condition expression. When condition is true, it returns result1. When condition is false, it returns result2, the else branch is not mandatory. Example case when {code} = 1 then 'Pass' else 'Fail' end | condition is an expression that returns a boolean value | ANY | |
| filter(where condition) | This function filters based on the result of the condition expression. If the condition returns true, the current record meets the filter criteria. Example count(1) filter (where state = 'authorized') | condition is an expression that returns a boolean value | ARRAY | |
| calculate(expr) | This function indicates prioritizing the calculation of the aggregation expression. Example SUM({energy}) FILTER (WHERE {logindate} > CALCULATE(min({logindate}))) | Parameter is an aggregation expression | ANY | mongo、hive、impala、Kylin open source edition、hbase、phoenix、elastic search、solr |