您能在此查询上帮助我:我不明白为什么timestamp_millis不起作用
SELECT DATE,
MAX(CASE WHEN customDimensions.index = 1 THEN customDimensions.value END) AS CUSTOMDIMENSIONS_VALUE,
visitNumber,
fullvisitorid,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2, '') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3, '') AS HITS_PAGE_PAGEPATHLEVEL3,
hits.page.pageTitle,
TIMESTAMP_SECONDS(visitStartTime) AS visitStart,
TIMESTAMP_MILLIS(1000 * visitStartTime + hits.time) AS hitStart,
hits.hitNumber,
hits.hour,
hits.minute,
hits.isEntrance,
hits.isExit,
CONCAT(fullvisitorid, STRING(visitid)) AS sessionid
FROM (FLATTEN([Table], customDimensions.value ))
WHERE hits.page.hostname CONTAINS 'X'' AND customDimensions.value != "null" AND hits.type = 'PAGE' AND (customDimensions.index = 1 OR hits.customDimensions.index = 11)
GROUP EACH BY DATE, visitStartTime, fullVisitorId, visitNumber, device.deviceCategory, hits.page.pagePath, hits.page.pageTitle, hits.hitNumber, hits.time, hits.hour, hits.minute, hits.isEntrance, hits.isExit
LIMIT 100000
我不明白为什么timestamp_millis不起作用。你能帮我
吗?
查询的问题是您在同一查询中使用了BigQuery Legacy SQL
和Standard SQL
的混合物。
例如:
查询的一部分下方 - 是传统SQL功能
FROM (FLATTEN([Table], customDimensions.value ))
TIMESTAMP_MILLIS
和TIMESTAMP_SECONDS
仅由标准SQL支持 - 但是您可以在Legacy SQL中找到类似的功能(例如MSEC_TO_TIMESTAMP
和SEC_TO_TIMESTAMP()
)
您需要坚持一个或另一个。BigQuery Team推荐使用Standard SQL
,我绝对第二!