时间戳

首页 > 解决方案 > 在 Google Big Query 中执行简单分组

sql - 在 Google Big Query 中执行简单分组

问题描述

我对谷歌大查询有最简单的查询，它不断返回错误

Grouping by expressions of type STRUCT is not allowed

我只是想从两个位置选择电子邮件列表，将它们合并到一个 cte 中，并计算 cte 中的频率以识别重复项。

这应该很容易 - 我错过了什么？

with a as (select properties.email as email, 'loc1' as tag from `loc1.contacts`),

b as (select properties.email as email, 'loc2' as tag from `loc2.contacts`),

c as (
select * from a 
union all
select * from b
)

select email, count(email) from c group by 1

sample data:

email/tag
bob@email.com/loc1
bob@email.com/loc2

expected results:
email/count
bob@email.com/2

标签： sqlgoogle-bigquery

解决方案

看起来我需要添加 .value 才能实际获取电子邮件字段的值，以下查询按预期工作

with a as (select properties.email.value as email, 'loc1' as tag from `loc1.contacts`),

b as (select properties.email.value as email, 'loc2' as tag from `loc2.contacts`),

c as (
select * from a 
union all
select * from b
)

select email, count(email) from c group by 1

推荐阅读