google-bigquery - BigQuery条件拆分聚合失败

我有一个类别列，该列包含一个字符串，该字符串在由“|”分隔的可变位置包含子类别字段。每个子类别的位置取决于字符串中项目的数量。例如:

category    subcat1    subcat2    subcat3
a|b|c       b          c          a
x|y|a|b     b          null       a

因此，为了解决一个类别，我有:

SELECT
  a.category AS category,
  case
    WHEN COUNT(SPLIT(a.category, "|")) = 4 then nth(4, SPLIT(a.category, "|"))
    WHEN COUNT(SPLIT(a.category, "|")) = 3 then nth(2, SPLIT(a.category, "|"))
    WHEN COUNT(SPLIT(a.category, "|")) = 2 then nth(2, SPLIT(a.category, "|"))
    else null
  end as subcat1,
  --nth(2, SPLIT(a.category, "|")) as x      --uncomment for success.  see below
FROM
  [interim_groups.articles_unique] as a

运行此命令失败，并显示以下信息:

SELECT clause has mix of aggregations 'subcat1' and fields 'category' without GROUP BY clause

现在我不想要group by子句，并且没有一个子句是有意义的，但是如果我包含它，它就会开始抱怨作用域聚合，这似乎是朝错误的方向发展。

如果我使用if语句而不是case语句，也会发生相同的情况。

现在这是奇怪的地方。如果我的查询中有注释行(或last(SPLIT(a.category, "|")) as x)，则查询可以顺利通过。

这是一个错误吗？我的查询看起来正确，并且在查询中以某种方式使其通过的额外列很奇怪。
有没有比只留一个不必要的列来稳定查询更好的方法来解决此问题？

最佳答案

该查询缺少'WITHIN RECORD'关键字。

SELECT
  a.category AS category,
  case
    WHEN COUNT(SPLIT(a.category, "|")) = 4 then nth(4, SPLIT(a.category, "|"))
    WHEN COUNT(SPLIT(a.category, "|")) = 3 then nth(2, SPLIT(a.category, "|"))
    WHEN COUNT(SPLIT(a.category, "|")) = 2 then nth(2, SPLIT(a.category, "|"))
    else null
  end WITHIN RECORD as subcat1 ,
FROM (SELECT category FROM
  (SELECT 'a|b|c' category), (SELECT 'a|b' category), (SELECT 'a|b|c|d' category)) a

请参阅WITHIN的文档:

WITHIN关键字专门用于聚合函数，以跨记录和嵌套字段中的子级和重复字段进行聚合。指定WITHIN关键字时，需要指定要聚合的范围-
WITHIN RECORD:在记录内的重复值中聚合数据。

https://cloud.google.com/bigquery/docs/data#within