We’re excited to introduce some fresh functions to enhance your data manipulation. Check them out!
Aggregation Functions
- corr:
corr(table, field1, field2)— Pearson correlation coefficient. - string_agg:
string_agg(expression, sep: separator)— Concatenate expression values. - percentile_cont:
percentile_cont(expression, percentile)— Value at a given percentile (interpolated). - percentile_disc:
percentile_disc(expression, percentile)— Value at a specific percentile (discrete). - min_by:
min_by(table, value, by)— Fetch value from the row with minimum in another field. - max_by:
max_by(table, value, by)— Grab value from the row with maximum in another field.
Time Intelligence Functions
- date_format:
date_format(datetime, format)— Format dates easily. - from_unixtime:
from_unixtime(number)— Convert Unix timestamp to datetime. - last_day:
last_day(datetime, date_part)— Get the last day of a period.
Text Functions
- find:
find(text, substring)— Locate a substring. - left/right/mid:
left(text, length),right(text, length),mid(text, start, length)— Extract from left, right, or mid. - len/lpad/rpad:
len(text),lpad(text, length, pad_string),rpad(text, length, pad_string)— Length, left/right padding. - lower/upper:
lower(text),upper(text)— Change case of text. - trim/ltrim/rtrim:
trim(text),ltrim(text),rtrim(text)— Remove whitespace from sides. - regexp_extract/match/replace:
regexp_extract(text, regex, ...),regexp_match(text, regex),regexp_replace(text, regex, substitute)— Regex operations on text. - replace:
replace(text, old_substring, new_substring)— Replace all occurrences of a substring. - split_part:
split_part(text, delimiter, part_number)— Split text and return a specific part.
Window Functions
- first_value:
first_value(expression)— Value from the first row. - last_value:
last_value(expression)— Value from the last row. - nth_value:
nth_value(expression, N)— Value from the nth row. - ntile:
ntile(n)— Divides rows into ranked groups. - percent_rank:
percent_rank()— Calculates the relative percentile rank of a value.
⭐ Learn more: You can see all AQL functions in our cheatsheet.
Got questions or feedback? We’d love to hear from you! Your input helps us make Holistics better every day. 😊