Supported functions

This topic describes all built-in functions that are supported by Realtime Compute for Apache Flink.

For Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0, some built-in functions are supported based on Flink V1.12. For more information, see Scalar functions, Table-valued functions, and Aggregate functions. The following tables describe all built-in functions supported by Realtime Compute for Apache Flink based on Flink V1.15.

The supported functions may vary based on the Flink version. For more information about the built-in functions supported by Realtime Compute for Apache Flink, see Flink V1.12 built-in functions, Flink V1.13 built-in functions, Flink V1.14 built-in functions, Flink V1.15 built-in functions, Flink V1.16 built-in functions, and Flink V1.17 built-in functions.

Note

If the functions that you require are not built in Realtime Compute for Apache Flink, you can use user-defined functions (UDFs). For more information about UDFs, see Overview.

Scalar functions

Category	Function	Description
Comparison functions	value1 = value2	Returns true if value1 is equal to value2.
	value1 <> value2	Returns true if value1 is not equal to value2.
	value1 > value2	Returns true if value1 is greater than value2.
	value1 >= value2	Returns true if value1 is greater than or equal to value2.
	value1 < value2	Returns true if value1 is less than value2.
	value1 <= value2	Returns true if value1 is less than or equal to value2.
	value IS NULL	Returns true if the value is NULL.
	value IS NOT NULL	Returns true if the value is not NULL.
	value1 IS DISTINCT FROM value2	Returns true if the data types or values of value1 and value2 are different. All NULL values are considered equal.
	value1 IS NOT DISTINCT FROM value2	Returns true if the data types and values of value1 and value2 are the same. All NULL values are considered equal.
	value1 BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3	Returns true by default or when the ASYMMETRIC keyword is used if the following condition is met: value1 is greater than or equal to value2 and less than or equal to value3.
	value1 NOT BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3	Returns true by default or when the ASYMMETRIC keyword is used if the following condition is met: value1 is less than value2 or greater than value3.
	string1 LIKE string2 [ ESCAPE char ]	Returns true if string1 matches string2.
	string1 NOT LIKE string2 [ ESCAPE char ]	Returns true if string1 does not match string2.
	string1 SIMILAR TO string2 [ ESCAPE char ]	Returns true if string1 matches the SQL regular expression string2.
	string1 NOT SIMILAR TO string2 [ ESCAPE char ]	Returns true if string1 does not match the SQL regular expression string2.
	value1 IN (value2 [, value3]* )	Returns true if value1 exists in the list of values (value2, value3, …).
	value1 NOT IN (value2 [, value3]* )	Returns true if value1 does not exist in the list of values (value2, value3, …).
	EXISTS (sub-query)	Returns true if at least one row of data is returned for the subquery.
	value IN (sub-query)	Returns true if the value is equal to one row of data in the subquery result set.
	value NOT IN (sub-query)	Returns true if the value is not included in the rows of data returned by the subquery.
Logical functions	BITAND	Performs a bitwise AND operation.
	BITNOT	Performs a bitwise NOT operation.
	BITOR	Performs a bitwise OR operation.
	BITXOR	Performs a bitwise XOR operation.
	boolean1 OR boolean2	Returns true if boolean1 or boolean2 is true.
	boolean1 AND boolean2	Returns true if boolean1 and boolean2 are true.
	NOT boolean	Returns true if boolean is false. Returns false if boolean is true. Returns unknown if boolean is unknown.
	boolean IS FALSE	Returns true if boolean is false. Returns false if boolean is true or unknown.
	boolean IS NOT FALSE	Returns true if boolean is true or unknown. Returns false if boolean is false.
	boolean IS TRUE	Returns true if boolean is true. Returns false if boolean is false or unknown.
	boolean IS NOT TRUE	Returns true if boolean is false or unknown. Returns false if boolean is true.
	boolean IS UNKNOWN	Returns true if boolean is unknown. Returns false if boolean is true or false.
	boolean IS NOT UNKNOWN	Returns true if boolean is true or false. Returns false if boolean is unknown.
Arithmetic functions	+ numeric	Returns a numeric value.
	- numeric	Returns a value that is opposite the numeric value.
	numeric1 + numeric2	Returns the sum of numeric1 and numeric2.
	numeric1 - numeric2	Returns the difference of numeric1 and numeric2.
	numeric1 * numberic2	Returns the product of numeric1 multiplied by numeric2.
	numeric1 / numeric2	Returns the quotient of numeric1 divided by numeric2.
	numeric1 % numeric2	Returns the remainder of numeric1 divided by numeric2.
	POWER(numeric1, numeric2)	Returns the value of numeric1 to the power of numeric2.
	ABS(numeric)	Returns the absolute value of the numeric value.
	SQRT(numeric)	Returns the square root of the numeric value.
	LN(numeric)	Returns the natural logarithm of the numeric value with the base number of e.
	LOG10(numeric)	Returns the natural logarithm of the numeric value with the base number of 10.
	LOG2(numeric)	Returns the natural logarithm of the numeric value with the base number of 2.
	LOG(numeric2) LOG(numeric1, numeric2)	Returns the natural logarithm of numeric2 if one input parameter is called. Returns the natural logarithm of numeric2 with the base number of numeric1 if two input parameters are called. The value of numeric2 must be greater than 0 and the value of numeric1 must be greater than 1.
	EXP(numeric)	Returns the result of e raised to the power of numeric.
	CEIL(numeric) CEILING(numeric)	Rounds up a number and returns the smallest integer that is greater than or equal to numeric.
	FLOOR(numeric)	Rounds down a number and returns the largest integer that is less than or equal to numeric.
	SIN(numeric)	Returns the sine of the numeric value.
	SINH(numeric)	Returns the hyperbolic sine of the numeric value. The return value is of the DOUBLE data type.
	COS(numeric)	Returns the cosine of the numeric value.
	TAN(numeric)	Returns the tangent value of the numeric value.
	TANH(numeric)	Returns the hyperbolic tangent value of the numeric value. The return value is of the DOUBLE data type.
	COT(numeric)	Returns the cotangent of the numeric value.
	ASIN(numeric)	Returns the arcsine of the numeric value.
	ACOS(numeric)	Returns the arccosine of the numeric value.
	ATAN(numeric)	Returns the arctangent of the numeric value.
	ATAN2(numeric1, numeric2)	Returns the arctangent of the coordinate (numeric1, numeric2).
	COSH(numeric)	Returns the hyperbolic cosine of the numeric value. The return value is of the DOUBLE data type.
	DEGREES(numeric)	Returns the degree of the radian numeric.
	RADIANS(numeric)	Returns the radian of the degree numeric.
	SIGN(numeric)	Returns the sign of the numeric value.
	ROUND(numeric, INT)	Returns a value rounded to INT decimal places for the numeric value.
	PI()	Returns a value that is extremely close to pi.
	E()	Returns a value that is extremely close to e.
	RAND()	Returns a pseudorandom double-precision value in the range of [0.0, 1.0).
	RAND(INT)	Returns a pseudorandom double-precision value in the range of [0.0, 1.0) with an initial seed of INT.
	RAND_INTEGER(INT)	Returns a pseudorandom integer in the range of [0, INT).
	RAND_INTEGER(INT1,INT2)	Returns a pseudorandom integer in the range of [0, INT2) with an initial seed of INT1.
	UUID()	Returns a Universally Unique Identifier (UUID) string based on RFC 4122 Type 4. The UUID string is pseudo randomly generated.
	BIN(INT)	Returns a string of the INTEGER data type in the binary format.
	HEX(numeric) HEX(string)	Returns an integer NUMERIC value or a string in the hexadecimal format.
	TRUNCATE(numeric1,integer2)	Returns a number that is truncated to the number of decimal places that are specified by integer2.
String functions	string1 \|\| string2	Returns the concatenation of string1 and string2.
	CHAR_LENGTH(string) CHARACTER_LENGTH(string)	Returns the number of characters in a string.
	UPPER(string)	Returns a string in uppercase letters.
	LOWER(string)	Returns a string in lowercase letters.
	POSITION(string1 IN string2)	Returns the position of the first occurrence of string1 in string2. The position starts from 1. Returns 0 if string1 does not exist in string2.
	TRIM([ BOTH \| LEADING \| TRAILING ] string1 FROM string2)	Removes a string that begins, ends, or begins and ends with string1 from string2 and returns the result. This function removes all characters at the end of the string until a character that is not in the TRAILING parameter exists. For example, TRIM(TRAILING 'fe' from 'abcdef') returns abcd.
	LTRIM(string)	Removes the spaces on the left side of a string.
	RTRIM(string)	Removes the spaces on the right side of a string.
	REPEAT(string, int)	Returns a string that repeats the specified string for the number of times that are specified by int.
	REGEXP_REPLACE	Replaces the specified string with the desired string and returns a new string.
	OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ])	Replaces a total number of integer2 characters in string1 with string2 from the position specified by integer1 and returns a new string. By default, integer2 indicates the length of string2.
	SUBSTRING(string FROM integer1 [FOR integer2 ])	Returns a substring that starts from the position specified by integer1 in a string and has a length specified by integer2. By default, integer2 specifies the length from the position integer1 to the end of the string.
	REPLACE(string1,string2, string3)	Replaces all occurrences of string2 with string3 in string1 and returns a new string. string2 and string3 are non-overlapping.
	REGEXP_EXTRACT(string1, string2[,integer])	Splits string1 based on the regular expression specified by string2 and returns the string at the specified position specified by integer.
	INITCAP(string)	Converts a string into a string in the specified format. In this format, the first letter of each word is in uppercase, and the other letters of each word are in lowercase. Each word in the string represents a sequence of alphanumeric characters.
	CONCAT(string1, string2, ...)	Concatenates multiple strings and returns a new string. If an input parameter is set to NULL, NULL is returned.
	CONCAT_WS(string1, string2, string3, ...)	Returns a string that concatenates string2, string3, … with a separator specified by string1.
	LPAD(string1, integer, string2)	Left pads string1 with string2 to the length specified by integer.
	RPAD(string1, integer, string2)	Right pads string1 with string2 to the length specified by integer.
	FROM_BASE64(string)	Decodes string1 by using the Base64 algorithm.
	TO_BASE64(string)	Encodes string1 by using the Base64 algorithm.
	ASCII(string)	Returns the ASCII code of the first character in a string.
	CHR(integer)	Returns the ASCII character that has the binary value equivalent to a value specified by integer.
	DECODE(binary, string)	Decodes a string in the specified encoding format, such as US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, or UTF-16.
	ENCODE(string1, string2)	Encodes a string in the specified encoding format, such as US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, or UTF-16.
	INSTR(string1, string2)	Returns the position of the first occurrence of string2 in string1.
	LEFT(string, integer)	Returns the leftmost characters with a length of integer from a string.
	RIGHT(string, integer)	Returns the rightmost characters with a length of integer from a string.
	LOCATE(string1, string2[, integer])	Returns the position of the first occurrence of string1 in string2 after the position specified by integer.
	PARSE_URL	Parses a specific part of a URL and returns the obtained value.
	REGEXP	Searches for the specified string by using a regular expression and returns a Boolean value based on the search result.
	REVERSE(string)	Returns a string in reverse order.
	SPLIT_INDEX	Splits a string into several segments based on the specified delimiter and returns the field at the specified position.
	STR_TO_MAP(string1[, string2, string3])	Splits string1 into key-value pairs by using a delimiter and returns a map.
	SUBSTR(string[, integer1[, integer2]])	Returns a substring that starts from the position specified by integer1 in a string. The substring has a length specified by integer2.
Time functions	DATE string	Returns a date value in SQL parsed from a string in the yyyy-MM-dd format.
	DATEDIFF	Calculates the difference between two date values.
	DATE_ADD	Returns a date value that is a specified number of days later than a given date.
	DATE_SUB	Returns a date value that is a specified number of days earlier than a given date.
	TIME string	Returns a time value in SQL parsed from a string in the HH:mm:ss format.
	TIMESTAMP string	Returns a timestamp in SQL parsed from a string in the yyyy-MM-dd HH:mm:ss[.SSS] format.
	INTERVAL string range	Parses intervals in milliseconds in SQL from a string in the dd hh:mm:ss.fff format or parses intervals in months in SQL from a string in the yyyy-mm format.
	LOCALTIME	Returns the current time in SQL in the local time zone. The return value is of the TIME(0) type.
	LOCALTIMESTAMP	Returns the current time in SQL in the local time zone. The return value is of the TIMESTAMP(3) type.
	CURRENT_TIME	Returns the current time in SQL in the local time zone. This function is equivalent to the LOCAL_TIME function.
	CURRENT_DATE	Returns the current date in SQL in the local time zone.
	CURRENT_TIMESTAMP	Returns the current timestamp in SQL in the local time zone.
	NOW()	Returns the current timestamp in SQL in the local time zone. This function is equivalent to the CURRENT_TIMESTAMP function.
	CURRENT_ROW_TIMESTAMP()	Returns the current timestamp in SQL in the local time zone.
	EXTRACT(timeinteravlunit FROM temporal)	Returns a value of the LONG data type extracted from the timeintervalunit part of temporal.
	YEAR(date)	Returns the year in which a date value in SQL falls.
	QUARTER(date)	Returns the quarter in which a date value in SQL falls.
	MONTH(date)	Returns the month in which a date value in SQL falls.
	WEEK(date)	Returns the sequence number of the week in the year in which a date value in SQL falls.
	DAYOFYEAR(date)	Returns the sequence number of the day in the year in which a date value in SQL falls.
	DAYOFMONTH	Returns the sequence number of the day in the month in which a date value in SQL falls.
	HOUR(timestamp)	Returns the hour component of a timestamp in SQL.
	MINUTE(timestamp)	Returns the minute component of a timestamp in SQL.
	SECOND(timestamp)	Returns the second component of a timestamp in SQL.
	FLOOR(timepoint TO timeintervalunit)	Returns a value that rounds a point in time down to the time unit that is specified by timeintervalunit.
	CEIL(timespoint TO timeintervaluntit)	Returns a value that rounds a point in time up to the time unit that is specified by timeintervalunit.
	(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)	Returns true if the two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) overlap.
	DATE_FORMAT	Converts a date value of the STRING data type into the specified format.
	DATE_FORMAT_TZ	Converts data of the TIMESTAMP data type into a string of the specified format based on the specified time zone.
	TIMESTAMPADD(timeintervalunit, interval, timepoint)	Returns a new point in time by adding a period of time to a given point in time. The period of time is equivalent to timeintervalunit multiplied by interval.
	TIMESTAMPDIFF(timepointunit,timepoint1, timepoint2)	Returns the time interval between timepoint1 and timepoint2.
	CONVERT_TZ(string1, string2, string3)	Converts a datetime string1 from the time zone string2 into the value of the time zone string3. The datetime string1 is in the default ISO timestamp format yyyy-MM-dd HH:mm:ss.
	FROM_UNIXTIME	Returns a date value of the VARCHAR data type.
	UNIX_TIMESTAMP	Converts a date value into a UNIX timestamp.
	TO_DATE	Converts a date value of the INT or VARCHAR data type to the DATE data type.
	TO_TIMESTAMP_LTZ(numeric, precision)	Converts a time to a value of the TIMESTAMP_LTZ data type. The time is a UNIX timestamp. Unit: milliseconds or seconds.
	TO_TIMESTAMP(string1[, string2])	Converts string1 in the format of string2 in the UTC+0 time zone into a timestamp. By default, the format specified by string2 is yyyy-MM-dd HH:mm:ss.
	TO_TIMESTAMP_TZ	Converts a date value of the VARCHAR data type into the TIMESTAMP data type based on the specified time zone.
	CURRENT_WATERMARK(rowtime)	Returns the current watermark for the given time column property specified by rowtime.
Conditional functions	CASE value WHEN value1_1 [, value1_2]* THEN RESULT1 (WHEN value2_1 [, value2_2 ]* THEN result_2)* (ELSE result_z) END	Returns resultX if the first time value is included in a list of values (valueX_1, valueX_2, …).
	CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END	Returns resultX when the first condition X is met.
	NULLIF(value1,value2)	Returns NULL if value1 is equal to value2. Returns value1 if value1 is not equal to value2.
	COALESCE(value1,value2 [, value3]*)	Returns the first non-NULL value from value1, value2, ….
	IF(condition,true_value, false_value)	Returns true_value if the condition is met. Returns false_value if the condition is not met.
	IFNULL(input,null_replacement)	Returns null_replacement if the input parameter is NULL. Returns the input parameter if the input parameter is not NULL.
	IS_ALPHA(string)	Returns true if all characters in a string are letters. Returns false if any characters in a string are not letters.
	IS_DECIMAL(string)	Returns true if a string can be parsed to a valid number. Returns false if a string cannot be parsed to a valid number.
	IS_DIGIT(string)	Returns true if all characters in a string are digits. Returns false if any characters in a string are not digits.
	GREATEST(value1[,value2]*)	Returns the maximum value of the input parameters. Returns NULL if an input parameter is set to NULL.
	LEAST(value1[,value2]*)	Returns the minimum value of the input parameters. Returns NULL if an input parameter is set to NULL.
Data type conversion functions	CONV	Performs base conversation on numeric values or characters.
	CAST(value AS type)	Returns a new value that is converted into type.
	TYPEOF(input) \| TYPEOF(input, force_serializable)	Returns the string representation of the data type of the input expression.
Collection functions	CARDINALITY(array)	Returns the number of elements in an array.
	array '[' INT ']'	Returns the element at the position specified by INT in an array.
	ELEMENT(array)	Returns the only element of an array whose cardinality is 1.
	CARDINALITY(map)	Returns the number of entries in a map.
	map'[' value ']'	Returns the value that corresponds to the specified key in a map.
JSON functions	IS JSON [ { VALUE \| SCALAR \| ARRAY \| OBJECT } ]	Determines whether a given string is a valid JSON string.
	JSON_EXISTS(jsonValue, path [ { TRUE \| FALSE \| UNKNOWN \| ERROR } ON ERROR ])	Determines whether a JSON string meets the given path search criteria.
	JSON_STRING(value)	Serializes a value into a JSON string that contains the serialized value.
	JSON_VALUE	Extracts the expression of the specified path from a JSON string.
	JSON_QUERY(jsonValue, path [ { WITHOUT \| WITH CONDITIONAL \| WITH UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL \| EMPTY ARRAY \| EMPTY OBJECT \| ERROR } ON EMPTY ] [ { NULL \| EMPTY ARRAY \| EMPTY OBJECT \| ERROR } ON ERROR ])	Extracts a JSON value from a JSON string.
	JSON_OBJECT([[KEY] key VALUE value]* [ { NULL \| ABSENT } ON NULL ])	Generates a JSON object from a list of key-value pairs.
	JSON_OBJECTAGG([KEY] key VALUE value [ { NULL \| ABSENT } ON NULL ])	Aggregates key-value expressions into a JSON object.
	JSON_ARRAY([value]* [ { NULL \| ABSENT } ON NULL ])	Generates a JSON array from a list of values.
	JSON_ARRAYAGG(items [ { NULL \| ABSENT } ON NULL ])	Generates a JSON object by aggregating items into an array.
Value construction functions	-- implicit constructor with parenthesis (value1 [, value2]*)	Returns a row of data created from a list of values (value1, value2, …).
	ARRAY '['value1 [, value2 ]* ']'	Returns an array created from a list of values (value1, value2, …).
	MAP '['value1, value2 [, value3, value4 ]* ']'	Returns a map created from a list of key-value pairs ((value1, value2), (value3, value4), …).
Value acquisition functions	tableName.compositeType.field	Extracts the value of a field of a composite data type of Flink, such as Tuple or POJO, by name.
Value acquisition functions	tableName.compositeType.*	Converts the subtypes of a composite data type of Flink, such as Tuple or POJO, into a separate field.
Grouping functions	GROUP_ID()	Returns an integer that uniquely identifies a combination of grouping keys.
Grouping functions	GROUPING(expression1 [, expression2]* ) \| GROUPING_ID(expression1 [, expression2]* )	Returns a bit vector of the given grouping expressions.
Hash functions	MD5(string)	Returns the MD5 hash value of a string in the format of a string of 32 hexadecimal digits.
	SHA1(string)	Returns the SHA-1 hash value of a string in the format of a string of 40 hexadecimal digits.
	SHA224(string)	Returns the SHA-224 hash value of a string in the format of a string of 56 hexadecimal digits.
	SHA256(string)	Returns the SHA-256 hash value of a string in the format of a string of 64 hexadecimal digits.
	SHA384(string)	Returns the SHA-384 hash value of a string in the format of a string of 96 hexadecimal digits.
	SHA512(string)	Returns the SHA-512 hash value of a string in the format of a string of 128 hexadecimal digits.
	SHA2(string,hashLength)	Returns a hash value by using a hash function, such as SHA-224, SHA-256, SHA-384, or SHA-512.
	MURMUR_HASH	Specifies the 32-bit hash value for a parameter by using the hash32x86 method.
	MURMUR_HASH_64	Specifies the 64-bit hash value for a parameter by using the hash128x64 method.
Other functions	ISNAN	Checks whether the specified floating-point number is NAN (Not a number). This function is similar to the Double.isNan() or Float.isNan() function in Java.
Other functions	KEYVALUE	Splits a string into key-value pairs based on the delimiter specified by split1, splits the key-value pairs based on the delimiter specified by split2, and returns the value that corresponds to the key.

Table-valued functions

Function	Description
GENERATE_SERIES	Generates a series of padding data based on the rule of from, from+1, from+2 ... to-1.
JSON_TUPLE	Retrieves the values that are represented by each path string from a JSON string.
MULTI_KEYVALUE	Splits a string into key-value pairs based on the key-value pair delimiter specified by split1, splits a key-value pair based on the delimiter specified by split2, and returns the values of key_name1 and key_name2.
STRING_SPLIT	Splits a string into substrings based on the specified delimiter and returns a list of substrings.

Aggregate functions

Function	Description
COUNT([ ALL ] expression \| DISTINCT expression1 [, expression2]*)	Returns the number of input rows for an expression that is not NULL by default or if the ALL keyword is used.
COUNT(*) \| COUNT(1)	Returns the number of input rows.
AVG([ ALL \| DISTINCT ] expression)	Calculates the average value of the expressions in all input rows by default or if the ALL keyword is used.
SUM([ ALL \| DISTINCT ] expression)	Returns the sum of the expressions in all input rows by default or if the ALL keyword is used.
MAX([ ALL \| DISTINCT ] expression)	Returns the maximum value of the expressions in all input rows by default or if the ALL keyword is used.
MIN([ ALL \| DISTINCT ] expression )	Returns the minimum value of the expressions in all input rows by default or if the ALL keyword is used.
STDDEV_POP([ ALL \| DISTINCT ] expression)	Returns the population standard deviation of the expressions in all input rows by default or if the ALL keyword is used.
STDDEV_SAMP([ ALL \| DISTINCT ] expression)	Returns the standard deviation of samples of the expressions in all input rows by default or if the ALL keyword is used.
VAR_POP([ ALL \| DISTINCT ] expression)	Returns the standard deviation of samples of the expressions in all input rows by default or if the ALL keyword is used.
VAR_SAMP([ ALL \| DISTINCT ] expression)	Returns the sample variance of the expressions in all input rows by default or if the ALL keyword is used.
COLLECT([ ALL \| DISTINCT ] expression)	Returns multiple expressions across all input rows by default or if the ALL keyword is used.
VARIANCE([ ALL \| DISTINCT ] expression)	This function is equivalent to the VAR_SAMP() function.
RANK()	Returns the rank of a value in a group of values.
DENSE_RANK()	Returns the rank of a value in a group of values. The result is the sum of 1 and the previously assigned rank value.
ROW_NUMBER()	Calculates the sequence number of a row based on the sequence of rows in a window partition. The row number starts from 1.
LEAD(expression [, offset] [, default])	Returns the expression value at the offset row of data that follows the current row in a window. Note The LEAD function is not supported when a deployment is in streaming mode.
LAG(expression [, offset] [, default])	Returns the expression value at the offset row of data that precedes the current row in the window.
LISTAGG(expression [, separator])	Concatenates the values of a string expression and separates the values with a delimiter.
APPROX_COUNT_DISTINCT	Improves the performance of your deployments.
FIRST_VALUE	Returns the first non-NULL record of a data stream.
LAST_VALUE	Returns the last non-NULL record of a data stream.