The Dataset contains records with following keys: userID, period and points.
Is it possible to query one record per period
and by max points
.
Example scenario
Dataset
[
/* period: 2016-01-01 */
{ userID: 1, period: '2016-01-01', points: 100},
{ userID: 1, period: '2016-01-01', points: 200},
{ userID: 1, period: '2016-01-01', points: 300},
/* period: 2016-01-02 */
{ userID: 1, period: '2016-01-02', points: 50},
{ userID: 1, period: '2016-01-02', points: 70},
{ userID: 1, period: '2016-01-02', points: 10},
/* period: 2016-01-03 */
{ userID: 1, period: '2016-01-03', points: 80},
{ userID: 1, period: '2016-01-03', points: 20},
{ userID: 1, period: '2016-01-03', points: 0},
]
Query results:
These are the desired query results. One record per each period and max points for that period
{ userID: 1, period: '2016-01-02', points: 300},
{ userID: 1, period: '2016-01-03', points: 70},
{ userID: 1, period: '2016-01-04', points: 80},
This is possible with the $apply
query option defined in the OData Extension for Data Aggregation Version 4.0, assuming the entity set to be queried is named PointHistory
:
GET PointHistory?$apply=groupby((period),topcount(1,points))
This will group the records by period
, order the records in each group by points
and then return the topmost record per group.