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

问题描述

Google Analytics BigQuery表结构如下（传统SQL符号 - 仅显示相关字段）：

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

表，下面的查询效果很好：

  SELECT 
 visitId，
 MIN（h.hour）AS firstHitHour 
 FROM 
`my-table.ga_sessions_20161122`，UNNEST（hits）AS h 
 GROUP BY 
 visitId 
   
 
但是使用这种替代语法：
 
 $ p $  SELECT 
 visitId ，
（SELECT MIN（小时）FROM UNNEST（hits））as firstHitHour 
 FROM 
`my-table.ga_sessions_20161122` 
 GROUP BY 
 visitId 
  

触发以下错误：

错误：UNNEST表达式引用既不分组也不是agg的列匹配已注册

我知道 UNNEST（点击）必须以某种方式分组或聚合，但由于这一列是一个数组（重复），它究竟意味着什么？

如果我尝试根据要求对 hits >列进行分组：

  （SELECT MIN（小时）FROM UNNEST（hits）as h GROUP BY h）as firstHitHour 
  

然后我得到一个不允许使用类型为STRUCT的表达式进行分组
$ b

如何修正这种替代语法以产生与第一个相同的结果？

解决方案

我的第一个答案是针对此问题的原始版本。

当我回答时，我意识到你已经将它改变为完全不同的一种：o）



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

我认为在替代版本中，您根本不需要GROUP BY ，因为你操作的是原始的（未拼合的）行和每行（visitId）你计算firstHitHour

  SELECT 
 visitId，
（SELECT MIN（小时）FROM UNNEST（hits））as firstHitHour 
 FROM 
`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屋！