Created
August 7, 2017 08:01
-
-
Save lonehacker/90703ceb95bc502f38195661e934fa44 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE aggregations (name regclass primary key, last_update timestamp); | |
INSERT INTO aggregations VALUES ('hourly_ssp_aggregates', now() - interval '15 minute'); | |
CREATE OR REPLACE FUNCTION compute_hourly_ssp_aggregates() | |
RETURNS void LANGUAGE plpgsql AS $function$ | |
DECLARE | |
start_time timestamp; | |
end_time timestamp := now() - interval '15 minute'; -- exclude in-flight requests | |
BEGIN | |
SELECT last_update INTO start_time FROM aggregations WHERE name = 'hourly_ssp_aggregates'::regclass; | |
UPDATE aggregations SET last_update = end_time WHERE name = 'hourly_ssp_aggregates'::regclass; | |
SET LOCAL citus.all_modifications_commutative TO on; -- for on-premises, replication factor >1 only | |
EXECUTE $$ | |
INSERT INTO hourly_ssp_aggregates | |
SELECT | |
r.loghour, | |
r.date_utc, | |
r.date_hr, | |
r.site_id, | |
CASE WHEN s.name IS NULL THEN 'NoName' ELSE s.name END as name, | |
r.ad_slot, | |
r.ad_size, | |
r.impression_type, | |
r.adapter_type, | |
CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END as pub_id, | |
r.deal_id, | |
r.ad_platform, | |
cast(sum(r.requests) as bigint), | |
cast(sum(r.impressions) as bigint), | |
cast(sum(r.clicks) as bigint), | |
cast(sum(r.total_revenue) as decimal), | |
cast(sum(r.total_net_revenue) as decimal), | |
cast(sum(r.total_gross_revenue) as decimal), | |
cast(sum(r.total_auction_revenue) as decimal) | |
FROM | |
(SELECT | |
a.loghour as loghour, | |
b.loghour as loghour2, | |
CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END as date_utc, | |
CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END as date_hr, | |
CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END as site_id, | |
CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END as ad_slot, | |
CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END as ad_size, | |
CASE WHEN b.impression_type IS NULL THEN 'NA' ELSE b.impression_type END as impression_type, | |
CASE WHEN b.adapter_type IS NULL THEN 'NA' ELSE b.adapter_type END as adapter_type, | |
b.pub_group_id, | |
CASE WHEN b.deal_id IS NULL THEN 'NA' ELSE b.deal_id END as deal_id, | |
CASE WHEN b.ad_platform IS NULL THEN 'NA' ELSE b.ad_platform END as ad_platform, | |
SUM(count) as requests, | |
SUM(b.impressions) as impressions, | |
SUM(b.clicks) as clicks, | |
SUM(b.total_revenue) as total_revenue, | |
SUM(b.total_net_revenue) as total_net_revenue, | |
SUM(b.total_gross_revenue) as total_gross_revenue, | |
SUM(b.total_auction_revenue) as total_auction_revenue | |
FROM | |
( | |
select | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text) as date_utc, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text) as date_hr, | |
logtext->>'SiteId' as site_id, | |
logtext->>'AdSlot' as ad_slot, | |
logtext->>'AdSize' as ad_size, | |
logtext->>'groupPubId' as pub_group_id, | |
logtext->>'dealId' as deal_id, | |
logtext->>'adPlatformType' as ad_platform, | |
sum(cast(logtext->>'count' as int)) as count | |
from | |
s2s_request | |
where | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) | |
AND | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text) | |
group by | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text), | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text), | |
logtext->>'SiteId', | |
logtext->>'AdSlot', | |
logtext->>'AdSize', | |
logtext->>'groupPubId', | |
logtext->>'dealId', | |
logtext->>'adPlatformType' | |
)a | |
FULL OUTER JOIN | |
( | |
select | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text) as date_utc, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text) as date_hr, | |
logtext->>'sid' as site_id, | |
logtext->>'adSlot' as ad_slot, | |
logtext->>'adSize' as ad_size, | |
logtext->>'it' as impression_type, | |
logtext->>'adapterType' as adapter_type, | |
logtext->>'pgId' as pub_group_id, | |
logtext->>'dealId' as deal_id, | |
logtext->>'adPlatform' as ad_platform, | |
sum(case when logtext->>'et'='IMPRESSION' then 1 else 0 end) as impressions, | |
sum(case when logtext->>'et'='CLICK' then 1 else 0 end) as clicks, | |
sum(cast(logtext->>'gp' as decimal)/1000) as total_revenue, | |
sum(cast(logtext->>'np' as decimal)/1000) as total_net_revenue, | |
sum(cast(logtext->>'gp' as decimal)/1000) as total_gross_revenue, | |
sum(cast(logtext->>'ap' as decimal)/1000) as total_auction_revenue | |
from | |
tracker | |
where | |
logtext->>'adapterType' = 'OPENRTB' AND | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text) | |
group by | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text), | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text), | |
logtext->>'sid',logtext->>'adSlot',logtext->>'adSize',logtext->>'it',logtext->>'adapterType',logtext->>'pgId', | |
logtext->>'dealId',logtext->>'adPlatform' | |
) b | |
ON | |
a.date_utc=b.date_utc AND | |
a.date_hr=b.date_hr AND | |
a.pub_group_id=b.pub_group_id AND | |
a.ad_size=b.ad_size AND | |
a.loghour=b.loghour | |
GROUP BY | |
a.loghour, | |
b.loghour, | |
CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END, | |
CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END, | |
CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END, | |
CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END, | |
CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END, | |
b.impression_type, | |
b.adapter_type, | |
b.pub_group_id, | |
b.deal_id, | |
b.ad_platform | |
)r | |
LEFT OUTER JOIN | |
(SELECT | |
cast(id as text) as site_id, | |
account_id as pub_id, | |
name as name | |
FROM | |
site_l | |
)s | |
ON | |
r.site_id = s.site_id | |
where | |
r.site_id is not NULL | |
group by | |
r.loghour, | |
r.date_utc, | |
r.date_hr, | |
r.site_id, | |
CASE WHEN s.name IS NULL THEN 'NoName' ELSE s.name END, | |
r.ad_slot, | |
r.ad_size, | |
r.impression_type, | |
r.adapter_type, | |
CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END, | |
r.deal_id, | |
r.ad_platform | |
ON CONFLICT (loghour,date_utc, date_hr,siteid,name,adslot,adsize,impressiontype,adaptertype,grouppubid,dealid,adplatform) | |
DO UPDATE | |
SET bidssent =+ excluded.bidssent, | |
adslotrequestsent =+ excluded.adslotrequestsent, | |
bidresponses =+ excluded.bidresponses, | |
bidwins =+ excluded.bidwins, | |
numimpressions =+ excluded.numimpressions, | |
numclicks =+ excluded.numclicks, | |
totalrevenue =+ excluded.totalrevenue, | |
totalnetrevenue =+ excluded.totalnetrevenue, | |
totalgrossrevenue =+ excluded.totalgrossrevenue, | |
totalauctionrevenue =+ excluded.totalauctionrevenue$$ | |
USING start_time, end_time; | |
EXECUTE $$ | |
INSERT INTO hourly_ssp_aggregates | |
SELECT | |
r.loghour, | |
r.date_utc, | |
r.date_hr, | |
r.site_id, | |
s.name, | |
r.ad_slot, | |
r.ad_size, | |
r.impression_type, | |
r.adapter_type, | |
CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END as pub_id, | |
r.deal_id, | |
r.ad_platform, | |
cast(r.requests as bigint), | |
cast(r.impressions as bigint), | |
cast(r.clicks as bigint), | |
cast(r.total_revenue as decimal), | |
cast(r.total_net_revenue as decimal), | |
cast(r.total_gross_revenue as decimal), | |
cast(r.total_auction_revenue as decimal) | |
FROM | |
(SELECT | |
a.loghour as loghour, | |
b.loghour as loghour2, | |
CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END as date_utc, | |
CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END as date_hr, | |
CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END as site_id, | |
CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END as ad_slot, | |
CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END as ad_size, | |
CASE WHEN b.impression_type IS NULL THEN 'NA' ELSE b.impression_type END as impression_type, | |
CASE WHEN b.adapter_type IS NULL THEN 'NA' ELSE b.adapter_type END as adapter_type, | |
b.pub_group_id, | |
CASE WHEN b.deal_id IS NULL THEN 'NA' ELSE b.deal_id END as deal_id, | |
CASE WHEN b.ad_platform IS NULL THEN 'NA' ELSE b.ad_platform END as ad_platform, | |
SUM(count) as requests, | |
SUM(b.impressions) as impressions, | |
SUM(b.clicks) as clicks, | |
SUM(b.total_revenue) as total_revenue, | |
SUM(b.total_net_revenue) as total_net_revenue, | |
SUM(b.total_gross_revenue) as total_gross_revenue, | |
SUM(b.total_auction_revenue) as total_auction_revenue | |
FROM | |
( | |
select | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text) as date_utc, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text) as date_hr, | |
logtext->>'siteId' as site_id, | |
logtext->>'adName' as ad_slot, | |
logtext->>'size' as ad_size, | |
sum(cast(logtext->>'count' as int)) as count | |
from | |
prebid | |
where | |
loghour = cast(to_char(NOW(),'YYYY_MM_DD') as text) AND | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text) | |
group by | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text), | |
cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text), | |
logtext->>'siteId',logtext->>'adName', | |
logtext->>'size' | |
)a | |
FULL OUTER JOIN | |
( | |
select | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text) as date_utc, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text) as date_hr, | |
logtext->>'sid' as site_id, | |
logtext->>'adSlot' as ad_slot, | |
logtext->>'adSize' as ad_size, | |
logtext->>'it' as impression_type, | |
logtext->>'adapterType' as adapter_type, | |
logtext->>'pgId' as pub_group_id, | |
logtext->>'dealId' as deal_id, | |
logtext->>'adPlatform' as ad_platform, | |
sum(case when logtext->>'et'='IMPRESSION' then 1 else 0 end) as impressions, | |
sum(case when logtext->>'et'='CLICK' then 1 else 0 end) as clicks, | |
sum(cast(logtext->>'gp' as decimal)/1000) as total_revenue, | |
sum(cast(logtext->>'np' as decimal)/1000) as total_net_revenue, | |
sum(cast(logtext->>'gp' as decimal)/1000) as total_gross_revenue, | |
sum(cast(logtext->>'ap' as decimal)/1000) as total_auction_revenue | |
from | |
tracker | |
where | |
loghour = cast(to_char(NOW(),'YYYY_MM_DD') as text) AND | |
logtext->>'adapterType' = 'HTTP' AND | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text) | |
group by | |
loghour, | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text), | |
cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text), | |
logtext->>'sid',logtext->>'adSlot',logtext->>'adSize',logtext->>'it',logtext->>'adapterType',logtext->>'pgId', | |
logtext->>'dealId',logtext->>'adPlatform' | |
) b | |
ON | |
a.date_utc=b.date_utc AND | |
a.date_hr=b.date_hr AND | |
a.site_id=b.site_id AND | |
a.ad_size=b.ad_size AND | |
a.loghour=b.loghour | |
GROUP BY | |
a.loghour, | |
b.loghour, | |
CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END, | |
CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END, | |
CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END, | |
CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END, | |
CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END, | |
b.impression_type, | |
b.adapter_type, | |
b.pub_group_id, | |
b.deal_id, | |
b.ad_platform) r | |
LEFT OUTER JOIN | |
(SELECT | |
id as site_id, | |
account_id as pub_id, | |
name as name | |
FROM | |
site_l | |
) s | |
ON | |
CAST(r.site_id as int) = s.site_id | |
ON CONFLICT (loghour,date_utc, date_hr,siteid,name,adslot,adsize,impressiontype,adaptertype,grouppubid,dealid,adplatform) | |
DO UPDATE | |
SET bidssent =+ excluded.bidssent, | |
adslotrequestsent =+ excluded.adslotrequestsent, | |
bidresponses =+ excluded.bidresponses, | |
bidwins =+ excluded.bidwins, | |
numimpressions =+ excluded.numimpressions, | |
numclicks =+ excluded.numclicks, | |
totalrevenue =+ excluded.totalrevenue, | |
totalnetrevenue =+ excluded.totalnetrevenue, | |
totalgrossrevenue =+ excluded.totalgrossrevenue, | |
totalauctionrevenue =+ excluded.totalauctionrevenue$$ | |
USING start_time, end_time; | |
END; | |
$function$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment