第一天安装程序的原始BigQuery日志数据 [英] Raw BigQuery Log-data for 1st Day Installers
问题描述
pre $
#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'20170601'与'20170621'AND
(SELECT user_dim.first_open_timestamp_micros
FROM UNNEST(user_dim )AS用户
WHERE user.first_open_timestamp_micros BETWEEN 1496275200000 AND 1496361600000);
我只得到以下输出:UNNEST中引用的值必须是数组。UNNEST包含STRUCT类型的表达式。我最初的问题是:如何将其转换为数组,以给我适当的输出?
在Mikhail的回答后更新:执行了以下查询,但没有提供任何数据作为输出。我知道应该有应该显示的用户,因为如果我预览实际表格(查看屏幕截图),我会看到表中存在的值应该是输出的一部分(但不是)。
#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事件
WHERE _TABLE_SUFFIX BETWEEN'20170601'AND' 20170621'
和user_dim.first_open_timestamp_micros BETWEEN 1496275200000和1496361600000
LIMIT 10;
实际表格的屏幕截图:
转换时间戳,表明上面的会话确实应该显示在2 user_dim.first_open_timestamp_micros-results,但不... ...
1496275200000转换:
假设此时间戳以毫秒为单位:
GMT:2017年6月1日星期四12:00:00 AM
您的时区:2017年6月1日星期四2:00:00上午GMT + 02:00 DST
1496353126947000转换:
假设此时间戳为微秒(1 / 1,000,000秒):
GMT:2017年6月1日星期四9: 38:46.947 PM
您所在的时区:2017年6月1日星期四11:38:46.947 PM GMT + 02:DST
1496361600000转换:
假设这个时间estamp以毫秒为单位:
GMT:2017年6月2日星期五12:00:00 AM
您所在的时区:2017年6月2日星期五上午2:00:00 GMT + 02:00 DST
问题:
为什么该值不会显示在输出中?请尝试以下内容
我假设 - user_dim
是记录
,并且 event_dim
是记录,重复
$ b
#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事件
WHERE _TABLE_SUFFIX BETWEEN'20170601'和'20170621'
AND user_dim.first_open_timestamp_micros BETWEEN 1496275200000 AND 1496361600000
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);
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 ?
Update after Mikhail's answer:
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;
Screenshot of the actual table:
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 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 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 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
Question:
- Am I missing something, why would that value not show up in the output ?
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屋!