sql - Transposing Strings
问题描述
I am exploring mlb baseball statcast data, which is at the pitch level. I want to summarize the pitches at the plate appearance level. The relevant columns:
game_pk | pitcher_id | batter_id | inning | pitch_number | pitch_type | plt_apprnc_pk (unique)
----------------------------------------------------------------------------------------------
492321 | 542881 | 518466 | 2 | 1 | FF | 4923215428815184662
492321 | 543475 | 571448 | 1 | 2 | SL | 4923215434755714481
492321 | 543475 | 571448 | 1 | 1 | FF | 4923215434755714481
492321 | 543475 | 571448 | 1 | 3 | FF | 4923215434755714481
I am working on a query only showing one iteration of each plate appearance (plt_apprnc_pk), followed by pitch_number columns (1st pitch, 2nd pitch, 3rd pitch, etc...) showing each pitch type for the respective pitch number. In essence I want to transpose the pitch_type by pitch_number.
plt_apprnc_pk (unique)| first_pitch | second_pitch | third_pitch
----------------------------------------------------------------
4923215434755714481 | SL | FF | FF
I tried doing this with Case statements (ie CASE WHEN pitch_number = 1 THEN pitch_type END). I keep getting multiple rows for each plt_apprnc_pk.
plt_apprnc_pk (unique)| first_pitch | second_pitch | third_pitch
------------------------------------------------------------------
4923215434755714481 | SL | NULL | NULL
4923215434755714481 | NULL | FF | NULL
4923215434755714481 | NULL | NULL | FF
What is the best way to transpose these strings based on pitch_number while keeping only one iteration of the unique key??
解决方案
I tried doing this with Case statements (ie CASE WHEN pitch_number = 1 THEN pitch_type END). I keep getting multiple rows for each plt_apprnc_pk.
The pattern here is called conditional aggregation:
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 thrid_pitch
-- ...
--,MAX(CASE WHEN pitch_number = n THEN pitch_type END) AS nth_pitch
FROM tab
GROUP BY plt_apprnc_pk;
Note: The maximum "pitch_number" has to be known in advance.
推荐阅读
- sql-server - 在“选择”中更好地查询“选择”
- javascript - Angular formatDate() 将我的值设置为以前的值
- c# - 为什么 Visual Studio 会自动使用所有异步方法创建 ApiController?如何在其中创建带有同步方法的 ApiController?
- ios - 为什么在 64 位架构的 iOS 中,数字指针的地址增加了 1 个字节,而不是 8 个字节?
- java - 尝试使用 thymeleaf 进行 Spring Boot 表单验证,但出现我无法弄清楚的错误
- javascript - 如何更改按钮中的文本颜色
- amazon-web-services - 如何提取从 AWS EventBridge 中继到 ECS Fargate 的事件
- optimization - CPLEX 和 MOSEK 与 YALMIP 给出不同的结果
- c# - 可以在不使用属性的情况下获取元组值吗?
- python - 尝试使用网络摄像头时 yolo 编译出现问题