查询执行期间超出了Bigquery资源-优化

Question

我对这个查询有一个问题。

SELECT event_date, country, COUNT(*) AS sessions,
       AVG(length) AS average_session_length
  FROM (

SELECT country, event_date, global_session_id,
       (MAX(event_timestamp) - MIN(event_timestamp))/(60 * 1000 * 1000) AS length
  FROM (
SELECT user_pseudo_id,
       event_timestamp,
       country,
       event_date,
       SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,
       SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS user_session_id
  FROM (
       SELECT *,
              CASE WHEN event_timestamp - last_event >= (30*60*1000*1000) 
                     OR last_event IS NULL 
                   THEN 1 ELSE 0 END AS is_new_session
         FROM (
              SELECT user_pseudo_id,
                     event_timestamp,
                     geo.country,
                     event_date,
                     LAG(event_timestamp,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_event
                FROM `xxx.events*`
              ) last
       ) final
       ) session
 GROUP BY global_session_id, country, event_date

       ) agg
WHERE length >= (10/60)
group by country, event_date

Google Cloud Console给出了这个错误

Resources exceeded during query execution: The query could not be executed in the allotted memory.

我知道这可能是OVER子句的问题，但我不知道如何编辑查询以获得相同的结果。如果能帮上忙我会很感激的。谢谢你们！

Answer 1

如果非要我猜的话，应该是这句话：

  SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,

我建议修改代码，这样“全局”会话id对于每个用户来说都是本地的：

  SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS global_session_id,

如果您调整了查询，并且这基本上是有效的，那么资源问题就被修复了。下一步是弄清楚如何获得所需的全局id。最简单的解决方案是为每个用户使用一个本地id。