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