group and cogroup

Groups matched records. The group and cogroup statements are interchangeable. However, cogroup is typically used to operate on more than 1 input stream.

Syntax

1result = group rows by field;
2result = group rows by (field1, field2, ...);
3result = group rows by expression[, rows by expression ...];
4result = group rows by expression [left | right | full], rows by expression;

Simple Grouping

Adds one or more columns to a group. If data is grouped by a value that’s null in a row, that whole row is removed from the result.

Syntax:

1result = group rows by field;

1result = group rows by (field1, field2, ...);

The order of the fields matters for limit queries, but not for top queries.

Note

Group by 1 dimension:

a = group a by year;

Group by multiple dimensions:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2a = group a by (year, month);
3a = foreach a generate year as year, month as month;

Inner Cogrouping

Cogrouping means that two input streams, called left and right are grouped independently and arranged side by side. Only data that exists in both groups appears in the results.

Syntax:

1result = cogroup rows by expression[, rows by expression ...];

This example is a simple cogroup operation on 2 datasets:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = load "0Fbyy000000002qCAA/0Fcyy000000002WCAQ";
3a = cogroup a by carrier, b by carrier;

You can cogroup more than 2 datasets:

1result = cogroup a by keya, b by keyb, c by keyc;

This example performs a cogroup operation:

1z = cogroup x by (day,origin), y by (day,airport);

You can’t have the same stream on both sides of a cogroup operation. To perform a cogroup operation on 1 dataset, load the dataset twice so you have 2 streams.

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
3b = cogroup a by ClosedDate, b by CreatedDate;
4c = foreach b generate sum(a.Amount) as Amount;

You can also load 1 dataset and filter it into 2 different streams:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2a = filter a by "region" in ["West"];
3a = filter a by "status" in ["closed"];
4b = filter a by "year" in [2014];
5c = filter a by "year" in [2015];
6d = cogroup b by ("state"), c by ("state");
7d = foreach d generate "state" as "state", sum(b.Amount) as "Amount_2014", sum(c.Amount) as "Amount_2015";

This code throws an error because it performs a cogroup operation on a single stream, a:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = cogroup a by ClosedDate, a by CreatedDate;
3c = foreach b generate sum(a.Amount) as Amount;

To use aggregate functions when cogrouping, specify which input side to use in the aggregate function. For example, if you have an a side and a b side, and each contains a particular measure, use one of these syntaxes:

1sum(inputSide['myMeasure'])
2sum(inputSide::myMeasure)
3sum(inputSide.myMeasure)

This query is valid because it uses the third syntax form to specify that miles comes from the a side.

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = load "0Fbyy000000002qCAA/0Fcyy000000002WCAQ";
3c = cogroup a by x, b by y;
4d = foreach c generate a.x as x, a.y as y, sum(a.miles) as miles;

This query isn’t valid because miles doesn't specify which side it is coming from:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = load "0Fbyy000000002qCAA/0Fcyy000000002WCAQ";
3c = cogroup a by x, b by y;
4d = foreach c generate a.x as x, a.y as y, sum(miles) as miles;

If a lens or dashboard has a cogroup query, specify the input stream for projections and for count() aggregations on cogroup queries, as in this example:

1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = load "0Fbyy000000002qCAA/0Fyy000000002WCAQ";
3c = cogroup a by 'OwnerName', b by 'OwnerName';
4c = foreach c generate a['OwnerName'] as 'OwnerName', sum(a['AmountConverted']) /
5    sum(b['Amount']) as 'sum_target_completed', count(a) as count;

Outer Cogrouping

Outer cogrouping combines groups as an outer join. For the half-matches, null rows are added. The grouping keys are taken from the input that provides the value.

Syntax:

1result = cogroup rows by expression [left | right | full], rows by expression;

Specify left, right, or full to indicate whether to perform a left outer join, a right outer join, or a full join.

Example: z = cogroup x by (day,origin) left, y by (day,airport);

You can apply an outer cogrouping across more than 2 sets of data. This example does a left outer join from a to b, with a right join to c:

result = cogroup a by keya left, b by keyb right, c by keyc;

Outer joins return null when there is no match, instead of defaulting to zero.

Note

Wave Analytics SAQL Reference

group and cogroup

Syntax

Simple Grouping

Inner Cogrouping

Outer Cogrouping