maximum hitNumber per visitID - google bigquery

Asked

Viewed 30 times

0

I need to collect only the last hitnumber per visitID in a query from a Google database Analytics via google big query. Below follows my current code. I have reached the point of ordering the query where the maximum hitnumber appears first by visitId.

SELECT 
    visitNumber,
    visitId,
    date, 
    hitNumber,
    device.operatingSystem,
    device.operatingSystemVersion,
    device.mobileDeviceBranding,
    device.screenResolution,
    appInfo.exitScreenName,
    appInfo.screenName,
    eventInfo.eventCategory,
    eventInfo.eventAction,
    eventInfo.eventLabel,
  FROM `nomeConta.idConta.ga_sessions_yyyymmdd`  
  CROSS JOIN UNNEST (hits)
  WHERE appInfo.exitScreenName = "nomeTelaErro"
    and appInfo.screenName like "%identificacaoTela%"
    and date >= 'yyyymmdd'
  order by  visitID, hitNumber DESC

1 answer

0

You can use a Function window to find out the biggest user hitNumber and then filter only the hit that is equal to this hitNumber. It would be something like that:

with user_hits as (
    SELECT 
        visitNumber,
        visitId,
        date, 
        hitNumber,
        device.operatingSystem,
        device.operatingSystemVersion,
        device.mobileDeviceBranding,
        device.screenResolution,
        appInfo.exitScreenName,
        appInfo.screenName,
        eventInfo.eventCategory,
        eventInfo.eventAction,
        eventInfo.eventLabel,
        -- descobre o maior hitNumber por visitId, sem agregar a tabela
        MAX(hitNumber) OVER (PARTITION BY visitId) as maxHitNumber
    FROM `nomeConta.idConta.ga_sessions_yyyymmdd`  
    CROSS JOIN UNNEST (hits)
    WHERE appInfo.exitScreenName = "nomeTelaErro"
    and appInfo.screenName like "%identificacaoTela%"
    and date >= 'yyyymmdd'
    order by  visitID, hitNumber DESC
)
SELECT 
    *
FROM user_hits
WHERE hitNumber = maxHitNumber

You can see more about window functions in the Bigquery documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=pt-br

Browser other questions tagged

You are not signed in. Login or sign up in order to post.