[OASIS Issue Tracker] (ODATA-1477) Aggregation on different levels

[OASIS Issues Tracker <workgroup_mailer@lists.oasis-open.org>]

Heiko Theissen created ODATA-1477:
-------------------------------------

             Summary: Aggregation on different levels
                 Key: ODATA-1477
                 URL: https://issues.oasis-open.org/browse/ODATA-1477
             Project: OASIS Open Data Protocol (OData) TC
          Issue Type: Improvement
          Components: Data Aggregation
            Reporter: Heiko Theissen


Use case: Display top 4 countries by number of sales, with aggregate sales amount and number of account responsibles per country.

This requires aggregation on two different levels:
 * per distinct account responsible per country
 * per country

This can be achieved with a {{concat}} transformation inside {{groupby}}
{code:java}
$apply=groupby((Country),concat(
  groupby((AccountResponsible))/aggregate($count as number_of_leaves),
  aggregate(SalesNumber,SalesAmount)
))
/orderby(SalesNumber desc)/top(4)
{code}
that returns two rows per country
||Country||number_of_leaves||SalesNumber||SalesAmount||
|DE|2000|Â|Â|
|DE|Â|500|5000|
|UK|1500|Â|Â|
|UK|Â|400|5500|

But then the subsequent {{orderby}} cannot sort the countries, because each country occupies two rows. Before the {{orderby}}, the table would need to be compressed into
||Country||number_of_leaves||SalesNumber||SalesAmount||
|DE|2000|500|5000|
|UK|1500|400|5500|

One way to achieve this is
{code:java}
$apply=groupby((Country),concat(
  groupby((AccountResponsible))/aggregate($count as number_of_leaves),
  aggregate(SalesNumber,SalesAmount)
)/aggregate(
  number_of_leaves with max as number_of_leaves2,
  SalesNumber with max as SalesNumber2,
  SalesAmount with max as SalesAmount2
))
/orderby(SalesNumber2 desc)/top(4)
{code}
But this is cumbersome and requires renaming the properties.

Can [OData-Aggr] offer a more elegant solution?



--
This message was sent by Atlassian Jira
(v8.3.3#803004)