Function overview |V3.2.4|OceanBase Database| docs|Distributed Database

Function overview

Last Updated：2023-10-27 09:57:43 Updated

You can use OceanBase built-in functions directly in SQL statements. The values passed to an SQL function must be of the supported data types. If the values passed to an SQL function are not of a data type expected by the SQL function, OceanBase Database converts the values to the supported data type before it executes the SQL function.

A function is similar to an operator. You can pass some data elements to a function as the input parameters, and then the function returns the results. However, a function differs greatly from an operator in terms of input parameters. Unlike an operator that has a fixed number of parameters, a function can have a variable number of parameters, which can be one, two, or more.

Function name (parameter, parameter...)

Functions may also have no parameters, similar to pseudocolumns. However, unlike pseudocolumns that usually return a different value for each row in the result set, functions without variables return the same value for each row.

Null values in a function

For most functions, the value returned for a null input parameter value is null. In this case, you can use the NVL function to return a non-null value. For example, in a commission records table, if the commission column commitsion_pct is NULL, the NVL(commission_pct,0) expression returns 0. If commitsion_pct is not NULL, the expression returns the actual commission value.

Function categories and lists

The following lists describe the data types of parameters and return values for each type of function.

Notice

When you apply SQL functions to LOB columns, OceanBase Database creates temporary LOB columns when it processes the SQL or PL/SQL statements and some restrictions apply. For more information, see Compatibility with Oracle.

In this chapter, functions are divided into two categories:

Single-row functions, including numeric functions, character functions that return characters, character functions that return numbers, datetime functions, general comparison functions, conversion functions, encoding and decoding functions, NULL-related functions, environment and identifier functions, and hierarchical functions.
Statistical functions, including aggregate functions and analytic functions

A single-row function returns a result value for each row in a queried table or view. A single-row function can be used in the SELECT, WHERE, START WITH, CONNECT BY, or HAVING clause in an SQL statement.

Analytic and aggregate functions calculate an aggregate value based on a group of rows. The difference is that aggregate functions return only one value (one row) for each group, whereas analytic functions return multiple rows for each group. A rowset group is also called a window. An aggregate function is usually used together with the GROUP BY clause in a SELECT statement. In this case, the database divides the rows in a queried table or view into groups, applies an aggregate function to each group of rows, and returns one result row for each group.

For an analytic function, you need to use the OVER keyword to specify the window. For more information about window functions, see Window functions.

Numeric functions

The input and output of a numeric function are both numeric values. The data type of the return value of most numeric functions is NUMBER and can be accurate to 38 decimal places. Some advanced algebraic functions, such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH(), are accurate to 36 decimal places. Other algebraic functions, such as ACOS(), ASIN(), ATAN(), and ATAN2(), are accurate to 30 decimal places.

Category	Subcategory	Function	Description
Single-row function	Numeric function	ABS	Returns the absolute (positive) value of the specified numeric expression.
Single-row function	Numeric function	ACOS	Returns an angle in radians whose cosine is a given `NUMBER` expression, which is also known as arccosine.
Single-row function	Numeric function	ASIN	Returns the arc sine of a number.
Single-row function	Numeric function	ATAN	Returns the arc tangent of a number.
Single-row function	Numeric function	ATAN2	Returns the arc tangent of `y` and `x`, which is the angle between the ray from (0,0) to the point (x,y) and the x-axis.
Single-row function	Numeric function	BITAND	Performs the bitwise AND operation. Both the input and output are the same `INT` type.
Single-row function	Numeric function	CEIL	Returns the smallest integer that is greater than or equal to the value of `numeric_expression`.
Single-row function	Numeric function	COS	Returns the cosine of an angle.
Single-row function	Numeric function	COSH	Returns the hyperbolic cosine of a number.
Single-row function	Numeric function	EXP	Returns the value of e raised to the power specified by numeric_expression.
Single-row function	Numeric function	FLOOR	Returns the largest integer that is smaller than or equal to the value of `numeric_expression`.
Single-row function	Numeric function	LN	Returns the logarithm of `numeric_expression` to base e.
Single-row function	Numeric function	LOG	Returns the logarithm of `y` to base `x`.
Single-row function	Numeric function	MOD	Returns the remainder of `x` divided by `y`.
Single-row function	Numeric function	NANVL	Determines if the input value `n1` is not a number (`NaN`) and returns the result.
Single-row function	Numeric function	POWER	Returns `x` raised to the power of `y`.
Single-row function	Numeric function	REMAINDER	Returns the remainder of `x` divided by `y`.
Single-row function	Numeric function	ROUND	Returns a rounded `numeric` value.
Single-row function	Numeric function	SIGN	Returns the sign of `n`. The function returns 1 if n > 0, -1 if n < 0, and 0 if n = 0.
Single-row function	Numeric function	SIN	Returns the sine of an angle.
Single-row function	Numeric function	SINH	Returns the hyperbolic sine of a number.
Single-row function	Numeric function	SQRT	Returns the square root of `n`.
Single-row function	Numeric function	TAN	Returns the tangent of an angle.
Single-row function	Numeric function	TANH	Returns the hyperbolic tangent of a number.
Single-row function	Numeric function	TRUNC	Returns a `numeric` value truncated to a given `precision`.
Single-row function	Numeric function	WIDTH_BUCKET	Creates equal-width buckets based on the given value and returns the bucket number into which the value of `expr` falls. Equal-width buckets are equiwidth histograms, in which the histogram range is divided into intervals that have an identical size.

Character function that returns characters

The maximum length of the return value of a function is subject to the data type. Assume that the return value of a function is of the VARCHAR2 data type. If the return value exceeds the maximum limit of the VARCHAR2 data type, OceanBase Database truncates the result and returns the truncated value. No error is returned to the client.

Notice

If the data type of the return value is CLOB, when the length of the return value exceeds the maximum limit, OceanBase Database does not return the value but returns an error.

Category	Subcategory	Function	Description
Single-row function	Character function that returns characters	CHR	Converts `n` to one or more equivalent characters and returns these characters. The return value is associated with the character set of the system.
Single-row function	Character function that returns characters	CONCAT	Concatenates two character strings.
Single-row function	Character function that returns characters	INITCAP	Returns a string with the first letter of each word in uppercase and other letters in lowercase.
Single-row function	Character function that returns characters	LOWER	Converts a string to lowercase.
Single-row function	Character function that returns characters	LPAD	Left-pads `c1` with `c2` to `n` characters in length.
Single-row function	Character function that returns characters	LTRIM	Removes characters from the left end of a string.
Single-row function	Character function that returns characters	REGEXP_REPLACE	Replaces a sequence of characters that match a regular expression with another set of characters.
Single-row function	Character function that returns characters	REGEXP_SUBSTR	Searches a string for a regular expression pattern and returns the matching substring. It is an extension of the `SUBSTR` function.
Single-row function	Character function that returns characters	REPLACE	Replaces all occurrences of a substring in a string with a new substring.
Single-row function	Character function that returns characters	RPAD	Right-pads `c1` with `c2` to `n` characters in length.
Single-row function	Character function that returns characters	RTRIM	Removes characters from the right end. This function can be very useful in formatting the output of a query.
Single-row function	Character function that returns characters	SUBSTR	Returns a substring. A multi-byte character, for example, a Chinese character or a full-width character, counts as one character.
Single-row function	Character function that returns characters	TRANSLATE	Replaces specified characters in a character expression with new characters. A multi-byte character, for example, a Chinese character or a full-width character, counts as one character.
Single-row function	Character function that returns characters	TRANSLATE ... USING	Converts characters into the specified database character set or national character set
Single-row function	Character function that returns characters	TRIM	Removes the leading and/or trailing characters of a string.
Single-row function	Character function that returns characters	UPPER	Converts a string into all uppercase.

Character functions that return numbers

Category	Subcategory	Function	Description
Single-row function	Character function that returns numbers	ASCII	Returns the ASCII code value of the leftmost character in a character expression.
Single-row function	Character function that returns numbers	INSTR	Searches for a specified character in a string and returns the position of the character that is found.
Single-row function	Character function that returns numbers	LENGTH	Returns the length of a string.
Single-row function	Character function that returns numbers	REGEXP_COUNT	Returns the number of occurrences of a regular expression in the source string.
Single-row function	Character function that returns numbers	REGEXP_INSTR	Returns the location of the substring that matches a regular expression pattern in the source string.

Datetime functions

Datetime functions operate on three input data types: date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

Functions that support only the DATE data type include ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY.

For these functions, if you give a TIMESTAMP value, OceanBase Database implicitly converts the value before it passes the value to the function, and returns a value of the DATE data type.

We recommend that you execute SELECT * FROM NLS_DATABASE_PARAMETERS to check the current NLS format before you use a datetime function:

obclient> SELECT * FROM NLS_DATABASE_PARAMETERS;
+-------------------------+------------------------------+        
| PARAMETER               | VALUE                        |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT         | DD-MON-RR                    |
| NLS_TIMESTAMP_FORMAT    | YYYY-MM-DD HH24:MI:SS        |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TERRITORY           | AMERICA                      |
| NLS_SORT                | BINARY                       |
| NLS_COMP                | BINARY                       |
| NLS_CHARACTERSET        | AL32UTF8                     |
| NLS_NCHAR_CHARACTERSET  | AL16UTF16                    |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_NUMERIC_CHARACTERS  | .,                           |         
+-------------------------+------------------------------+
13 rows in set

If the format is inconsistent with that in the previous example, you can execute the following command to change the format:

obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected 

obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected 
/* Specify 9 decimal places for fractional seconds in the return value */

obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected

Notice

MONTHS_BETWEEN returns a number.

ROUND and TRUNC cannot implicitly convert one data type to another. Therefore, you must provide a value of the DATE data type for these two functions. Otherwise, an error is returned.

Other functions accept all three types of parameters and return a value of the same data type as the input parameters.

Category	Subcategory	Function	Description
Single-row function	Datetime function	ADD_MONTHS	Returns a date of `date` plus `n` month. If the value of `n` is negative, the function returns a date of `date` minus `n` months.
Single-row function	Datetime function	CURRENT_DATE	Returns the current date in the time zone of the current session.
Single-row function	Datetime function	CURRENT_TIMESTAMP	Returns the current date in the time zone of the current session, in a value of the `TIMESTAMP WITH TIME ZONE` data type. The return value contains the time zone information.
Single-row function	Datetime function	DBTIMEZONE	Returns the time zone of the current database instance. In OceanBase Database, the database time zone is set to +00:00 and cannot be modified.
Single-row function	Datetime function	EXTRACT (datetime)	Extracts the year, month, day, hour, minute, and second from the specified time field or expression.
Single-row function	Datetime function	FROM_TZ	Combines a `TIMSTAMP` value and a time zone value into a `TIMESTAMP WITH TIME ZONE` value.
Single-row function	Datetime function	LAST_DAY	Returns the date of the last day of the month that contains `date`.
Single-row function	Datetime function	LOCALTIMESTAMP	Returns the current date in the time zone of the current session, in a value of the `TIMESTAMP` data type.
Single-row function	Datetime function	MONTHS_BETWEEN	Returns the number of months between `date1` and `date2`.
Single-row function	Datetime function	NEW_TIME	OceanBase Database does not support this function.
Single-row function	Datetime function	NEXT_DAY	Returns the date of the day of week specified by `c1` in the week after the week that contains `d1`.
Single-row function	Datetime function	NUMTODSINTERVAL	Converts `n` to an `INTERVAL DAY TO SECOND` value in the unit specified by `interval_unit`.
Single-row function	Datetime function	NUMTOYMINTERVAL	Converts `n` to an `INTERVAL YEAR TO MONTH` value in the unit specified by `interval_unit`.
Single-row function	Datetime function	ROUND (date)	Returns `date` rounded to the unit specified by `fmt`.
Single-row function	Datetime function	SESSIONTIMEZONE	Returns the time zone of the current session.
Single-row function	Datetime function	SYS_EXTRACT_UTC	Returns the Coordinated Universal Time (UTC) that corresponds to the specified time.
Single-row function	Datetime function	SYSDATE	Returns the current date.
Single-row function	Datetime function	SYSTIMESTAMP	Returns the current system date, including the time zone and fractional seconds with 6 decimal places of precision.
Single-row function	Datetime function	TO_CHAR (datetime)	Converts a value of the `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`, `INTERVAL DAY TO SECOND`, or `INTERVAL YEAR TO MONTH` data type to a value of the `VARCHAR2` data type in the format specified by `fmt`.
Single-row function	Datetime function	TO_DSINTERVAL	Converts a string of the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `INTERVAL DAY TO SECOND` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Datetime function	TO_TIMESTAMP	Converts a string to a value of the `TIMESTAMP` data type.
Single-row function	Datetime function	TO_TIMESTAMP_TZ	Converts a string to a value of the `TIMESTAMP WITH TIME ZONE` data type.
Single-row function	Datetime function	TO_YMINTERVAL	Converts a string of the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `INTERVAL YEAR TO MONTH` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Datetime function	TRUNC (date)	Returns `date` truncated to the nearest date in the unit specified by `fmt`. In addition, the return value must be earlier than the value of `date`.
Single-row function	Datetime function	TZ_OFFSET	Returns the time zone offset of the `n` time zone. A time zone offset is the difference (in hour and minute) from the Greenwich Mean Time (GMT).

General comparison functions

General comparison functions allow you to quickly locate the maximum and minimum values in a data set.

Category	Subcategory	Function	Description
Single-row function	General comparison function	GREATEST	Returns the maximum value in a list of one or more expressions.
Single-row function	General comparison function	LEAST	Returns the minimum value in a list of one or more expressions.

Conversion functions

Conversion functions allow you to convert a source data type into another data type.

Category	Subcategory	Function	Description
Single-row function	Conversion function	ASCIISTR	Converts a string in any character set to an ASCII string by using the database character set.
Single-row function	Conversion function	BIN_TO_NUM	OceanBase Database does not support this function.
Single-row function	Conversion function	CAST	Converts one built-in data type to another built-in data type.
Single-row function	Conversion function	CHARTOROWID	Converts a value from the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to the `ROWID` data type.
Single-row function	Conversion function	CONVERT	Converts a string from one character set to another.
Single-row function	Conversion function	HEXTORAW	Converts a string that contains hexadecimal digits of the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the RAW data type.
Single-row function	Conversion function	NUMTODSINTERVAL	Adds an interval unit to a numeric expression to convert the expression to a value of the `INTERVAL DAY TO SECOND` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Conversion function	NUMTOYMINTERVAL	Adds an interval unit to a numeric expression to convert the expression to a value of the `INTERVAL YEAR TO MONTH` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Conversion function	RAWTOHEX	Converts a binary number to a hexadecimal string.
Single-row function	Conversion function	ROWIDTOCHAR	Converts a `rowid` value to a value of the `VARCHAR2` data type.
Single-row function	Conversion function	ROWIDTONCHAR	Converts a `rowid` value to a value of the `NVARCHAR2` data type.
Single-row function	Conversion function	TO_BINARY_DOUBLE	Returns a 64-bit double-precision floating-point number.
Single-row function	Conversion function	TO_BINARY_FLOAT	Returns a 32-bit single-precision floating-point number.
Single-row function	Conversion function	TO_BLOB	Converts `LONG RAW` and `RAW` values to `BLOB` values.
Single-row function	Conversion function	TO_CHAR (character)	Converts a value of the `NCHAR`, `NVARCHAR2`, or `CLOB` data type to the database character set.
Single-row function	Conversion function	TO_CHAR (datetime)	Converts a value of the `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`, `INTERVAL DAY TO SECOND`, or `INTERVAL YEAR TO MONTH` data type to a value of the `VARCHAR2` data type in the format specified by `fmt`.
Single-row function	Conversion function	TO_CHAR (number)	Converts `n` of the `NUMBER`, `BINARY_FLOAT`, or `BINARY_DOUBLE` data type to a value of the `varchar2` data type in the format specified by `fmt`.
Single-row function	Conversion function	TO_CLOB	Converts an `NCLOB` value in a `LOB` column or other strings to a `CLOB` value.
Single-row function	Conversion function	TO_DATE	Converts a string of the `CHAR`, `VARCHAR`, `NCHAR`, or `NVARCHAR2` data type to a value of the DATE data type.
Single-row function	Conversion function	TO_DSINTERVAL	Converts a string of the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `INTERVAL DAY TO SECOND` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Conversion function	TO_MULTI_BYTE	Converts single-byte characters to their corresponding multi-byte characters.
Single-row function	Conversion function	TO_NUMBER	Converts `expr` to a numeric value.
Single-row function	Conversion function	TO_NCHAR (character)	Converts a value of the `CHAR`, `VARCHAR2`, `CLOB`, or `NCLOB` data type to the national character set and returns a value of the `NVARCHAR2` data type.
Single-row function	Conversion function	TO_NCHAR (datetime)	Converts a value of the `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`, `INTERVAL YEAR TO MONTH`, or `INTERVAL DAY TO SECOND` data type from the database character set to the national character set.
Single-row function	Conversion function	TO_NCHAR (number)	Converts a numeric value of the `NUMBER`, `BINARY_FLOAT`, or `BINARY_DOUBLE` data type to a string in the national character set.
Single-row function	Conversion function	TO_SINGLE_BYTE	Converts multi-byte characters to their corresponding single-byte characters.
Single-row function	Conversion function	TO_TIMESTAMP	Converts a string to a value of the `TIMESTAMP` data type.
Single-row function	Conversion function	TO_TIMESTAMP_TZ	Converts a string to a value of the `TIMESTAMP WITH TIME ZONE` data type.
Single-row function	Conversion function	TO_YMINTERVAL	Converts a string of the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of the `INTERVAL YEAR TO MONTH` data type. This function can be used to perform addition and subtraction operations on a datetime value.
Single-row function	Conversion function	UNISTR	Converts text literals or Unicode encoding values to strings in the national character set.

Encoding and decoding functions

You can use encoding and decoding functions in OceanBase Database to encode and decode data.

Category	Subcategory	Function	Description
Single-row function	Encoding and decoding function	DECODE	Returns the corresponding value based on the conditions.
Single-row function	Encoding and decoding function	DUMP	Returns a value containing the data type code, length in bytes, and internal representation of the given expression.
Single-row function	Encoding and decoding function	ORA_HASH	Generates the HASH value for an expression
Single-row function	Encoding and decoding function	VSIZE	Returns the number of bytes of `X`.

Category	Subcategory	Function	Description
Single-row function	NULL-related function	COALESCE	Returns the first non-null expression in the parameter list that contains at least two parameters.
Single-row function	NULL-related function	LNNVL	Verifies whether one or two operands of a condition is `NULL`.
Single-row function	NULL-related function	NANVL	Determines if the input value `n1` is not a number (`NaN`) and returns the result.
Single-row function	NULL-related function	NULLIF	Determines whether two expressions are equal.
Single-row function	NULL-related function	NVL	Returns a non-NULL value from two expressions. If the results of `expr1` and `expr2` are both NULL, the NVL function returns NULL.
Single-row function	NULL-related function	NVL2	Returns different values based on whether the expression is null.

Environment functions

Environment functions mainly provide environment information of sessions or tenant instances.

Category	Subcategory	Function	Description
Single-row function	Environment function	SYS_CONTEXT	Returns the value of a parameter associated with the context namespace at the current instant.
Single-row function	Environment function	SYS_GUID	Generates and returns a 16-byte globally unique identifier, namely, a globally unique sequence number.
Single-row function	Environment function	UID	Returns the ID of the current user.
Single-row function	Environment function	USER	Returns the username of the current user.

Hierarchical functions

Category	Subcategory	Function	Description
Single-row function	Hierarchical function	SYS_CONNECT_BY_PATH	Returns the path of a column value from root to node. The column values for each row returned by the `CONNECT BY` condition are separated by the specified separator.

Aggregate functions

Category	Subcategory	Function	Description
Statistical function	Aggregate function	APPROX_COUNT_DISTINCT	Returns the number of distinct rows in the parameter column.
Statistical function	Aggregate function	AVG	Returns the average value of a numeric value column.
Statistical function	Aggregate function	CORR	Calculates the coefficient of correlation of a set of number pairs.
Statistical function	Aggregate function	COUNT	Queries the number of rows of `expr`.
Statistical function	Aggregate function	COVAR_POP	Calculates the population covariance of a set of number pairs.
Statistical function	Aggregate function	COVAR_SAMP	Calculates the sample covariance of a set of number pairs.
Statistical function	Aggregate function	CUME_DIST	Calculates the cumulative distribution of a value in a group of values.
Statistical function	Aggregate function	DENSE_RANK	Calculates the rank of a row in an ordered group of rows.
Statistical function	Aggregate function	GROUPING	Distinguishes a `NULL` in a superaggregate row generated by an extension (such as `ROLLUP`) of the `GROUP BY` clause from a `NULL` in a regular row.
Statistical function	Aggregate function	KEEP	Sorts the values in a set of rows based on a given sorting rule and returns the first or last value.
Statistical function	Aggregate function	LISTAGG	Sorts data in each group specified in the `ORDER BY` clause and then concatenates the values of the measure column. This function is used to convert a column into a row.
Statistical function	Aggregate function	MAX	Returns the maximum value in the specified column.
		MEDIAN	Returns the median in a set of values. The median is the middle value after the values are sorted.
Statistical function	Aggregate function	MIN	Returns the minimum value in the specified column.
Statistical function	Aggregate function	PERCENT_RANK	Calculates the cumulative distribution of a value in a group of values.
Statistical function	Aggregate function	PERCENTILE_CONT	An inverse distribution function that assumes a continuous distribution model. It returns an interpolated value that falls into the specified percentile value with respect to the sort specification.
Statistical function	Aggregate function	PERCENTILE_DISC	Returns the value with the smallest cumulative distribution value (with respect to the same sort specification) that is greater than or equal to the specified percentile value.
Statistical function	Aggregate function	RANK	Returns the rank of a value in a list of values.
Statistical function	Aggregate function	REGR_ (Linear Regression)	A linear regression function. It fits an ordinary-least-squares regression line to a set of number pairs.
Statistical function	Aggregate function	ROLLUP	Returns a subtotal for values of each data group and returns a grand total for values of all groups during statistical data analysis and report generation.
Statistical function	Aggregate function	STDDEV	Calculates the standard deviation.
Statistical function	Aggregate function	STDDEV_POP	Calculates the population standard deviation.
Statistical function	Aggregate function	STDDEV_SAMP	Calculates the sample standard deviation.
Statistical function	Aggregate function	SUM	Returns the sum of values in the specified column.
Statistical function	Aggregate function	VAR_POP	Returns the population variance of a non-null set of numbers, with `NULL`s ignored.
Statistical function	Aggregate function	VAR_SAMP	Returns the sample variance of a set of numbers, with `NULL`s ignored.
Statistical function	Aggregate function	VARIANCE	Returns the variance of the specified column.
Statistical function	Aggregate function	APPROX_COUNT_DISTINCT	Returns the approximate number of unique rows in a column. You can use this function to further calculate the selectivity of referenced columns.

Analytic function

Category	Subcategory	Function	Description
Statistical function	Analytic function	AVG	Returns the average value of a numeric value column.
Statistical function	Analytic function	CORR	Calculates the coefficient of correlation of a set of number pairs.
Statistical function	Analytic function	COUNT	Queries the number of rows of `expr`.
Statistical function	Analytic function	COVAR_POP	Calculates the population covariance of a set of number pairs.
Statistical function	Analytic function	COVAR_SAMP	Calculates the sample covariance of a set of number pairs.
Statistical function	Analytic function	CUME_DIST	Calculates the cumulative distribution of a value within a group of values.
Statistical function	Analytic function	DENSE_RANK	Calculate the rank of a row in an ordered group of rows and returns the rank as a `NUMBER`.
Statistical function	Analytic function	FIRST_VALUE	Returns the first value in an ordered group of values.
Statistical function	Analytic function	KEEP	Sorts the values in a set of rows based on a given sorting rule and returns the first or last value.
Statistical function	Analytic function	LAG	Provides access to multiple rows of a table without a self-join.
Statistical function	Analytic function	LAST_VALUE	Returns the last value in an ordered group of values.
Statistical function	Analytic function	LEAD	Provides access to multiple rows in a table without a self join. Given some rows returned by a query and a position of the cursor, the `LEAD` function provides access to a row at a given physical offset beyond the given position.
Statistical function	Analytic function	LISTAGG	Converts a column into a row.
Statistical function	Analytic function	MAX	Returns the maximum value in the specified column.
Statistical function	Analytic function	MEDIAN	Returns the median in a set of values. The median is the middle value after the values are sorted.
Statistical function	Analytic function	MIN	Returns the minimum value in the specified column.
Statistical function	Analytic function	NTILE	Divides an ordered data set into several buckets specified by `expr` and assigns an appropriate bucket number to each row.
Statistical function	Analytic function	NTH_VALUE	Returns the value of `measure_expr` in the `n`-th row in the window defined by `analytic_clause`.
Statistical function	Analytic function	PERCENT_RANK	Similar to the `CUME_DIST` (cumulative distribution) function. The return value ranges from 0 to 1. The `PERCENT_RANK` function for the first row in any set is 0 and the return value is NUMBER.
Statistical function	Analytic function	PERCENTILE_CONT	An inverse distribution function that assumes a continuous distribution model. It returns an interpolated value that falls into the specified percentile value with respect to the sort specification.
Statistical function	Analytic function	PERCENTILE_DISC	Returns the value with the smallest cumulative distribution value (with respect to the same sort specification) that is greater than or equal to the specified percentile value.
Statistical function	Analytic function	RANK	Determines the rank of a group of values based on the `ORDER BY` expression in the `OVER` clause.
Statistical function	Analytic function	RATIO_TO_REPORT	Returns the ratio of a value to the sum of a group of values.
Statistical function	Analytic function	REGR_ (Linear Regression)	A linear regression function. It fits an ordinary-least-squares regression line to a set of number pairs.
Statistical function	Analytic function	ROW_NUMBER	Assigns a unique number to each row where it is applied.
Statistical function	Analytic function	STDDEV	Calculates the standard deviation.
Statistical function	Analytic function	STDDEV_POP	Calculates the population standard deviation.
Statistical function	Analytic function	STDDEV_SAMP	Calculates the sample standard deviation.
Statistical function	Analytic function	SUM	Returns the sum of values in the specified column.
Statistical function	Analytic function	VAR_POP	Returns the population variance for a group of values, with `NULL`s ignored.
Statistical function	Analytic function	VAR_SAMP	Returns the sample variance of a set of numbers, with `NULL`s ignored.
Statistical function	Analytic function	VARIANCE	Returns the variance of the specified column.
Statistical function	Analytic function	WMSYS.WM_CONCAT/WM_CONCAT	Concatenates the values of the measure column. This function is used to convert a column into a row.

More information

For more information about the keyword OVER in analytic functions, see Window functions.