从Google BigQuery的嵌套表中删除重复项

伊丹·莫西（Idan Moshe）

我们已经开始使用Google bigQeury嵌套表。我们很难在此表上识别重复项。我们尝试使用Rownumber（）函数，但这意味着我们需要深入研究每条记录和记录。我们正在谈论的是一张表，该表在建筑的基本巢穴中有10多个记录，并且有400多个字段

到目前为止，下面的代码是我们创建的，它可以识别重复项，但是正如我所写的那样，由于我们可以写的字段太多，所以有400多个字段

SELECT
    count (*) AS Number_Of_Records --this will let us know how many records there is
FROM 
    (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY field1, ... , fieldN) AS ranking 
    FROM
        `data.T1`,
        unnest(record1) as record1, --unnesting is a must with nested tables and records
        ....,
        unnest(recordN) as recordN
    )
WHERE
    ranking=1 --duplicats >1

我们想找到一种较短的方法来编写此查询以查找非重复行的数量，谢谢

塔米尔·克莱因（Tamir Klein）

一种方法是使用哈希函数来识别记录，并使用此哈希来删除重复项。

因此，假设您的JSON数据由字符串组成，则可以使用以下代码为记录生成唯一的哈希码：

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list -- Duplicate record
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT distinct FARM_FINGERPRINT(ARRAY_TO_STRING(list, '--')) AS text
FROM items;

结果是3行，而不是预期的4行（重复1行）