尝试使用last_modified_time时TABLE_QUERY失败

I have an existing query which uses TABLE_QUERY(), and filters the results based on creation_time:

我有一个使用TABLE_QUERY()的现有查询,并根据creation_time过滤结果:

SELECT 
     *
FROM (TABLE_QUERY( project_114151_dataset , "MSEC_TO_TIMESTAMP(creation_time) > DATE_ADD(CURRENT_TIMESTAMP(), -45, 'DAY') AND REGEXP_MATCH(table_id, r'^fact_[0-9]{8}$') "))

I want to change the query to run based on last_modified_time; since it is also a timestamp in msec, I changed the query to be as follows

我想将查询更改为基于last_modified_time运行;因为它也是以毫秒为单位的时间戳,所以我将查询更改为如下

SELECT 
     *
FROM (TABLE_QUERY( project_114151_dataset , "MSEC_TO_TIMESTAMP(last_modified_time) > DATE_ADD(CURRENT_TIMESTAMP(), -45, 'DAY') AND REGEXP_MATCH(table_id, r'^fact_[0-9]{8}$') "))

However, when running I am getting the following error:

但是,运行时我收到以下错误:

Error: Error evaluating subsidiary query

Not sure why I'm getting this error; I've verified that this field exists by running the following query, and it does indeed return in the results:

不知道为什么我会收到这个错误;我已经通过运行以下查询验证了该字段是否存在,并且确实在结果中返回:

SELECT * FROM project_114151_dataset.__TABLES__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < 
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Any help would be appreciated!

任何帮助,将不胜感激!

2 个解决方案

#1

The only fields available in the TABLE_QUERY() function are: project_id, dataset_id, table_id, creation_time, and type. They are the feilds returned by the __TABLES_SUMMARY__ pseudo-table. (try SELECT * FROM project_114151_dataset.__TABLES_SUMMARY__).

TABLE_QUERY()函数中唯一可用的字段是:project_id,dataset_id,table_id,creation_time和type。它们是__TABLES_SUMMARY__伪表返回的字段。 (尝试SELECT * FROM project_114151_dataset .__ TABLES_SUMMARY__)。

#2

The expression inside TABLE_QUERY cannot use data form the table, it has to use constants and free functions (such as CURRENT_TIMESTAMP) only.

TABLE_QUERY里面的表达式不能使用表格中的数据,它只能使用常量和*函数(如CURRENT_TIMESTAMP)。