Это мой вопрос: пожалуйста, как преобразовать его в стандартный SQL? Большое вам спасибо за вашу помощь. Я не знаю, как это делать.
SELECT date,
max(case when customDimensions.index = 1 then customDimensions.value end) AS CUSTOMDIMENSIONS_VALUE,
visitNumber,
fullvisitorid,
visitStartTime,
SEC_TO_TIMESTAMP(visitStartTime) AS humain,
TIME (visitStartTime+3600 ) AS Paris_timezone,
hits.hour,
hits.minute,
CONCAT(fullvisitorid, STRING(visitid)) AS sessionid,
max(CASE WHEN hits.customDimensions.index = 11 THEN hits.customDimensions.value END) AS localproductname,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2, '') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3, '') AS HITS_PAGE_PAGEPATHLEVEL3,
MAX(CASE WHEN hits.customDimensions.index = 14 THEN hits.customDimensions.value END) AS assetpurpose,
hits.hitNumber,
FROM (FLATTEN([85801771.ga_sessions_20161025], customDimensions.value )),
(FLATTEN([85801771.ga_sessions_20161026], customDimensions.value )),
WHERE customDimensions.value != "null" AND customDimensions.value = "968a9587-0614-4155-9597-bf17aef42125" AND hits.type = 'PAGE' AND (customDimensions.index = 1 OR hits.customDimensions.index = 11 OR hits.customDimensions.index = 14
OR hits.customDimensions.index = 27 ) AND hits.page.hostname CONTAINS 'website.fr' AND hits.type = 'PAGE'
GROUP EACH BY DATE, visitStartTime, humain, Paris_timezone, hits.hour, hits.minute, fullVisitorId, sessionid, visitNumber, device.deviceCategory, hits.page.pagePath, HITS_PAGE_PAGEPATHLEVEL2, HITS_PAGE_PAGEPATHLEVEL3, hits.hitNumber,
LIMIT 100000
Я пытаюсь перевести его на стандартный sql, но получаю следующую ошибку:
Синтаксическая ошибка: неожиданный литерал с плавающей запятой «85801771.»
Может, в моем запросе есть и другие ошибки.
стандартный SQL:
SELECT date,
max(case when customDimensions.index = 1 then customDimensions.value end) AS CUSTOMDIMENSIONS_VALUE,
visitNumber,
fullvisitorid,
visitStartTime,
SEC_TO_TIMESTAMP(visitStartTime) AS humain,
TIME (visitStartTime+3600 ) AS Paris_timezone,
hits.hour,
hits.minute,
CONCAT(fullvisitorid, STRING(visitid)) AS sessionid,
max(CASE WHEN hits.customDimensions.index = 11 THEN hits.customDimensions.value END) AS localproductname,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2, '') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3, '') AS HITS_PAGE_PAGEPATHLEVEL3,
MAX(CASE WHEN hits.customDimensions.index = 14 THEN hits.customDimensions.value END) AS assetpurpose,
hits.hitNumber,
FROM (FLATTEN([85801771.ga_sessions_20161025], customDimensions.value )),
(FLATTEN([85801771.ga_sessions_20161026], customDimensions.value )),
WHERE customDimensions.value != "null" AND customDimensions.value = "968a9587-0614-4155-9597-bf17aef42125" AND hits.type = 'PAGE' AND (customDimensions.index = 1 OR hits.customDimensions.index = 11 OR hits.customDimensions.index = 14
OR hits.customDimensions.index = 27 ) AND hits.page.hostname CONTAINS 'website.fr' AND hits.type = 'PAGE'
GROUP BY DATE, visitStartTime, humain, Paris_timezone, hits.hour, hits.minute, fullVisitorId, sessionid, visitNumber, device.deviceCategory, hits.page.pagePath, HITS_PAGE_PAGEPATHLEVEL2, HITS_PAGE_PAGEPATHLEVEL3, hits.hitNumber,
LIMIT 100000