Oracle含日期小数偏移的RANGE窗口函数转BigQuery语法问题

核心差异说明：Oracle中针对日期时间字段的RANGE窗口使用的小数偏移默认以「天」为单位，而BigQuery要求时间类排序字段的RANGE偏移必须使用标准INTERVAL时间间隔类型，不能直接传入数值。
首先换算原SQL中的两个偏移量：

• 0.000023148148 天 = 1天 / 43200 = 2秒
• 0.003472222 天 = 1天 / 288 = 5分钟

修改后的完整代码 #

CASE WHEN SUBSTR(PHONE, 1, 3) NOT IN ('123','345')
  AND total_count IS NULL
  AND identity IS NOT NULL
  AND MAX(1) OVER(
    PARTITION BY PHONE
    ORDER BY START_DATE_AND_TIME
    RANGE BETWEEN INTERVAL 2 SECOND FOLLOWING AND INTERVAL 5 MINUTE FOLLOWING
  ) = 1 THEN 1
END AS CALL

补充说明 #

• 原Oracle中的substr(PHONE, 0, 3)在BigQuery中建议替换为SUBSTR(PHONE, 1, 3)，BigQuery字符串索引从1开始，起始位填0会被自动识别为1，显式写1兼容性更好。
• 上述语法对START_DATE_AND_TIME为TIMESTAMP、DATETIME类型均生效。

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