BigQuery中两个chicago_taxi_trips查询结果差异的技术咨询

核心差异点 #

两个查询的唯一区别在于WHERE子句中对trip_start_timestamp的过滤逻辑：

第一个查询的时间过滤 #

WHERE EXTRACT(DATE FROM trip_start_timestamp) > '2016-01-01'
AND EXTRACT(DATE FROM trip_start_timestamp) < '2016-04-01'

第二个查询的时间过滤 #

WHERE trip_start_timestamp > '2016-01-01' AND 
      trip_start_timestamp < '2016-04-01'

结果不一致的原因 #

BigQuery对不同类型的时间比较逻辑存在差异，导致两个查询的实际过滤范围不同：

1. 第二个查询的实际范围
   当直接用timestamp类型字段和字符串'2016-01-01'比较时，字符串会被自动解析为timestamp类型的2016-01-01 00:00:00 UTC，同理'2016-04-01'解析为2016-04-01 00:00:00 UTC。
   因此第二个查询会包含2016-01-01 00:00:01 到 2016-03-31 23:59:59之间的所有行程。

2. 第一个查询的实际范围
   用EXTRACT(DATE)提取日期后，得到的是DATE类型值，和字符串'2016-01-01'比较时，字符串会被解析为DATE类型的2016-01-01。

   • EXTRACT(DATE FROM trip_start_timestamp) > '2016-01-01' 要求行程日期必须是2016-01-02及之后
   • EXTRACT(DATE FROM trip_start_timestamp) < '2016-04-01' 要求行程日期必须是2016-03-31及之前
     因此第一个查询会排除2016年1月1日当天的所有行程，只包含2016-01-02 00:00:00 到 2016-03-31 23:59:59之间的行程。

具体结果差异表现 #

• 第二个查询的num_trips（行程数量）会比第一个查询多，多出来的部分就是2016年1月1日当天符合条件的所有行程。
• 由于1月1日的行程数据被包含/排除，两个查询的SUM(trip_miles)和SUM(trip_seconds)会不同，最终计算出的avg_mph（平均时速）也会存在差异。

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