UNNEST 表达式引用既不分组也不聚合的列 [英] UNNEST expression references column which is neither grouped nor aggregated

问题描述

Google Analytics BigQuery 表的结构如下(旧 SQL 表示法 - 仅显示相关字段):

visitId:整数命中:记录/重复hits.hour: 整数

在一个这样的表上，以下查询运行良好:

SELECT访问 ID，MIN(h.hour) AS firstHitHour从`my-table.ga_sessions_20161122`，UNNEST(hits) AS h通过...分组访问ID

但是使用这种替代语法:

SELECT访问 ID，(SELECT MIN(hour) FROM UNNEST(hits)) 作为 firstHitHour从`my-table.ga_sessions_20161122`通过...分组访问ID

触发以下错误:

<块引用>

错误:UNNEST 表达式引用了既未分组也未聚合的列命中

我知道 UNNEST(hits) 必须以某种方式分组或聚合，但由于此列是一个 数组(重复)，它究竟是什么意思?>

如果我按照要求尝试将 hits 列分组"，如下所示:

(SELECT MIN(hour) FROM UNNEST(hits) as h GROUP BY h) as firstHitHour

然后我得到一个 Grouping by Expressions of type STRUCT is not allowed 错误.

如何更正此替代语法以产生与第一个相同的结果?

解决方案

我的第一个答案是针对这个问题的原始版本.
当我回答时，我意识到您已将其更改为完全不同的 :o)

所以下面的答案是针对您问题的最新版本:

我认为在替代"版本中，您根本不需要 GROUP BY，因为您逐行对原始(未展平)进行操作，并且为每一行(visitId)计算 firstHitHour

SELECT访问 ID，(SELECT MIN(hour) FROM UNNEST(hits)) 作为 firstHitHour从`my-table.ga_sessions_20161122`

在您的初始查询中 - 您有点将每一行的所有记录展平 - 所以这就是为什么您需要将它们重新分组

Google Analytics BigQuery tables are structured like this (Legacy SQL notations - only relevant fields are shown):

visitId:                      INTEGER
hits:                         RECORD/REPEATED
hits.hour:                    INTEGER

On one such table, the following query works well:

SELECT
  visitId,
  MIN(h.hour) AS firstHitHour
FROM
  `my-table.ga_sessions_20161122`, UNNEST(hits) AS h
GROUP BY
  visitId

But using this alternative syntax:

SELECT
  visitId,
  (SELECT MIN(hour) FROM UNNEST(hits)) as firstHitHour
FROM
  `my-table.ga_sessions_20161122`
GROUP BY
  visitId

Triggers the following error:

Error: UNNEST expression references column hits which is neither grouped nor aggregated

I understand that UNNEST(hits) must be somehow grouped or aggregated, but since this column is an array (repeated), what does it mean exactly?

If I try to "group the column hits", as requested, like this:

(SELECT MIN(hour) FROM UNNEST(hits) as h GROUP BY h) as firstHitHour

Then I get a Grouping by expressions of type STRUCT is not allowed error.

How can this alternative syntax be corrected to produce the same result as the first one?

解决方案

My first Answer is for original version of this question.
When I answered, I realized you have changed it to quite different one :o)

So below answer is for most recent version of your question:

I think that in "alternative" version you just do not need GROUP BY at all, because you operate on original (un-flattened) row by row and for each row (visitId) you calculate firstHitHour

SELECT
  visitId,
  (SELECT MIN(hour) FROM UNNEST(hits)) as firstHitHour
FROM
  `my-table.ga_sessions_20161122`

In your initial query - you kind of flattening all records for each row - so that's why you need then to group them back

这篇关于UNNEST 表达式引用既不分组也不聚合的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！