T152122 Hive query for impression outage by language and db (project)
ActivePublic
Actions

Authored by AndyRussG on Dec 7 2016, 6:22 PM.

Tags

Referenced Files

	F5002061: T152122 Hive query for impression outage by language and db (project)
	Dec 7 2016, 6:22 PM

Subscribers

None

	SELECT
	good.average as goodaverage, bad.average as badaverage, bad.average/good.average as ratio, good.uselang, good.db
	FROM (
	SELECT
	( count(*) / 120 ) as average, uselang, db
	FROM
	andyrussg.t152122beaconimpression20161201
	WHERE
	substr(time, 12, 2) in ('07', '10')
	GROUP BY
	uselang, db
	) good
	JOIN (
	SELECT
	( count(*) / 44) as average, uselang, db
	FROM
	andyrussg.t152122beaconimpression20161201
	WHERE
	time > '2016-12-01 08:10'
	AND time < '2016-12-01 08:55'
	GROUP BY
	uselang, db
	) bad
	ON
	good.uselang = bad.uselang
	AND good.db = bad.db;

	SELECT
	good.average as goodaverage, bad.average as badaverage, bad.average/good.average as ratio, good.uselang, good.db
	FROM (
	SELECT
	( count(*) / 120 ) as average, uselang, db
	FROM
	andyrussg.t152122beaconimpression20161202
	WHERE
	substr(time, 12, 2) in ('07', '10')
	GROUP BY
	uselang, db
	) good
	JOIN (
	SELECT
	( count(*) / 44) as average, uselang, db
	FROM
	andyrussg.t152122beaconimpression20161202
	WHERE
	time > '2016-12-02 08:10'
	AND time < '2016-12-02 08:55'
	GROUP BY
	uselang, db
	) bad
	ON
	good.uselang = bad.uselang
	AND good.db = bad.db;

T152122 Hive query for impression outage by language and db (project)ActivePublicActions