PerfQL Functions
- Anastasia Baranouskaya
- Aleksandr Bogush
This page has a list of all available PerfQL operators which could be used for custom metric creation:
The following functions could be useful for creating Custom Metric:
Common Table Expressions - it can chain calculations in one SELECT Query
FILTER Clause - helps to make aggregation on subset of the set returned by WHERE
Window Functions - to compute rolling average in an elegant an laconic way
See examples of PerfQL Quries for different cases
The table below represents a limited set of Postgres Standard Functions
Function | Description |
|---|---|
avg | returns the average of a series of numbers |
count | to get the number of rows that match a specific condition of a query: |
max | SELECT MAX(amount) |
min | SELECT |
string_agg | concatenates a list of strings and places a separator between them: |
sum | SELECT SUM (amount) AS total |
grouping | SELECT |
mode | returns the most frequent input value (arbitrarily choosing the first one if there are multiple equally-frequent results): |
percentile_cont | continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed: |
percentile_disc | discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction: |
stddev | historical alias for stddev_samp: |
stddev_pop | population standard deviation of the input values: |
stddev_samp | sample standard deviation of the input values |
substring | the substring function returns a part of string.: |
trim | the TRIM() function removes the longest string that contains a specific character from a string. |
convert | convert a value of one type to another |
substr | extract substring (same as substring(string from from for count)) |
coalesce | accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null. |
nullif | function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1. |
greatest | returns the “greatest” or “largest” value from the list of expressions. |
least | returns the “least” or “smallest” value from the list of expressions. |
age | accepts two TIMESTAMP values. It subtracts the second argument from the first one and returns an interval as a result. |
current_date | returns a DATE value that represents the current date. |
current_time | returns a TIME WITH TIME ZONE value that represents the current time with time zone. |
current_timestamp | returns a TIMESTAMP WITH TIME ZONE that represents the date and time at which the transaction started. |
date_part | allows to retrieve subfields e.g., year, month, week from a date or time value. |
date_trunc | truncates a TIMESTAMP or an INTERVAL value based on a specified date part e.g., hour, week, or month and returns the truncated timestamp or interval with a level of precision. |
extract | retrieves a field such as a year, month, and day from a date/time value. |
isfinite | check if a date, a timestamp, or an interval is finite or not (not +/-infinity) |
now | returns the current date and time. |
timeofday | current date and time |
to_timestamp | converts a string to a timestamp according to the specified format. |
to_char | converts a timestamp, an interval, an integer, a double precision, or a numeric value to a string. |
to_date | helps convert a string to a date. |
to_number | converts a character string to a numeric value. |
abs | calculate the absolute value of a number |
cbrt | calculate the cube root of a number |
ceil | round a number up to the nearest integer, which is greater than or equal to number |
ceiling | same as CEIL |
degrees | convert radians to degrees |
div | return the integer quotient of two numeric values |
exp | return the exponential value in scientific notation of a number |
floor | round a number down to the nearest integer, which is less than or equal to number |
ln | return the natural logarithm of a numeric value |
log | return the logarithm of a numeric value to a specified base |
mod | divide the first parameter by the second one and return the remainder |
pi | return the value of PI |
power | raise a numeric value to the power of a second numeric value |
radians | convert degrees to radians |
round | round a number to the nearest integer or to a specified decimal places |
scale | return the number of decimal digits in the fractional part |
sign | return the sign (positive, negative) of a numeric value |
sqrt | return the square root of a numeric value |
trunc | truncate a numeric value to a whole number of to the specified decimal places |
width_bucket | assign values to buckets in an equi-width histogram. |
setseed | sets the internal seed for the random number generator (the function random). Allowed values are floating-point numbers between -1 and 1, which are then multiplied by 231-1. |
lower | convert a string to lowercase |
upper | convert a string to uppercase |
lower_inc | this function is used to check whether the lower bound inclusive or not within the specified range and return a boolean value. |
upper_inc | this function is used to check whether the upper bound is inclusive or not within the specified range and return a boolean value. |
lower_inf | this function is used to check whether the lower bound is infinite or not within the specified range and return a boolean value. |
upper_inf | this function is used to check whether the upper bound is infinite or not within the specified range and return a boolean value. |
nextval | to get the next value from the sequence to you use the nextval() function: |
setval | reset the counter value of the sequence object. Setting the last_value field of the sequence to the specified value and its is_called field to true indicates that the next nextvalue will increment the sequence before returning the value. |
currval | returns the last value of the sequence captured by nextval in the current session. (If nextval has never been invoked on this sequence in this session, an error will be reported. ) Note that because this function returns a session range value and can give a predictable result, it can be used to determine whether other sessions have executed nextval. |
lastval | returns the last value returned by nextval in the current session. This function is equivalent to currval, except that it does not use the sequence name as a parameter, it grabs the sequence used by the last nextval in the current session. If the current session has not invoked nextval, calling lastval will result in an error. |
generate_series | to get multiple random numbers between two integers, you use the following statement: |
generate_subscripts | generate a series comprising the given array's subscripts. |
ascii | return the ASCII code value of a character or Unicode code point of a UTF8 character |
chr | convert an ASCII code to a character or a Unicode code point to a UTF8 character |
concat | concatenate two or more strings into one |
concat_ws | concatenate strings with a separator |
format | format arguments based on a format string |
initcap | convert words in a string to title case |
left | return the first n character in a string |
lpad | pad on the left a a string with a character to a certain length |
ltrim | remove the longest string that contains specified characters from the left of the input string |
parse_ident | splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions.) Note that this function does not truncate over-length identifiers. If you want truncation you can cast the result to name[]. parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable} |
quote_ident | returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. quote_ident('Foo bar') → "Foo bar" |
quote_literal | returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. quote_literal(E'O\'Reilly') → 'O''Reilly' |
quote_nullable | returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled |
regexp_matches | match a POSIX regular expression against a string and returns the matching substrings |
regexp_replace | replace substrings that match a POSIX regular expression by a new substring |
regexp_split_to_array | splits string using a POSIX regular expression as the delimiter |
regexp_split_to_table | splits string using a POSIX regular expression as the delimiter |
repeat | repeat string the specified number of times |
replace | replace all occurrences in a string of substring from with substring to |
reverse | return reversed string. |
right | return last n characters in the string. When n is negative, return all but first |n| characters. |
split_part | split a string on a specified delimiter and return nth substring |
strpos | the PostgreSQL strpos function returns the location of a substring in a string. |
starts_with | returns true if string starts with prefix. |
to_ascii | converts string to ASCII from another encoding, which may be identified by name or number. If encoding is omitted the database encoding is assumed (which in practice is the only useful case). The conversion consists primarily of dropping accents. Conversion is only supported from LATIN1, LATIN2, LATIN9, and WIN1250 encodings. to_ascii('Karél') → Karel |
to_hex | converts the number to its equivalent hexadecimal representation. to_hex(2147483647) → 7fffffff |
char_length | returns number of characters in the string. |
character_length | returns number of characters in the string. |
row_number | it is a window function that assigns a sequential integer to each row in a result set. |
ntile | allows you to divide ordered rows in the partition into a specified number of ranked groups as equal size as possible. |
lag | provides access to a row that comes before the current row at a specified physical offset. In other words, from the current row the LAG() function can access data of the previous row, or the row before the previous row, and so on. |
lead | provide access to a row that follows the current row at a specified physical offset. |
first_value | returns a value evaluated against the first row in a sorted partition of a result set. |
last_value | returns the last value in an ordered partition of a result set. |
nth_value | returns a value from the nth row in an ordered partition of a result set. |
rpad | fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. |
rtrim | remove the longest string containing only characters from characters (a space by default) from the end of string |
translate | any character in string that matches a character in the from set is replaced by the corresponding character in the to set |
Length() | It is used to find the length of a string i.e. number of characters in the given string. |
dense_rank() | Returns the rank of the current row, without gaps; this function effectively counts peer groups. |
array_to_string | This function is used to concatenate array elements using supplied delimiter and optional null string. |
unnest | This function is used to expand an array to a set of rows. |
jsonb_array_elements() | Expands a JSON array to a set of JSON values. |