Hey there
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.