Statistics/sandbox: Difference between revisions
From UFFL Wiki
(Created page with "Mad is using this sandbox to review a variety of ways data can be imported from the DDSPF2021 MDB file (via MySQL) into meaningful information for the Wiki. <!--- IMPORTING...") |
mNo edit summary |
||
(22 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
Mad is using this sandbox to review a variety of ways data can be imported from the DDSPF2021 MDB file (via MySQL) into meaningful information for the Wiki. | Mad is using this sandbox to review a variety of ways data can be imported from the DDSPF2021 MDB file (via MySQL) into meaningful information for the Wiki. | ||
<!--- | <!--- GM SPREADSHEET ROSTER TESTS ---> | ||
{{#get_db_data: | {{#get_db_data: | ||
db=ufflData | db=ufflData | ||
|from=Players | |from=Players | ||
|order by= | |where=Team='{{{teamCode|}}}' | ||
|order by= Jersey | |||
|data=pid=PlayerID,pos=Position,jerseynum=Jersey,firstName=FirstName,lastName=LastName,speciesCollege=College,ht=Height,wt=Weight,yearsPro=Experience,salary=Salary,ctryrs=ContractYears,injury=Injury,injwks=InjuryLength,rating=Overall | |||
}} | }} | ||
{| class="wikitable sortable" | |||
{| class="wikitable" | |- | ||
! PID | ! style="color:#AAA"|PID # | ||
! Jersey | ! Jersey # | ||
! College | ! Position | ||
! | ! Name | ||
! Species/College | |||
! Years | |||
! Height (in) | |||
! Weight (lbs) | |||
! OVR Rating | |||
! Current Salary | |||
! Years on Contract | |||
! Injured? | |||
! Injury Weeks Remaining{{#for_external_table:<nowiki/> | |||
{{!}}- | {{!}}- | ||
{{!}} {{{ | {{!}} {{{pid}}} | ||
{{!}} {{{jerseynum}}} | {{!}} {{{jerseynum}}} | ||
{{!}} {{{ | {{!}} {{{pos}}} | ||
{{!}} {{{ | {{!}} {{{firstName}}} {{{lastName}}} | ||
{{!}} {{{speciesCollege}}} | |||
{{!}} {{{yearsPro}}} | |||
{{!}} {{{ht}}} | |||
{{!}} {{{wt}}} | |||
{{!}} {{{rating}}} | |||
{{!}} {{{salary}}} | |||
{{!}} {{{ctryrs}}} | |||
{{!}} {{{injury}}} | |||
{{!}} {{{injwks}}} | |||
}} | |||
|}{{#clear_external_data:}} | |||
<!--- TEAM SEASON LEADERS | |||
{{TeamLeaderStats | |||
|season=2015 | |||
|team=CHI | |||
}} | }} | ||
---><!--- TOP 10 pages | |||
{{#get_db_data: | |||
db=ufflData | |||
|from=PlayerCareerStats | |||
|where=PassAtt <> '0' | |||
|order by= PassAtt DESC | |||
|limit = 10 | |||
|data=team1=Team,playerName1=PlayerID,recordValue1=PassAtt | |||
}}{{#get_db_data: | |||
db=ufflData | |||
|from=PlayerCareerStats | |||
|where=PassCmp <> '0' | |||
|order by= PassCmp DESC | |||
|limit = 10 | |||
|data=team2=Team,playerName2=PlayerID,recordValue2=PassCmp | |||
}}{{#get_db_data: | |||
db=ufflData | |||
|from=PlayerCareerStats | |||
|where=PassPct <> '0' | |||
|order by= PassPct DESC | |||
|limit = 10 | |||
|data=team3=Team,playerName3=PlayerID,recordValue3=PassPct | |||
}}{| class="wikitable" style="border:0; font-size:12px; width: 100%" | |||
|- | |||
! colspan="4" style="border: 1px solid blue" |Pass Attempts | |||
! colspan="4" style="border: 1px solid green" |Pass Completions | |||
! colspan="4" style="border: 1px solid red" |Pass Completion % | |||
|- | |||
! # | |||
! Team | |||
! Player | |||
! Record | |||
! # | |||
! Team | |||
! Player | |||
! Record | |||
! # | |||
! Team | |||
! Player | |||
! Record | |||
{{#display_external_table:template=Top10Records}} | |||
|} | |||
{{#clear_external_data:}} | |||
---> | |||
<!--- TEAM ANNUAL STATISTICS FOR TEAM PAGES: Further work would be needed to make this a fully automated template. | |||
{{#get_db_data: | |||
db=ufflData | |||
|from=TeamSeasonStats | |||
|where=Team='CHI' | |||
|order by= Season ASC | |||
|data = season=Season,wins=Wins,losses=Losses,ties=Ties,winPct=WinPct,points=PointsFor,ptsAgainst=PointsAgainst | |||
}} | |||
{{#get_db_data: | |||
db=ufflData | |||
|from=TeamCareerStats | |||
|where=Team='CHI' | |||
|data = totalWins=Wins,totalLosses=Losses,totalTies=Ties,winPctTot=WinPct,totalPts=Points,totalPtsAgst=PointsAgainst | |||
}} | |||
{| class="wikitable sortable" | |||
|- | |||
! colspan="7"|Team Yearly Stats | |||
|- | |||
! Season | |||
! W | |||
! L | |||
! T | |||
! Win % | |||
! PF | |||
! PA | |||
|- | |||
| 2014 | |||
| 5 | |||
| 11 | |||
| 0 | |||
| {{Winning percentage|5|11}} | |||
| 262 | |||
| 359{{#for_external_table:<nowiki/> | |||
{{!}}- | |||
{{!}} {{{season}}} | |||
{{!}} {{{wins}}} | |||
{{!}} {{{losses}}} | |||
{{!}} {{{ties}}} | |||
{{!}} {{{winPct}}} | |||
{{!}} {{{points}}} | |||
{{!}} {{{ptsAgainst}}} | |||
}} | |||
|- class="sortbottom" | |||
|<b>Total:</b> | |||
{{!}} {{#expr:(5+{{#external_value:totalWins}})}} | |||
{{!}} {{#expr:(11+{{#external_value:totalLosses}})}} | |||
{{!}} {{#external_value:totalTies}} | |||
{{!}} {{#external_value:winPctTot}} | |||
{{!}} {{#expr:(262+{{#external_value:totalPts}})}} | |||
{{!}} {{#expr:(359+250+266+288+305+265)}} | |||
|} | |} | ||
---> | ---> | ||
<!--- IMPORTING PLAYER SEASON STATS | <!--- IMPORTING PLAYER SEASON STATS | ||
{{#get_db_data: | {{#get_db_data: | ||
db = ufflData | db = ufflData | ||
Line 69: | Line 192: | ||
{{!}} {{{passAttempts}}} | {{!}} {{{passAttempts}}} | ||
{{!}} {{{passCompletes}}} | {{!}} {{{passCompletes}}} | ||
{{!}} {{{ | {{!}} {{{passPercent}}} | ||
{{!}} {{{passerRating}}} | {{!}} {{{passerRating}}} | ||
{{!}} {{{passYards}}} | {{!}} {{{passYards}}} | ||
Line 85: | Line 208: | ||
}} | }} | ||
|} | |} | ||
IMPORTING PLAYER RECORDS | |||
{{#get_db_data: | |||
db = ufflData | |||
|from = PlayerRecords | |||
|where = PlayerID='351' | |||
|order by = RecordType ASC | |||
|data = record=RecordID,week=Week,season=Season,teamID=TeamID,vsteamID=VsTeamID,recordVal=RecordValue | |||
}} | |||
{| style="border:0; font-size:12px; cellpadding:0.5; cellspacing:0.5; width: 100%" class="wikitable sortable" | |||
|- | |||
! colspan="6"|Records | |||
|- | |||
! Record ID | |||
! Week | |||
! Season | |||
! Team | |||
! Versus | |||
! Value{{#for_external_table:<nowiki/> | |||
{{!}}- | |||
{{!}} {{{record}}} | |||
{{!}} {{{week}}} | |||
{{!}} {{{season}}} | |||
{{!}} {{Convert/teamid|{{{teamID}}}}} | |||
{{!}} {{Convert/teamid|{{{vsteamID}}}}} | |||
{{!}} {{{recordVal}}} | |||
}} | |||
|} | |||
Issues: | |||
- Convert/teamid is not working on the TeamID and VsTeamID columns | |||
- I do not know yet how to make the RecordID field meaningful (each number corresponds to a record type, and it may change depending on player position) | |||
---> |
Latest revision as of 02:26, 27 June 2021
Mad is using this sandbox to review a variety of ways data can be imported from the DDSPF2021 MDB file (via MySQL) into meaningful information for the Wiki.
PID # | Jersey # | Position | Name | Species/College | Years | Height (in) | Weight (lbs) | OVR Rating | Current Salary | Years on Contract | Injured? | Injury Weeks Remaining |
---|