Supported Functions and Formulas

This section describes the standard and advanced functions that are available when working with Hi-Res Analytics. For information about using functions in dashboards, see Calculating Values in Lenses and Filters.

Functions on Strings

The table below details the Anzo functions for string data types.

Function (syntax)
Argument: Data Type
Description Return Type
BUSINESS_ENTITY_EXCLUDER(text)
text: string
Removes from strings suffixes that represent business entities. String
CONCATURL(text, ...)
text: string
Concatenates the values for the specified properties or expressions and returns the concatenation as an xsd:anyURI value. URI
CONCATENATE(text, ...)
text: string
Concatenates the values for the specified properties or expressions and returns the concatenation as an xsd:string value. String
CONTAINS(text, pattern)
text: string
pattern: string
Determines if the values for a property contain the specified string. Results are grouped under True or False. Boolean
ENCODE_FOR_URI(text)
text: string
Returns results encoded as URIs. URI
ESCAPEHTML(text)
text: string
Escapes the specified string for use in HTML. String
FIND(find_text, within_text, start_num)
find_text: string
within_text: string
start_num: integer
Determines if the specified text exists in another text string. Integer
GROUPCONCAT(separator, valueSeparator, serialize, valueLimit, rowLimit, delimitBlanks, text)
separator: string
valueSeparator: string
serialize: boolean
valueLimit: integer
rowLimit: integer
delimitBlanks: boolean
text: string
Performs a string concatenation all of the values that are bound to a property. String
LANG(value)
value: string
Returns any language tags that exist for the specified property's literal values and groups the results under any language tags or "blank" if a language tag does not exist for a record. String
LANGMATCHES(language_tag, language_range)
language_tag: string
language_range: string
Determines whether any of the values for a property contain a language tag from the specified range of tags. Boolean
LCASE(value)
value: string literal
Converts string values to lower case in the filter. String
LEFT(text, number_of_characters)
text: string
number_of_characters: integer
Returns the specified number of characters starting from the left of the string. String
LEN(value)
value: string
Calculates the length of the string values. Integer
LEVENSHTEIN_DIST(value1, value2)
value1: string
value2: string
Calculates the Levenshtein distance or measure of similarity between the specified strings. The distance is the number of edits required to transform the first string into the second string. Integer
LOWER(text, language, country, variant)
text: string
language: string
country: string
variant: string
Converts string values to lower case letters. String
MD5(value)
value: term
Calculates the MD5 hash of string values. String
MID(text, start_num, num_chars)
text: string
start_num: integer
num_chars: integer
Returns the specified number of characters from a string, starting from the chosen position in the string. String
REGEX(text, pattern, [flags])
text: string
pattern: string
flags: string
Determines whether the specified string matches a regular expression pattern. You can use the optional flags argument to include one or more modifier flags that further define the pattern. For information about flags, see the Flags section of the W3C XQuery 1.0 and XPath 2.0 Functions and Operators specification. Boolean
REPLACE(text, pattern, replacement, flags)
text: string
pattern: string
replacement: string
flags: string
Extends the REGEX function to provide the ability to take a replacement pattern and return the replaced string. String
RIGHT(text, num_chars)
text: string
num_chars: integer
Returns the specified number of characters, starting at the end of a string. String
RULE_BASED_LOCALITY_SENSITIVE_HASH(text)
text: string
Transforms the specified string by normalizing across spacing and characters, removing punctuation and special characters, and cleaning common English affixes. String
SEARCH(text, pattern, required, wildcard, remove, escape)
text: string
pattern: string
required: string
wildcard: string
remove: string
escape: string
Uses text search semantics to determine whether the specified text matches a regular expression pattern. Boolean
STRAFTER(text, pattern)
text: string
pattern: string
Returns the part of a string that comes after the pattern that you specify. String
STRBEFORE(text, pattern)
text: string
pattern: string
Returns the part of a string that comes before the pattern that you specify. String
STRDT(value, URI("data_type"))
value: string
data_type: string
Casts a string value to the specified data type. A URI function, such as TOURI, IRI, or URI, is required to specify the data type, which is a URI. For example, the following formula casts a regionkey column from a string to an integer:
STRDT([{region}regionkey], TOURI("xsd:int"))
Term
STRENDS(text, pattern)
text: string
pattern: string
Determines whether the specified string ends with the given pattern. Boolean
STRLANG(text, language)
text: string
language: string
Constructs a literal value with the specified language tag. String
STRSTARTS(text, pattern) Determines whether the specified string value starts with the given pattern. Boolean
STRUUID() Returns a string that is the result of generating a Universally Unique Identifier (UUID). String
SUBSTITUTE(text, old_text, new_text, instance_num)
text: string
old_text: string
new_text: string
instance_num: integer
Substitutes new text for old text in a string. String
TOURI(value)
value: string
Casts a string value to a URI. URI
TRIM(text)
text: string
Removes all spaces from values except for single spaces between words. String
UPPER(text, language, country, variant)
text: string
language: string
country: string
variant: string
Converts all lower case letters to upper case letters. String

Functions on RDF Terms

The table below details the Anzo functions for RDF term types: literal values, URIs, and blank nodes.

Function (syntax)
Argument: Data Type
Description Return Type
ADD(term1, term2)
term1: term
term2: term
Adds the results from the expressions that you specify. Term
AVERAGEIF(values_to_test, criterion, values_to_average)
values_to_test: term
criterion: term
values_to_average: integer
Calculates the averages of the values that meet the specified criterion. Integer
AVERAGEIFS(values_to_average, values_to_test, criteria, ...)
values_to_average: numeric
values_to_test: term
criteria: term
Similar to the AVERAGEIF function but enables you to specify multiple criteria. Integer
BNODE(term)
term: term
For use with Presence, Hierarchy, and Types filters to determine whether blank nodes exist for properties. You can also perform the BNODE function on literal values. Term
BOOLEAN(term)
term: term
Creates an xsd:boolean type based on label of the input term. Boolean
BOUND(term)
term: term
Determines which records include a value for the specified property and returns "True" for records that include a value or "False" for records that do not include a value. Boolean
CASE(value, criteria, ..., result, ..., default)
value: term
criteria: term
result: term
default: term
Enables you to add IF/THEN logic. CASE expressions evaluate a series of conditions for the properties that you specify and return results when the test returns true. The optional "default" argument is a default value to return if none of the tests pass. Term
CEILING(number)
number: term
Calculates the ceiling (the next whole number up from the value if the value has a fractional part) of the values that exist for the selected property and then groups the results into the list of ceiling values. CEILING returns the value itself if it is a whole number. Term
CHOOSE_BY_MAX(test, value)
test: term
value: term
Calculates the maximum values from the first expression or property and returns the values from the second expression or property that correspond to the maximum values. For example, in an imaginary sales data set, the following formula returns the IDs for the buyers who spent the most: CHOOSE_BY_MAX([{Sales}Price Paid], [{Sales}Buyer Id]) Term
CHOOSE_BY_MIN(test, value)
test: term
value: term
Calculates the minimum values from the first expression or property and returns the values from the second expression or property that correspond to the minimum values. For example, in an imaginary sales data set, the following formula returns the IDs for the buyers who spent the least: CHOOSE_BY_MIN([{Sales}Price Paid], [{Sales}Buyer Id]) Term
COALESCE(value, ...)
value: term
Evaluates any number of expressions and returns the results for the first expression that does not raise an error. Errors occur if an expression evaluates to an unbound variable or a non-RDF term. Term
COUNT(value)
value: term
Counts the number of values for the selected property. Integer
COUNT_DISTINCT(value)
value: term
Counts the number of unique values for the selected property. Integer
COUNTIF(value, criterion)
value: term
criterion: term
Calculates the counts of the values that meet the specified criterion. Integer
COUNTIFS(value, criteria, ...)
value: term
criteria: term
Similar to the COUNTIF function but enables you to specify multiple criteria. Integer
DATATYPE(term)
term: literal value
For use with Presence, Hierarchy, and Types filters. URI
DATEVALUE(date_text)
date_text: term
Groups results under the specified literal date value. Date
EQUAL(value1, value2)
value1: term
value2: term
Determines whether value1 is equal to value2. Boolean
GE(value1, value2)
value1: term
value2: term
Performs a greater than or equal to (>=) comparison between value1 and value2. Boolean
GT(value1, value2)
GT functions on numerics, booleans, dateTimes, and terms in this priority order
Performs a greater than (>) comparison between value1 and value2. Boolean
IF(test, value_if_true, value_if_false, value_if_error)
test: boolean
value_if_true: term
value_if_false: term
value_if_error: term
Evaluates one expression and returns a second expression depending on the answer. Term
IFERROR(value, value_if_error, ...)
value: term
value_if_error: term
Synonym for COALESCE. Term
IN(value, test_value, ...)
value: term
test_value: term
Determines whether any of the values for the first property are found in the other specified expressions or properties. Anzo groups the results under True or False. Boolean
ISBLANK(value)
value: term
Determines whether the property has blank node values and groups the results under True or False. Boolean
ISDATATYPE(value, data_type)
value: term
data_type: URI
Determines whether the values for a property are the specified data type and groups the results under True or False. Boolean
ISERROR(value)
value: term
Determines whether the argument evaluates to an error and groups the results under True or False. Boolean
ISIRI(value)
ISURI(value)
value: term
Determines whether the argument is an IRI. ISIRI and ISURI return true if the value is an IRI or URI (and is not blank) and false if it is not. Boolean
ISLITERAL(value)
value: term
Determines whether the property has literal values. Boolean
ISNUMERIC(value)
value: term
Determines whether the property has numeric values. Boolean
LE(value1, value2)
value1: term
value2: term
Performs a less than or equal to (<=) comparison between value1 and value2. Boolean
LOCALNAME(URI) Returns only the local name portion of a URI. String
LONG(value)
value: term
Displays numeric values in xsd:long format. Long
LT(value1, value2)
value1: term
value2: term
Performs a less than (<) comparison between value1 and value2. Boolean
MAX(value, ...)
value: term
Aggregate function that calculates the maximum values for each aggregate group. Term
MAXVAL(value, ...)
value: literal
Computes the maximum values for the specified arguments. Literal
MD5(value)
value: term
Calculates the MD5 hash of string values. String
METADATAGRAPHURI(URI) Returns the metadata graph URI for the specified input URI. URI
MIN(value, ...)
value: term
Aggregate function that calculates the minimum values Term
MINVAL(value, ...)
value: literal
Computes the minimum values for the specified arguments. Literal
MODE(value)
value: term
Aggregate function that returns the number that occurs most frequently in each aggregate group. Numeric
NAMESPACE(URI) Returns the namespace for the specified URI values. String
NOT(value)
value: boolean
Performs logical negation on the specified expression. Boolean
NOT_EQUAL(value1, value2)
value1: term
value2: term
Performs a not equal (!=) comparison between value1 and value2. Boolean
NOT_IN(value, test_value, ...)
value: term
test_value: term
Tests whether the value is not found in the test_value list of expressions. Boolean
OR(logical1, logical2)
logical1: boolean
logical2: boolean
Calculates the logical OR of the input values. Boolean
PARTITIONINDEX(value, start, interval)
value: literal
start: literal
interval: literal
Returns the zero-based index of the bucket in which the value falls. The buckets start at the specified start and are sized according to the specified interval. The first bucket is (start, start+interval): closed on the low end and open on the high end. PARTITIONINDEX returns less than 0 if the value does not fall into any bucket, such as when the value is less than start or if the comparison is indeterminate for date and time data types. Integer
SAMETERM(term1, term2)
term1: term
term2: term
Determines whether the specified RDF terms are the same. Boolean
SAMPLE(term)
term: term
Returns an arbitrary value from the group to represent the given variable. Term
SERIALIZE(term)
term: term
Returns the string representation of the specified term. String
SHA1(term) Calculates the SHA1 hash of the specified term. String
SHA224(term) Calculates the SHA224 hash of the specified term. String
SHA256(term) Calculates the SHA256 hash of the specified term. String
SHA384(term) Calculates the SHA384 hash of the specified term. String
SHA512(term) Calculates the SHA512 hash of the specified term. String
STR(term) Returns a string representation of the values for the selected property. String
STRLEN(term) Calculates the length of the specified term. Integer
SUBSTR(term, start, [length])
term: term
start: integer
length: integer
Returns a substring of the specified term. The start argument indicates the character position to start the substring with. The first character in the term is position 1. The optional length argument specifies the number of characters to return. String
SUMIF(values_to_test, criterion, values_to_sum)
values_to_test: term
criterion: term
values_to_sum: numeric
Calculates the sums of the values that match the specified criterion. Integer
SUMIFS(values_to_sum, values_to_test, criteria, ...)
values_to_sum: numeric
values_to_test: term
criteria: term
Similar to the SUMIF function but enables you to specify multiple criteria. Integer
TEXT(value, format)
value: term
format: string
Formats a term value as text. String
UCASE(term)
term: term
Returns the specified term as an uppercase string value. String
UNBOUND() Returns an unbound term Term
UUID() Generates a new IRI from the Universally Unique Identifier (UUID) Uniform Resource Name (URN) namespace. URI

Functions on Numerics

The table below details the Anzo functions for numeric data types. "Term" indicates an RDF term type value: a literal value, URI, or blank node.

Function (syntax)
Argument: Data Type
Description Return Type
ABS(number)
number: numeric value
Calculates the absolute values that exist for the selected property. Numeric
ADD(term1, term2)
term1: term
term2: term
Adds the results from the expressions that you specify. Term
AVERAGEIF(values_to_test, criterion, values_to_average)
values_to_test: term
criterion: term
values_to_average: integer
Calculates the averages of the values that meet the specified criterion. Integer
AVERAGEIFS(values_to_average, values_to_test, criteria, ...)
values_to_average: numeric
values_to_test: term
criteria: term
Similar to the AVERAGEIF function but enables you to specify multiple criteria. Integer
AVG(number)
number: numeric value
Calculates the averages of the values that exist for the selected property. Numeric
CEILING(number)
number: term
Calculates the ceiling (the next whole number up from the value if the value has a fractional part) of the values that exist for the selected property and then groups the results into the list of ceiling values. CEILING returns the value itself if it is a whole number. Term
CHOOSE_BY_MAX(test, value)
test: term
value: term
Calculates the maximum values from the first expression or property and returns the values from the second expression or property that correspond to the maximum values. For example, in an imaginary sales data set, the following formula returns the IDs for the buyers who spent the most: CHOOSE_BY_MAX([{Sales}Price Paid], [{Sales}Buyer Id]) Term
CHOOSE_BY_MIN(test, value)
test: term
value: term
Calculates the minimum values from the first expression or property and returns the values from the second expression or property that correspond to the minimum values. For example, in an imaginary sales data set, the following formula returns the IDs for the buyers who spent the least: CHOOSE_BY_MIN([{Sales}Price Paid], [{Sales}Buyer Id]) Term
COS(angle)
angle: double
Calculates the cosines of the values that exist for the selected property. Double
DECIMAL(value)
value: term
Returns numeric results in decimal format. Numeric
DIVIDE(value1, value2)
value1: numeric
value2: numeric
Divides the values for the first property or expression by the values for the second property or expression and groups the results into the list of division values. Numeric
DOUBLE(value)
value: term
Displays the results of the specified numeric property in xsd:double format. Double
EXP(number)
number: double
Raises the results to the power of the specified number. Double
FACT(number)
number: integer
Calculates the factorial of the results by the specified number. Integer
FLOAT(value)
value: term
Returns numeric results in float format and groups the results into the list of float values Float
FLOOR(number)
number: term
Calculates the floor (the closest whole number down from the value if the value has a fractional part) of the values that exist for the selected property and then groups the results into the list of floor values. FLOOR returns the value itself if it is a whole number. Term
FORMATDATE(value, format)
value: term
format: string
Formats a numeric or date value into date text. String
FORMATFRACTION(value, tolerance, seperate_whole_number)
value: term
tolerance: double
separate_whole_number: boolean
Returns results in fraction format rather than decimal format. String
FORMATNUMBER(value, format)
value: term
format: numeric format
Formats a numeric value into text in the specified format.  
GE(value1, value2)
value1: term
value2: term
Performs a greater than or equal to (>=) comparison between value1 and value2. Boolean
GT(value1, value2)
GT functions on numerics, booleans, dateTimes, and terms in this priority order
Performs a greater than (>) comparison between value1 and value2. Boolean
HAMMING_DIST(value1, value2)
value1: long
value2: long
Calculates the hamming distance between two values. Integer
HAVERSINE_DIST(lat1, lon1, lat2, lon2)
lat1: double
lon1: double
lat2: double
lon2: double
Computes the haversine distance between two latitude and longitude values. Double
INTEGER(value)
value: term
Returns numeric results in integer format. Integer
LE(value1, value2)
value1: term
value2: term
Performs a less than or equal to (<=) comparison between value1 and value2. Boolean
LN(value)
value: double
Calculates the natural logarithm of numeric values. Double
LOG(number, base)
number: double
base: double
Calculates the specified base logarithm of numeric values. Double
LONG(value)
value: term
Displays numeric values in xsd:long format. Long
LT(value1, value2)
value1: term
value2: term
Performs a less than (<) comparison between value1 and value2. Boolean
MAX(value, ...)
value: term
Aggregate function that calculates the maximum values for each aggregate group. Term
MAXVAL(value, ...)
value: literal
Computes the maximum values for the specified arguments. Literal
MEDIAN(value)
value: numeric
Aggregate function that calculates the median value for each aggregate group. Numeric
MIN(value, ...)
value: term
Aggregate function that calculates the minimum values Term
MOD(number, divisor)
number: integer
divisor: integer
Calculates the modulo or remainder of the division between two numeric values. Integer
MODE(value)
value: term
Aggregate function that returns the number that occurs most frequently in each aggregate group. Numeric
MODEPERCENT(value)
value: numeric
Aggregate function that calculates the percentage of the values that belong to the mode. Numeric
MULTIPLY(value1, value2)
value1: numeric
value2: numeric
Multiplies value1 by value2. Numeric
NPV(rate, year, value)
rate: numeric
year: numeric
value: numeric
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Numeric
PI() Returns the value for PI. Double
POWER(number, power)
number: numeric
power: numeric
Raises the specified number to the specified power. Double
QUOTIENT(numerator, denominator)
numerator: numeric
denominator: numeric
Calculates the quotient for the specified values. Integer
RAD(angle)
angle: double
Converts degrees to radians. Double
RAND() Returns a random double value between 0 and 1. Double
RANDBETWEEN(bottom, top)
bottom: numeric
top: numeric
Returns a random integer between the specified values (inclusive). If the input values are decimal types, Anzo returns a random integer between the ceil(bottom) and floor(top). Integer
ROUND(number)
number: double
Rounds a numeric value to the nearest integer. Integer
ROUNDDOWN(number, num_digits)
number: numeric
num_digits: integer
Rounds a numeric value down by the specified number of digits. Numeric
ROUNDUP(number, num_digits)
number: numeric
num_digits: integer
Rounds a numeric value up by the specified number of digits. Numeric
SIN(angle)
angle: double
Calculates the sine of the specified value. Double
SQRT(number)
number: double
Calculates the square root of the specified number. Double
STDEV(number)
number: numeric
Calculates the standard deviation of a group of numbers. Numeric
STDEVP(number)
number: numeric
Calculates the standard deviation product of a group of numbers. Numeric
SUM(number)
number: numeric
Calculates the sums of the values that exist for the selected property. Integer
SUMIF(values_to_test, criterion, values_to_sum)
values_to_test: term
criterion: term
values_to_sum: numeric
Calculates the sums of the values that match the specified criterion. Integer
SUMIFS(values_to_sum, values_to_test, criteria, ...)
values_to_sum: numeric
values_to_test: term
criteria: term
Similar to the SUMIF function but enables you to specify multiple criteria. Integer
SUMPRODUCT(number)
number: numeric
Calculates the sum of the product of the specified numeric values. Numeric
SUMSQ(number)
number: numeric
Calculates the square root of each number in the group and adds them all together. Numeric
TAN(angle)
angle: double
Calculates the tangent of the specified angle. Double
TIME(hour, minute, second)
hour: integer
minute: integer
second: integer
Converts the specified hour, minute, and second integer values as a time value. Time
VAR(number)
number: numeric
Calculates the variance for a group of numbers, i.e., how widely the values vary from the average of the values. Numeric
VARP(number)
number: numeric
Calculates the variance for a sample group of numbers, i.e., how widely the values vary from the average of the values. Numeric

Functions on Dates, Times, and Durations

The table below details the Anzo functions for date, time, and duration data types.

Function (syntax)
Argument: Data Type
Description Return Type
DATE(year, month, day)
year: integer
month: integer
day: integer
Groups results under the date (year, month, day) that you type. Date
DATEPART(date_value)
date_value: date or dateTime
Returns the date portion of a dateTime value. Date
DATETIME(value)
value: datetime, string (the string is parsed to datetime), or long (time in milliseconds since epoch)
Returns the appropriate dateTime based on the specified input value. Date
DATEVALUE(date_text)
date_text: term
Groups results under the specified literal date value. Date
DAY(date_value)
date_value: date or dateTime
Returns as an integer (1-31) the day portions of the values that exist for the selected property. Integer
DAYSFROMDURATION(value)
value: duration or numeric
Returns the day portion of duration values. String
DUR_TO_MILLIS(value)
value: date or dateTime
Displays date or date time values as the time in milliseconds. Long
DURATION(number)
number: long
Displays the specified values in duration format (PnYnMnDTnHnMnS). Duration
DURATIONFORMAT(millis, format)
millis: numeric
format: duration
Displays the specified values in duration format and groups the results into the list of durations. This function enables you to specify the duration format to use. The default format is H:mm:ss.SSS. String
DURATIONPERIODFORMAT(start, end, format)
tart: duration or numeric
end: duration or numeric
format: duration
Calculates the duration between the specified start and end values. This function also enables you to specify the duration format. The default format is PYYYYMMDDThhmmss.SSS. String
FORMATDATE(value, format)
value: term
format: string
Formats a numeric or date value into date text. String
GT(value1, value2)
GT functions on numerics, booleans, dateTimes, and terms in this priority order
Performs a greater than (>) comparison between value1 and value2. Boolean
HOUR(value)
value: time or dateTime
Returns the hour portions of the values that exist for the selected property. Integer
MASKEDDATETIME(value, year, month, day, hour, minute, second, millis)
value: date or dateTime
year: boolean
month: boolean
day: boolean
hour: boolean
minute: boolean
second: boolean
millis: boolean
Given an xsd:date or an xsd:dateTime value, this function returns the appropriate xsd:dateTime with the included parts of the date set to specific values. DateTime
MILLIS(value)
value: date or dateTime
Displays date or datetime values as the time in milliseconds. Date
MINUTE(value)
value: time
Returns the minute portions of the values that exist for the selected property. Integer
MONTH(value)
value: date
Returns as an integer (1-12) the month portions of the values that exist for the selected property. Integer
NOW(timezone)
timezone: string
Returns the current date and time. DateTime
NOWMILLIS() Returns the current date and time in epoch milliseconds. Long
PARSEDATETIME(date_string, output_type)
date_string: string
output_type: URI
Returns the specified string or literal value as a date, time, or datetime value. DateTime
SECOND(time_value)
time_value: date or dateTime
Returns the second portions of the values that exist for the selected property. Integer
TIME(hour, minute, second)
hour: integer
minute: integer
second: integer
Converts the specified hour, minute, and second integer values as a time value. Time
TIMEPART(value)
value: string, time, or dateTime
Returns the appropriate time based on the input value. Time
TODAY() Returns today's date. Date
WEEKDAY(date_value, return_type)
date_value: date
return_ype: integer
Returns the day of the week that corresponds to the specified date. Integer
WEEKNUM(date_value, return_type)
date_value: data
return_type: integer
Returns the week of the year that the specified date occurs in. Integer
YEAR(date_value)
date_value: date or dateTime
Returns as an integer (1900-9999) the year portions of the values that exist for the selected property. Integer
YEARMONTH(date_value)
date_value: date
Returns the year-month of the specified date. DateTime

Functions on Boolean Values

The table below details the Anzo functions for boolean data types.

Function (syntax)
Argument: Data Type
Description Return Type
AND(logical1, logical2)
logical1: boolean
logical2: boolean
Calculates the logical AND of the input values. Boolean
NOT(value)
value: boolean
Performs logical negation on the specified expression. Boolean
OR(logical1, logical2)
logical1: boolean
logical2: boolean
Calculates the logical OR of the input values. Boolean

Window Aggregate Functions

Window aggregates operate on a particular partition or window of the result set. Unlike grouped aggregate functions that group the result set and return a single row, window aggregates retain the resulting rows and return a value for each row.

Except for WINDOW_NTILE, WINDOW_PERCENTILE, and WINDOW_QUARTILE, use the following syntax for window aggregates:

WINDOW_FUNCTION(value, partition_over, order_by, order, start_frame, 
start_frame_type, start_frame_value, end_frame_type, end_frame_value)

The table below lists the supported window aggregates and provides the syntax for the WINDOW_NTILE, WINDOW_PERCENTILE, and WINDOW_QUARTILE functions.

Function (syntax) Description Return Type
WINDOW_AVG Returns the average of the input values. Numeric
WINDOW_COUNT Returns the count of the specified values. Integer
WINDOW_MAX Returns the maximum of the input values. Numeric
WINDOW_MIN Returns the minimum of the input values. Numeric
WINDOW_NTILE(ntile, value, order_by, partition_over) Divides the rows in the partition into the specified number of ranked groups and returns the group that each value belongs to. Numeric
WINDOW_PERCENTILE(value, order_by, partition_over) Like using NTILE(100), this function divides the rows in the partition into 100 ranked groups and returns the group that each value belongs to. Numeric
WINDOW_PRODUCT Returns the product of the input values. Numeric
WINDOW_QUARTILE(value, order_by, partition_over) Like using NTILE(4), this function divides the rows in the partition into 4 ranked groups and returns the group that each value belongs to. Numeric
WINDOW_SUM Returns the sum of the input values. Numeric
Related Topics