search

BurntSushi / nfldb

A library to manage and update NFL data in a relational database.
The Unlicense
1.08k stars 264 forks source link

Play-by-play Score State #158

Open anthonydelage opened 8 years ago

anthonydelage commented 8 years ago

Hi @BurntSushi,

Has any work been done to compile play-by-play score state? The goal here would be to use score state to qualify plays.

Some examples would be:

I can't find score at the play/drive level in the schema (though I may be missing something).

Thanks, AD

BurntSushi commented 8 years ago

The only thing you can do is ask for the score at a particular point in the game: http://pdoc.burntsushi.net/nfldb#nfldb.Game.score_at_time

The queries you pose are fascinating, but would probably require specialized SQL to run efficiently (perhaps even a new table if the data needs to be rearranged).

anthonydelage commented 8 years ago

Yeah, I can see from the source that you're essentially re-running history (for that game) every time score_at_time is run. Is there anything in the Gamecenter JSON related to score that could be added to the tables' base schema?

@BurntSushi

BurntSushi commented 8 years ago

Not to my knowledge. You'd have to build it yourself.

anthonydelage commented 8 years ago

What are your thoughts on building it using a materialized view with a window function in the DB?

BurntSushi commented 8 years ago

I don't have any idea. I'd need to see a more detailed design. On Jun 12, 2016 4:00 PM, "Anthony Delage" notifications@github.com wrote:

What are your thoughts on building it using a materialized view with a window function in the DB?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BurntSushi/nfldb/issues/158#issuecomment-225457106, or mute the thread https://github.com/notifications/unsubscribe/AAb34jw4wZ5HhYahWo0pamxKdR29qSEgks5qLGV7gaJpZM4Iycxj .