需要帮助创建一个视图，该视图将为我提供 EPL 球队每场比赛的平均进球数和失球数-解网

问：

我正在努力想出正确的代码来计算每场比赛的平均进球数和失球数，并将其四舍五入到小数点后第二位（即每场比赛 2.34 个进球）。我有两个表 EPLTeams2023，其中包含一列包含所有团队名称。我有另一个名为 Matches_Duplicate 的表，其中包含匹配结果。该表的列包括 MatchID、HomeTeam、AwayTeam、HomeScore、AwayScore 和 MatchDate

基本上，我想要一个包含以下列的视图：团队 - AvgGoalsScoredPerGame - AvgGoalsConcededPerGame - TotalMatchesPlayed

这是我尝试的查询，但它似乎没有给我小数点后两位的平均值，我不确定它是否拉入了正确的分数：

CREATE VIEW EPLTeamStats2023 AS
SELECT
    t.Team AS TeamName,
    ROUND(AVG(goals_scored), 2) AS AvgGoalsScored,
    ROUND(AVG(goals_conceded), 2) AS AvgGoalsConceded,
    COUNT(*) AS TotalMatchesPlayed
FROM
    EPLTeams2023 t
JOIN (
    SELECT
        HomeTeam AS Team,
        HomeScore AS goals_scored,
        AwayScore AS goals_conceded
    FROM
        Matches_Duplicate
    WHERE
        HomeTeam IS NOT NULL
        AND AwayTeam IS NOT NULL
        AND HomeScore IS NOT NULL
        AND AwayScore IS NOT NULL
        AND MatchDate IS NOT NULL
        AND MatchDate >= '2023-08-01'
    UNION ALL
    SELECT
        AwayTeam AS Team,
        AwayScore AS goals_scored,
        HomeScore AS goals_conceded
    FROM
        Matches_Duplicate
    WHERE
        HomeTeam IS NOT NULL
        AND AwayTeam IS NOT NULL
        AND HomeScore IS NOT NULL
        AND AwayScore IS NOT NULL
        AND MatchDate IS NOT NULL
        AND MatchDate >= '2023-08-01'
) m ON t.Team = m.Team
GROUP BY
    t.Team;

sql-server

CREATE VIEW EPLTeamStats2023 AS
SELECT
    t.Team AS TeamName,
    ROUND(AVG(CAST(goals_scored AS FLOAT)), 2) AS AvgGoalsScored,
    ROUND(AVG(CAST(goals_conceded AS FLOAT)), 2) AS AvgGoalsConceded,
    COUNT(*) AS TotalMatchesPlayed
FROM
    EPLTeams2023 t
JOIN (
    SELECT
        HomeTeam AS Team,
        HomeScore AS goals_scored,
        AwayScore AS goals_conceded
    FROM
        Matches_Duplicate
    WHERE
        HomeTeam IS NOT NULL
        AND AwayTeam IS NOT NULL
        AND HomeScore IS NOT NULL
        AND AwayScore IS NOT NULL
        AND MatchDate IS NOT NULL
        AND MatchDate >= '2023-08-01'
    UNION ALL
    SELECT
        AwayTeam AS Team,
        AwayScore AS goals_scored,
        HomeScore AS goals_conceded
    FROM
        Matches_Duplicate
    WHERE
        HomeTeam IS NOT NULL
        AND AwayTeam IS NOT NULL
        AND HomeScore IS NOT NULL
        AND AwayScore IS NOT NULL
        AND MatchDate IS NOT NULL
        AND MatchDate >= '2023-08-01'
) m ON t.Team = m.Team
GROUP BY
    t.Team;

来源：

CREATE VIEW EPLTeamStats2023 AS
SELECT  t.Team AS TeamName
,   ROUND(AVG(goals_scored * 1.0), 2) AS AvgGoalsScored
,   ROUND(AVG(goals_conceded * 1.0), 2) AS AvgGoalsConceded
,   COUNT(*) AS TotalMatchesPlayed
FROM    EPLTeams2023 t
INNER JOIN Matches_Duplicate d
    ON  t.team IN (d.HomeTeam, AwayTeam)
WHERE   HomeTeam IS NOT NULL
AND AwayTeam IS NOT NULL
AND HomeScore IS NOT NULL
AND AwayScore IS NOT NULL
AND MatchDate >= '2023-08-01'
GROUP BY t.Team;

根据索引的不同，它可能会慢一点，但看起来会短一些，并且复制粘贴错误的可能性较小。基本上，您连接一次重复表并像以前一样计算平均值。

另外，我更喜欢这样做：使值变得“可平均”，但它可能与 CASTing 相同。goals_scored * 1.0

上一个：我正在尝试比较同一表中的数据行

下一个：普雷斯托 |Athena：非键列中的重复值

需要帮助创建一个视图，该视图将为我提供 EPL 球队每场比赛的平均进球数和失球数

Need help creating a view that will give me average goals scored and conceded per game for for EPL teams

评论

评论