Skip to content

Instantly share code, notes, and snippets.

@zjuul

zjuul/wide_to_long.sql

Last active Apr 20, 2020
Embed
What would you like to do?
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

This comment has been minimized.

Copy link
Owner Author

@zjuul zjuul commented Apr 20, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.