BigQuery中对角元素的和

如果您被绑定到您的模式/设计-这意味着您有代表标签和数组的列-下面应该适合您

#standardSQL
SELECT SUM(CAST(REGEXP_EXTRACT(TO_JSON_STRING(t), 
    CONCAT(r'(?::\d*.*?){', CAST(predictedGroup + 1 AS STRING), r'}:(\d*)')) AS INT64)
  ) AS sum_of_diagonal
FROM `project.dataset.sampleTable` t  

您可以使用下面的问题中的示例数据测试、玩上面的游戏

#standardSQL
WITH `project.dataset.sampleTable` AS (
  SELECT 0 AS predictedGroup , 60 AS label0 , 20 AS label1, 20 AS label2 UNION ALL
  SELECT 1, 20 , 60 , 20 UNION ALL
  SELECT 2, 20 , 20 , 60
)
SELECT SUM(CAST(REGEXP_EXTRACT(TO_JSON_STRING(t), 
    CONCAT(r'(?::\d*.*?){', CAST(predictedGroup + 1 AS STRING), r'}:(\d*)')) AS INT64)
  ) AS sum_of_diagonal
FROM `project.dataset.sampleTable` t  

结果

Row sum_of_diagonal  
1   180  

如果您对数据表示采用这种方法,那么可能会出现如下情况:

WITH sampleTable as 
(
select 0 as predictedGroup, [60, 20, 20] as labelVals
union all
select 1, [20 , 60 , 20]
union all
select 2, [20 , 20 , 60]
)

SELECT SUM(labelVals[SAFE_OFFSET(predictedGroup)]) FROM sampleTable