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).
RETURN results AS SELECT SET(WineID) AS IDs FROM WineState GROUP BY Body
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
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
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.