SQL技术问询：如何移除PGA赛事数据Finish列开头的'T'字符

我有一组PGA巡回赛的赛事结果数据，希望移除Finish列中部分字符串开头的'T'字符，以便计算平均值。该列字符串长度不固定，部分行还包含'CUT'值。我已使用WHERE Finish not like "CUT"语句排除了'CUT'值，但尝试多种函数均无法移除'T'，恳请提供帮助。

补充信息 #

以下是我目前已实现的SQL代码，可按球员分组聚合计算各项平均值，符合预期需求：

SELECT
  DISTINCT(player),
  ROUND(AVG(CAST(sg_putt as numeric)),2) as avg_sg_putt, 
  ROUND(AVG(CAST(sg_arg as numeric)),2) as avg_sg_arg, 
  ROUND(AVG(CAST(sg_app as numeric)),2) as avg_sg_app, 
  ROUND(AVG(CAST(sg_ott as numeric)),2) as avg_sg_ott, 
  ROUND(AVG(CAST(sg_t2g as numeric)),2) as avg_sg_t2g, 
  ROUND(AVG(CAST(sg_total as numeric)),2) as avg_sg_total,
  SUM(made_cut) as cuts_made,
  COUNT(DISTINCT(tournament_id)) as total_played
FROM
  `pga_stats_2015_2022.stats`
WHERE 
  season >= 2017 AND
  sg_putt not like "NA" AND
  sg_arg not like "NA" AND
  sg_app not like "NA" AND
  sg_ott not like "NA" AND
  sg_t2g not like "NA" AND
  sg_total not like "NA"
GROUP BY player
HAVING total_played > 50
ORDER BY(avg_sg_total) DESC

从你提供的表名格式判断，应该是使用BigQuery环境，以下两种方法可以快速解决Finish列的'T'移除问题：

方法1：正则表达式替换开头的'T' #

用REGEXP_REPLACE精准匹配字符串开头的'T'并移除，再转换为数值计算平均值：

SELECT
  player,
  -- 处理Finish列并计算平均排名
  ROUND(AVG(CAST(REGEXP_REPLACE(Finish, '^T', '') AS NUMERIC)), 2) AS avg_finish,
  -- 保留原有聚合字段
  ROUND(AVG(CAST(sg_putt as numeric)),2) as avg_sg_putt, 
  ROUND(AVG(CAST(sg_arg as numeric)),2) as avg_sg_arg, 
  ROUND(AVG(CAST(sg_app as numeric)),2) as avg_sg_app, 
  ROUND(AVG(CAST(sg_ott as numeric)),2) as avg_sg_ott, 
  ROUND(AVG(CAST(sg_t2g as numeric)),2) as avg_sg_t2g, 
  ROUND(AVG(CAST(sg_total as numeric)),2) as avg_sg_total,
  SUM(made_cut) as cuts_made,
  COUNT(DISTINCT(tournament_id)) as total_played
FROM
  `pga_stats_2015_2022.stats`
WHERE 
  season >= 2017 AND
  sg_putt not like "NA" AND
  sg_arg not like "NA" AND
  sg_app not like "NA" AND
  sg_ott not like "NA" AND
  sg_t2g not like "NA" AND
  sg_total not like "NA" AND
  Finish NOT LIKE 'CUT' AND
  -- 过滤处理后为空的异常值
  REGEXP_REPLACE(Finish, '^T', '') != ''
GROUP BY player
HAVING total_played > 50
ORDER BY avg_sg_total DESC

• ^T表示仅匹配字符串开头的'T'，不会误删中间或结尾的'T'；
• 移除DISTINCT(player)，因为GROUP BY player已经自动实现分组去重，无需额外声明。

方法2：字符串截取判断 #

如果确认'T'只出现在开头，也可以用字符截取的方式处理：

SELECT
  player,
  ROUND(AVG(CAST(
    CASE 
      WHEN LEFT(Finish, 1) = 'T' THEN SUBSTR(Finish, 2)
      ELSE Finish
    END AS NUMERIC)), 2) AS avg_finish,
  -- 保留原有聚合字段
  ROUND(AVG(CAST(sg_putt as numeric)),2) as avg_sg_putt, 
  ROUND(AVG(CAST(sg_arg as numeric)),2) as avg_sg_arg, 
  ROUND(AVG(CAST(sg_app as numeric)),2) as avg_sg_app, 
  ROUND(AVG(CAST(sg_ott as numeric)),2) as avg_sg_ott, 
  ROUND(AVG(CAST(sg_t2g as numeric)),2) as avg_sg_t2g, 
  ROUND(AVG(CAST(sg_total as numeric)),2) as avg_sg_total,
  SUM(made_cut) as cuts_made,
  COUNT(DISTINCT(tournament_id)) as total_played
FROM
  `pga_stats_2015_2022.stats`
WHERE 
  season >= 2017 AND
  sg_putt not like "NA" AND
  sg_arg not like "NA" AND
  sg_app not like "NA" AND
  sg_ott not like "NA" AND
  sg_t2g not like "NA" AND
  sg_total not like "NA" AND
  Finish NOT LIKE 'CUT'
GROUP BY player
HAVING total_played > 50
ORDER BY avg_sg_total DESC

• LEFT(Finish,1)提取第一个字符，判断是否为'T'，是则从第2位开始截取后续内容，否则直接使用原字符串。

内容的提问来源于stack exchange，提问作者Wesley Edward