How to list average of last-n values

[Daniel Lidström]

Hello,

I'd like to display an average of the last 20 series played by the bowlers on my team.
That is, for each one of them show their total average and also their average for the last
20 series. Total average is easy with map/reduce, but average for last 20 does not seem
to fit with this model. I can calculate the last 20 for a single player like follows:

    public class Pins_ByMatchDate : AbstractIndexCreationTask<Match, Pins_ByMatchDate.Result>
    {
        public Pins_ByMatchDate()
        {
            Map = matches => from match in matches
                             from team in match.Teams
                             from serie in team.Series
                             from table in serie.Tables
                             from game in table.Games
                             select new
                             {
                                 Date = match.Date.ToShortDateString(),
                                 Player = game.Player,
                                 Pins = game.Pins
                             };

            Store(x => x.Date, FieldStorage.Yes);
            Store(x => x.Player, FieldStorage.Yes);
            Store(x => x.Pins, FieldStorage.Yes);
        }

        public class Result
        {
            public string Date { get; set; }
            public string Player { get; set; }
            public int Pins { get; set; }
        }
    }

Then:
        public ActionResult Last(int count, string player)
        {
            var list = Session.Query<Pins_ByMatchDate.Result, Pins_ByMatchDate>()
                .Where(r => r.Player == player)
                .OrderByDescending(r => r.Date)
                .AsProjection<Pins_ByMatchDate.Result>()
                .Take(count)
                .ToList();
            return Json(list, JsonRequestBehavior.AllowGet);
        }

And http://localhost:63818/Home/Last?count=2&player=Kjell%20Persson returns

[{"Date":"03/26/2011","Player":"Kjell Persson","Pins":172},{"Date":"03/26/2011","Player":"Kjell Persson","Pins":194}]

But how can I create an index with all players, last-n average precalculated? I.e. I want http://localhost:63818/Home/Last?count=2
to return

[{"Player":"Kjell Persson","Pins":368,"Games":2},{"Player":"Mikael Axelsson","Pins":388,"Games":2}]

For this example I used 2. I want 20 but I can just as well hard-code it in the index, if necessary. I.e. I don't need to have a dynamic value.
I'd be thankful for any suggestions!

/Daniel Lidström
Stockholm, Sweden

[Itamar Syn-Hershko]

Are you going to query on this value? if not, just retrieve the last 20 and calc this in your app, or you can probably use TransformResults to do that for you and save on network traffic

Otherwise I'd go the bundle route.

[Daniel Lidström]

I want to be able to sort on this value, as part of an overview page with all players. So I am thinking I want to avoid N+1 here.

2011/11/16 Itamar Syn-Hershko <ita...@hibernatingrhinos.com>

--
Daniel

[Matt Warren]

Couldn't you maintain a rolling/sliding window average in a Map/Reduce
index? Using an algorithm like the one here
http://cheind.wordpress.com/2010/01/23/simple-moving-average/?

On Nov 16, 1:48 pm, Daniel Lidström <dlidst...@gmail.com> wrote:
> Hello,
>
> I'd like to display an average of the last 20 series played by the bowlers
> on my team.
> That is, for each one of them show their total average and also their
> average for the last
> 20 series. Total average is easy with map/reduce, but average for last 20
> does not seem

> to fit with this model. I can calculate the last 20 for a *single* player

> But how can I create an index with *all* players, last-n average
> precalculated? I.e. I wanthttp://localhost:63818/Home/Last?count=2<http://localhost:63818/Home/Last?count=2&player=Kjell%20Persson>

[Itamar Syn-Hershko]

Interesting, how would you do this in a M/R index tho?

Would love to see a KB article added with a concrete sample :)

[Itamar Syn-Hershko]

The immediate solution that comes to mind is having a property that is updated by your TeamMemeber entity and is updated automatically whenever a new game result is added

This could also reside in an external entity, and merged in to the index using AddMap

[Matt Warren]

Yeah I need to think it through a bit more and it's probably harder
than I think ;-)

I'll try and have a go at making a code sample sometime over the next
few days.

On Nov 16, 2:21 pm, Itamar Syn-Hershko <ita...@hibernatingrhinos.com>
wrote:

> Interesting, how would you do this in a M/R index tho?
>
> Would love to see a KB article added with a concrete sample :)
>

[Daniel Lidström]

Thanks for the suggestions. I was hoping there would be a way with indexes, they are easier to evolve than hard entities, and of course handle historic data.

2011/11/16 Itamar Syn-Hershko <ita...@hibernatingrhinos.com>

--
Daniel

[Daniel Lidström]

It's definitely an interesting approach. I couldn't see how to apply it within an index though. Would love to see any results from you :-)

2011/11/16 Matt Warren <matt...@gmail.com>

--
Daniel

[Ryan Heath]

If we could map what the count is per player and assign a virtual sequence number
somethink like:

                             select new
                             {
                                 Date = match.Date.ToShortDateString(),
                                 Player = game.Player,

                                 Pins = game.Pins,
                                Count = <Total count for this player>,
                                Sequence=<a virtual index number>
                             }

Then filter: where item.Sequence >= (item.Count - 20)

Then the reduce part would be a simple average on the remaining items.

Would something like this be feasible?

// Ryan

Sent from my iPhone

[Itamar Syn-Hershko]

This is basically just a matter of adding a Count property to the entity and having this business logic managed within your class, instead of in an index...

[Ryan Heath]

Not really, count and sequence number are not stored anywhere in a document and perhaps it shouldn't.

I think the hard part here is to come up with a sequence number for a particular item.

How do/would we know what is sequence number would be of an item perhaps based on some orderning?

// Ryan

[Oren Eini (Ayende Rahien)]

Probably based on the date of the game?

[Ryan Heath]

How about doing all the work in the reduce part?
We group the items per player, and then in the reduce part sort the group on the date and calc the average of the max last 20 items per group.
Would that work?

// Ryan

Sent from my iPhone

[Itamar Syn-Hershko]

Probably, but how would you group the items per player?

[Ryan Heath]

Unless I miss the obvious but group by player(name)?

// Ryan

Sent from my iPhone

[Itamar Syn-Hershko]

I'll be glad to see your working index definition :)

[Ryan Heath]

I wonder what i'm missing :)
I try it as soon as i get home.

[Ryan Heath]

Take a look at https://gist.github.com/1376107

Is this what we are looking for?

I had two gotcha's.

- I had to change type of Result.Pins to double otherwise no results were returned.

- At first I created "let dates = grouped.OrderByDescending(x => x.Date).Take(3)" and worked further with "dates" 

but that throwed an exception error CS1977: Cannot use a lambda expression as an argument to a dynamically dispatched operation without first casting it to a delegate or expression tree type

// Ryan

[Matt Warren]

Ryan,

Haha, I did something really similar last night, see here https://gist.github.com/1376252

You then get the output like this http://i.imgur.com/k9fzI.png, that includes overall average, average over last 2 games etc.

It need a bit of tidying up, but the basic idea is there and it *seems* to work.

[Oren Eini (Ayende Rahien)]

Matt & Ryan,

Awesome, I don't think that I would have been able to think about that, really nice.

[Matt Warren]

BTW in my solution you don't have to keep the entire list of Last2Games (see http://i.imgur.com/k9fzI.png), if you only care about the average. But it did make it easier to debug as you could see which games were being used for the average.

[Itamar Syn-Hershko]

Real nice, would definitely love to see a KB article on this http://beta.ravendb.net/kb/add

[Matt Warren]

As a more general question, should "let blah = something" statements work inside Raven Map/Reduce indexes? Something like this:

Reduce = results => from result in results

group result by result.Player

into g

let last2Games = g.SelectMany(x => x.Last2Games).OrderByDescending(x => x.Date).Take(2)

select new

{

MinDate = new DateTime(g.Min(x => x.MinDate.Ticks)),

Last2Games = last2Games,

Player = g.Key,

AveragePins = g.Average(x => x.AveragePins),

Last2GamesAvg = last2Games.Average(x => x.Pins), 

TotalGames = g.Sum(x => x.TotalGames)

}

I tried using one but kept getting errors when Raven tried to compile the code, the same error as Ryan,

[Oren Eini (Ayende Rahien)]

Yes, they should work.

[Daniel Lidström]

I haven't tried it yet, but from the looks of it this is correct. This is an exhilarating feeling! Many thanks to both of you for spending your valuable time to help me out!

/Daniel

2011/11/18 Ryan Heath <ryan.q...@gmail.com>

--
Daniel

[Ryan Heath]

Matt hahaha nice!

I was surprised I did not get any results when the averagePin was not a double but an integer.
Maybe a exception should be thrown instead of silently return nothing?

// Ryan

[Oren Eini (Ayende Rahien)]

An exception IS probably raised, look at the stats to see it