BigQuery SQL：如何在F1车手聚合统计查询中获取最近夺冠赛事信息

嘿，我明白你遇到的问题了——之前的子查询要么返回全局的最近夺冠日期，要么拉回所有夺冠记录，没法精准拿到每个车手自己的最近夺冠日期。下面给你两种靠谱的解决方案：

方法1：使用关联子查询（直接嵌入主查询） #

你可以在主查询的SELECT列表里加一个关联子查询，它会针对每个车手的driverId单独查找其最近的夺冠日期。修改后的完整SQL如下：

SELECT 
  drivers.driverId, 
  driverRef, 
  SUM(IF(results.position <=5,1,0)) AS top_five_finishes, 
  SUM(IF(results.position <=3,1,0)) AS podium_finishes, 
  SUM(IF(results.position = 1,1,0)) AS number_of_wins, 
  MAX(races.date) AS most_recent_race,
  -- 新增：针对当前车手获取最近夺冠日期
  (SELECT MAX(r.date) 
   FROM `formula1.all_results` res 
   JOIN `formula1.all_races` r ON res.raceId = r.raceId 
   WHERE res.driverId = drivers.driverId AND res.position = 1) AS most_recent_win_date
FROM `formula1.all_results` AS results 
FULL JOIN `formula1.all_drivers` AS drivers ON results.driverId = drivers.driverId 
FULL JOIN `formula1.all_races` AS races ON results.raceId = races.raceId 
GROUP BY driverRef, driverId 
ORDER BY number_of_wins DESC

这个子查询的关键是WHERE res.driverId = drivers.driverId——它把子查询的范围限定在了当前分组的车手身上，所以返回的是该车手自己的最新夺冠日期。如果车手没有夺冠记录，这个字段会返回NULL，符合统计逻辑。

方法2：使用CTE（公共表达式）提前计算最近夺冠日期 #

如果觉得子查询嵌入主查询不够直观，你可以先用CTE生成一张“车手-最近夺冠日期”的临时表，再和主查询关联。这种方式可读性更强，数据量大时性能也更稳定：

WITH driver_recent_wins AS (
  -- 先计算每个车手的最近夺冠日期
  SELECT 
    res.driverId,
    MAX(r.date) AS most_recent_win_date
  FROM `formula1.all_results` res
  JOIN `formula1.all_races` r ON res.raceId = r.raceId
  WHERE res.position = 1
  GROUP BY res.driverId
)
-- 主查询关联临时表
SELECT 
  drivers.driverId, 
  driverRef, 
  SUM(IF(results.position <=5,1,0)) AS top_five_finishes, 
  SUM(IF(results.position <=3,1,0)) AS podium_finishes, 
  SUM(IF(results.position = 1,1,0)) AS number_of_wins, 
  MAX(races.date) AS most_recent_race,
  drw.most_recent_win_date
FROM `formula1.all_results` AS results 
FULL JOIN `formula1.all_drivers` AS drivers ON results.driverId = drivers.driverId 
FULL JOIN `formula1.all_races` AS races ON results.raceId = races.raceId 
LEFT JOIN driver_recent_wins drw ON drivers.driverId = drw.driverId
GROUP BY driverRef, driverId, drw.most_recent_win_date
ORDER BY number_of_wins DESC

为什么之前的尝试没成功？ #

• 第一个子查询没有关联外部的driverId，所以它是在全表范围内找最近的夺冠日期，而不是针对单个车手；
• 第二个子查询返回了所有夺冠记录，但没有按车手分组取最大日期，所以需要用MAX()聚合或者窗口函数（比如ROW_NUMBER()）筛选出最新的那一条。

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