Aggregate Query Commands (OLAP)

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

---

Doradus OLAP supports two commands for submitting aggregate queries. Details of aggregate query parameters and the output formats returned by queries are described in the section [OLAP Aggregate Queries](https://github.com/dell-oss/Doradus/wiki/OLAP-Aggregate-Queries).

Aggregate Query via URI

An aggregate query can submit all parameters in the URI of a GET request. The REST command is:

GET /{application}/{table}/_aggregate?{params}

where {application} is the application name, {table} is the perspective table, and {params} are URI parameters separated by ampersands (&). The following parameters are supported:

• m=metric expression list (required): A list of one or more metric expressions to calculate for selected objects. A metric expression is an algebraic expression consisting of functions, constants, simple math operators, and parentheses. Each expression is computed across selected objects, optionally subdivided into groups as defined by the grouping parameter. Example metric parameters:

    m=COUNT(*)
    m=DISTINCT(Name)
    m=SUM(Size)
    m=MAX(Sender.Person.LastName)
    m=AVERAGE(SendDate)
    m=COUNT(*) / COUNT(Sender.Person)
    m=COUNT(*) / COUNT(Sender.Person) / (DATEDIFF(DAY,"2010-01-01","2011-12-31") + 1)
    m=MAX(Size), MIN(Size), AVERAGE(Size), COUNT(*)		// 4 metric epressions
  

• q=text (optional): A DQL query expression that defines which objects to include in metric computations. If omitted, all objects are selected (same as q=*). Examples:

    q=*
    q=LastName=Smith
    q=ALL(InternalRecipients.Person.Domain).IsInternal = false
  

• f=grouping list (optional): A list of one or more grouping expressions, which divide computations into single- or multi-level groups. When this parameter is omitted, the corresponding global query computes a single value for each metric expression. When provided, the corresponding grouped query computes a value for each group value/metric expression combination. Examples:

    f=Tags
    f=TOP(3,Sender.Person.Department)
    f=BATCH(Size,1000,10000,100000),TOP(5,ExternalRecipients.MessageAddress.Domain.Name)
  

• pair=pair list: Defines two fields that are used to compute special dual role queries. See the section Pair Parameter for a description. Example:

    pair=Sender,InternalRecipients
  

• shards=shards (required*): A comma-separated list of shard names. Only the specified shards are searched. Either this or the range parameter must be provided. Examples:

    shards=2010-01-01
    shards=13Q1,13Q2,13Q2
  

• range=shard-from[,shard-to] (required*): A starting shard name and optionally an ending shard name. Only shard names in the specified range are searched. If the ending shard-to name is omitted, all shards whose name is greater than or equal to the shard-from name are searched. Either this or the shards parameter must be provided. Examples:

    range=2013-11-01,2013-12-31		// "2013-11-01" <= shard name <= "2013-12-31"
    range=2013-01-01			    // "2013-01-01" <= shard name
    range=2013				        // "2013" <= shard name
  

• xshards=shards (optional): A comma-separated list of shard names to search for objects referenced by xlinks. If the aggregate query has no xlinks, this parameter is ignored. If this and the xrange parameter are both omitted, the scope of xlink searching is the same as the shards or range parameter. Either this of the xrange parameter can be provided, but not both. See the shards parameter above for examples.

• xrange=shard-from[,shard-to] (optional): A starting shard name and optional ending shard name that defines the range of shards to search for objects referenced by xlinks. If the ending shard-to name is omitted, all shards whose name is greater than or equal to the shard-from name are searched. If the aggregate query has no xlinks, this parameter is ignored. If this and the xshards parameter are both omitted, the xlink search scope is the same as defined by the shards or range parameter. Either this of the xshards parameter can be provided, but not both. See the range parameter above for examples.

Below is an example aggregate query using URI parameters:

GET /Email/Message/_aggregate?q=Size>10000&m=COUNT(*)&f=TOP(3,Sender.Person.Department)&range=2014

An example response in XML is:

<results>
	<aggregate metric="COUNT(*)" query="Size>10000" group="TOP(3,Sender.Person.Department)"/>
	<totalobjects>1254</totalobjects>
	<summary>1254</summary>
	<totalgroups>37</totalgroups>
	<groups>
		<group>
			<metric>926</metric>
			<field name="Sender.Person.Department">(null)</field>
		</group>
		<group>
			<metric>82</metric>
			<field name="Sender.Person.Department">HR</field>
		</group>
		<group>
			<metric>45</metric>
			<field name="Sender.Person.Department">Sales Technical Specialists</field>
		</group>
	</groups>
</results>

In JSON:

{"results": {
	"aggregate": {
		"metric": "COUNT(*)",
		"query": "Size>10000",
		"group": "TOP(3,Sender.Person.Department)"
	},
	"totalobjects": "1254",
	"summary": "1254",
	"totalgroups": "37",
	"groups": [
		{"group": {
			"metric": "926",
			"field": {"Sender.Person.Department": "(null)"}
		}},
		{"group": {
			"metric": "82",
			"field": {"Sender.Person.Department": "HR"}
		}},
		{"group": {
			"metric": "45",
			"field": {"Sender.Person.Department": "Sales Technical Specialists"}
		}}
	]
}}

Aggregate Query via Entity

Aggregate query parameters can be provided in an input entity instead of URI parameters. The same REST command is used except that no URI parameters are provided. Because some browsers/HTTP frameworks do not support HTTP GET-with-entity, this command also supports the PUT method even though no modifications are made. Both of the following are equivalent:

GET /{application}/{table}/_aggregate
PUT /{application}/{table}/_aggregate

where {application} is the application name and {table} is the perspective table. The input entity must be a JSON or XML document whose root element is aggregate-searc``h. Aggregate query parameters are given as child elements. URI query parameters map to the following element names:

URI Parameter	Element name
q	query
m	metric
f	grouping-fields
pair	pair
shards	shards
range	shards-range
xshards	x-shards
xrange	x-shards-range

Below is an example aggregate query request entity in XML:

<aggregate-search>
	<query>Size&gt;10000</query>
	<metric>COUNT(*)</metric>
	<grouping-fields>TOP(3,Sender.Person.Department)</grouping-fields>
	<shards-range>2014</shards-range>
</aggregate-search>

In JSON:

{"aggregate-search": {
	"query": "Size>10000",
	"metric": "COUNT(*)",
	"grouping-fields": "TOP(3,Sender.Person.Department)",
	"shards-range": "2014"
}}