sql - 在 BigQuery 中,计算分组中两行之间的差异
问题描述
with
my_stats as (
select 24996 as competitionId, 17 as playerId, 'on' as onOff, 8 as fga, 4 as fgm, 0.50 as fgPct union all
select 24996 as competitionId, 17 as playerId, 'off' as onOff, 5 as fga, 3 as fgm, 0.60 as fgPct union all
select 24996 as competitionId, 24 as playerId, 'on' as onOff, 9 as fga, 6 as fgm, 0.67 as fgPct union all
select 24996 as competitionId, 24 as playerId, 'off' as onOff, 3 as fga, 1 as fgm, 0.33 as fgPct union all
select 24996 as competitionId, 27 as playerId, 'on' as onOff, 5 as fga, 4 as fgm, 0.8 as fgPct
),
my_output as (
select 24996 as competitionId, 17 as playerId, 'diff' as onOff, 3 as fga, 1 as fgm, -0.1 as fgPct union all
select 24996 as competitionId, 24 as playerId, 'diff' as onOff, 6 as fga, 5 as fgm, 0.34 as fgPct
)
select * from my_stats
select * from my_output
这是一个简单的示例来演示我们正在努力解决的问题。我们有 table my_stats
,其中主键是 的组合competitionId, playerId, onOff
,并且onOff
列只能是“on”或“off”。对于单个competitionId, playerId
then(有两行,一行用于“on”,一行用于“off”),我们希望从所有其他列中减去值(on - off)。
希望my_output
表格清楚地说明我们需要什么输出。在 的情况下playerId = 27
,由于该玩家没有“关闭”行,因此可以简单地从输出中删除它们,因为无需进行计算。
解决方案
另一种基于自连接的解决方案:
select
t1.competitionId,
t1.playerId,
'diff' as onOff,
t1.fga - t2.fga as fga,
t1.fgm - t2.fgm as fgm,
t1.fgpct - t2.fgpct as fgpct
from my_stats as t1
join my_stats as t2
on t1.competitionId = t2.competitionId
and t1.playerId = t2.playerId
where t1.onOff = 'on'
and t2.onOff = 'off'
您应该检查哪种方法更有效