Functions
The following table describes the IBM® Netezza® SQL functions and analytic functions that display in the nzsql command help.
Function | Description | Syntax |
---|---|---|
AVG | Returns the average of the expression. | AVG(column reference | value expression | *) over(window_spec) |
COUNT | Returns the number of rows in the query. | COUNT(column reference | value expression | *) over(window_spec) |
CURRENT CATALOG | Returns the current catalog name (database name) | CURRENT_CATALOG |
CURRENT DATE | Returns the current date | CURRENT_DATE |
CURRENT PATH | Returns the current PATH value | CURRENT_PATH |
CURRENT SCHEMA | Returns the current schema name (user name) | CURRENT_SCHEMA |
CURRENT TIME | Returns the current local time | CURRENT_TIME |
CURRENT TIMESTAMP | Returns the current date and time | CURRENT_TIMESTAMP |
CURRENT TRANSACTION PATH | Returns the current path value after changing the path within a transaction or stored procedure. | CURRENT_TX_PATH |
CURRENT TRANSACTION SCHEMA | Returns the name of the current schema inside a transaction block or procedure body. The value is null unless a schema is set using SET SCHEMA inside the transaction block or stored procedure. | CURRENT_TX_SCHEMA |
CURRENT USER | Returns the current user name | CURRENT_USER |
DATE PART | Extracts subfield from date/time value or extracts subfield from interval value. Similar to EXTRACT | DATE_PART('text', timestamp)
|
DATE TRUNC | Truncates the date to a specified precision | DATE_TRUNC(text, timestamp) |
DENSE RANK | Calculates the rank of a row in an ordered group of rows. | DENSE_RANK() over(window_spec) |
EXTRACT | Extracts the subfield from date/time value or the subfield from interval value | EXTRACT(identifier from timestamp)
|
FIRST VALUE | Returns the first value in an ordered set of values. | FIRST_VALUE(column reference | value expression | *)
over(window_spec) |
LAG | Provides access to more than one row of a table at the same time without a self-join at a given offset before that position. | LAG(value_expression [, offset [, default]]) over(window_spec) |
LAST VALUE | Returns the last value in an ordered set of values. | LAST_VALUE(column reference | value expression | *)
over(window_spec) |
LEAD | Provides access to more than one row of a table at the same time without a self-join at a given offset beyond that position. | LEAD(value_expression [, offset [, default]]) over(window_spec) |
LOWER | Converts a string to lowercase | LOWER(string) |
MAX | Returns the maximum value of the expression. | MAX(column reference | value expression | *) over(window_spec) |
NOW | Returns the current date and time (equivalent to current_timestamp) | NOW() |
POSITION | Locates the specified substring | POSITION(substring in string) |
RANK | Calculates the rank of a value in a group of values. | RANK() over(window_spec) |
ROW NUMBER | Assigns a unique number to each row to which it is applied. | ROW_NUMBER() over(window_spec) |
STDDEV | Returns the standard deviation of the expression, which is the square root of the variance. | STDDEV(column reference | value expression | *) over(window_spec) |
STDDEV POPULATION | Returns the population standard deviation, this is the same as the square root of the var_pop function. | STDDEV_POP(column reference | value expression | *)
over(window_spec) |
STDDEV SAMPLE | Returns the sample standard deviation, this is the same as the square root of the var_samp function. | STDDEV_SAMP(column reference | value expression | *)
over(window_spec) |
SUBSTRING | Extracts a substring from a string | SUBSTRING(string [from integer] [for integer]) |
SUM | Returns the sum of the expression. | SUM(column reference | value expression | *) over(window_spec) |
TIMEOFDAY | Returns high-precision date and time | TIMEOFDAY() |
TIMESTAMP | Converts a date to a timestamp Combines date and time into a timestamp |
TIMESTAMP(date)
|
TO CHAR | Converts the following data types into string:
|
to_char(timestamp, text)
|
TO DATE | Converts a string to a date | to_date(text, text) |
TO NUMBER | Converts a string to a numeric | to_number(text, text) |
TO TIMESTAMP | Converts a string to a timestamp | to_timestamp(text, text) |
TRIM | Removes the longest string that contains only the characters (a space by default) from the beginning/end/both ends of the string | TRIM([leading | trailing | both] [characters] from
string) |
UPPER | Converts a string to uppercase text | UPPER(string) |
VARIANCE | Returns the variance of the expression. | VARIANCE(column reference | value expression | *) over(window_spec) |
VARIANCE POPULATION | Returns the population variance. | VAR_POP(column reference | value expression | *) over(window_spec) |
VARIANCE SAMPLE | Returns the sample variance. | VAR_SAMP(column reference | value expression | *) over(window_spec) |