第一天安装者的原始 BigQuery 日志数据 [英] Raw BigQuery Log-data for 1st Day Installers

问题描述

我是 StandardSQL 的新手，我想导出在给定日期(2017 年 6 月 1 日)安装的用户的原始 BigQuery 日志数据，以及安装日期 (D0-D20) 和之后的前 3 周会话数据.

I am fairly new to StandardSQL, and wanted to export Raw BigQuery log-data for users that installed on a given date (1st June 2017) with first 3-week's session data on and after install-date (D0-D20).

    #StandardSQL
    SELECT user_dim.first_open_timestamp_micros, user_dim.app_info.app_instance_id, event_dim.date, event_dim.name, event_dim.timestamp_micros, event_dim.previous_timestamp_micros
    FROM `your_table_id.app_events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20170601' AND '20170621' AND
    (SELECT user_dim.first_open_timestamp_micros
    FROM UNNEST(user_dim) AS user
    WHERE user.first_open_timestamp_micros BETWEEN 1496275200000 AND 1496361600000);

我只得到以下输出:UNNEST 中引用的值必须是数组.UNNEST 包含 STRUCT 类型的表达式". 我最初的问题是:我如何将其转换为数组，以给我合适的输出?

I am only getting the following output: "Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT". My initial question was: How would I convert this into an array, to give me the appropriate output ?

在米哈伊尔回答后更新:

执行了以下查询，但没有给出任何数据作为输出.我知道应该有用户应该出现，因为如果我预览实际表(查看屏幕截图)，我会看到表中存在的值应该是输出的一部分(但不是).

The following query executed, but did not give any data as output. I know there should be users that should show up, because if I preview the actual table (view screenshot), I see values that exist in the table that should be part of the output (but is not).

#standardSQL
SELECT 
  user_dim.first_open_timestamp_micros, 
  user_dim.app_info.app_instance_id, 
  event.date, event.name, 
  event.timestamp_micros, 
  event.previous_timestamp_micros
FROM `your_table_id.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20170601' AND '20170621' 
AND user_dim.first_open_timestamp_micros BETWEEN 1496275200000 AND 1496361600000
LIMIT 10;

实际表格的屏幕截图:

转换时间戳，表明上面的会话确实应该出现在 2 个 user_dim.first_open_timestamp_micros-results 的中间，但没有...

Converting the timestamps, shows that the above session should indeed show up in the middle of the 2 user_dim.first_open_timestamp_micros-results, but does not...

1496275200000 转换:假设这个时间戳以毫秒为单位:格林威治标准时间:2017 年 6 月 1 日星期四 12:00:00您所在的时区:2017 年 6 月 1 日星期四凌晨 2:00:00 GMT+02:00 DST

1496275200000 Converted: Assuming that this timestamp is in milliseconds: GMT: Thursday, June 1, 2017 12:00:00 AM Your time zone: Thursday, June 1, 2017 2:00:00 AM GMT+02:00 DST

1496353126947000 转换:假设此时间戳以微秒为单位(1/1,000,000 秒):格林威治标准时间:2017 年 6 月 1 日星期四晚上 9:38:46.947您所在的时区:2017 年 6 月 1 日星期四晚上 11:38:46.947 GMT+02:00 DST

1496353126947000 Converted: Assuming that this timestamp is in microseconds (1/1,000,000 second): GMT: Thursday, June 1, 2017 9:38:46.947 PM Your time zone: Thursday, June 1, 2017 11:38:46.947 PM GMT+02:00 DST

1496361600000 转换:假设这个时间戳以毫秒为单位:格林威治标准时间:2017 年 6 月 2 日星期五上午 12:00:00您所在的时区:2017 年 6 月 2 日星期五凌晨 2:00:00 GMT+02:00 DST

1496361600000 Converted: Assuming that this timestamp is in milliseconds: GMT: Friday, June 2, 2017 12:00:00 AM Your time zone: Friday, June 2, 2017 2:00:00 AM GMT+02:00 DST

问题:

• 我是否遗漏了什么，为什么该值不会显示在输出中?

推荐答案

试试下面的
我假设 - user_dim 是 record，而 event_dim 是 record，repeat

Try below
I assumed - user_dim is record, and event_dim is record, repeated

#standardSQL
SELECT 
  user_dim.first_open_timestamp_micros, 
  user_dim.app_info.app_instance_id, 
  event.date, event.name, 
  event.timestamp_micros, 
  event.previous_timestamp_micros
FROM `your_table_id.app_events_*`, UNNEST(event_dim) AS event
WHERE _TABLE_SUFFIX BETWEEN '20170601' AND '20170621' 
AND user_dim.first_open_timestamp_micros BETWEEN 1496275200000 AND 1496361600000

这篇关于第一天安装者的原始 BigQuery 日志数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！