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

Add a function to determine aggregated values within common expressions

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: V4.0_CS02
    • Fix Version/s: V4.0_CSD04
    • Component/s: Data Aggregation
    • Labels:
      None
    • Environment:

      Applied

    • Proposal:
      Hide

      1.

      Add a new section before 3.16 Evaluating $apply to define a function aggregate that has a single parameter accepting an aggregate-function expression and returns the aggregated value of type Edm.PrimitiveType as the result from applying the aggregate expression on an input collection provided by the context.

      An aggregate-function expression offers the same capabilities as an aggregate expression defined for the aggregate transformation except that it does not support aggregation on related entities, and it never includes an alias for introducing a dynamic property containing the aggregated value.

      The function can be applied in any expression derived from the commonExpr rule (see **[OData-ABNF]). Within $apply, the function is applied as part of an argument expression to a transformation, and the input set of that transformation is the input collection for the function. Outside of $apply, the function may be applied in requests querying collections in an argument expression for a system query option and a lambda operator. Inside a system query option, the collection on which it operates is used as input collection for the function. This is also valid for nested query options used as arguments, like $filter in $expand. Inside a lambda predicate expression the input collection for the aggregate function depends on the path expression used in the aggregateFunctionExpr (see ABNF below): For a path expression starting with the lambda variable the input collection is given by the target of the navigation path prepended to the lambda operator; for a path expression starting with $it the collection addressed by the resource path is the input collection; for other path expressions the input collection is given by the collection of instances at the origin of the navigation path prepended to the lambda operator.

       

      2.
      In the Data Aggregation’s ABNF, define the rule for computeAggregateMethodCallExpr as

      aggregateMethodCallExpr = 'aggregate' OPEN BWS aggregateFunctionExpr BWS CLOSE 
      
      aggregateFunctionExpr   = '$count'
                              / commonExpr aggregateWith [ aggregateFrom ]
                              / / [ inscopeVariableExpr "/" ] pathPrefix primitiveProperty aggregateWith [ aggregateFrom ]
                              / / [ inscopeVariableExpr "/" ] pathPrefix customAggregate [ aggregateFrom ]
      

      Add the aggregateMethodCallExpr rule to the set of rules recognized by methodCallExpr within a commonExpr:

      methodCallExpr =/ aggregateMethodCallExpr
      
      Show
      1. Add a new section before 3.16 Evaluating $apply to define a function aggregate that has a single parameter accepting an aggregate-function expression and returns the aggregated value of type Edm.PrimitiveType as the result from applying the aggregate expression on an input collection provided by the context. An aggregate-function expression offers the same capabilities as an aggregate expression defined for the aggregate transformation except that it does not support aggregation on related entities, and it never includes an alias for introducing a dynamic property containing the aggregated value. The function can be applied in any expression derived from the commonExpr rule (see ** [OData-ABNF] ). Within $apply, the function is applied as part of an argument expression to a transformation, and the input set of that transformation is the input collection for the function. Outside of $apply, the function may be applied in requests querying collections in an argument expression for a system query option and a lambda operator. Inside a system query option, the collection on which it operates is used as input collection for the function. This is also valid for nested query options used as arguments, like $filter in $expand. Inside a lambda predicate expression the input collection for the aggregate function depends on the path expression used in the aggregateFunctionExpr (see ABNF below): For a path expression starting with the lambda variable the input collection is given by the target of the navigation path prepended to the lambda operator; for a path expression starting with $it the collection addressed by the resource path is the input collection; for other path expressions the input collection is given by the collection of instances at the origin of the navigation path prepended to the lambda operator.   2. In the Data Aggregation’s ABNF, define the rule for computeAggregateMethodCallExpr as aggregateMethodCallExpr = 'aggregate' OPEN BWS aggregateFunctionExpr BWS CLOSE  aggregateFunctionExpr   = '$count' / commonExpr aggregateWith [ aggregateFrom ]                         / / [ inscopeVariableExpr "/" ] pathPrefix primitiveProperty aggregateWith [ aggregateFrom ] / / [ inscopeVariableExpr "/" ] pathPrefix customAggregate [ aggregateFrom ] Add the aggregateMethodCallExpr rule to the set of rules recognized by methodCallExpr within a commonExpr: methodCallExpr =/ aggregateMethodCallExpr
    • Resolution:
      Show
      https://github.com/oasis-tcs/odata-abnf/pull/11  

      Description

      Currently, there is no possibility to combine values of a single entity with an aggregated value determined from the input set in a single calculation. Expressions passed to the compute transformation always operate on properties of the individual entities in the input set. This prevents use cases like calculating the ratio of an entity's property value to an aggregated value from the input set. Examples for ratio calculations can be found in ODATA-947

        Attachments

          Activity

          Hide
          mikep Michael Pizzo (Inactive) added a comment - - edited

          Could we prefix the aggregate with the input expression?

          $filter=Products/any(p: p/aggregate(Amount with average) gt 10)

          Show
          mikep Michael Pizzo (Inactive) added a comment - - edited Could we prefix the aggregate with the input expression? $filter=Products/any(p: p/aggregate(Amount with average) gt 10)
          Hide
          gerald.krause1 Gerald Krause added a comment -

          Examples:

          GET Products?$filter=Sales/any(s:s/Amount gt aggregate($it/Sales/Amount with average))

          GET Products?$filter=Sales/any(s:s/Amount gt aggregate(Sales/Amount with average))

          GET Categories?$expand=Products($filter=Sales/any(s:s/Amount gt aggregate(Sales/Amount with average)))

          GET Categories?$expand=Products($filter=Sales/any(s:s/Amount gt aggregate($root/Categories/Products/Sales/Amount with average)))

          Show
          gerald.krause1 Gerald Krause added a comment - Examples: GET Products?$filter=Sales/any(s:s/Amount gt aggregate($it/Sales/Amount with average)) GET Products?$filter=Sales/any(s:s/Amount gt aggregate(Sales/Amount with average)) GET Categories?$expand=Products($filter=Sales/any(s:s/Amount gt aggregate(Sales/Amount with average))) GET Categories?$expand=Products($filter=Sales/any(s:s/Amount gt aggregate($root/Categories/Products/Sales/Amount with average)))
          Hide
          handl Ralf Handl added a comment -

          2019-01-17

          Show
          handl Ralf Handl added a comment - 2019-01-17
          Hide
          gerald.krause1 Gerald Krause added a comment -

          For the record: The proposal requires some examples in my comment from 2018/12/04 to be rewritten as follows:

          4. $filter
          Products with total sales amount > 3

          GET ~/Products?$filter=Sales/any(p:aggregate(p/Amount with sum) gt 3)

          5. $orderby (NEW: requires $apply)
          Customers in descending order of their aggregated sales volume

          GET ~/Customers?$apply=groupby((ID),compute(aggregate(Sales/Amount with sum) as TotalAmount))&$orderby=TotalAmount desc

          6. Lambda operators
          a) Product categories with at least one product having an aggregated sales amount greater than 3

          GET ~/Categories?$filter=Products/any(p:Sales/any(s:aggregate(s/Amount with sum) gt 3))

          Show
          gerald.krause1 Gerald Krause added a comment - For the record: The proposal requires some examples in my comment from 2018/12/04 to be rewritten as follows: 4. $filter Products with total sales amount > 3 GET ~/Products?$filter=Sales/any(p:aggregate(p/Amount with sum) gt 3) 5. $orderby (NEW: requires $apply) Customers in descending order of their aggregated sales volume GET ~/Customers?$apply=groupby((ID),compute(aggregate(Sales/Amount with sum) as TotalAmount))&$orderby=TotalAmount desc 6. Lambda operators a) Product categories with at least one product having an aggregated sales amount greater than 3 GET ~/Categories?$filter=Products/any(p:Sales/any(s:aggregate(s/Amount with sum) gt 3))
          Hide
          heiko.theissen Heiko Theissen added a comment -

          These examples are problematic, because they use expressions like $filter and any that, according to the spec, look at each instance of a collection in turn, without being aware of the entire collection. But the aggregate function requires awareness of the entire collection.

          Implementors may therefore find the aggregate function impossible to implement without reworking their existing implementations of $filter and any, etc.

          To tidy this up, something like the "context" in the XPath specification might have to be introduced here.

          Show
          heiko.theissen Heiko Theissen added a comment - These examples are problematic, because they use expressions like $filter and any that, according to the spec, look at each instance of a collection in turn, without being aware of the entire collection. But the aggregate  function requires awareness of the entire collection. Implementors may therefore find the aggregate function impossible to implement without reworking their existing implementations of  $filter and any , etc. To tidy this up, something like the "context"  in the XPath specification might have to be introduced here.

            People

            • Assignee:
              Unassigned
              Reporter:
              gerald.krause1 Gerald Krause
            • Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: