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
FROM WineState
GROUP BY Body

The result of this statement might be:

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

Keep in mind that when using GROUP BY that EQL preserves rows in which the group key is the empty set or a NULL value). Therefore, Records 1, 2, 17, 20, and 21 are returned even though they have no Body assignments (because the empty set is returned for those records).

For more information on the GROUP BY clause, see GROUP/GROUP BY clauses.

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
FROM WineState
GROUP BY MEMBERS(Body) AS BodyType

The result might be:

BodyType      IDs
-------------------------------------------------------
| Supple  | { 14, 15 }                                |
| Firm    | { 22, 25 }                                |
| Fresh   | { 19 }                                    |
| 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 }        |
|         | { 1, 17, 2, 20, 21 }                                |
-------------------------------------------------------

Note that like the previous example, Records 1, 2, 17, 20, and 21 are returned.

For more information on MEMBERS, see MEMBERS extension.