Launched: New AQL Functions For Better Data Manipulation πŸš€

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

:star: 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. :blush:

4 Likes

Here are some examples demonstrating how each function works in real scenarios, so you can better understand their use cases.

Calculate correlations between users’ sign-up and order volume

Bucketing users sign up into 4 buckets and show their percentile

Formatting dates directly in AQL with date_format

With the addition of split_part and regexp_replace, Holistics AI can now handle tasks like extracting domains from email addresses or replacing specific substrings, making data cleanup much easier.

Experience the power of AQL through a simplified interface - now available in Holistics AI (Alpha).

Join the waitlist to try it out, and don’t miss our community post and announcement blog post to learn more about what else Holistics AI :sparkles: can do!

4 Likes