Menu:

Home Articles Custom Crunch
 

Crunches:

Best Crunches
 

Contact:

Email Me
 

Links:

AFL Tables

Welcome to the Hawk Haven

The aim of the Haven is to keep you up to date on all things AFL / Hawthorn from a statistical perspective.

The SQL database has over 4,500 games, stretching all the way back to the 2000 season.

 "Another Post"  Thu Jul 18th, 2024.
Place holding again...
 "Post 3"  Mon Jul 15th, 2024.
Placeholder for new set up
 "Experiment"  Wed Nov 15th, 2023.

Warning: Attempt to read property "num_rows" on bool in /home/darwinet/pageTable.php on line 102
Try

Sorry, no data table was returned from this Crunch.

SET @temp = '', @versus = '', @versus2 = '', @game = 0;

WITH inside AS
(SELECT _when, _rnd, _ha, _tm, _op, _vn, _score, _cwd, _tmRung, _opRung, _tmFin, _opFin, _for AS '_for', _agt AS '_agt', (_for) - (_agt) AS '_mgn', IF((_for) - (_agt) > 0, 'W', IF((_for) - (_agt) = 0, 'D', 'L')) AS '_WL', ROW_NUMBER() OVER (PARTITION BY _tm ORDER BY _when DESC) as '_game', DATEDIFF(_when, LAG(_when, 1) OVER (PARTITION BY _tm ORDER BY _when)) AS '_tmDays', DATEDIFF(_when, LAG(_when, 1) OVER (PARTITION BY _op ORDER BY _when)) AS '_opDays', LAG(_vn, 1) OVER (PARTITION BY _tm ORDER BY _when) AS '_pre' FROM haven WHERE YEAR(_when) BETWEEN 2010 AND 2023),

middle AS (SELECT *,

@temp := if (_tm > _op, CONCAT(LEFT(_op, 2), LEFT(_tm, 2)), CONCAT(LEFT(_tm, 2), LEFT(_op, 2))) AS _temp,
@versus2 := IF (_HA = 'H' AND LOCATE(@temp, @versus) > 0, CONCAT(@versus2, @temp, '.'), @versus2) AS _V2,
@versus := IF (_HA = 'H' AND LOCATE(@temp, @versus) = 0, CONCAT(@versus, @temp, '.'), @versus) AS _V1,
@game := @game + 1,

ROW_NUMBER() OVER (ORDER BY _when DESC) as '_rank' FROM inside WHERE YEAR(_when) = 2023 AND _rnd < 30)

SELECT @versus, @game, CHAR_LENGTH(@versus2), CHAR_LENGTH(@versus), _tm AS 'Team', count(*) AS 'P', SUM(IF(_for > _agt, 1, 0)) AS 'W', SUM(IF(_for = _agt, 1, 0)) AS 'L', SUM(IF(_for = _agt, 1, 0)) AS 'D', SUM(_for) as 'For_.0', SUM(_agt) as 'Agt', SUM(IF(_for > _agt, 4 / IF(LOCATE(_temp, @versus2) > 0, 2, 1), IF(_for = _agt, 2 / IF(LOCATE(_temp, @versus2) > 0, 2, 1), 0))) AS 'Pts', 100 * SUM(_for) / SUM(_agt) AS '%_.1%' FROM middle WHERE 1 GROUP BY _tm ORDER BY SUM(IF(_for > _agt, 40, IF(_for = _agt, 20, 0))) + (SUM(_for) / SUM(_agt)) DESC

 "Custom Crunches"  Tue Oct 10th, 2023.
Resurrecting the Android scripts into PHP and JS. You can try it out from the menu on the left.

Very much a work in progress.

 "Utopia"  Fri Oct 6th, 2023.
I've resurrected the "Utopia" Crunch.

Imagine a perfect season - where each team plays each other twice, home and away.

This crunch goes back to find the last time each team played each other. Even back to 2017 for the last time the Eagles played the Lions at home.

You can find the ladder in the new 'Crunch' section (menu to the left).

 "Progress"  Wed Oct 4th, 2023.
The Magpies and the Lions are now the 'yard sticks' of the competition.

So how does everyone else hold up?

Try:
#TeamPWLDForAgtPts%
1Haw541048540816119
2Bul532038937912103
3Tig5230423486887
4Cat52304554198109
5Dem5230385411894
6Swa5230390431890
7Eag5140296540455
8Bom5140329448473
9Cro51404124094101
10Doc5140366469478
11Pow5140430503485
12Sun5140357459478
13Blu5140361415487
14Gia5050335473071
15Sai5050303385079
16Kan5050321569056
  5.01.43.60.0377.3450.35.585.3
 

Well, it seems the Hawks are doing quite well!

 "First Steps"  Tue Oct 3rd, 2023.
Time to try our first "crunch" using the new database.

The tables are fully scripted! You can sort the columns by clicking on the column header (click a second time to reverse the sort). Hovering over the column name should give you a descriptor and/or and average value for that column. And clicking the team name should open up a second table with more detailed breakdown of the last 25 games involved.

2023 Home and Away Season:
#TmPWDLFAPts%Days
1Mag2318052,1421,68772127333
2Lio2317062,1801,77168123332
3Pow2317062,1491,90668113331
4Dem2316072,0791,66064125331
5Blu2313191,9221,69754113331
6Sai23130101,7751,64752108332
7Gia23130102,0181,88552107331
8Swa23121102,0501,86350110331
9Bul23120111,9191,76648109332
10Cro23110122,1931,87744117332
11Bom23110121,8382,0504490333
12Cat23101122,0881,85542113332
13Tig23101121,8561,9834294331
14Doc23100131,8351,8984097332
15Sun2390141,8392,0063692332
16Haw2370161,6862,1012880332
17Kan2330201,6572,3181272332
18Eag2330201,4182,6741253332
  23.011.40.211.4  46.0102.3331.8
 
 "We're back"  Sun Oct 1st, 2023.
After a bit of a (mainly COVID related) hiatus.
- We've just had a change in hosting services (VentraIP).
- I've taken the opportunity to rebuild the site from scratch!
- A simplified structure for the database of game results.
- Migrating from MySQL 5 to MariaDB 10 (steep learning curve).