BigQuery如何先按id分组再按重复RECORD的type二次分组聚合

注意前提 #

s 是重复记录类型，需要先展开处理，同时 s.amount 是字符串类型，求和前需要转换为数值类型，以下实现用 SAFE_CAST 避免非法数值导致查询报错，你可以根据实际数据格式换成 CAST(... AS INT64)。

第二种输出格式（按ID+type分行，更灵活） #

不需要预先知道type的枚举值，通用度更高：

WITH base AS (
  SELECT 
    id,
    cost,
    s_item.type,
    SAFE_CAST(s_item.amount AS FLOAT64) AS amount
  FROM `你的表完整名称`
  LEFT JOIN UNNEST(s) AS s_item
),
id_cost_total AS (
  -- 先按ID聚合计算总cost
  SELECT id, SUM(cost) AS sum_cost
  FROM base
  GROUP BY id
)
SELECT 
  b.id,
  ict.sum_cost AS `sum(cost)`,
  b.type,
  SUM(b.amount) AS `sum(amount)`
FROM base b
INNER JOIN id_cost_total ict ON b.id = ict.id
WHERE b.type IS NOT NULL -- 不需要保留无s记录的ID可保留此行，否则删除
GROUP BY b.id, ict.sum_cost, b.type
ORDER BY b.id, b.type

第一种输出格式（type转列，行转列） #

适合type枚举值固定的场景，以下以type枚举为aa、bb、cc为例：

WITH base AS (
  SELECT 
    id,
    cost,
    s_item.type,
    SAFE_CAST(s_item.amount AS FLOAT64) AS amount
  FROM `你的表完整名称`
  LEFT JOIN UNNEST(s) AS s_item
),
id_cost_total AS (
  SELECT id, SUM(cost) AS sum_cost
  FROM base
  GROUP BY id
),
type_agg AS (
  SELECT 
    id,
    SUM(IF(type = 'aa', amount, 0)) AS `sum(aa)`,
    SUM(IF(type = 'bb', amount, 0)) AS `sum(bb)`,
    SUM(IF(type = 'cc', amount, 0)) AS `sum(cc)`
  FROM base
  GROUP BY id
)
SELECT 
  ict.id,
  ict.sum_cost AS `sum(cost)`,
  ta.`sum(aa)`,
  ta.`sum(bb)`,
  ta.`sum(cc)`
FROM id_cost_total ict
INNER JOIN type_agg ta ON ict.id = ta.id
ORDER BY ict.id

如果type枚举不固定，你可以使用BigQuery的动态SQL或者PIVOT语法实现动态行转列。

内容的提问来源于stack exchange，提问作者vego