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