BigQuery - 按数组分组 [英] BigQuery - Group by on arrays

问题描述

我想对数组进行分组.

示例查询:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.323316 cost UNION ALL
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.342825 cost 
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
    UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM `project.dataset.table`

以上查询的结果:

Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.323316     
                    department      hrd      
2   compute         application    scaled-server  0.342825     
                    department     hrd

我想要如下结果:

Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.666141     
                    department      hrd   

推荐答案

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.323316 cost UNION ALL
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.342825 cost 
), temp AS (
  SELECT description, labels, SUM(cost) AS cost
  FROM `project.dataset.table`
  GROUP BY description, labels
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
    UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM temp

这篇关于BigQuery - 按数组分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！