almanac.httparchive.org/sql/2021/media/portrait_landscape_square.sql at main · HTTPArchive/almanac.httparchive.org · GitHub

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
58
59
60
CREATE TEMPORARY FUNCTION getSrcsetInfo(responsiveImagesJsonString STRING)
RETURNS ARRAY<STRUCT<imgURL STRING, approximateResourceWidth INT64, approximateResourceHeight INT64, aspectRatio NUMERIC, isPortrait BOOL, isLandscape BOOL, isSquare BOOL>>
LANGUAGE js AS '''
  const parsed = JSON.parse( responsiveImagesJsonString );
  if ( parsed && parsed.map ) {
    return parsed.map( d => {
      const aspectRatio = ( d.approximateResourceWidth > 0 && d.approximateResourceHeight > 0 ?
        Math.round( ( d.approximateResourceWidth / d.approximateResourceHeight ) * 1000 ) / 1000 : -1 );
      return {
        imgURL: d.url,
        approximateResourceWidth: Math.floor( d.approximateResourceWidth || 0 ),
        approximateResourceHeight: Math.floor( d.approximateResourceHeight || 0 ),
        aspectRatio: aspectRatio,
        isPortrait: aspectRatio < 1 && aspectRatio > 0,
        isLandscape: aspectRatio > 1,
        isSquare: aspectRatio == 1
      }
    });
  }
''';

WITH imgs AS (
  SELECT
    _TABLE_SUFFIX AS client,
    url AS pageURL,
    imgURL,
    approximateResourceWidth,
    approximateResourceHeight,
    isPortrait,
    isLandscape,
    isSquare
  FROM
    `httparchive.pages.2021_07_01_*`,
    UNNEST(getSrcsetInfo(JSON_QUERY(JSON_VALUE(payload, '$._responsive_images'), '$.responsive-images')))
  WHERE
    approximateResourceWidth > 1 AND
    approximateResourceHeight > 1
),

counts_per_client AS (
  SELECT
    client,
    COUNTIF(isPortrait) AS portraits,
    COUNTIF(isLandscape) AS landscapes,
    COUNTIF(isSquare) AS squares,
    COUNT(0) AS numberOfImagesPerClient
  FROM
    imgs
  GROUP BY
    client
)

SELECT
  client,
  SAFE_DIVIDE(portraits, numberOfImagesPerClient) AS percentPortrait,
  SAFE_DIVIDE(landscapes, numberOfImagesPerClient) AS percentLandscape,
  SAFE_DIVIDE(squares, numberOfImagesPerClient) AS percentSquare,
  numberOfImagesPerClient
FROM
  counts_per_client