-
-
Notifications
You must be signed in to change notification settings - Fork 169
/
meta_important_properties.sql
57 lines (52 loc) · 1.11 KB
/
meta_important_properties.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
48
49
50
51
52
53
54
55
56
57
#standardSQL
CREATE TEMPORARY FUNCTION getImportantProperties(css STRING)
RETURNS ARRAY<STRUCT<property STRING, freq INT64>>
LANGUAGE js
OPTIONS (library = "gs://httparchive/lib/css-utils.js")
AS '''
try {
var ast = JSON.parse(css);
let ret = {
total: 0,
important: 0,
properties: {}
};
walkDeclarations(ast, ({property, important}) => {
ret.total++;
if (important) {
ret.important++;
incrementByKey(ret.properties, property);
}
});
ret.properties = sortObject(ret.properties);
return Object.entries(ret.properties).map(([property, freq]) => {
return {property, freq};
});
} catch (e) {
return [];
}
''';
SELECT
client,
property,
COUNT(DISTINCT page) AS pages,
SUM(freq) AS freq,
SUM(SUM(freq)) OVER (PARTITION BY client) AS total,
SUM(freq) / SUM(SUM(freq)) OVER (PARTITION BY client) AS pct
FROM (
SELECT
client,
page,
important.property,
important.freq
FROM
`httparchive.almanac.parsed_css`,
UNNEST(getImportantProperties(css)) AS important
WHERE
date = '2021-07-01')
GROUP BY
client,
property
ORDER BY
pct DESC
LIMIT 500