大查询查询耗时 >15 分钟

Question

我还没有遇到过在BigQuery中花费超过一分钟左右的查询，除了这个。有人能解释一下为什么这个问题要花这么长时间吗?

如果有谷歌工程师正在查看此任务，则任务ID为job_qHSgmV1CKbJnQeEZpqw1cb1g6f4。

SELECT
  DATE(s.signdatetime) fts_date,
  s.passportid,
  s.lifetime_tracking_source,
  SUM(if (MONTH(o.optindatetime)=MONTH(s.signdatetime),o.cpl,NULL)) rev_first_month
FROM
  tableau.signatures s
LEFT JOIN EACH tableau.optins o
ON
  s.passportid=o.passportid
WHERE
  DATE(s.signdatetime) BETWEEN '2015-10-12'
  AND '2015-10-13'
  AND s.disposition="accepted"
  AND s.FTS="Y"
  AND o.finalstatus="collected"
  AND DATE(o.optindatetime) BETWEEN '2015-10-12'
  AND '2015-10-13'
GROUP BY
  1,
  2,
  3 

Answer 1

表有多大?我建议在执行连接之前过滤数据:

SELECT
  DATE(s.signdatetime) fts_date,
  s.passportid,
  s.lifetime_tracking_source,
  SUM(if (MONTH(o.optindatetime)=MONTH(s.signdatetime),o.cpl,NULL)) rev_first_month
FROM (
  SELECT signdatetime, passportid, lifetime_tracking_source,
  FROM tableau.signatures 
  WHERE DATE(signdatetime) BETWEEN '2015-10-12' AND '2015-10-13'
  AND disposition="accepted"
  AND FTS="Y"
) s
LEFT JOIN EACH (
  SELECT cpl, optindatetime, passportid
  FROM tableau.optins 
  WHERE finalstatus="collected"
  AND DATE(optindatetime) BETWEEN '2015-10-12' AND '2015-10-13'
) o
ON
  s.passportid=o.passportid
GROUP BY
  1,
  2,
  3