Grouping by sets

EQL provides support for grouping by sets.

Using GROUP BY

In the normal grouping syntax for the GROUP BY clause, EQL groups by set equality (that is, rows for which the sets are equal are placed into the same group).

For example, assume a data set in which Body is a multi-assign attribute and every record has at least one Body assignment except for Records 1, 2, 17, 20, and 21. This query is made against that data set:

RETURN results AS
  SELECT
    SET(WineID) AS IDs
  GROUP BY Body

The result of this statement might be:

Body                      IDs
------------------------------------------------------------
| { Silky, Tannins }  | { 10, 12, 13, 16, 18, 3, 5, 7, 9 } |
| { Robust }          | { 23, 24, 6, 8 }                   |
| { Robust, Tannins } | { 11, 4 }                          |
| { Firm, Robust }    | { 22, 25 }                         |
| { Fresh, Robust }   | { 19 }                             |
| { Supple }          | { 14, 15 }                         |
------------------------------------------------------------

Keep in mind that when using GROUP BY that NULL values and empty sets are ignored if selecting from the corpus (which is the case in this query). Therefore, Records 1, 2, 17, 20, and 21 are not returned because they have no Body assignments (and thus the empty set is returned for those records).

For more information on the GROUP BY clause, see Specifying GROUP BY.

Using GROUP BY MEMBERS

The MEMBERS extension to GROUP BY allows grouping by the members of a set. To illustrate the use of MEMBERS, the previous example can be re-written as:

RETURN results AS
  SELECT
    SET(WineID) AS IDs
  GROUP BY MEMBERS(Body) AS BodyType

The result might be:

BodyType      IDs
-------------------------------------------------------
| Fresh   | { 19 }                                    |
| Firm    | { 22, 25 }                                |
| Supple  | { 14, 15 }                                |
| Robust  | { 11, 19, 22, 23, 24, 25, 4, 6, 8 }       |
| Tannins | { 10, 11, 12, 13, 16, 18, 3, 4, 5, 7, 9 } |
| Silky   | { 10, 12, 13, 16, 18, 3, 5, 7, 9 }        |
-------------------------------------------------------

Note that like the previous example, Records 1, 2, 17, 20, and 21 are not returned because they have empty sets for the Body attribute.

For more information on MEMBERS, see MEMBERS extension.