BigQuery StandardSQL:使用_TABLE_SUFFIX的最近7天 [英] BigQuery StandardSQL: Last 7 Days using _TABLE_SUFFIX
问题描述
问题:我想使用_TABLE_SUFFIX从多个Google Analytics会话表中提取数据,但我希望将后缀参数设置为七天前和一天前之间(即拉数据为过去7天)
当前语法(不起作用):
pre > #StandardSQL
SELECT
日期,
SUM(totals.visits)AS访问
FROM
`projectname.123456789.ga_sessions_ *`
WHERE
_TABLE_SUFFIX BETWEEN
'DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -7 DAY)'和
'DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 DAY)'
GROUP BY
日期
ORDER BY
日期ASC
消息有效:此查询在运行时将处理0 B。在我看来,语法没有错误,但BigQuery无法读取我的日期函数,因此无法将它们后缀到ga_sessions_ *通配符。
灵感: BigQuery Cookbook有一个legacySQL的例子,我一直以此为基础:( https://support.google.com/analytics/answer/4419694?hl=zh_CN#7天)
#LegacySQL
SELECT
日期,
SUM(totals.visits)AS访问
FROM
(TABLE_DATE_RANGE([73156703.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(),-7,'DAY'),
DATE_ADD(CURRENT_TIMESTAMP(),-1,'D'')) )
GROUP BY
日期
ORDER BY
日期ASC
使用DATE_SUB代替DATE_ADD并使用CURRENT_DATE而不是CURRENT_TIMESTAMP:
WHERE
_TABLE_SUFFIX BETWEEN
'DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY)'和
'DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)'
导致有效: 在DATE_SUB和CURRENT_DATE附近使用DATE_FORMAT以获取没有破折号的日期: 导致有效:这个查询将在运行时处理0 B。 尝试跳过连字符''在DATE_SUB子句附近 导致出现以下错误消息错误::运算符BETWEEN对于参数类型STRING,DATE,DATE没有匹配的签名。支持的签名:(ANY)BETWEEN(ANY)AND(ANY) 预先感谢您, 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): 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 Inspiration: BigQuery Cookbook has an example for legacySQL that I have been basing this on: (https://support.google.com/analytics/answer/4419694?hl=en#7days) Things I've tried: (that doesn't work) Using DATE_SUB instead of DATE_ADD and using CURRENT_DATE instead of CURRENT_TIMESTAMP: Resulting in "Valid: This query will process 0 B when run." Using DATE_FORMAT around DATE_SUB and CURRENT_DATE in order to get the dates without dashes: Resulting in "Valid: This query will process 0 B when run." Tried skippingt he hyphens '' around the DATE_SUB clause 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)" Thanks in advance, 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
这篇关于BigQuery StandardSQL:使用_TABLE_SUFFIX的最近7天的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
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))'
WHERE
_TABLE_SUFFIX BETWEEN
DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY)AND
DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
_TABLESUFFIX
转换为 DATE
,您应该将 CURRENT_DATE
表达式转换为字符串:
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))
#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
#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
WHERE
_TABLE_SUFFIX BETWEEN
'DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)' AND
'DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)'
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))'
WHERE
_TABLE_SUFFIX BETWEEN
DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
_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))