主页 / dba / 问题 / 298284
Accepted
Andrea Moro
Asked: 2021-08-20 13:49:20 +0800 CST

bigquery 中的 UNNEST 运算符：聚合结果时的奇怪行为

  • 772

我试图了解 UNNEST 运算符如何在存储CRUX 数据（Chrome UX 报告）的 Google 的公共数据库上工作。

在此页面上提供了一些示例。

我可以理解以下内容：

  • 所有密度的总和为 1（或 100%）
  • 密度分为三种类型（手机、平板电脑、台式机）
  • bin start 和 end 在使用时对数据进行切片

本文提供了一些使用 UNNEST 运算符的示例，该运算符扩展了最里面的数组，从而也可以进行分组操作。

所以像下面这样的查询

SELECT
  SUM(fcp.density) AS fast_fcp
FROM
  `chrome-ux-report.all.201809`,
  UNNEST(first_contentful_paint.histogram.bin) AS fcp
WHERE
  origin = 'https://developers.google.com'

返回所有 FCP 密度的总和，值为 0.999999。

我本来希望第二个 SUM 在第二个 UNNESTED 运算符上的工作类似；但是，当我使用两个字段并进行求和时，会发生一些奇怪的事情。

例如以下

SELECT
  SUM(fcp.density) AS fast_fcp,
  SUM(lcp.density) AS fast_lcp
FROM
  `chrome-ux-report.all.201809`,
  UNNEST(first_contentful_paint.histogram.bin) AS fcp
  UNNEST(largest_contentful_paint.histogram.bin) AS lcp
WHERE
  origin = 'https://developers.google.com'

产生类似的东西

Row f0_ f1_ 
1   393.12850000000896 352.06599999922156

奇怪的是，如果不使用聚合，unnest 运算符会按预期工作，并且列会按预期在列中展开。

有人可以帮助我了解门后发生的事情以及如何获得一系列领域的总和。

例如

Site;fcp;cls;fid
https://developers.google.com;0.4;0.2;0.1
https://www.google.com;0.1;0.4;0.3

最终目标将是选择一个起始值的底层括号来找出“好”的网站，但我需要先确定为什么上述方法不起作用。

google-bigquery

2 个回答

  1. Best Answer

    Andriy M 的回答很好地描述了UNNEST工作原理。我将在 CrUX 数据集后面添加更多上下文，以及如何获得所需的答案。

    如果您有兴趣分析直方图以找到每个指标的“快速”体验百分比，您可以UNNEST使用materialized数据集完全跳过该方法。例如：

    SELECT
  fast_fcp,
  fast_lcp
FROM
  `chrome-ux-report.materialized.metrics_summary`
WHERE
  date = '2021-07-01' AND
  origin = 'https://developers.google.com'

    结果：

    fast_fcp    fast_lcp
0.6843      0.7234

    查询统计：0.6 sec elapsed, 9.3 MB processed

    该materialized.metrics_summary表是根据 Chrome 团队设置的主观“快速”阈值进行预处理的。生成此表的查询将保存到materialized.metrics_query视图中。在其中，您可以看到底层UNNEST的 s 是如何工作的：

    WITH fcp AS (
SELECT
  yyyymm,
  origin,
  ROUND(SUM(IF(bin.start < 1800, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.experimental.global`
LEFT JOIN
  UNNEST(first_contentful_paint.histogram.bin) AS bin
WHERE
  yyyymm = @yyyymm AND
  bin IS NOT NULL
GROUP BY
  yyyymm,
  origin
),
lcp AS (
SELECT
  yyyymm,
  origin,
  ROUND(SUM(IF(bin.start < 2500, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.experimental.global`
LEFT JOIN
  UNNEST(largest_contentful_paint.histogram.bin) AS bin
WHERE
  yyyymm = @yyyymm AND
  bin IS NOT NULL
GROUP BY
  yyyymm,
  origin
)

    每个指标都聚合在语句的其自己的单独部分中，该部分WITH在后续SELECT语句中输出：

    SELECT
  yyyymm,
  origin,
  fcp.fast AS fast_fcp,
  lcp.fast AS fast_lcp
FROM
  fcp
LEFT JOIN
  lcp
USING
  (yyyymm, origin)

    （简化为省略与此问题无关的指标）

    使用这种UNNEST方法，我将编写一个查询来计算给定来源的快速 FCP 和 LCP：

    WITH fcp AS (
SELECT
  ROUND(SUM(IF(bin.start < 1800, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.all.202107`,
  UNNEST(first_contentful_paint.histogram.bin) AS bin
WHERE
  origin = 'https://developers.google.com'
),
lcp AS (
SELECT
  ROUND(SUM(IF(bin.start < 2500, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.all.202107`,
  UNNEST(largest_contentful_paint.histogram.bin) AS bin
WHERE
  origin = 'https://developers.google.com'
)

SELECT
  fcp.fast AS fast_fcp,
  lcp.fast AS fast_lcp
FROM
  fcp,
  lcp

    结果：

    fast_fcp    fast_lcp
0.6843      0.7234

    查询统计：0.7 sec elapsed, 143.4 GB processed

    所以它产生与表格相同的结果materialized.metrics_summary，但它消耗了 15000 倍的数据。这是因为查询all.202107需要处理整个表，即使我们只对单个来源感兴趣。

    让我们稍微重写查询以使用与experimental.global物化视图相同的表。该表是all数据集的分区和集群版本。分区yyyymm意味着 BigQuery 永远不会处理202107发布之外的数据，而集群origin意味着 BigQuery 可以在找到我们正在寻找的来源后停止处理。

    WITH fcp AS (
SELECT
  ROUND(SUM(IF(bin.start < 1800, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.experimental.global`,
  UNNEST(first_contentful_paint.histogram.bin) AS bin
WHERE
  yyyymm = 202107 AND
  origin = 'https://developers.google.com'
),
lcp AS (
SELECT
  ROUND(SUM(IF(bin.start < 2500, bin.density, 0)), 4) AS fast
FROM
  `chrome-ux-report.experimental.global`,
  UNNEST(largest_contentful_paint.histogram.bin) AS bin
WHERE
  yyyymm = 202107 AND
  origin = 'https://developers.google.com'
)

SELECT
  fcp.fast AS fast_fcp,
  lcp.fast AS fast_lcp
FROM
  fcp,
  lcp

    结果：

    fast_fcp    fast_lcp
0.6843      0.7234

    查询统计：0.7 sec elapsed, 63.9 MB processed

    结果相同，处理的字节数更少，但仍不如物化数据集简单或便宜。

    我知道这是一个很长的答案，但希望它能说服您materialized在依赖标准快速/慢速阈值时使用数据集，或者experimental.global在需要自定义阈值时回退到数据集。

    • 2

  2. UNNEST生成一个行集，就像读取常规表一样。两个UNNEST电话给你两个行集。您没有提供任何连接条件来匹配这两个行集。这意味着它们是交叉连接的，并且您得到一个的行UNNEST数乘以另一个的行数。该FROM子句在该子句之前进行评估SELECT，这意味着交叉连接发生在聚合之前。因此，这两个SUM调用最终都会聚合由交叉连接生成的多个重复项，这可以理解为您提供了您没有预料到的结果。

    您需要将两个UNNESTs 彼此分开聚合。我对 BigQuery 语法不是很熟悉，但大概这样的东西应该可以工作：

    SELECT
  (SELECT SUM(fcp.density) FROM UNNEST(  first_contentful_paint.histogram.bin) AS fcp) AS fast_fcp,
  (SELECT SUM(lcp.density) FROM UNNEST(largest_contentful_paint.histogram.bin) AS lcp) AS fast_lcp
FROM
  `chrome-ux-report.all.201809`
WHERE
  origin = 'https://developers.google.com'
;
    • 1

相关问题