-
-
Notifications
You must be signed in to change notification settings - Fork 167
/
image_formats.sql
47 lines (47 loc) · 1 KB
/
image_formats.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
46
47
#standardSQL
# CSS-initiated image format popularity
SELECT
client,
format,
COUNT(0) AS freq,
SUM(COUNT(0)) OVER (PARTITION BY client) AS total,
COUNT(0) / SUM(COUNT(0)) OVER (PARTITION BY client) AS pct
FROM (
SELECT
client,
page,
url AS img_url,
JSON_VALUE(payload, '$._initiator') AS css_url,
IF(mimeType = 'image/avif', 'avif', IF(mimeType = 'image/webp', 'webp', format)) AS format
FROM
`httparchive.almanac.requests`
WHERE
date = '2021-07-01' AND
type = 'image')
JOIN (
SELECT
client,
page,
url AS css_url
FROM
`httparchive.almanac.requests`
WHERE
date = '2021-07-01' AND
type = 'css')
USING
(client, page, css_url)
JOIN (
SELECT
_TABLE_SUFFIX AS client,
url AS page,
JSON_EXTRACT_SCALAR(image, '$.url') AS img_url
FROM
`httparchive.pages.2021_07_01_*`,
UNNEST(JSON_EXTRACT_ARRAY(JSON_EXTRACT_SCALAR(payload, '$._Images'), '$')) AS image)
USING
(client, page, img_url)
GROUP BY
client,
format
ORDER BY
pct DESC