优化动态7天队列Firebase BigQuery
优化动态7天队列Firebase BigQuery
提问于 2018-08-24 00:09:49
我根据我们的移动应用程序的数据编写了下面的查询。由于用户基数较高，当我在底部添加ORDER BY时，我得到了一个400Request error "Resources exceeded during query execution: The query could not be executed in the allotted memory"。
问:我可以做些什么来优化查询，同时仍然将ORDER BY保留在底部？
我已经添加了firebase的演示数据集，但我认为他们的数据集太小了，不会有问题(与我的数据集相比，我的数据集有500-1000万条记录)。
SELECT
f.user_pseudo_id,
f.event_timestamp,
DATE(TIMESTAMP_MICROS(f.event_timestamp)) as event_timestamp_date,
f.event_name,
f.user_first_touch_timestamp,
DATE(TIMESTAMP_MICROS(f.user_first_touch_timestamp)) as user_first_touch_date,
CASE WHEN r.has_appRemove >= 1 THEN "removed" ELSE "not-removed" END AS status_after_first7days
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*` f
LEFT JOIN (
SELECT user_pseudo_id, 1 has_appRemove
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`
WHERE DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) < DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)
AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND _TABLE_SUFFIX < FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
AND platform = "ANDROID"
AND event_name = "app_remove"
GROUP BY user_pseudo_id
) r on f.user_pseudo_id = r.user_pseudo_id
WHERE
DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) < DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)
AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND _TABLE_SUFFIX < FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
AND platform = "ANDROID"
ORDER BY 1,2 ASC回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-24 04:42:47
您可以应用窗口/分析函数，而不是下面示例中的连接(未测试)
#standardSQL
SELECT
user_pseudo_id,
event_timestamp,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_timestamp_date,
event_name,
user_first_touch_timestamp,
DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) AS user_first_touch_date,
COUNTIF(event_name = "app_remove") OVER(PARTITION BY user_pseudo_id) > 0 isRemoved
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`
WHERE
DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) < DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)
AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND _TABLE_SUFFIX < FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
AND platform = "ANDROID"
ORDER BY 1,2 ASC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51990070
复制相关文章
相似问题
腾讯云开发者
