google-bigquery - Big Query Deduplication 查询示例解释

Question

有人可以解释这个用于重复数据删除的 Bigquery 查询吗？为什么我们需要使用 [OFFSET(0)]？我认为它用于获取聚合数组中的第一个元素，对吗？这不是和 LIMIT 1 一样吗？为什么我们需要聚合整个表？为什么我们可以在单个单元格中聚合整个表格？

 # take the one name associated with a SKU
    WITH product_query AS (
      SELECT 
      DISTINCT 
      v2ProductName,
      productSKU
      FROM `data-to-insights.ecommerce.all_sessions_raw` 
      WHERE v2ProductName IS NOT NULL 
    )
    SELECT k.* FROM (
    # aggregate the products into an array and 
      # only take 1 result
      SELECT ARRAY_AGG(x LIMIT 1)[OFFSET(0)] k 
      FROM product_query x 
      GROUP BY productSKU # this is the field we want deduplicated
    );

Answer 1

让我们从一些我们想要去重的数据开始:

WITH table AS (SELECT * FROM UNNEST([STRUCT('001' AS id, 1 AS a, 2 AS b), ('002', 3,5), ('001', 1, 4)]))

SELECT *
FROM table t

现在，我将使用 t 来引用整行，而不是 *:

SELECT t
FROM table t

如果我按 id 对这些行中的每一行进行分组会发生什么:

SELECT t.id, ARRAY_AGG(t) tt
FROM table t
GROUP BY 1

现在我将具有相同 ID 的所有行组合在一起。但让我只选择一个:

SELECT t.id, ARRAY_AGG(t LIMIT 1) tt
FROM table t
GROUP BY 1

这可能看起来不错，但它仍然是一个数组中的一行。我怎样才能只得到行，而不是数组:

SELECT t.id, ARRAY_AGG(t LIMIT 1)[OFFSET(0)] tt
FROM table t
GROUP BY 1

如果我想返回没有分组 id 或 tt 前缀的行:

SELECT tt.*
FROM (
  SELECT t.id, ARRAY_AGG(t LIMIT 1)[OFFSET(0)] tt
  FROM table t
  GROUP BY 1
)

这就是您根据行 ID 删除重复行的方式。

如果您需要选择特定的行 - 例如给定时间戳的最新行，只需像 ARRAY_AGG(t ORDER BY timestamp DESC LIMIT 1)

那样对聚合进行排序