BigQuery技术问题：计算最小与最大观测时间戳的秒级差值

问题核心 #

你当前的代码存在两个关键问题：

1. 直接对TIME类型做减法得到的是INTERVAL类型，无法直接得到秒级数值；
2. app和pseudo_key字段在GROUP BY中的使用方式不符合BigQuery的分组规则，容易引发报错或结果异常。

修正后的SQL代码 #

SELECT
  user_id,
  ANY_VALUE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'tms_app')) AS app,
  CONCAT(ga_session_id, ANY_VALUE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'tms_app'))) AS pseudo_key,
  EXTRACT(DATE FROM event_timestamp_utc) AS Event_Date,
  MIN(event_timestamp_utc) AS start_ts,
  MAX(event_timestamp_utc) AS end_ts,
  TIMESTAMP_DIFF(MAX(event_timestamp_utc), MIN(event_timestamp_utc), SECOND) AS usage_time_seconds
FROM `BigQ Location`
WHERE event_date_cdt >= '2024-01-01'
GROUP BY user_id, ga_session_id, EXTRACT(DATE FROM event_timestamp_utc)
ORDER BY Event_Date, ga_session_id, start_ts

关键修改说明 #

1. 直接使用完整时间戳计算：
   放弃提取TIME类型，直接用MIN(event_timestamp_utc)和MAX(event_timestamp_utc)获取分组内的首尾时间戳，保留完整的日期和毫秒信息，计算结果更精准。
2. 用TIMESTAMP_DIFF计算秒级间隔：
   这是BigQuery官方推荐的时间差计算方法，通过指定SECOND参数直接得到秒数结果，避免手动转换的误差。
3. 聚合处理app字段：
   使用ANY_VALUE()聚合函数获取分组内的app值，确保同一个用户、会话、日期下的app取值一致，同时避免因UNNEST(event_params)导致的分组逻辑错误。
4. 简化分组条件：
   移除GROUP BY中的app和pseudo_key，因为这两个字段是通过聚合生成的，不属于分组键范畴。

若需保留纯时间格式的起止时间 #

如果必须展示当天的时间部分（如17:02:22.800977），可以在上述代码基础上调整start_ts和end_ts的生成方式：

-- 仅修改起止时间字段的生成逻辑
TIME(MIN(event_timestamp_utc)) AS start_ts,
TIME(MAX(event_timestamp_utc)) AS end_ts,

内容的提问来源于stack exchange，提问作者Desert Spider