mysql - 请求检索 FullvisitorId 列表 - BigQuery

Question

我想获取 FullvisitorId 列表。但我不知道如何在我的请求中选择 FullvisitorId 列表..目标是特别检索 FullvisitorId 列表，而不是所有 session

标准SQL

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'

Answer 1

您可以通过运行简单地获取不同的 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。