每个最后一个日期的BigQuery过滤器并使用分区 [英] 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

推荐答案

现在

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过滤器并使用分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！