使用SQL中的CAST将时间转换为整数

Question

我想得到超过0的值的平均trip_duration，所以我想将时间强制转换为int64，以便能够在where子句中将其campare为0，但它并没有真正起作用。

(SELECT (ended_at-started_at) as trip_duration
FROM `case-study-bikes-trips.bikes_trips.03_2021` )
select CAST (avg(trip_duration) as INT64)
from duration 

它告诉我"；从INTERVAL到INT64的无效强制转换"；。

Answer 1

考虑以下修复：

with duration as (
select datetime_diff(ended_at, started_at, minute) as trip_duration
from `case-study-bikes-trips.bikes_trips.03_2021` 
)
select avg(trip_duration) as avg_trip_duration_in_min
from duration