Skip to content

Instantly share code, notes, and snippets.

@zjuul
Last active April 20, 2020 07:27
Show Gist options
  • Save zjuul/e56cd50b759315c654031eeeaac5cfb6 to your computer and use it in GitHub Desktop.
Save zjuul/e56cd50b759315c654031eeeaac5cfb6 to your computer and use it in GitHub Desktop.
Transform your long data to wide data - to break down the request in phases
create or replace table `PROJECTNAME.DATASETNAME.performance_long` partition by DATE(timestamp) as
with base as (
select
timestamp, date, event_timestamp, clientid, user_id, device_category, country, url, hostname,
page_path, page_title, page_referrer, type
from `PROJECTNAME.DATASETNAME.performance_wide`
)
,intervals as (
select event_timestamp, clientid,
'Prompt for unload' as phase, unloadeventend - unloadeventstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'Redirect' as phase, redirectend - redirectstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'AppCache' as phase, domainlookupstart - fetchstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'DNS' as phase, connectstart - domainlookupstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'TCP' as phase, requeststart - connectstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'Request' as phase, responsestart - requeststart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'Response' as phase, responseEnd - responsestart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'To domInteractive' as phase, dominteractive - responseEnd as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'domInteractive to Complete' as phase, domcomplete - dominteractive as value
from `PROJECTNAME.DATASETNAME.performance_wide`
union all
select event_timestamp, clientid,
'Load' as phase, loadeventend - loadeventstart as value
from `PROJECTNAME.DATASETNAME.performance_wide`
)
select * from base right join intervals using(event_timestamp, clientid)
@zjuul
Copy link
Author

zjuul commented Apr 20, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment