BigQuery:如何在分区表上运行分析函数查询

Question

我有一个包含多个版本相同数据的表。每个版本都可以通过时间戳字段来识别。

这是我提出的查询，它只获取我的数据的最新版本，使用一个窗口函数：

WITH tbl AS (
  SELECT
    *,
    first_value(`timestamp`) OVER (PARTITION BY concatenate ORDER BY `timestamp` DESC) AS latest
  FROM
    dataset.table as tbl
)

SELECT
  *  
FROM tbl
WHERE
  `timestamp` = latest;

串联是表示我的行的唯一性的字段的组合。

但是，为了优化这一点，我创建了一个分区表，如下所示，以减少由于数据的许多版本而在我的查询中处理的数据量：

CREATE TABLE
  `dataset.partitioned_table`
PARTITION BY
  DATE(`timestamp`)
  CLUSTER BY concatenate AS
SELECT * FROM `dataset.table`;

但是，当我对新分区数据运行查询时，BigQuery处理的数据量与非分区数据完全相同，即它应该处理大约三分之一的数据(我正在用3个版本的数据进行测试)。

我的感觉是，窗口函数正在遍历整个数据，当使用分区表时，可能有更好的方法来完成这个任务，但是，我还没有找到如何做到这一点。

还尝试用假日期创建表分区，但按时间戳和级联字段进行聚类，但结果相同。

Answer 1

您的查询每次都会读取整个表，这是因为它的编写方式。

检查CTE ( WITH子句)，它不受任何WHERE子句的限制，以减少读取的数据量。

如果只想读取表的最新分区，则必须执行以下操作：

WITH tbl AS (
  SELECT
    *,
    first_value(`timestamp`) OVER (PARTITION BY concatenate ORDER BY `timestamp` DESC) AS latest
  FROM
    dataset.table as tbl

  #### this is the super important bit to reduce your reads ####
  WHERE DATE(timestamp) >= DATE('whatever_you_think_your_last_few_partitions_should_be')

)

SELECT
  *  
FROM tbl
WHERE
  `timestamp` = latest;

基本上，您需要在查询的早期使用WHERE进行筛选，以减少处理的数据量。

请记住，我添加了whatever_you_think_your_last_few_partitions_should_be，因为这实际上取决于您是否希望扫描最后X个分区的重复值，或者日期范围为1周，或者仅仅是最后一天。取决于你的解决方案。