/
football_match_his_l_s_attack_defence_strength_30.sql
195 lines (184 loc) · 8.17 KB
/
football_match_his_l_s_attack_defence_strength_30.sql
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
/************************************************
betting vault feature link-satellite
- for link with historic matches in division
- for home / away attack strenght & defence weakness
v1: - initial
- feature calcutlation
- 30 match_history
v2: - changed name
v3: - num zero games added
*************************************************/
--create or replace view betting_dv.football_match_his_l_s_attack_defence_strength_30
--drop table betting_dv.football_match_his_l_s_attack_defence_strength_30;
create or replace table betting_dv.football_match_his_l_s_attack_defence_strength_30
as
select
fixtures.football_match_his_lid,
round(home_str.avg_team_goals_for,2) avg_home_team_goals_for,
round(home_str.avg_team_goals_against,2) avg_home_team_goals_against,
round(away_str.avg_team_goals_for,2) avg_away_team_goals_for,
round(away_str.avg_team_goals_against,2) avg_away_team_goals_against,
round(home_str.avg_league_goals_for,2) avg_league_home_goals_for,
round(home_str.avg_league_goals_against,2) avg_league_home_goals_against,
round(away_str.avg_league_goals_for,2) avg_league_away_goals_for,
round(away_str.avg_league_goals_against,2) avg_league_away_goals_against,
round(home_str.home_attacking_strength,2) home_attacking_strength,
round(home_str.home_defence_strength,2) home_defence_strength,
round(away_str.away_attacking_strength,2) away_attacking_strength,
round(away_str.away_defence_strength,2) away_defence_strength,
(home_str.home_attacking_strength * away_str.away_defence_strength * home_str.avg_league_goals_for) home_expect_goals,
(away_str.away_attacking_strength * home_str.home_defence_strength * away_str.avg_league_goals_for) away_expect_goals,
home_str.num_games home_num_games,
away_str.num_games away_num_games,
home_str.league_num_games home_league_num_games,
away_str.league_num_games away_league_num_games,
home_str.league_num_zero_games home_league_num_zero_games,
away_str.league_num_zero_games away_league_num_zero_games
from
--match combinations
betting_dv.football_match_his_l fixtures
--home strength weakness
join
(
select
football_division_hid,
football_season_hid,
match_date,
football_team_home_hid,
league_num_games,
num_games,
league_num_zero_games,
avg_team_goals_for,
avg_team_goals_against,
avg_league_goals_for,
avg_league_goals_against,
round(avg_team_goals_for / avg_league_goals_for, 2) home_attacking_strength,
round(avg_team_goals_against / avg_league_goals_against, 2) home_defence_strength
from
(
select distinct
football_division_hid,
football_season_hid,
match_date,
football_team_home_hid,
count(match_date) over (partition by football_division_hid, football_season_hid, match_date) league_num_games,
count(match_date) over (partition by football_division_hid, football_season_hid, match_date, football_team_home_hid) num_games,
sum(case when goals_for = 0 then 1 else 0 end) over (partition by football_division_hid, football_season_hid, match_date) league_num_zero_games,
avg(goals_for) over (partition by football_division_hid, football_season_hid, match_date, football_team_home_hid) avg_team_goals_for,
avg(goals_against) over (partition by football_division_hid, football_season_hid, match_date, football_team_home_hid) avg_team_goals_against,
avg(goals_for) over (partition by football_division_hid, football_season_hid, match_date) avg_league_goals_for,
avg(goals_against) over (partition by football_division_hid, football_season_hid, match_date) avg_league_goals_against
from
(
select
dates.football_division_hid,
dates.football_season_hid,
dates.match_date,
his.match_date his_match_date,
his.football_team_home_hid,
his.football_team_away_hid,
stat.full_time_home_goals goals_for,
stat.full_time_away_goals goals_against,
dense_rank() over (partition by dates.football_division_hid, dates.football_season_hid, dates.match_date, his.football_team_home_hid order by his.match_date desc) rang_team
from
(
select distinct
football_division_hid,
football_season_hid,
match_date
from
betting_dv.football_match_his_l
) dates
join betting_dv.football_team_division_l team_division
on dates.football_season_hid = team_division.football_season_hid and
dates.football_division_hid = team_division.football_division_hid
join betting_dv.football_match_his_l his
on dates.football_division_hid = his.football_division_hid and
dates.match_date > his.match_date and
team_division.football_team_hid = his.football_team_home_hid
join betting_dv.football_match_his_l_s_statistic stat
on his.football_match_his_lid = stat.football_match_his_lid
)
where
rang_team <= 30
)
) home_str
on (
fixtures.football_division_hid = home_str.football_division_hid and
fixtures.football_season_hid = home_str.football_season_hid and
fixtures.match_date = home_str.match_date and
fixtures.football_team_home_hid = home_str.football_team_home_hid
)
--away strength weakness
join
(
select
football_division_hid,
football_season_hid,
match_date,
football_team_away_hid,
league_num_games,
num_games,
league_num_zero_games,
avg_team_goals_for,
avg_team_goals_against,
avg_league_goals_for,
avg_league_goals_against,
round(avg_team_goals_for / avg_league_goals_for, 2) away_attacking_strength,
round(avg_team_goals_against / avg_league_goals_against, 2) away_defence_strength
from
(
select distinct
football_division_hid,
football_season_hid,
match_date,
football_team_away_hid,
count(match_date) over (partition by football_division_hid, football_season_hid, match_date) league_num_games,
count(match_date) over (partition by football_division_hid, football_season_hid, match_date, football_team_away_hid) num_games,
sum(case when goals_for = 0 then 1 else 0 end) over (partition by football_division_hid, football_season_hid, match_date) league_num_zero_games,
avg(goals_for) over (partition by football_division_hid, football_season_hid, match_date, football_team_away_hid) avg_team_goals_for,
avg(goals_against) over (partition by football_division_hid, football_season_hid, match_date, football_team_away_hid) avg_team_goals_against,
avg(goals_for) over (partition by football_division_hid, football_season_hid, match_date) avg_league_goals_for,
avg(goals_against) over (partition by football_division_hid, football_season_hid, match_date) avg_league_goals_against
from
(
select
dates.football_division_hid,
dates.football_season_hid,
dates.match_date,
his.match_date his_match_date,
his.football_team_home_hid,
his.football_team_away_hid,
stat.full_time_away_goals goals_for,
stat.full_time_home_goals goals_against,
dense_rank() over (partition by dates.football_division_hid, dates.football_season_hid, dates.match_date, his.football_team_away_hid order by his.match_date desc) rang_team
from
(
select distinct
football_division_hid,
football_season_hid,
match_date
from
betting_dv.football_match_his_l
) dates
join betting_dv.football_team_division_l team_division
on dates.football_season_hid = team_division.football_season_hid and
dates.football_division_hid = team_division.football_division_hid
join betting_dv.football_match_his_l his
on dates.football_division_hid = his.football_division_hid and
dates.match_date > his.match_date and
team_division.football_team_hid = his.football_team_away_hid
join betting_dv.football_match_his_l_s_statistic stat
on his.football_match_his_lid = stat.football_match_his_lid
)
where
rang_team <= 30
)
) away_str
on (
fixtures.football_division_hid = away_str.football_division_hid and
fixtures.football_season_hid = away_str.football_season_hid and
fixtures.match_date = away_str.match_date and
fixtures.football_team_away_hid = away_str.football_team_away_hid
)
;