如何在BigQuery中查找相对于日期的SQL计数差异

Question

我有什么:

我有一个表，看起来像这样:

tbody> <<tr>鲍勃莎拉莎拉

用户	资源	时间戳
鲍勃	账户	2021 - 01 - 01 t12:00:00 UTC
	首页	2021 - 01 - 01 t12:01:30 UTC
	条款	2021 - 01 - 02 t08:45:10 UTC
	条款	2021 - 01 - 03 t09:04:59 UTC

如果我理解正确的话，

select user, resource,
countif(timestamp_trunc(hour, timestamp) = timestamp_trunc(hour, current_timestamp)) as count_this_hour,
countif(timestamp_trunc(hour, timestamp) = timestamp_add(timestamp_trunc(hour, current_timestamp), interval -1 hour)) as count_last_hour,
(countif(timestamp_trunc(hour, timestamp) = timestamp_trunc(hour, current_timestamp)) /
nullif(countif(timestamp_trunc(hour, timestamp) = timestamp_add(timestamp_trunc(hour, current_timestamp), interval -1 hour)) 
)
) as ratio
from `table` t
group by user, resource;