sql - 在 BQ 中通过 STRUCT 聚合

Question

当我运行以下查询时

WITH total_count AS (
  SELECT
    id,
    pid,
    click_ts,
    stacked ,
    SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count),   
stacked AS (
  SELECT *
  FROM total_count
  WHERE stacked > 1
)

SELECT
  id,
  pid,
  total_count,
  SUM(stacked) AS stacked 
FROM stacked
GROUP BY 1, 2, 3

我收到一个错误:

No matching signature for aggregate function SUM for argument types: STRUCT. Supported signatures: SUM(INT64); SUM(FLOAT64) at [21:47]

当我尝试在以下 View 上执行 SUM(stacked) 时:

WITH total_count AS (
  SELECT
    id,
    pid,
    click_ts,
    stacked ,
    SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count), 

SELECT *
FROM total_count
WHERE stacked > 1 

一切正常!任何想法如何在同一个查询中使用它？

Answer 1

#standardSQL
WITH total_count AS (
  SELECT id, pid, click_ts, stacked ,
  SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count), 
stacked AS (
  SELECT *
  FROM total_count
  WHERE stacked > 1 ) 
SELECT id, pid, total_count, SUM(stacked.stacked) AS stacked 
FROM stacked
GROUP BY 1,2, 3