Chief Delphi - Jaci's Annual FRC Datadump 2016

Page 2 of 3

Show 100 post(s) from this thread on one page

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)

Ether

13-05-2016 14:35

Re: Jaci's Annual FRC Datadump 2016

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

Code:

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

13-05-2016 14:41

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586430)

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

13-05-2016 14:59

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Whatever (Post 1586411)

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

13-05-2016 15:04

Re: Jaci's Annual FRC Datadump 2016

Thank you

Foster

13-05-2016 15:05

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586446)

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

13-05-2016 15:08

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Foster (Post 1586466)

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

13-05-2016 15:23

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by jvriezen (Post 1586453)

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

13-05-2016 15:27

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586473)

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

Fixed.

markmcgary

13-05-2016 15:28

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586446)

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

Code:

(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

13-05-2016 15:39

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by markmcgary (Post 1586477)

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 :)

Quote:

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

13-05-2016 15:48

Re: Jaci's Annual FRC Datadump 2016

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

13-05-2016 15:55

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586484)

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

13-05-2016 15:57

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by Ether (Post 1586486)

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

13-05-2016 16:05

Re: Jaci's Annual FRC Datadump 2016

Quote:

Originally Posted by antman (Post 1586489)

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:

Code:

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

13-05-2016 16:33

Re: Jaci's Annual FRC Datadump 2016

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?

All times are GMT -5. The time now is 02:56.

Page 2 of 3

Show 100 post(s) from this thread on one page