1

I have an entity which has an "amount" attribute and a "date" attribute. I have many of these entities, multiple in a day. I want to provide a daily avg of amounts, say over the span of a month.

I understand how to get the average of day's worth of amounts using the aggregate functions. But I don't understand how to return a list of daily averages for all the days in my data set.

It seems like there must be a better way than to create a fetch request for each day? Perhaps i have to utilize subqueries in some fashion?

(Related, but different, I also would like to calculate a moving average, so that I could return, say, a 20 day moving average of amounts. In this case, a list of avg.'s, each of which is the 20day average at that day)

rmaddy
  • 314,917
  • 42
  • 532
  • 579
user903309
  • 41
  • 5

2 Answers2

1

Core Data is not a database. There are things you can do in a database that you need to write code for in Core Data (technically you would need to write code in a database as well). This is a case where you will need to write code.

So you can either fetch per day and have the store calculate the average or you can retrieve all of the data you are interested in and do the daily average in memory.

Doing one fetch per day will be less code but will be slower since you need to hit the store once for each day.

Doing one fetch and calculating in memory will be faster but will take more code.

Personally, I would fetch all relevant data from the store and then iterate over a predicate and have the predicate calculate the average per day.

Marcus S. Zarra
  • 46,571
  • 9
  • 101
  • 182
  • Hi Marcus. Thanks for your comment. It looks like you have lots of experience in Core Data, so if you would check my posted answer and comment if you see problems, I'd appreciate it. – user903309 Apr 28 '16 at 02:10
  • Does it work? If so then you do not need my confirmation :) Since you are able to adjust the data model and the incoming data then you have a greater flexibility than your original question implied and you have found a cleaner solution. Well done :) – Marcus S. Zarra Apr 28 '16 at 15:11
0

So, it seems with a slight change to the dataset, this is indeed possible. But, for this solution at least, you need to be in charge of your dataset.

I added an attribute "dateDay" which is an int that represents the number of days from an anchor date to the "date" attribute in my entity. (see Number of days between two NSDates)

Then you group by "dateDay" and use an "average:" NSExpression. The NSExpression aggregate operates on the groups when using groupBy.

NSExpression* ex = [NSExpression expressionWithFormat:@"average:(amount)"];
NSExpressionDescription* ed = [[NSExpressionDescription alloc] init];
ed.name = @"result";
ed.expression = ex;
ed.expressionResultType = NSInteger32AttributeType;

NSFetchRequest* request = [[NSFetchRequest alloc] initWithEntityName:@"Transaction"];
request.resultType = NSDictionaryResultType;
request.propertiesToFetch = @[@"dateDay", ed];
request.propertiesToGroupBy = @[@"dateDay"];

NSError     * error             = nil;
NSArray     * resultsArray      = [context executeFetchRequest:request error:&error];

Although propertiesToGroupBy is documented to take an NSExpression, it seems that it cannot perform computations, so passing an NSExpression to reduce the NSDate to a component that can be grouped on does not work. Hence the need for the additional attribute.

Community
  • 1
  • 1
user903309
  • 41
  • 5