Compound Grouping: GROUP Sets

Doradus Spider allows the aggregate query grouping parameter to consist of multiple grouping sets. Each grouping set is enclosed in a GROUP function; multiple grouping sets are separated by commas. This feature is known as compound grouping. The general syntax is:

GROUP(<expression 1>),GROUP(<expression 2>),...,GROUP(<expression n>)

Each <expression n> parameter must use one of the following forms:

•	A “*” can be used to compute a global aggregate (i.e., GROUP(*)). The metric function is computed for all selected objects just as in an aggregate query with no grouping parameter. The GROUP(*) function should be specified at most once since there is only one metric value for a global aggregate.

•	A single-level grouping expression, consisting of a single scalar field or a field path (e.g., GROUP(Tags)).

•	A multi-level grouping expression, consisting of a comma-separated list of scalar fields and/or field paths (e.g., GROUP(TRUNCATE(SendDate,DAY),Tags)).

Each single- and multi-level grouping expression must be relative to the perspective table. The same set of objects selected by the aggregate query is passed to each grouping set, and separate metric computations are performed for each grouping set. Aggregate queries that use compound grouping perform a single pass through the selected objects and computes multiple grouping sets at the same time.

Consider this aggregate query:

GET /Msgs/Message/_aggregate?m=MAX(Size)
&cf=GROUP(*),GROUP(TRUNCATE(SendDate,WEEK)),GROUP(TOP(2,TERMS(Subject)),Tags)
&q=SendDate > 2013-10-15

This compound grouping aggregate query selects messages whose SendDate is >= 2013-10-15, and it computes the following:

•	The maximum Size value of selected messages (GROUP(*)).

•	The maximum Size of selected messages grouped by SendDate truncated to WEEK granularity (GROUP(TRUNCATE(SendDate,WEEK))).

•	The maximum Size of selected messages grouped first by the top 2 terms used in the Subject field and then by the Tags field (GROUP(TOP(2,TERMS(Subject)),Tags)). Because composite grouping was requested (&cf), this multi-level grouping expression uses the composite grouping technique.

Compound aggregate queries compute all grouping sets in a single pass. The query above returns XML results such as the following:

<results>

<aggregate metric="MAX(Size)" query="SendDate > 2009-10-15"
group="GROUP(*),GROUP(TRUNCATE(SendDate,WEEK)),GROUP(TOP(2,TERMS(Subject)),Tags)"/>

<totalobjects>6032</totalobjects>

<groupsets>

<groupset>

<value>16796009</value>

</groupset>

<groupset group="TRUNCATE(SendDate,WEEK)">

<summary>16796009</summary>

<groups>

<group>

<field name="SendDate">2010-07-12 00:00:00</field>

<metric>965230</metric>

</group>

...

</groups>

</groupset>

<groupset group="TOP(2,TERMS(Subject)),Tags">

<summary>16796009</summary>

<totalgroups>15267</totalgroups>

<groups>

<group>

<summary>16796009</summary>

<field name="Subject">scalepan</field>

<groups>

<group>

<metric>16796009</metric>

<field name="Tags">AfterHours</field>

</group>

...

</groups>

</group>

...

<group composite="true">

<field name="Subject">*</field>

<groups>

<group>

<metric>7317</metric>

<field name="Tags">(null)</field>

</group>

...

</groups>

</group>

</groups>

</groupset>

</groupsets>

</results>

In JSON:

{"results": {

"aggregate": {

"group": "GROUP(*),GROUP(TRUNCATE(SendDate,WEEK)),GROUP(TOP(2,TERMS(Subject)),Tags)",

"query": "SendDate > 2009-10-15",

"metric":"MAX(Size)"

},

"totalobjects": "6032",

"groupsets": [

{"groupset": {

"value": "16796009"

}},

{"groupset": {

"group": "TRUNCATE(SendDate,WEEK)",

"summary": "16796009",

"groups": [

{"group": {

"field": {"SendDate": "2010-07-12 00:00:00"},

"metric": "16796009"

}},

...

]

}},

{"groupset": {

"group": " TOP(2,TERMS(Subject)),Tags ",

"summary": "16796009",

"totalgroups": "15267",

"groups": [

{"group": {

"summary": "16796009",

"field": {"Subject": "scalepan"},

"groups": [

{"group": {

"metric": "16796009",

"field": {"Tags": "AfterHours"}

}},

{"group": {

"metric": "16796009",

"field": {"Tags": "Customer"}

}}

]

}},

...

{"group": {

"composite": "true",

"field": {"Origin": "*"},

"groups": [

{"group": {

"metric": "7317",

"field": {"Tags": "(null)"}

}},

...

]

}}

]

}}

]

}}

Notable aspects of a compound group result:

•	As with all aggregate queries, the outer results element contains an aggregate element that confirms the aggregate query parameters.

•	The results element also contains a groupsets element, which contains one groupset element per grouping set, that is, for each GROUP function.

•	The contents of each groupset element follows the format applicable for global, single-level, or multi-level aggregate queries, except that they do not contain an aggregate element.

•	As with all grouped aggregate queries, each groupset and each non-leaf group contains a summary value.

•	Only multi-level grouping sets can contain a composite group, denoted by a composite=true element and a field value of “*”.