[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: [OASIS Issue Tracker] (ODATA-947) Transformation for computing ratios with aggregated values
[ https://issues.oasis-open.org/browse/ODATA-947?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gerald Krause updated ODATA-947:
--------------------------------
Description:
It is interesting to get the ratio between an individual and an aggregated value from the same input set.
Examples:
1. Customer classification: Revenue per customer in relation to total revenue
2. Revenue trend: Revenue per year in relation to total revenue for any given customer
3. Order size: Order volume relative to average order volume
4. Controlling: Actual/plan deviation per product compared to overall deviation for some year
They cannot be expressed with combining groupby/aggregate, because the individual entity values are no longer available after these transformations have produced an aggregated value.
The ratio transformation takes a comma-separated list of one or more parameters describing a value, an aggregate expression, and an alias:
ratio(valueExpression to aggregateExpression as alias, …)
A valueExpression may be an expression valid in a $filter system query option on the input set that results in a simple value. The aggregateExpression is an aggregate expression as defined for transformation aggregate (section 3.1). The alias introduces a dynamic property for the ratio values in the result set that are of type Edm.Decimal with unspecified precision and variable scale.
The transformation first determines the aggregated value for the input set by applying the aggregateExpression. Then, it calculates for every entity in the input set the ratio between the valueExpression and the aggregated value and adds the result as dynamic property to the entity that is put into the result set.
Applied to examples:
Re 1:
GET ~/Sales?$apply=
groupby((Customer),aggregate(Amount with sum as CustomerAmount))
/ratio(CustomerAmount to CustomerAmount with sum as RevenueContribution)
Re 2:
GET ~/Sales?$apply=
groupby((Customer,Year) aggregate(Amount with sum as CustomerYearAmount))
/groupby((Customer),ratio(CustomerYearAmount to CustomerYearAmount with sum as RevenueTrend)
Re 3:
GET ~/Sales?$apply=ratio(Amount to Amount with average as RelativeOrderSize)
Re 4:
Add an entity set SalesPlan(Year,Product,PlannedRevenue), and a 1:n navigation property SalesPlan: Product -> SalesPlan.
GET ~/Sales?$apply=
filter(Time/Year eq 2015)
/groupby((Product),aggregate(Amount with sum as ActualRevenue,
Product/SalesPlan('2015')/PlannedRevenue with sum as PlannedRevenue))
/ratio(PlannedRevenue sub ActualRevenue to PlannedRevenue sub ActualRevenue with sum as RelativeDeviation)
In all these example, the value expression is identical with the base in the aggregate expression, that is, they follow the pattern: ratio(X to X with M as A). If there were no other use cases, the syntax could be simplified to: ratio(X with M as A).
was:
It is interesting to get the ratio between an individual and an aggregated value from the same input set.
Examples:
1. Customer classification: Revenue per customer in relation to total revenue
2. Revenue trend: Revenue per year in relation to total revenue for any given customer
3. Order size: Order volume relative to average order volume
4. Controlling: Actual/plan deviation per product compared to overall deviation for some year
They cannot be expressed with combining groupby/aggregate, because the individual entity values are no longer available after these transformations have produced an aggregated value.
The ratio transformation takes a comma-separated list of one or more parameters describing a value, an aggregate expression, and an alias:
ratio(valueExpression to aggregateExpression as alias, …)
A valueExpression may be an expression valid in a $filter system query option on the input set that results in a simple value. The aggregateExpression is an aggregate expression as defined for transformation aggregate (section 3.1). The alias introduces a dynamic property for the ratio values in the result set that are of type Edm.Decimal with unspecified precision and variable scale.
The transformation first determines the aggregated value for the input set by applying the aggregateExpression. Then, it calculates for every entity in the input set the ratio between the valueExpression and the aggregated value and adds the result as dynamic property to the entity that is put into the result set.
Applied to examples:
Re 1:
GET ~/Sales?$apply=
groupby((Customer),aggregate(Amount with sum as CustomerAmount))
/ratio(CustomerAmount to CustomerAmount with sum as RevenueContribution)
Re 2:
GET ~/Sales?$apply=
groupby((Customer,Year) aggregate(Amount with sum as CustomerYearAmount))
/groupby((Customer),ratio(CustomerYearAmount to CustomerYearAmount with sum as RevenueTrend)
Re 3:
GET ~/Sales?$apply=ratio(Amount to Amount with average as RelativeOrderSize)
Re 4:
Add an entity set SalesPlan(Year,Product,PlannedRevenue), and a 1:n navigation property SalesPlan: Product SalesPlan.
GET ~/Sales?$apply=
filter(Time/Year eq 2015)
/expand(Product/SalesPlan,filter(Year eq 2015))
/groupby((Product),aggregate(Amount with sum as ActualRevenue,
Product/SalesPlan/PlannedRevenue with sum as PlannedRevenue))
/ratio(PlannedRevenue sub ActualRevenue to PlannedRevenue sub ActualRevenue with sum as RelativeDeviation)
In all these example, the value expression is identical with the base in the aggregate expression, that is, they follow the pattern: ratio(X to X with M as A). If there were no other use cases, the syntax could be simplified to: ratio(X with M as A).
> Transformation for computing ratios with aggregated values
> ----------------------------------------------------------
>
> Key: ODATA-947
> URL: https://issues.oasis-open.org/browse/ODATA-947
> Project: OASIS Open Data Protocol (OData) TC
> Issue Type: New Feature
> Components: OData Extension for Data Aggregation
> Affects Versions: V4.0_CS02
> Reporter: Gerald Krause
> Fix For: V4.0_CSD04
>
>
> It is interesting to get the ratio between an individual and an aggregated value from the same input set.
> Examples:
> 1. Customer classification: Revenue per customer in relation to total revenue
> 2. Revenue trend: Revenue per year in relation to total revenue for any given customer
> 3. Order size: Order volume relative to average order volume
> 4. Controlling: Actual/plan deviation per product compared to overall deviation for some year
> They cannot be expressed with combining groupby/aggregate, because the individual entity values are no longer available after these transformations have produced an aggregated value.
> The ratio transformation takes a comma-separated list of one or more parameters describing a value, an aggregate expression, and an alias:
> ratio(valueExpression to aggregateExpression as alias, …)
> A valueExpression may be an expression valid in a $filter system query option on the input set that results in a simple value. The aggregateExpression is an aggregate expression as defined for transformation aggregate (section 3.1). The alias introduces a dynamic property for the ratio values in the result set that are of type Edm.Decimal with unspecified precision and variable scale.
> The transformation first determines the aggregated value for the input set by applying the aggregateExpression. Then, it calculates for every entity in the input set the ratio between the valueExpression and the aggregated value and adds the result as dynamic property to the entity that is put into the result set.
> Applied to examples:
> Re 1:
> GET ~/Sales?$apply=
> groupby((Customer),aggregate(Amount with sum as CustomerAmount))
> /ratio(CustomerAmount to CustomerAmount with sum as RevenueContribution)
> Re 2:
> GET ~/Sales?$apply=
> groupby((Customer,Year) aggregate(Amount with sum as CustomerYearAmount))
> /groupby((Customer),ratio(CustomerYearAmount to CustomerYearAmount with sum as RevenueTrend)
> Re 3:
> GET ~/Sales?$apply=ratio(Amount to Amount with average as RelativeOrderSize)
> Re 4:
> Add an entity set SalesPlan(Year,Product,PlannedRevenue), and a 1:n navigation property SalesPlan: Product -> SalesPlan.
> GET ~/Sales?$apply=
> filter(Time/Year eq 2015)
> /groupby((Product),aggregate(Amount with sum as ActualRevenue,
> Product/SalesPlan('2015')/PlannedRevenue with sum as PlannedRevenue))
> /ratio(PlannedRevenue sub ActualRevenue to PlannedRevenue sub ActualRevenue with sum as RelativeDeviation)
> In all these example, the value expression is identical with the base in the aggregate expression, that is, they follow the pattern: ratio(X to X with M as A). If there were no other use cases, the syntax could be simplified to: ratio(X with M as A).
--
This message was sent by Atlassian JIRA
(v6.2.2#6258)
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]