BigQuery: ошибка стандартного sql для запроса таблицы?

Не могли бы вы мне помочь с преобразованием этого запроса в стандартный sql для BigQuery? Я получил эту ошибку. Невозможно получить доступ к странице поля для значения с типом ARRAY> по адресу hits.page.hostname LIKE 'website.fr'. Скажите, пожалуйста, разобраться с этой проблемой, чтобы исправить ее.

#standardSQL
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,
MAX(CASE WHEN hits.customDimensions.index = 27 THEN hits.customDimensions.value END) AS pathology,
hits.hitNumber
FROM  `85801771.ga_sessions*`
CROSS JOIN UNNEST(customDimensions) AS customDimension
CROSS JOIN UNNEST(hits) AS hit
WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026'
AND hits.page.hostname LIKE 'website.fr'
AND customDimensions.value != "null"
  AND hits.page.pagePath LIKE 'poiu'
  AND hits.type = 'PAGE'
  AND (customDimensions.index = 1
  OR hits.customDimensions.index = 11
  OR hits.customDimensions.index = 14
  OR hits.customDimensions.index = 27 )
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
ORDER BY DATE,
visitStartTime,
device.deviceCategory,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber DESC
LIMIT 100000

google-bigquery bigquery-standard-sql
person sPujade    schedule 22.03.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

В вашем запросе вы даете псевдоним hit элементам, возвращаемым UNNEST(hits), и псевдоним customDimension элементам, возвращаемым UNNEST(customDimensions). Если вы хотите сослаться на конкретное обращение, а не на массив, используйте вместо этого hit, а если вы хотите сослаться на отдельное настраиваемое измерение, используйте вместо этого customDimension. Например, ваш фильтр может выглядеть примерно так:

WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026' AND
  hit.page.hostname LIKE '%website.fr%' AND
  customDimension.value != "null" AND
  hit.page.pagePath LIKE '%poiu%' AND
  hit.type = 'PAGE' AND
  customDimension.index IN (1, 11, 14, 27)
person Elliott Brossard    schedule 22.03.2017
comment
Эллиот. Я попытался изменить, но столкнулся со следующей проблемой: «Имя столбца customDimensions неоднозначно» Я хочу отфильтровать конкретное обращение - для моего имени хоста), а затем - по массиву для моего настраиваемого измерения - person sPujade; 22.03.2017
comment
Я попытался изменить запрос: WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026' AND hit.page.hostname LIKE '% website.fr%' AND hits.customDimension.value IS NOT NULL AND hit.page.pagePath LIKE '% poiu % 'AND hit.type =' PAGE 'AND customDimension.index IN (1,11,14,27), но я получил следующую ошибку: «Невозможно получить доступ к полю customDimension для значения с типом ARRAY» К сожалению :-( - person sPujade; 22.03.2017
comment
На самом деле вы не последовали моему предложению использовать customDimension вместо customDimensions. - person Elliott Brossard; 22.03.2017
comment
прости, Эллиотт, я изменю его и снова протестирую - person sPujade; 22.03.2017
comment
Снова ошибка @ Elliott: UNNEST нельзя применить к таблице: customDimension - person sPujade; 22.03.2017
comment
Разве customDimensions не поле внутри hits? Вероятно, вам понадобится: CROSS JOIN UNNEST(hits) AS hit CROSS JOIN UNNEST(hit.customDimensions) AS customDimension - person Elliott Brossard; 22.03.2017

arrow_upward
1
arrow_downward

Есть пара ошибок, которые я нашел в запросе. Я внес исправления, надеюсь, это поможет вам.

SELECT DATE,MAX(CASE
  WHEN customDimensions.index = 1 THEN customDimensions.value END) AS CUSTOMDIMENSIONS_VALUE,  visitNumber,  fullvisitorid,  visitStartTime, TIMESTAMP_SECONDS(visitStartTime) AS humain, EXTRACT(DATE FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Paris_timezone')  AS Paris_timezone, 
  hits.hour,  hits.minute,  CONCAT(CAST(fullVisitorId AS string),CAST(visitId AS string)) AS sessionid,  MAX(CASE
  WHEN customDimensions.index = 11 THEN 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 customDimensions.index = 14 THEN customDimensions.value END) AS assetpurpose,
MAX(CASE WHEN customDimensions.index = 27 THEN customDimensions.value END) AS pathology,
hits.hitNumber
FROM `85801771.ga_sessions*`
CROSS JOIN UNNEST(customDimensions) AS customDimensions
CROSS JOIN UNNEST(hits) AS hits
WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026'
AND hits.page.hostname LIKE 'website.fr'
AND customDimensions.value != "null"
  AND hits.page.pagePath LIKE 'poiu'
  AND hits.type = 'PAGE'
  AND (customDimensions.index = 1
  OR customDimensions.index = 11
  OR customDimensions.index = 14
  OR customDimensions.index = 27 )
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
ORDER BY DATE,
visitStartTime,
device.deviceCategory,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber DESC
LIMIT 100000
person HKE    schedule 11.07.2017
comment
Большое спасибо Харришу и извиняюсь за мой поздний поздний ответ - person sPujade; 14.09.2017