我想获取 FullvisitorId 的列表。但是我不知道如何在我的请求中选择完整访客ID列表..目标是检索特别是 FullvisitorId 的列表,而不是所有会话
标准数据库
SELECT
date,
visitStartTime,
TIMESTAMP_SECONDS(visitStartTime) as starttime_UTC,
DATETIME(TIMESTAMP_SECONDS(visitStartTime), "Europe/Amsterdam") as starttime_LOCAL,
totals.timeOnSite,
trafficSource.source,
trafficSource.referralPath,
hits.hour,
hits.minute,
hits.hitNumber,
fullVisitorId,
hits.isEntrance,
hits.isExit,
hits.referer,
hits.eventInfo.eventAction,
hits.latencyTracking.userTimingValue,
hits.type,
CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string)) AS case_id,
CONCAT(CAST( EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d',date) ) AS string) ,"/",
CAST( EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d',date))AS string) , "/" ,
CAST( EXTRACT(DAY FROM PARSE_DATE('%Y%m%d',date) ) AS string), " " ,
CAST(hits.hour AS string), ":" ,
CAST(hits.minute AS string), ":" ,
CAST(hits.hitNumber AS string)) AS dated,
(SELECT MAX( IF(index = 4,value,NULL) ) FROM UNNEST(hits.customDimensions)) type_page,
(SELECT MAX( IF(index = 6,value,NULL) ) FROM UNNEST(hits.customDimensions)) Univers
FROM `ga-orange-pro-etp.*****.ga_sessions_20190616*` , UNNEST(hits) as hits
WHERE (SELECT MAX( IF(index = 10,value,NULL) ) FROM UNNEST(hits.customDimensions)) = 'prod'
您可以通过运行以下命令简单地获取不同fullVisitorId
的列表:
SELECT
DISTINCT fullVisitorId
FROM `ga-orange-pro-etp.*****.ga_sessions_20190616*` , UNNEST(hits) as hits
WHERE (SELECT MAX( IF(index = 10,value,NULL) ) FROM UNNEST(hits.customDimensions)) = 'prod'
如果删除DISTINCT
它将返回每行的值,因此您将看到重复的fullVisitorId
。