基于pitch_number转置MLB Statcast投球数据并保留唯一plt_apprnc_pk单条记录的最优方案

你遇到的问题非常典型：单独用CASE语句只会为每一次投球生成一行记录，没办法把同一打席的多条记录合并成单条。解决这个问题的核心是用聚合函数配合CASE语句，再按唯一打席ID分组。

核心思路 #

对于每个plt_apprnc_pk（唯一打席标识），我们需要把不同pitch_number对应的pitch_type提取到对应列，同时合并同一打席的所有记录。这里可以用MAX()（或MIN()，因为同一打席里每个pitch_number只会对应一个pitch_type）来聚合CASE语句的结果，自动过滤掉NULL值，再通过GROUP BY plt_apprnc_pk确保每个打席只保留一行数据。

完整SQL查询示例 #

假设你的数据表名为statcast_pitches，对应的查询语句如下：

SELECT
  plt_apprnc_pk,
  MAX(CASE WHEN pitch_number = 1 THEN pitch_type END) AS first_pitch,
  MAX(CASE WHEN pitch_number = 2 THEN pitch_type END) AS second_pitch,
  MAX(CASE WHEN pitch_number = 3 THEN pitch_type END) AS third_pitch
  -- 如果打席的投球数超过3次，继续添加类似的MAX(CASE...)语句即可
FROM statcast_pitches
GROUP BY plt_apprnc_pk;

效果说明 #

执行这个查询后，同一plt_apprnc_pk的所有投球记录会被合并：

• 当pitch_number=1时，CASE返回对应的pitch_type，其他行的该CASE结果为NULL，MAX()会提取出非空的那个值；
• 同理处理pitch_number=2、3的情况，最终每个打席只会生成一行记录，和你预期的输出完全一致：

扩展提示 #

如果你的数据中存在投球数超过3次的打席，只需要继续添加对应的MAX(CASE WHEN pitch_number = N THEN pitch_type END) AS nth_pitch语句即可。如果不确定数据里的最大投球数，可以先运行下面的查询统计：

SELECT plt_apprnc_pk, MAX(pitch_number) AS max_pitches_per_pa
FROM statcast_pitches
GROUP BY plt_apprnc_pk
ORDER BY max_pitches_per_pa DESC;

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