Supported Functions
You can use any of the following functions in an Adrenaline DataFlow:
General Functions
Function Name |
Description |
Structure |
---|---|---|
|
Return the first parameter if non null, otherwise return the second parameter |
|
|
Return null if the two input expressions are equal, otherwise return the first expression. |
|
|
Similar to |
|
Mathematical Functions
Function Name |
Description |
Structure |
---|---|---|
|
Takes in a number and returns the nearest whole number rounded up. |
|
|
Takes in a number and returns ths nearest whole number rounded down. |
|
|
Return the natural log of the input parameter. |
|
|
Return the base 10 log of the input parameter. |
|
|
Returns the first number raised to the power of the second number. |
|
|
Returns a uniformly-distributed random number x, where 0 <= x < 1. Takes a parameter of any type as a seed. |
|
|
Returns a uniformly-distributed random number x, where 0 <= x < 1. |
|
|
Rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated). |
|
|
Returns the square root of the input parameter. Returns null if the result is not a real number. |
|
|
Constructs equiwidth histograms, in which the histogram range is divided into intervals (buckets) of identical sizes. In addition, values below the low bucket return 0, and values above the high bucket return bucket_count +1. Returns an integer value. |
|
Date Functions
Function Name |
Description |
Structure |
---|---|---|
|
Adds a time value to a date or datetime. The time is represented as the timestamp component of a string date time value. |
|
|
Converts a datetime value from one timezone to another. Timezones can be hour offsets, timezone codes, or full timezone names (e.g. -06:00, MDT, America/Denver) |
|
|
Extracts the date portion from a datetime expression. |
|
|
Truncates a datetime expression to part specified. |
|
|
Returns the number of days between two date values |
|
|
Return the full name of a the weekday for a date. |
|
|
Return the day of the month for a date. |
|
|
Return the day number in the week for a date. The return value is the same as WEEKDAY. |
|
|
Return the day number in the year for a date. |
|
|
Returns a date from a numerical representation of the date (days since epoch). See |
|
|
Returns a date from a unix based time (seconds since epoch). Optionally a pattern for the output format can be included. See |
|
|
Returns the hour portion of a datetime value. |
|
|
Returns the last day of the month for a given date or datetime. |
|
|
Returns the month number for a given date or datetime. |
|
|
Returns the full name of the month for a given date or datetime. |
|
|
Returns the current time in the timezone of the query request context. |
|
|
Adds a number of months to a period. Period is a number formatted YYYYMM or YYMM. The result is a long formatted as YYYYMM. |
|
|
Difference in months between two periods. Period is a number formatted YYYYMM or YYMM. Both periods should be the same format. The result is the number of months. |
|
|
Returns the quarter (1-4) for a given date/datetime. |
|
|
Takes a number of seconds and returns them formatted as a time (HH:MM:SS). |
|
|
Returns the seconds part of a date time value. |
|
|
Parses a string into a date. Returns a STRING if the format equals '%T' or starts with '%H'. Otherwise returns a DATETIME if the format string is longer than 10 characters, and returns a DATE in all other cases. |
|
|
Subtracts an interval from a date/datetime. Returns a date if the input is a date and the interval is in days or greater, otherwise returns a datetime. |
|
|
Subtracts a number of seconds from a date/datetime. The seconds can be decimal numbers to represent sub-second values. |
|
|
Returns the current date time. |
|
|
Returns the time portion of a datetime as a string formatted HH:MM:SS. |
|
|
Formats the time portion of a datetime as a string. |
|
|
Convert a time portion of a datetime to seconds. |
|
|
Return the time difference between two time/datetimes as a HH:MM:SS string. |
|
|
Returns a datetime value based on the arguments. Input should either be a date or a valid datetime string. |
|
|
Converts an input into a DATE value. |
|
|
Return the number of days between a date expression and the year 0. |
|
|
Returns the seconds since epoch (unix timestamp for a given date). |
|
|
Returns the week of year for a given date. Optionally a second "mode" parameter can be passed to indicate what starting day of the week to use and whether or not to use iso weeks. The option parameter is a two digit number. * First digit 1 or 2. 1 for January first based weeks, and 2 for the ISO standard weeks * Second digit for the first day in the week Sunday - 1, Monday - 2, etc Currently only 11 and 22 are supported. |
|
|
Return the day number in week for a given date/datetime. The return value is the same as DAYOFWEEK. |
|
|
Return the current year for a date/datetime. An optional mode parameter may also be passed, see |
|
|
Return the year and week number as YYYYWW. An optional mode parameter may also be passed, see |
|
String Functions
Function Name |
Description |
Structure |
---|---|---|
|
Formats a date into a string value. |
|
|
Capitalizes the first letter of each word. Words are delimited by white space or characters that are not alphanumeric. |
|
|
Returns the position (1 based) of a search string in another string, or zero if the string is not found. |
|
|
Returns X characters from the start of a string. |
|
|
Returns the length of a string. |
|
|
Returns a string with all letters lowercased. |
|
|
Left-pads a string with another string, to a certain length. |
|
|
Returns the number of bytes in the string. |
|
|
Replaces all occurrences of a substring in a string with a new string. |
|
|
Returns X characters from the end of a string. |
|
|
Right-pads a string with another string up to X total characters. |
|
|
Splits a string by a given character and returns the requested part (1 based). |
|
|
Returns a substring of a specified length, starting at start_pos (1 based). |
|
|
Converts an expression to a string based on the passed in format. |
|
|
Converts a string into a number. An optional pattern can be passed in. If the pattern doesn't include decimals the result will be LONG, otherwise the result will be a DOUBLE. |
|
|
Trims whitespace from the begining and end of a string. |
|
|
Converts all characters in a string to uppercase. |
|
Aggregate Functions
Function Name |
Description |
Structure |
---|---|---|
|
Calculates an approximate median for a group of numerical values. The error tolerance may vary between engines. |
|
|
Calculates an approximate percentile for a group of numerical values. The percentile value must between 0 and 1. The error tolerance may vary between engines. |
|
|
Calculates the average of a group of numbers. |
|
|
Counts the number of rows in the result set. If the DISTINCT keyword is used it counts the number of rows with distinct values for the passed in expression. |
|
|
Transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. A max_length can be supplied with an optional default value if the max_length is exceeded. |
|
|
Returns the maximum value in the result set group for the expression. |
|
|
Returns the median value in the result set group for the expression. Only works on numeric types. |
|
|
Returns the minimum value in the result set group for the expression. |
|
|
Returns the statistical sample standard deviation in the result set group for the expression. Only works on numeric types. |
|
|
Returns the statistical population standard deviation in the result set group for the expression. Only works on numeric types. |
|
|
Returns the sum of all values in the result set group for the expression. Only works on numeric types. |
|
|
Returns the population variance in the result set group for the expression. Only works on numeric types. |
|
|
Returns the coefficient of correlation in the result set group for the expression. Only works on numeric types. |
|
Analytic Functions
Function Name |
Description |
Structure |
---|---|---|
|
Returns the first value in the partition for a given expression. If a window order by clause is not specified the value returned is not guaranteed to be consistent. |
|
|
Returns the value of an expression in previous rows based on the offset provided (default 1). Optionally the user can supply a default value if the offset falls out of the range of the partition. |
|
|
Returns the value of an expression in subsequent rows based on the offset provided (default 1). Optionally the user can supply a default value if the offset falls out of the range of the partition. |
|
|
Returns the last value in the partition for a given expression. If a window order by clause is not specified the value returned is not guaranteed to be consistent. |
|
|
Returns the maximum value of an expression within a partition. An order by clause can be supplied and if so the max will be calculated only with values previous to the current row based on the ordering. |
|
|
Returns the minimum value of an expression within a partition. An order by clause can be supplied and if so the min will be calculated only with values previous to the current row based on the ordering. |
|
|
Equally divides an ordered data set (partition) into a {value} number of subsets within a window, where the subsets are numbered 1 through the value in parameter constant‑value. For example, if constant‑value= 4 and the partition contains 20 rows, NTILE divides the partition rows into four equal subsets of five rows. NTILE assigns each row to a subset by giving row a number from 1 to 4. The rows in the first subset are assigned 1, the next five are assigned 2, and so on. If the number of partition rows is not evenly divisible by the number of subsets, the rows are distributed so no subset is more than one row larger than any other subset, and the lowest subsets have extra rows. For example, if constant‑value= 4 and the number of rows = 21, the first subset has six rows, the second subset has five rows, and so on. If the number of subsets is greater than the number of rows, then a number of subsets equal to the number of rows is filled, and the remaining subsets are empty. |
|
|
Simplified syntax for calculating a percentile. It takes in an expression and a percentile value and translates it into an analytic expression that is partitioned by the group by clause. |
|
|
Returns the value that would fall into the specified percentile among a set of values in each partition. |
|
|
Ranks each row from 1 to partition row count based on the order clause in the over expression. |
|
|
Similar to |
|
|
Similar to |
|
|
Sums the values in a partition. If an order by is included the sum will only include values at or preceding the current row according to that order. |
|
|
Similar to |
|
Comments
0 comments
Please sign in to leave a comment.