You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
29 lines
821 B
29 lines
821 B
-- Add up migration script here
|
|
CREATE OR REPLACE VIEW team_points_view
|
|
AS SELECT
|
|
teams.id AS team_id,
|
|
games.id AS game_id,
|
|
games.division AS division_id,
|
|
divisions.league AS league_id,
|
|
reg_win(games.id, teams.id) AS reg_wins,
|
|
reg_loss(games.id, teams.id) AS reg_losses,
|
|
ot_win(games.id, teams.id) AS ot_wins,
|
|
ot_loss(games.id, teams.id) AS ot_losses,
|
|
tie(games.id, teams.id) AS ties,
|
|
iihf_points(games.id, teams.id) AS points
|
|
FROM games
|
|
JOIN divisions
|
|
ON divisions.id=games.division
|
|
JOIN periods
|
|
ON periods.game=games.id
|
|
JOIN shots
|
|
ON shots.period=periods.id
|
|
JOIN game_players
|
|
ON game_players.id=shots.shooter
|
|
JOIN teams scoring_team
|
|
ON scoring_team.id=game_players.team
|
|
JOIN teams
|
|
ON teams.id=games.team_home
|
|
OR teams.id=games.team_away
|
|
GROUP BY team_id,game_id,division_id,league_id;
|