Page MenuHomePhabricator
Paste P9757

T236834_merge_new_old_logs_1.hql
ActivePublic

Authored by AndyRussG on Wed, Nov 27, 5:40 AM.
SELECT
new.dt,
new.ip,
new.cache_status,
new.http_status,
new.http_method,
new.uri_host,
new.uri_path,
new.uri_query,
new.content_type,
new.referer,
new.x_forwarded_for,
new.user_agent,
new.accept_language,
new.x_analytics,
new.range,
new.is_pageview,
new.record_version,
new.client_ip,
new.geocoded_data,
new.x_cache,
new.user_agent_map,
new.x_analytics_map,
new.ts,
new.access_method,
new.agent_type,
new.is_zero,
new.referer_class,
new.normalized_host,
new.pageview_info,
new.page_id,
new.namespace_id,
new.tags,
new.isp_data,
new.random_campaign AS new_random_campaign,
old.random_campaign AS old_random_campaign
FROM
( SELECT
*,
regexp_extract( uri_query, 'randomcampaign%22%3A([0-9.]*)', 1 ) AS random_campaign
FROM
wmf.webrequest
WHERE
year = 2019
AND month = 11
AND day = 20
AND uri_query LIKE "%CentralNoticeImpression%"
AND uri_path = '/beacon/event'
AND dt >= '2019-11-20T01:00:00Z'
AND dt <= '2019-11-20T22:59:59Z'
AND (
uri_query LIKE '%C19_WMDE_Mobile_Test_02%'
OR uri_query LIKE '%C19_WMDE_Test_11%'
) ) new
LEFT JOIN
( SELECT
*,
parse_url( concat( 'http://bla.org/woo/', uri_query ), 'QUERY', 'randomcampaign' ) AS random_campaign
FROM
wmf.webrequest
WHERE
year = 2019
AND month = 11
AND day = 20
AND uri_path = '/beacon/impression'
AND dt >= '2019-11-20T01:00:00Z'
AND dt <= '2019-11-20T22:59:59Z'
AND (
uri_query LIKE '%C19_WMDE_Mobile_Test_02%'
OR uri_query LIKE '%C19_WMDE_Test_11%'
) ) old
ON
( new.random_campaign = old.random_campaign );