Chief Delphi - Jaci's Annual FRC Datadump 2016

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)

- General Forum (http://www.chiefdelphi.com/forums/forumdisplay.php?f=16)

- - Jaci's Annual FRC Datadump 2016 (http://www.chiefdelphi.com/forums/showthread.php?t=148257)

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586491)

...he then corrected the SQL code but forgot to update the answer

Fixed.

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586502)

Question for Ether or anyone else interested: is there a simple way using awk or other tools to get information about streaks from relational data like this? I've always resorted to writing a program (VB, T-SQL, java etc) because the SQL gets too ugly. For example: longest winning streak for each team (crossing events). It would be hard to do with this data currently because the event dates are not included but easy enough to add event_date field to the events table. To simplify my question, assuming id in the matches table was in chronological order for each team, how would you approach getting each team's longest winning streak?

I think adding a 'sequence' column to the event table would allow you to sort a team's matches in chronological order, Week 0 events would have the lowest sequence through Einstein having the highest. Still would not be easy to from there with SQL, though.

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586502)

SQL doesn't do sequential queries (streaks) very well. AFAIR, it doesn't recognize the order of tuples (records) within a relation (table), and determining that two matches were "sequential for a given team" would be quite convoluted without procedural logic.

***********************

If the matches were sorted as you indicated (guaranteed that the matches for each team are in chronological order), which would not be too difficult to arrange, it would be a simple matter in awk or other scripting language to create arrays indexed on each team number:

long_start (start of the longest streak)
long_length (length of the longest streak)
curr_start (start of the current streak)
curr_length (length of the current streak)

Then, for each match, extend (or begin) the streak for each winner, and terminate the streak for each loser, taking care to update long_* from curr_* if curr_length[team] > long_length[team]. At the end of Einstein, close out all of the teams who won their last match (that is, curr_length>0).

The trickiest/finickiest part would probably be initializing the arrays, unless you decided to brute force it and have cells for teams which no longer exist or otherwise did not compete this year.

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586502)

longest winning streak for each team (crossing events).

I'd feed the *Qmatches.csv and *Pmatches.csv files*, in chrono order, to an AWK script something like this:

Code:



if ($11>$14) f1(5,8); else f1(8,5);



function f1(W,L){

        for(k=W;k<=W+2;k++)

                streak[$k]++;

        for(k=L;k<=L+2;k++){

                if(streak[$k]>max[$k])max[$k]=streak[$k]; 

                streak[$k]=0;

                }

        }

* http://www.chiefdelphi.com/media/papers/3243

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by jvriezen (Post 1586427)

Hmm... Franks blog post claims there were 13,303 matches played, but the matches table has only 13,302 rows.

Clearly there is something going on here and someone is hiding the data for a match that they don't want made public :D

All the data in the matches table is taken directly from TheBlueAlliance and you can see that for yourself in the `populate.rb` file. If you run it now, it will give even more matches because of offseason events.

Either TBA's missed a match or Frank's number is wrong, because I can assure you the data in the table is unaltered.

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586516)

I'd feed the *Qmatches.csv and *Pmatches.csv files*, in chrono order, to an AWK script something like this...

OK, ran the script.