Union @ Cornell

[adamw]

a database would make this pretty easy to figure out and compute without having to go look for stuff. I know we have something here is listed  even if it doesnt work.

Even if there was a database of every goal, its time and game -- computing "fastest in xx span" is not easy. I know because I have this data dating back to 2002 for all of college hockey, and the entire NHL, and I haven't gotten around to writing a method for computing it yet, because it's challenging. And I do this for a living.  Game level spans are hard enough - "for any floating x game span" ... I've done it for NHL. Haven't done it yet for NCAA.

[Trotsky]

Official box score confirms: Cornell's 5 goals were scored on 5 shots with nothing else occurring in between except for the 4 faceoffs and 2 wide Cornell shots between goals 2 and 3. There were not only no other shots, there were no other stoppages. Holy hell.

[Trotsky]

a database would make this pretty easy to figure out and compute without having to go look for stuff. I know we have something here is listed  even if it doesnt work.

I actually built a database with every Cornell (and opponent) goal since the program restart.

I could write a query and test this.  But I have never actually written a query that walks through the data that way (if I did that I could do *a lot* of analysis).  Maybe in retirement.

[Dafatone]

Official box score confirms: Cornell's 5 goals were scored on 5 shots with nothing else occurring in between except for the 4 faceoffs and 2 wide Cornell shots between goals 2 and 3. There were not only no other shots, there were no other stoppages. Holy hell.

We only had four shots on goal in an entire major? Cmon, get it together team.

[BMac]

I feel nerdsniped.

Can I get the schema for the db?

I think it's something like:
For each game order the CU goals by time and generate a row_number
For each game, compute the shortest period between two goals by self-joining on row_number = row_number + 1 and subtracting the timestamps, then taking the min of the results.
Do the same for 2, 3, 4, etc up to the max number of goals in a game. Use a left join to prevent joins beyond the number of possible goals. It's a manual query build but it's ok since there's what 12-15 max goals in a game?

If you get me a schema I'll try it....

[Trotsky]

I feel nerdsniped.

Can I get the schema for the db?

I think it's something like:
For each game order the CU goals by time and generate a row_number
For each game, compute the shortest period between two goals by self-joining on row_number = row_number + 1 and subtracting the timestamps, then taking the min of the results.
Do the same for 2, 3, 4, etc up to the max number of goals in a game. Use a left join to prevent joins beyond the number of possible goals. It's a manual query build but it's ok since there's what 12-15 max goals in a game?

If you get me a schema I'll try it....

I'll give you the whole db, it's Access.  I have been happily giving it away for years and  strongly suspect it is why the SID's records got a lot better (without acknowledgement because TRADITION!).

[adamw]

I feel nerdsniped.

Can I get the schema for the db?

I think it's something like:
For each game order the CU goals by time and generate a row_number
For each game, compute the shortest period between two goals by self-joining on row_number = row_number + 1 and subtracting the timestamps, then taking the min of the results.
Do the same for 2, 3, 4, etc up to the max number of goals in a game. Use a left join to prevent joins beyond the number of possible goals. It's a manual query build but it's ok since there's what 12-15 max goals in a game?

If you get me a schema I'll try it....

There's fancier ways to do this - with WINDOW functions. But if I told you, I'd have to kill you.

[BMac]

If you can figure out the right way to do this with a window function, good on you. I spent a little time thinking about it and figured it was easier to just build progressive CTEs manually for each goal amount since we don't need more than 12-15ish....

I'm sure it's possible though. I've even done regressive sql before and felt like a golden god

[billhoward]

I'm in favor of  having all stats dating to Beebe Lake days being digitized and query-able. By somebody else. I'm sort of in favor of handing them off to Chatbot GPT.

There is a danger that people having to write a Wednesday hockey column or fill dead air while there's yet another video replay will clamp onto a numerical pattern that has been calculated to several decimal places yet  has no actual value: Say, Cornell's record in one-goal games played after Coach has come back from an illness or game suspension, when Cornell is wearing white jerseys in Friday road games. You hear NFL announcers discussing whether to go for it on fourth down when the team has been 3 of 5 on recent non-punting fourth downs with less than 2 yards to go as if 60% odds guarantee the next one will work.

I did like the magic of the Celtics Bill Russell speaking to the mystery of whether the game clock reading :02 (pre-tenths on the clock) has 2.1 seconds or 2.9 seconds and you're down by one. That was poetry, not data.

[Trotsky]

I'm in favor of  having all stats dating to Beebe Lake days being digitized and query-able. By somebody else. I'm sort of in favor of handing them off to Chatbot GPT.

I'm working on it, dammit.

[upprdeck]

I dont think anyway its designed is gonna be that big a deal.

just ball park it say 75 yrs at 30 games so 2000 games at like at best 4 goals a game so 8000 rows.

any query for fastest 4 goals can ignore all the non 4+ goal games so probably cuts it by 1/2 - 2/3

just store the goals in order and go 1 to 4, 2 to 5, stop with x+3 greater than max goals..

probably could run thru it for any number of goals in a few seconds.
a query with a sub query for the +y.

[Trotsky]

The goals are already ordered. There are 7,103 of them for Cornell.

I had no intention of using database queries to do the work, I was planning on outputting the records to a data file and then writing a simple C++ program to do it. (Yes, I know, I'm old, be happy it's not PL/1 and get away from me with your heathen post-1983 data structures).

I was assuming I would run through them all, create a table and for each row record count n the # of uninterrupted Cornell goals it was and then dump the time elapsed for the prior 1, 2, ... n-1 Cornell goals into a linked list.

Then take that table and do the simple metrics.

I like thorough solutions.  With 7k records the run time will be trivial.

[617BigRed]

So sure has been discussed before but is there any chance in relatively near future that UPenn and Columbia get D-I hockey programs and Ivy can form a new 8-team conference with an autobid? Then Atlantic and ECAC can maybe merge and improve?

Hello, new poster here '05 grad now in Boston. 2003 was my sophomore year, had season tix. Thought that is how things were and would make Frozen Four every few years lol. Was in Buffalo for the national semifinal loss.  Been at Lynah East, Brown & MSG games for most years since. Was at Placid last time Red there few years ago, sat next to Vanderlaans dad for that loss. Happy to join you all here on eLynah!

[Trotsky]

Welcome.

There has never been any talk of Columbia going above club level.

Penn has a great barn.  It used to be a perennial suggestion that they might restore their program, which was still active in the mid-70s, but other than a very dedicated and tiny Penn hockey cult I know of no serious measure.

IMO it is more likely Brown drops D-1 someday than Penn adds.

[upprdeck]

Penn brings back hockey.. does not rejoin the ivy because they want to have scholies. they become decent as an independent because they are located close to so many.   This forces the ivies to get out of the dark ages