Jaci's Annual FRC Datadump 2016

[Ether]

If you export the database tables, the "sqlite_sequence" table lists the number of records in each table:

event_types			7
match_levels			5
defenses			9
districts			8
events				139
alliances			1088
alliance_picks			3336
matches				13302
match_defenses			133020
match_teams			79812
match_scores			26604
awards				3251
stats				6194
rankings			6194
district_teams			1180
district_rankings		1179
district_event_points		2789

[jvriezen]

Originally Posted by Ether

For all 3-alliance teams that actually played a match:

6138+6137+6208 = 18483 (blue)

11+56+41 = 108 (red)

Here is the SQL that gives the same answer:

sqlite> select a.team, b.team, c.team, max(a.team+b.team+c.team) from match_teams a join match_teams b on a.match=b.match join match_teams c on b.match = c.match where a.team <> b.team and a.team <> c.team and b.team <> c.team and a.alliance_color = b.alliance_color and b.alliance_color = c.alliance_color ;

6138|6137|6208|18483

sqlite> select a.team, b.team, c.team, min(a.team+b.team+c.team) from match_teams a join match_teams b on a.match=b.match join match_teams c on b.match = c.match where a.team <> b.team and a.team <> c.team and b.team <> c.team and a.alliance_color = b.alliance_color and b.alliance_color = c.alliance_color ;

41|11|56|108

[jvriezen]

Originally Posted by Whatever

I am not an SQL expert so I was hoping someone could help me out.

Some trivia questions I was curious in:
3. Alliance with the narrowest range of team numbers;
4. Alliance with the widest range of team numbers;

Widest range:

sqlite> select a.team, b.team, c.team, max(a.team - c.team) from match_teams a join match_teams b on a.match=b.match and a.team > b.team join match_teams c on b.match = c.match and b.team > c.team where a.alliance_color = b.alliance_color and b.alliance_color = c.alliance_color ;
6231|5536|11|6220

Narrowest:

sqlite> select a.team, b.team, c.team, min(a.team - c.team) from match_teams a join match_teams b on a.match=b.match and a.team > b.team join match_teams c on b.match = c.match and b.team > c.team where a.alliance_color = b.alliance_color and b.alliance_color = c.alliance_color ;

177|176|175|2

[Whatever]

Thank you

[Foster]

Originally Posted by Ether

I haven't done SQL in decades. I just cobbled up an AWK script to read the match_teams table.

Yay AWK, and once again showing there is more than one way to parse the data. I'm going to guess that Ether's was a little faster, since the data only will get a single read, the other method using SQL has quite a few joins.

[Ether]

Originally Posted by Foster

I'm going to guess that Ether's was a little faster

440 milliseconds on my 10-year-old Pentium D desktop running 32 bit XP.

[antman]

Originally Posted by jvriezen

select a.team, b.team, c.team, max(a.team+b.team+c.team) from match_teams a join match_teams b on a.match=b.match join match_teams c on b.match = c.match where a.team <> b.team and a.team <> c.team and b.team <> c.team and a.alliance_color = b.alliance_color and b.alliance_color = c.alliance_color ;

41|11|56|108

I think this "max" was supposed to be a "min".

[jvriezen]

Originally Posted by antman

I think this "max" was supposed to be a "min".

Fixed.

[markmcgary]

Originally Posted by Ether

I haven't done SQL in decades. I just cobbled up an AWK script to read the match_teams table.

(ac=="red") && ($3=="red"){red+=$4; next}

(ac=="blue") && ($3=="blue"){blue+=$4; next}

(ac=="red") && ($3!="red"){
	if(red>max){max=red; idmax=$1}
	if(red<min){min=red; idmin=$1}
	ac="blue"; blue=$4; next}

(ac=="blue") && ($3!="blue"){
	if(blue>max){max=blue; idmax=$1}
	if(blue<min){min=blue; idmin=$1}
	ac="red"; red=$4; next}

I had to do a little AWK research. If I read this correctly, your idmax and idmin will point to the record in match_teams that immediately follows the max/min alliances?

It's amazing how many tools there are to accomplish a given task. Always something new to learn.

[Ether]

Originally Posted by markmcgary

I had to do a little AWK research. If I read this correctly, your idmax and idmin will point to the record in match_teams that immediately follows the max/min alliances?

Yes. I could have subtracted 1, but this is not production code

It's amazing how many tools there are to accomplish a given task. Always something new to learn.

I started using AWK a lot back in '96 when my main workstation was a DEC VAX/VMS and I had to wrangle large text datasets in various wacky formats. It was a lifesaver.

[antman]

I think there is a problem with jvriezen's SQL to get the max sum of team numbers on an alliance. I can't find a match where the 3 teams 6204|6191|6212 are on the same alliance. Here is my approach to answering this question:

select m.match, m.alliance_color, sum(m.team) as team_number_total from match_teams m group by m.match, m.alliance_color order by sum(m.team) desc

It gives a descending order list with the winner being match 5986, blue alliance, team total 18483 (which agrees with Ether's answer).

[Ether]

Originally Posted by antman

I think there is a problem with both jvriezen's SQL to get the max sum of team numbers on an alliance and I guess Ether's awk method too (can't speak to that, haven't used awk), since they are giving the same answer...

team total 18483...the 3 teams on the blue side were 6137, 6138, and 6208.

Go look again at my post. That's the answer I gave.

[antman]

Originally Posted by Ether

Go look again at my post. That's the answer I gave.

Sorry, you're right, I was going by what jvriezen had said but his/her answer wasn't the same as yours. I'll edit my earlier post.

[Ether]

Originally Posted by antman

Sorry, you're right, I was going by what jvriezen had said but his/her answer wasn't the same as yours. I'll edit my earlier post.

His original SQL code had an error; he was pulling the top 3 team numbers from match 445 without regard to alliance:

2662,444,blue,6083
2663,444,blue,4739
2664,444,blue,5331
2665,445,red,5876
2666,445,red,4729
2667,445,red,6191
2668,445,blue,6212
2669,445,blue,3132
2670,445,blue,6204
2671,446,red,6018
2672,446,red,4788
2673,446,red,4774

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

[antman]

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?