PLAY WITH EINSTEIN – SAQL Functions

In this tutorial, we will learn about the SAQL Functions, what are the different functions that we can use with SAQL to manipulate the data and represent in chart/dashboard.

We will learn about below 2 types of SAQL functions: –

• Aggregate Functions

• Date Functions

Aggregate Function:- 

• Aggregate function perform computations on values.

• Aggregate function on an empty set returns null.
  • average() or avg():  Returns the average value of a numeric field.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, average(AnnualRevenue) as Revenue;

• count(): Returns the number of rows that match the query criteria.

For Example:

q = foreach q generate ‘Tier’ as ‘Tier’, count() as ‘count’;

• first(): Returns the value for the first tuple.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, first(AnnualRevenue) as Revenue;

• last(): Returns the value for the last tuple.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, last(AnnualRevenue) as Revenue;

• max():  Returns the maximum value of a field.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, max(AnnualRevenue) as maxRevenue;

• min():  Returns the minimum value of a field.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, min(AnnualRevenue) as maxRevenue;

• median(): Returns the middle number (by sorted order, ignoring null values). If there is no one middle number, then median returns the average of the two numbers closest to the middle.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, median(AnnualRevenue) as Revenue;

• sum(): Returns the sum of a numeric field.

For Example:

a1 = group a by Priority;

a2 = foreach a1 generate Priority as Priority, sum(AnnualRevenue) as Revenue;

Date Function: – SAQL date functions use to convert the dimensions and measures to dates. The dates can be used to sort, filter, and group data in your SAQL queries.

• date(year, month, day):  Returns a date that can be used in a filter. Specify the year, month, and day.

For Example:

q = filter q by date(‘CloseDate_Year’, ‘CloseDate_Month’, ‘CloseDate_Day’) in [“current day – 30 days”..];

• date_diff(datepart,startdate,enddate):  Returns an integer representing the interval that has elapsed between two dates. Datepart indicates the interval part to calculate: year, month, quarter, day, week, hour, minute, second

For Example:

q = foreach q generate date_diff(“year”, toDate(DateOfBirth, “yyyy-MM-dd”), now()) as age;

• date_to_epoch(date): Converts a date to epoch seconds. This is the reverse of the toDate(epoch_seconds) function.

For Example:

date_to_epoch(toDate(“2018-07-26 15:41:58”)) == 1532599918

• date_to_string(date, formatString): Converts a date to a string.This function must take a toDate() or now( ) function as its first argument.

For Example:

q = foreach q generate date_to_string(now( ), \”yyyy-MM-dd HH:mm:ss\”) as datestring;

• dateRange(startArray_y_m_d, endArray_y_m_d):  Returns a fixed date range. The first parameter is an array that specifies the start date in the range. The second parameter is an array that specifies the end of the range.

For Example:

q = dateRange([1970, 1, 1], [1970, 1, 31]);

• day_in_month(date): Returns an integer representing the day of the month for a specific date.

For Example:  q = foreach q generate day_in_month( toDate(OrderDate));

• day_in_quarter(date): Returns an integer representing the day of the quarter for a specific date.

For Example: q = foreach q generate day_in_quarter( toDate(OrderDate));

• day_in_week(date): Returns an integer representing the day of the week for a specific date.

For Example:  q = foreach q generate day_in_week( toDate(OrderDate));

• day_in_year(date): Returns an integer representing the day of the year for a specific date.

For Example:  q = foreach q generate day_in_week( toDate(Closedate));

• daysBetween(date1, date2): Returns the number of days between 2 dates as an integer.

For Example:

q = foreach q generate daysBetween( toDate( OrderDate, “yyyy-MM-dd” ),

   toDate( ShipDate, “yyyy-MM-dd” )) as daysToShip;

• toDate(epoch_seconds):  Converts Unix epoch seconds to a date. If epoch_seconds is 0,      toDate(epoch_seconds) returns ‘1970-01-01 00:00:00’.

For Example: toDate(“1532599918”) == toDate(“2018-07-26 15:41:58”)

• toString(date, formatString):  Converts a date to a string.This function must take a toDate() or now( ) function as its first argument.

q = foreach q generate toString(now( ), \”yyyy-MM-dd HH:mm:ss\”) as ds;

In the next blog, we will learn the remaining SAQL functions. Thanks for reading.

Sharing is caring 🙂 😉