BigQuery StandardSQL:过去 7 天使用 _TABLE_SUFFIX [英] BigQuery StandardSQL: Last 7 Days using _TABLE_SUFFIX

问题描述

问题:我想使用 _TABLE_SUFFIX 从多个 Google Analytics 会话表中提取数据，但我想将后缀参数设置为七天前"和一天前"(即拉过去 7 天的数据)

Question: I want to pull data from multiple Google Analytics sessions tables using _TABLE_SUFFIX, but I want to set the suffix parameters to between "seven days ago" and "one day ago" (i.e. pulling data for the last 7 days)

当前的语法(不起作用):

The current syntax (that doesn't work):

#StandardSQL
SELECT
  date,
  SUM (totals.visits) AS visits
FROM
  `projectname.123456789.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  'DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)' AND 
  'DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)'
GROUP BY 
   date
ORDER BY 
   date ASC

这给了我消息有效:此查询将在运行时处理 0 B."在我看来，语法没有错误，但 BigQuery 无法读取我的日期函数，因此无法将它们添加到 ga_sessions_* 通配符后缀

Which gives me the message "Valid: This query will process 0 B when run." To my eyes, there is no error in the syntax, but BigQuery is unable ot read my date functions and thus unable to suffix them to the ga_sessions_* wildcard

灵感:

BigQuery Cookbook 有一个我一直基于的 legacySQL 示例:(https://support.google.com/analytics/answer/4419694?hl=zh-CN#7days)

BigQuery Cookbook has an example for legacySQL that I have been basing this on: (https://support.google.com/analytics/answer/4419694?hl=en#7days)

#LegacySQL
SELECT
  date,
  SUM (totals.visits) AS visits
FROM 
  (TABLE_DATE_RANGE([73156703.ga_sessions_], 
  DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), 
  DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
  date
ORDER BY
  date ASC 

我尝试过的事情:(不起作用)

使用 DATE_SUB 代替 DATE_ADD 并使用 CURRENT_DATE 代替 CURRENT_TIMESTAMP:

Using DATE_SUB instead of DATE_ADD and using CURRENT_DATE instead of CURRENT_TIMESTAMP:

WHERE
   _TABLE_SUFFIX BETWEEN 
   'DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)' AND 
   'DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)'

导致有效:此查询在运行时将处理 0 B."

Resulting in "Valid: This query will process 0 B when run."

在 DATE_SUB 和 CURRENT_DATE 周围使用 DATE_FORMAT 以获得不带破折号的日期:

Using DATE_FORMAT around DATE_SUB and CURRENT_DATE in order to get the dates without dashes:

WHERE
   _TABLE_SUFFIX BETWEEN 
   'FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))' AND 
   'FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))'

导致有效:此查询在运行时将处理 0 B."

Resulting in "Valid: This query will process 0 B when run."

尝试跳过 DATE_SUB 子句周围的连字符 ''

Tried skippingt he hyphens '' around the DATE_SUB clause

WHERE
  _TABLE_SUFFIX BETWEEN 
  DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

导致出现以下错误消息错误:对于参数类型的运算符 BETWEEN 没有匹配的签名:STRING、DATE、DATE.支持的签名:(ANY) BETWEEN (ANY) AND (ANY)"

Resulting in the following error message "Error: No matching signature for operator BETWEEN for argument types: STRING, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY)"

提前致谢，

推荐答案

Elliott 的回答是正确的，但如果您想从 BigQuery 中获得最大性能来处理此类查询，而不是转换 _TABLESUFFIX 到 DATE，您应该将 CURRENT_DATE 表达式转换为字符串:

Elliott's answer is correct, but if you want to get the most performance out of BigQuery for such kind of query, instead of converting _TABLESUFFIX to DATE, you should convert CURRENT_DATE expressions to strings:

WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

这篇关于BigQuery StandardSQL:过去 7 天使用 _TABLE_SUFFIX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！