What URL is closest to a given metric's average value?

Analysis
1

Need some query help, but this is the gist of what I’m trying to do:

SELECT url, ABS(onLoad - AVG(onLoad)) deviation
FROM [httparchive:runs.latest_pages]
ORDER BY deviation ASC
LIMIT 1;

Any suggestions on fixing it up?

2

Hmm… Well, while its not pretty, I guess you could run two different queries:

  • compute the average
  • (manually) feed the average into ABS() comparison

@fhoffa is there a more clever way to tackle this one?

3

Ta-da:

SELECT url, ABS(onLoad - avg) deviation FROM (
  SELECT url, onLoad, AVG(onLoad) OVER() avg  
  FROM [httparchive:runs.latest_pages])
ORDER BY deviation  
LIMIT 1;
1 Like
4

Ah, clever! I tried something similar, but couldn’t get it to work… need to learn more about partition() and over().

5

That’s great, thanks for the help guys.

I used that as a starting point to combine many averages together for a more wholesome look at “the most average page ever”.

SELECT url, ABS(onLoad - avgOnLoad) +
ABS(TTFB - avgTTFB) +
ABS(fullyLoaded - avgFullyLoaded) +
ABS(visualComplete - avgVisualComplete) +
ABS(reqTotal - avgReqTotal) +
ABS(bytesTotal - avgBytesTotal) +
ABS(numDomains - avgNumDomains) +
ABS(numDomElements - avgNumDomElements) deviation
FROM (
SELECT url, onLoad, TTFB, fullyLoaded, visualComplete, reqTotal, bytesTotal, numDomains, numDomElements,
 AVG(onLoad) OVER() avgOnLoad,
 AVG(TTFB) OVER() avgTTFB,
 AVG(fullyLoaded) OVER() avgFullyLoaded,
 AVG(visualComplete) OVER() avgVisualComplete,
 AVG(reqTotal) OVER() avgReqTotal,
 AVG(bytesTotal) OVER() avgBytesTotal,
 AVG(numDomains) OVER() avgNumDomains,
 AVG(numDomElements) OVER() avgNumDomElements
FROM [httparchive:runs.latest_pages])
ORDER BY deviation  
LIMIT 9;
1 Like