We've recently published the first version of the
OData Extension for Data Aggregation. As its name suggests, it is an extension to
OData 4.0 that allows requesting aggregated data without violating the basic OData interaction paradigms.
In a nutshell it adds a single system query option
$apply that takes a sequence of transformations:
$apply= transformation / transformation / ...
The specification defines three types of standard transformations:
- Subsetting
- topcount(...), topsum(...), toppercent(...)
- bottomcount(...), bottomsum(...), bottompercent(...)
- filter(...)
- identity
- Reshaping
- aggregate(...)
- expand(...)
- Forking & Merging
- groupby(..., sequence )
- concat( sequence, sequence, ... )
In addition service-defined custom functions can be used as set transformations.
This provides you with a simple and powerful query language that covers all of the use cases we came up with, and then some.
Here are some examples, based on a fictitious Sales entity set:
- Aggregate one property, grouped by another
$apply=groupby((Product),aggregate(Revenue))
- Request distinct values of a property
$apply=groupby((Product))
$apply=aggregate(Revenue)
- First filter, then group and aggregate
$apply=filter(Revenue lt 1000)/groupby((Product),aggregate(Revenue))
- Count input entities per group
$apply=groupby((Product,Country),aggregate($count as NumberOfDeals))
- First group and sum, then group differently and average the sums calculated in the first step
$apply=groupby((Product,Month),aggregate(Revenue))
/groupby((Product),aggregate(Revenue with average
as MonthlyAverageRevenue))
And a lot more I won't mention here, just have a look at the
draft specification.
Your
feedback is welcome!
--Ralf