Uploaded image for project: 'OASIS Open Data Protocol (OData) TC'
  1. OASIS Open Data Protocol (OData) TC
  2. ODATA-1477

Aggregation on different levels

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Data Aggregation
    • Labels:
      None

      Description

      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

      $apply=groupby((Country),concat(
        groupby((AccountResponsible))/aggregate($count as number_of_leaves),
        aggregate(SalesNumber,SalesAmount)
      ))
      /orderby(SalesNumber desc)/top(4)
      

      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

      $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)
      

      But this is cumbersome and requires renaming the properties.

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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              heiko.theissen Heiko Theissen
            • Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: