时间戳

首页 > 解决方案 > 在 SQL 中计算具有别名的新字段

sql - 在 SQL 中计算具有别名的新字段

问题描述

您好我正在处理一些体育赌博数据并尝试划分列以尝试计算平均值和其他统计数据，基本上我想创建一个新列来计算以下内容--> Covered/(Covered+Loss+Push) 为Covered_Average。我觉得在 excel 中执行此操作要容易得多，但想知道在 SQL 中是否有效。

select g1.team_id, t.abbreviation,
     count(case when cast(g2.pts as float)-cast(g1.pts as float) < cast(spread2 as float) then 'Covered' end) as Covered,
     count(case when cast(g2.pts as float)-cast(g1.pts as float) > cast(spread2 as float) then 'Loss' end) as Loss,
    count(case when cast(g2.pts as float)-cast(g1.pts as float) = cast(spread2 as float) then 'Push' end) as Push 
     from nba_games_all g1
     left outer join nba_games_all g2 on g1.game_id = g2.game_id
     and g1.team_id =g2.a_team_id
     left join nba_teams_all t on t.team_id=g1.team_id
     left join nba_betting_spread bs on g1.game_id = bs.game_id
     and book_name = 'Bovada'
     left join nba_betting_money_line ml on g1.game_id = ml.game_id
     and ml.book_name = 'Bovada'
     where g1.season_year = '2017' 
     and g1.season_type = 'Regular Season'
     and g1.fg_pct >= '0.5'
     and g1.game_date >= Convert(datetime, '2017-11-02')
     group by g1.team_id, t.abbreviation
     order by Covered desc

标签： sqlaliasinteger-arithmetic

解决方案

我可能会写这样的查询：

 SELECT
 g1.team_id
 , t.abbreviation
 , CASE
        WHEN CAST(g2.pts AS FLOAT)-CAST(g1.pts AS FLOAT) < CAST(spread2 AS FLOAT) then 'Covered'
        when CAST(g2.pts AS FLOAT)-CAST(g1.pts AS FLOAT) > CAST(spread2 AS FLOAT) then 'Loss'
        when CAST(g2.pts AS FLOAT)-CAST(g1.pts AS FLOAT) = CAST(spread2 AS FLOAT) then 'Push'
        ELSE 'Error'
   END as 'clp'
 ,count(g1.came_id) as 'game_count'
 FROM nba_games_all g1
 INNER JOIN nba_games_all g2 ON g1.game_id = g2.game_id AND g1.team_id = g2.a_team_id
 INNER JOIN nba_betting_spread bs ON g1.game_id = bs.game_id
 INNER JOIN nba_teams_all t ON g1.team_id = t.team_id
 WHERE
    g1.season_year = '2017' 
    AND g1.season_type = 'Regular Season'
    AND g1.fg_pct >= '0.5'
    AND g1.game_date >= Convert(datetime, '2017-11-02')
GROUP BY
    1,2,3;

推荐阅读