BigQuery 按最后一个日期过滤并使用 Partition [英] BigQuery filter per the last Date and use Partition

问题描述

我问了如何过滤最后一个日期并得到了很好的答案(BigQuery，where子句中如何使用别名?)，它们都可以，但是，它们扫描整个表，字段SETTLEMENTDATE是一个分区字段，有没有办法只扫描一个分区

I asked how to filter the last date and got excellent answers (BigQuery, how to use alias in where clause?), they all work, but, they scan the whole table, the field SETTLEMENTDATE is a partition field, is there a way to scan only one partition

举个例子，我正在使用这个查询

as an example, I am using this query

#standardSQL
SELECT * EXCEPT(isLastDate) 
FROM (
  SELECT *, DATE(SETTLEMENTDATE) = MAX(DATE(SETTLEMENTDATE)) OVER() isLastDate
  FROM `biengine-252003.aemo2.daily`
)
WHERE isLastDate 

请最后日期并不总是当前日期，因为数据存在滞后

edit : please last date is not always current date, as there is lag in the data

推荐答案

现在 脚本在 BigQuery 中处于测试阶段，您可以声明一个包含目标日期的变量.举个例子:

Now that scripting is in beta in BigQuery, you can declare a variable that contains the target date. Here's an example:

SET max_date DATE DEFAULT (SELECT DATE(MAX(datehour)) FROM `fh-bigquery.wikipedia_v3.pageviews_2019` WHERE wiki='es');

SELECT MAX(views)
FROM `fh-bigquery.wikipedia_v3.pageviews_2019` 
WHERE DATE(datehour) = max_date
AND wiki='es'

这篇关于BigQuery 按最后一个日期过滤并使用 Partition的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！