如何在Firebase Bigquery SQL中按"operating system"获取"session duration"组？

Question

我尝试按"操作系统"(device.operating_system(和"日期"(event_date(获取"平均会话持续时间"组。

在Firebase博客中，他们向我们提供了此查询，以获取平均持续时间会话

SELECT SUM(engagement_time) AS total_user_engagement 
FROM (
SELECT user_pseudo_id, 
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 
"engagement_time_msec") AS engagement_time
FROM `FIREBASE_PROJECT` 
)
WHERE engagement_time > 0
GROUP BY user_pseudo_id

此查询按用户 ID 为我提供了用户参与度(每行都是不同的用户(：

row|total_user_engagement
---|------------------
1  |989646
2  |225655
3  |125489
4  | 58496
...|......

但是我不知道我必须在哪里添加"操作系统"和"event_date"变量才能按操作系统和日期获取此信息。我尝试了不同的查询，但没有结果。例如，为了通过操作系统获得此结果，我尝试了以下方法

SELECT SUM(engagement_time) AS total_user_engagement 
FROM (
SELECT device.operating_system, 
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 
"engagement_time_msec") AS engagement_time
FROM `FIREBASE_PROJECT` 
)
WHERE engagement_time > 0
GROUP BY device.operating_system

但它给了我一条错误消息(错误：无法识别的名称：[9：10] 的设备(。在其他查询中，device.operating_system被识别。

例如在那个：

SELECT 
event_date,
device.operating_system as os_type,
device.operating_system_version as os_version,
device.mobile_brand_name as device_brand, 
device.mobile_model_name as device_model,
count(distinct user_pseudo_id) as all_users
FROM `FIREBASE Project`
GROUP BY 1,2,3,4,5

我想要的结果是这样的：

row|event_date|OS     |total_user_engagement
---|----------------------------------------
1  |20191212  |ios    |989646
2  |20191212  |android|225655
3  |20191212  |ios    |125489
4  |20191212  |android| 58496
...

谢谢

Answer 1

此错误可能是因为您在外部查询中引用了变量device，而此变量仅在内部查询(子查询(中可见。我相信通过将查询的最后一行从GROUP BY device.operating_system自GROUP BY operating_system.

希望这会更清楚地说明这里发生的事情：内部查询正在访问表FIREBASE_PROJECT并从嵌套列device返回字段operating_system。外部查询访问内部查询的结果，因此它只能看到返回的字段operating_system，而看不到嵌套变量device中有关其原始上下文的信息。这就是为什么尝试在此级别引用device会失败的原因。

在您发布的另一个示例中，此问题不会出现，因为只有一个简单的查询。