时间戳

首页 > 解决方案 > 一列的 SQL AVG 值，其中另一列等于特定值

sql - 一列的 SQL AVG 值，其中另一列等于特定值

问题描述

我得到了投手、投手类型和投手速度的数据。

|------------------------------------------------|
| day | inning | pitcher| pitch_type| pitch_speed|
|  1       1        AE1     fastball|      97    |
|  1       1        AE1     fastball|      94    |
|  1       1        AE1      slider |      83    |
|  1       2        AE1     fastball|      96    |
|  1       2        AE1      slider |      86    |
|  1       2        AE1     fastball|      97    |
|------------------------------------------------|

有没有办法查询数据以获得特定音高类型的音高速度的平均值。

IE 一种返回 fastball_speed = 96 和 slider_speed = 84.5（平均值）的方法

标签： sqlgoogle-bigquery

解决方案

那这个呢？

select pitch_type, avg(pitch_speed) from your_table group by pitch_type

顺便说一句，在指定样本数据时，请使用 CTE 使求解器的工作更轻松：

#standardSql
with t as (
select   1 as day,      1 as inning,       'AE1' as pitcher,     'fastball' as pitch_type,    97 as pitch_speed union all
select   1 as day,      1 as inning,       'AE1' as pitcher,     'fastball' as pitch_type,    94 as pitch_speed union all
select   1 as day,      1 as inning,       'AE1' as pitcher,     'slider'   as pitch_type,    83 as pitch_speed union all
select   1 as day,      2 as inning,       'AE1' as pitcher,     'fastball' as pitch_type,    96 as pitch_speed union all
select   1 as day,      2 as inning,       'AE1' as pitcher,     'slider'   as pitch_type,    86 as pitch_speed union all
select   1 as day,      2 as inning,       'AE1' as pitcher,     'fastball' as pitch_type,    97 as pitch_speed
)
select pitch_type, avg(pitch_speed) from t group by pitch_type

推荐阅读