Wave Analytics SAQL Reference

SAQL Overview
Enable SAQL Logs in the Browser
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
Math Functions
Windowing Functions
coalesce()
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Aggregate Functions

Use aggregate functions to perform computations on values.

Using an aggregate function on an empty set returns null. For example, if you use an aggregate function with a nonmatching column of an outer cogrouping, you might have an empty set.

This table lists the aggregate functions that are supported:

Aggregate Function Description
avg() or average() Returns the average value of a numeric field.
For example, to calculate the average number of miles:
1a1 = group a by (origin, dest);
2a2 = foreach a1 generate origin as origin, dest as destination, average(miles) as miles;
count() Returns the number of rows that match the query criteria.
For example, to calculate the number of carriers:
1q = foreach q generate 'carrier' as 'carrier', count() as 'count';
The count() function operates on streams that were inputs to the group or cogroup statements. It doesn’t operate on the newly grouped stream or on an ungrouped stream. 
1a = load "0Fcyy000000002qCAA/0Fcyy000000002WCAQ";
2a1 = group a by (Year);
3q = foreach a1 generate count(a) as countYear, count() as count, Year as year;
4q = limit q 20;
You can’t pass a1 to the count() function because it’s a newly grouped stream.
first() Returns the value for the first tuple. To work as expected, you must be aware of the sort order or know that the values of that measure are the same for all tuples in the set.

For example, you can use these statements to compute the distance between each combination of origin and destination:

1a1 = group a by (origin, dest);
2a2 = foreach a1 generate origin as origin, dest as destination, first(miles) as miles;
last() Returns the value for the last tuple.

For example, to compute the distance between each combination of origin and destination:

1a1 = group a by (origin, dest);
2a2 = foreach a1 generate origin as origin, dest as destination, last(miles) as miles;
max() Returns the maximum value of a field.

This function takes only a measure as an argument. It can’t take a dimension.
median() Accepts a grouped expression of numeric type and returns the middle number (by sorted order, ignoring null values). If there is no one middle number (in other words, the count of non-null values is even), then median returns the average of the two numbers closest to the middle.

The expression can be any identifier, such as ‘xInt’ or ‘price’, but cannot be a complex expression, such as price/100 or ceil(distance), or a literal, such as 2.5.

1q = load "data/airline";
2q = group q by dest;
3q = foreach q generate dest, median(miles) as medMiles;
4limit q 5;
If median is not preceded by a group by clause, it treats each individual row as its own group: 
1q = load "data/airline";
2q = foreach q generate dest, median(miles) as medMiles;
3limit q 5;
min() Returns the minimum value of a field.

This function takes only a measure as an argument. It can’t take a dimension.
sum() Returns the sum of a numeric field.
1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2a = filter a by dest in ["ORD", "LAX", "ATL", "DFW", "PHX", "DEN", "LGA"]; 
3a = group a by carrier; 
4b = foreach a generate carrier as airline, sum(miles) as miles;
unique() Returns the count of unique values.

For example, to find how many origins and destinations a carrier flies from:

1a1 = group a by carrier;
2a2 = foreach a1 generate carrier as carrier, unique(origin) as origins, unique(dest) as destinations;