bigquery 中的 UNNEST 運算符：聚合結果時的奇怪行為

我試圖了解 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

最終目標將是選擇一個起始值的底層括號來找出“好”的網站，但我需要先確定為什麼上述方法不起作用。

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在需要自定義門檻值時回退到數據集。

引用自：https://dba.stackexchange.com/questions/298284