baseball/final_deepan.sql at master · deepsek/baseball · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
--------------------------------------------------
/*Player Impact Analyser*/

select (bat_play / bat_tot) as bat_stat, (field_play / field_tot) as field_stat, (pitch_play / pitch_tot) as pitch_stat
from
  (select avg(stat / tot) as bat_tot
  from
    (select (sum(singles) + (2 * sum(doubles)) + (3 * sum(triples)) + (4 * sum(rbi)) + (2 * sum(stolen_bases)) + (0.5 * sum(hits)) - (3 * sum(strikeouts)) + (4 * sum(home_runs))) as stat
    from BATYEAR A),
    (select sum(games) as tot
    from statyear C)),
  (select avg(stat / tot) as bat_play
  from
    (select (sum(singles) + (2 * sum(doubles)) + (3 * sum(triples)) + (4 * sum(rbi)) + (2 * sum(stolen_bases)) + (0.5 * sum(hits)) - (3 * sum(strikeouts)) + (4 * sum(home_runs))) as stat
    from BATYEAR
    group by PID
    having PID='adamsgl01'),
    (select sum(games) as tot
    from statyear
    group by PID
    having PID='adamsgl01')),
  (select avg(stat / tot) as field_tot
  from
    (select (sum(putouts) + (1.5 * sum(assists)) - (2 * sum(errors)) + (3 * sum (double_plays))) as stat
    from FIELDYEAR A),
    (select sum(games) as tot
    from statyear C)),
  (select avg(stat / tot) as field_play
  from
    (select (sum(putouts) + (1.5 * sum(assists)) - (2 * sum(errors)) + (3 * sum (double_plays))) as stat
    from FIELDYEAR
    group by PID
    having PID='adamsgl01'),
    (select sum(games) as tot
    from statyear
    group by PID
    having PID='adamsgl01')),
  (select avg(stat / tot) as pitch_tot
  from
    (select (sum(outs) + (5 * sum(shutouts)) - (2 * sum(homeruns)) - sum(walks) + sum(strikeouts)) as stat
    from PITCHYEAR A),
    (select sum(games) as tot
    from statyear C)),
  (select avg(stat / tot) as pitch_play
  from
    (select (sum(outs) + (5 * sum(shutouts)) - (2 * sum(homeruns)) - sum(walks) + sum(strikeouts)) as stat
    from PITCHYEAR
    group by PID
    having PID='adamsgl01'),
    (select sum(games) as tot
    from statyear
    group by PID
    having PID='adamsgl01'));

---------------------------------------------------------------

/*H2H Score Prediction*/

select team1, team2, score_low, round(score_low + abs(teamlow_chance - teamhigh_chance)) as score_high, (case when greatest(teamhigh_chance, teamlow_chance) = teamhigh_chance then bigger else smaller end) as winner
from
  (select team1, team2, (least(team1_stat, team2_stat) * dbms_random.value()) as teamlow_chance, (greatest(team1_stat, team2_stat) * dbms_random.value(0,0.5)) as teamhigh_chance,  (round(dbms_random.value() * 8) + 1) as score_low, (case when greatest(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as bigger, (case when least(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as smaller
  from
    (select team1, team2, (team_bat - team_field1 - team_pitch1) as team1_stat, (team_bat1 - team_field - team_pitch) as team2_stat
    from
      (select team_name as team1, avg(bat_stat) as team_bat
      from
        (select team_name, ((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE TEAM_NAME='Texas Rangers' and SEASON_YEAR='2002')
      group by team_name),
      (select avg(field_stat) as team_field
      from
        (select ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE TEAM_NAME='Texas Rangers' and SEASON_YEAR='2002')),
      (select avg(pitch_stat) as team_pitch
      from
        (select ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE TEAM_NAME='Texas Rangers' and SEASON_YEAR='2002')),
      (select team_name as team2, avg(bat_stat) as team_bat1
      from
        (select team_name, ((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE TEAM_NAME='Houston Astros' and SEASON_YEAR='2002')
      group by team_name),
      (select avg(field_stat) as team_field1
      from
        (select ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE TEAM_NAME='Houston Astros' and SEASON_YEAR='2002')),
      (select avg(pitch_stat) as team_pitch1
      from
        (select ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE TEAM_NAME='Houston Astros' and SEASON_YEAR='2002'))));

-------------------------------------------------------------------------

/*SEASON SIMULATION*/

select team1, team2, score_low, round(score_low + abs(teamlow_chance - teamhigh_chance)) as score_high, (case when greatest(teamhigh_chance, teamlow_chance) = teamhigh_chance then bigger else smaller end) as winner
from
  (select team1, team2, (least(team1_stat, team2_stat) * dbms_random.value()) as teamlow_chance, (greatest(team1_stat, team2_stat) * dbms_random.value(0,0.5)) as teamhigh_chance,  (round(dbms_random.value() * 8) + 1) as score_low, (case when greatest(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as bigger, (case when least(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as smaller
  from
    (select team1, (team_bat - team_field1 - team_pitch1) as team1_stat, team2, (team_bat1 - team_field - team_pitch) as team2_stat
    from
      ((select team_name as team1, avg(bat_stat) as team_bat
      from
        (select team_name, ((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name) natural join
      (select team_name as team1, avg(field_stat) as team_field
      from
        (select team_name, ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name) natural join
      (select team_name as team1, avg(pitch_stat) as team_pitch
      from
        (select team_name, ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name)),
      ((select team_name as team2, avg(bat_stat) as team_bat1
      from
        (select team_name, ((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name) natural join
      (select team_name as team2, avg(field_stat) as team_field1
      from
        (select team_name, ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name) natural join
      (select team_name as team2, avg(pitch_stat) as team_pitch1
      from
        (select team_name, ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE SEASON_YEAR='2002')
      group by team_name))
      where team1 != team2));

-------------------------------------------------------------------

/*H2H score prediction for user team*/

select team1, team2, score_low, round(score_low + abs(teamlow_chance - teamhigh_chance)) as score_high, (case when greatest(teamhigh_chance, teamlow_chance) = teamhigh_chance then bigger else smaller end) as winner
from
  (select team1, team2, (least(team1_stat, team2_stat) * dbms_random.value()) as teamlow_chance, (greatest(team1_stat, team2_stat) * dbms_random.value(0,0.5)) as teamhigh_chance,  (round(dbms_random.value() * 8) + 1) as score_low, (case when greatest(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as bigger, (case when least(team1_stat, team2_stat) = team1_stat then team1 else team2 end) as smaller
  from
    (select team1, team2, (team_bat - team_field1 - team_pitch1) as team1_stat, (team_bat1 - team_field - team_pitch) as team2_stat
    from
      (select name as team1, avg(bat_stat) as team_bat
      from
        (select pid, avg((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 1' and PL.user_email='[email protected]')
        group by pid),
        (select name from playeruserteam where name = 'Team 1' and user_email='[email protected]')
      group by name),
      (select avg(field_stat) as team_field
      from
        (select ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 1' and PL.user_email='[email protected]'))),
      (select avg(pitch_stat) as team_pitch
      from
        (select ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 1' and PL.user_email='[email protected]'))),
      (select name as team2, avg(bat_stat) as team_bat1
      from
        (select pid, avg((singles + (2 * doubles) + (3 * triples) + (4 * rbi) + (2 * stolen_bases) + (0.5 * hits) - (3 * strikeouts) + (4 * home_runs)) / games) as bat_stat
        from BATYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 2' and PL.user_email='[email protected]')
        group by pid),
        (select name from playeruserteam where name = 'Team 2' and user_email='[email protected]')
      group by name),
      (select avg(field_stat) as team_field1
      from
        (select ((putouts + (1.5 * assists) - (2 * errors) + (3 * double_plays)) / games) as field_stat
        from FIELDYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 2' and PL.user_email='[email protected]'))),
      (select avg(pitch_stat) as team_pitch1
      from
        (select ((outs + (5 * shutouts) - (2 * homeruns) - walks + strikeouts) / games) as pitch_stat
        from PITCHYEAR natural join statyear
        WHERE PID IN (select PID from PLAYERUSERTEAM PL where PL.name = 'Team 2' and PL.user_email='[email protected]')))));

-----------------------------------------------------------------------

/*Player Impact Analyser modified for player value analyser*/

select pid, (bat_play / bat_tot) as bat_stat, (field_play / field_tot) as field_stat, (pitch_play / pitch_tot) as pitch_stat
from
  (select avg(stat / tot) as bat_tot
  from
    (select (sum(singles) + (2 * sum(doubles)) + (3 * sum(triples)) + (4 * sum(rbi)) + (2 * sum(stolen_bases)) + (0.5 * sum(hits)) - (3 * sum(strikeouts)) + (4 * sum(home_runs))) as stat
    from BATYEAR A),
    (select sum(games) as tot
    from statyear C)),
  (select avg(stat / tot) as field_tot
  from
    (select (sum(putouts) + (1.5 * sum(assists)) - (2 * sum(errors)) + (3 * sum (double_plays))) as stat
    from FIELDYEAR A),
    (select sum(games) as tot
    from statyear C)),
  (select avg(stat / tot) as pitch_tot
  from
    (select (sum(outs) + (5 * sum(shutouts)) - (2 * sum(homeruns)) - sum(walks) + sum(strikeouts)) as stat
    from PITCHYEAR A),
    (select sum(games) as tot
    from statyear C)),
  ((select pid, avg(stat / tot) as bat_play
  from
    (select pid, (sum(singles) + (2 * sum(doubles)) + (3 * sum(triples)) + (4 * sum(rbi)) + (2 * sum(stolen_bases)) + (0.5 * sum(hits)) - (3 * sum(strikeouts)) + (4 * sum(home_runs))) as stat
    from BATYEAR
    group by PID) natural join
    (select pid, sum(games) as tot
    from statyear
    group by PID)
  group by pid) natural join
  (select pid, avg(stat / tot) as field_play
  from
    (select pid, (sum(putouts) + (1.5 * sum(assists)) - (2 * sum(errors)) + (3 * sum (double_plays))) as stat
    from FIELDYEAR
    group by PID) natural join
    (select pid, sum(games) as tot
    from statyear
    group by PID)
  group by pid) natural join
  (select pid, avg(stat / tot) as pitch_play
  from
    (select pid, (sum(outs) + (5 * sum(shutouts)) - (2 * sum(homeruns)) - sum(walks) + sum(strikeouts)) as stat
    from PITCHYEAR
    group by PID) natural join
    (select pid, sum(games) as tot
    from statyear
    group by PID)
  group by pid));

-------------------------------------------------------------