时间戳

首页 > 解决方案 > 在查询中包括字段，但不是聚合的

google-cloud-platform - 在查询中包括字段，但不是聚合的

问题描述

我在 bigQuery 中有以下查询：

    SELECT
    project.name,
    invoice.month,
    service.description,
    project.labels,  <-- error
    (SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0)) / 1000000)*-1
      AS credits,
    (SUM(CAST(cost * 1000000 AS int64))
      + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0))) / 1000000
      AS cost_after_credits
    FROM `xxxx`
    GROUP BY 1, 2, 3
    ORDER BY 1 ASC
    ;

如果我删除，查询工作正常，project.labels但我真的需要这些信息。这是我在控制台中看到的错误：SELECT list expression references project.labels which is neither grouped nor aggregated at [5:3]如果我尝试在 GROUP BY 中包含4我得到的字段：Grouping by expressions of type ARRAY is not allowed at [14:19]我知道这是因为该字段是一个record repeated字段。想知道如何更好地构建我的查询以获取每一行的标签。

标签： google-cloud-platformgoogle-bigquery

解决方案

第一个问题是因为如果未将列添加到 group by 子句，则无法将列添加到 select 语句中，第二个问题是因为您试图按数组进行分组。

为了解决这个问题，您可以使用TO_JSON_STRING，查询将如下所示：

 SELECT
    project.name,
    invoice.month,
    service.description,
    TO_JSON_STRING(project.labels),
    (SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0)) / 1000000)*-1
      AS credits,
    (SUM(CAST(cost * 1000000 AS int64))
      + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0))) / 1000000
      AS cost_after_credits
    FROM `xxxx`
    GROUP BY 1, 2, 3, TO_JSON_STRING(project.labels)
    ORDER BY 1 ASC
    ;

或者，如果您需要保留标签数组，您可以使用ANY_VALUE

SELECT
    project.name,
    invoice.month,
    service.description,
    ANY_VALUE(project.labels),
    (SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0)) / 1000000)*-1
      AS credits,
    (SUM(CAST(cost * 1000000 AS int64))
      + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                    FROM UNNEST(credits) c), 0))) / 1000000
      AS cost_after_credits
    FROM `xxxx`
    GROUP BY 1, 2, 3, TO_JSON_STRING(project.labels)
    ORDER BY 1 ASC
    ;

如果您想避免使用 GROUP BY，则需要进行左连接，即查询的左侧和表的右侧（xxxx在提供的示例中）

推荐阅读