google-bigquery - BigQuery 重复数据删除和分区表

Question

我想在 BigQuery 中创建一个允许执行以下操作的 View :

• 重复记录删除
• 将 _PARTITIONTIME 公开为用于过滤的列，以允许分区修剪

我知道执行重复数据删除的建议查询是:

SELECT
  * EXCEPT(row_number)
  FROM (
SELECT *
    ,ROW_NUMBER()
          OVER (PARTITION BY fct.KeyColumn ORDER BY fct.CreatedDatetime DESC) row_number
FROM `my-project.my_dataset.fact_table` fct)
WHERE
  row_number = 1

查询分区表的建议方法是:

SELECT
  *
FROM
  `my-project.my_dataset.fact_table`
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02');

如何将两者结合起来提供一个经过重复数据删除的 BigQuery View ，并提供一个列以允许在 View 的 where 子句中使用 _PARTITIONTIME。我读到了有关使用子选择等时分区修剪的限制的信息，这就是为什么我想知道这是否可能。

为了给您提供一些背景信息，该 View 位于正在流式传输的表的顶部，因此重复数据删除必须即时进行。

Answer 1

您需要让 View 每天返回最新行，以启用对分区时间的过滤。它会是这样的:

#standardSQL
SELECT day, latest_row.*
FROM (
  SELECT
    _PARTITIONTIME AS day,
    ARRAY_AGG(fct ORDER BY fct.CreatedDatetime DESC LIMIT 1)[OFFSET(0)] AS latest_row
  FROM `my-project.my_dataset.fact_table` AS fct
  GROUP BY day, KeyColumn
);

在外部查询中，latest_row.* 还应包含 KeyColumn。