-
-
Notifications
You must be signed in to change notification settings - Fork 169
/
image_weights.sql
45 lines (45 loc) · 1.02 KB
/
image_weights.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#standardSQL
# CSS-initiated image stats per page (count, weight)
SELECT
percentile,
client,
APPROX_QUANTILES(css_initiated_images_per_page, 1000)[OFFSET(percentile * 10)] AS css_initiated_images_per_page,
APPROX_QUANTILES(total_css_initiated_image_weight_per_page, 1000)[OFFSET(percentile * 10)] AS total_css_initiated_image_weight_per_page
FROM (
SELECT
client,
COUNT(0) AS css_initiated_images_per_page,
SUM(respSize) AS total_css_initiated_image_weight_per_page
FROM (
SELECT
client,
page,
JSON_VALUE(payload, '$._initiator') AS url,
respSize
FROM
`httparchive.almanac.requests`
WHERE
date = '2021-07-01' AND
type = 'image')
JOIN (
SELECT
client,
page,
url
FROM
`httparchive.almanac.requests`
WHERE
date = '2021-07-01' AND
type = 'css')
USING
(client, page, url)
GROUP BY
client,
page),
UNNEST([10, 25, 50, 75, 90, 100]) AS percentile
GROUP BY
percentile,
client
ORDER BY
percentile,
client