Multi metric Aggregate Queries (Spider)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/Spider Databases: Table-of-Contents) | Previous | [Next] (https://github.com/dell-oss/Doradus/wiki/Spider-REST-Commands)
Spider Aggregate Queries: Multi-metric Aggregate Queries

---

The metric parameter can use a comma-separated list of metric functions. Such multi-metric queries perform multiple metric computations in a single pass through the data. The simplest case uses no grouping parameter. For example:

.../_aggregate?m=COUNT(*),MAX(Size),AVERAGE(Size)

This query requests a count of all objects and the maximum and average values for the Size field. Multi-metric query results use an outer groupsets element containing one groupset for each metric function. The query above returns results such as the following:

<results>
	<aggregate metric="COUNT(*),MAX(Size),AVERAGE(Size)"/>
	<groupsets>
		<groupset metric="COUNT(*)">
			<value>6030</value>
		</groupset>
		<groupset metric="MAX(Size)">
			<value>16796009</value>
		</groupset>
		<groupset metric="AVERAGE(Size)">
			<value>31615.808</value>
		</groupset>
	</groupsets>
</results>

In JSON:

{"results": {
	"aggregate": {"metric": "COUNT(*),MAX(Size),AVERAGE(Size)"},
	"groupsets": [
		{"groupset": {
			"metric": "COUNT(*)",
			"value": "6030"
		}},
		{"groupset": {
			"metric": "MAX(Size)",
			"value": "16796009"
		}},
		{"groupset": {
			"metric": "AVERAGE(Size)",
			"value": "31615.808"
		}}
	]
}}

As shown, one groupset is provided for each metric function. Since there is no grouping parameter, the format of each groupset matches that of a global aggregate query: a metric element identifies which metric function is computed, and a value element provides the function’s value.

For grouped, multi-metric aggregate queries, each metric function is computed for all inner and outer group levels. Any mix of metric functions can be used except for the DISTINCT function, which cannot be used in a multi-metric queries. Grouped queries produce one groupset result for each metric function as in the non-grouped case. However, each groupset will contain groups that decompose the metric computations in the appropriate groups.

When a multi-metric aggregate query uses the TOP or BOTTOM function in the outer grouping field, the TOP or BOTTOM limit is derived from the first metric function. The outer and inner groups are selected on this metric function, and each metric function is performed for those groups. For example:

.../_aggregate?m=COUNT(*),MAX(Size),AVERAGE(Size)&f=TOP(2,Tags),TRUNCATE(SendDate,MONTH)

The COUNT(*) metric determines which outer groups are included in the TOP(2) grouping; the COUNT(*), MAX(Size), and AVERAGE(Size) functions are returned for all these 2 outer groups and the corresponding inner groups. A typical response to the query above in XML:

<results>		
	<aggregate metric="COUNT(*),MAX(Size),AVERAGE(Size)"
		group="TOP(2,Tags),TRUNCATE(SendDate,MONTH)"/>
	<groupsets>
		<groupset group="TOP(2,Tags),TRUNCATE(SendDate,MONTH)" metric="COUNT(*)">
			<summary>6030</summary>
			<totalgroups>3</totalgroups>
			<groups>
				...
			</groups>
		</groupset>
		<groupset group="TOP(2,Tags),TRUNCATE(SendDate,MONTH)" metric="MAX(Size)">
			<summary>16796009</summary>
			<totalgroups>3</totalgroups>
			<groups>
			...
			</groups>
		</groupset>
		<groupset group="TOP(2,Tags),TRUNCATE(SendDate,MONTH)" metric="AVERAGE(Size)">	
			<summary>31615.808</summary>	
			<totalgroups>3</totalgroups>	
			<groups>	
			...	
			</groups>	
		</groupset>	
	</groupsets>
</results>

Because the outer grouping level used the TOP function, a totalgroups value is provided for each outer grouping level. Here’s the same response in JSON:

{"results": {
	"aggregate": {
		"group": "TOP(2,Tags),TRUNCATE(SendDate,MONTH)",
		"metric": "COUNT(*),MAX(Size),AVERAGE(Size)"
	},
	"groupsets": [
		{"groupset": {
			"metric": "COUNT(*)",
			"group": "TOP(2,Tags),TRUNCATE(SendDate,MONTH)",
			"groups": [
			...
			],
			"summary": "6030",
			"totalgroups": "2"
		}},
		{"groupset": {
			"metric": "MAX(Size)",
			"group": "TOP(2,Tags),TRUNCATE(SendDate,MONTH)",
			"groups": [
			...
			],
			"summary": "16796009",
			"totalgroups": "2"
		}},
		{"groupset": {
			"metric": "AVERAGE(Size)",
			"group": "TOP(2,Tags),TRUNCATE(SendDate,MONTH)",
			"groups": [
			...
			],
			"summary": "31615",
			"totalgroups": "2"
		}}
	]
}}