如何在BigQuery SQL中处理Timestamp与String日期的月份差计算

原始SQL代码 #

SELECT 
    REPORTDATE,ARCHIVEDATE, RATE,GRID48_DR,
    REGEXP_REPLACE(SUBSTR(GRID48_DR, 1, MNTHS_BTWN - 1),
                   '\[\*a-zA-Z0-9\]', 'X') ||RATE||  
       SUBSTR(GRID48_DR, 1, LENGTH(GRID48_DR) - (MNTHS_BTWN)) GRID48_NEW
FROM 
    (SELECT  
         REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
         MONTHS_BETWEEN(to_date(ARCHIVEDATE,'MMYYYY'),to_date(REPORTDATE,'MMYYYY'))+1 AS MNTHS_BTWN
     FROM TRADE)

问题1：BigQuery中计算Timestamp与String日期的月份差（结果加1） #

BigQuery没有MONTHS_BETWEEN函数，需结合日期转换和DATE_DIFF函数实现：

1. 将String类型的REPORTDATE（格式MMYYYY）转为DATE类型：PARSE_DATE('%m%Y', REPORTDATE)
2. 将Timestamp类型的ARCHIVEDATE转为DATE类型：DATE(ARCHIVEDATE)
3. 用DATE_DIFF计算月份差后加1，最终表达式为：

DATE_DIFF(DATE(ARCHIVEDATE), PARSE_DATE('%m%Y', REPORTDATE), MONTH) + 1

问题2：替换子查询中的语句 #

直接将原语句里的月份差计算逻辑替换为BigQuery兼容写法，完整子查询如下：

SELECT  
    REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
    DATE_DIFF(DATE(ARCHIVEDATE), PARSE_DATE('%m%Y', REPORTDATE), MONTH) + 1 AS MNTHS_BTWN
FROM TRADE

代入原SQL后，完整的BigQuery兼容语句（同时修正原SUBSTR逻辑错误）：

SELECT 
    REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
    REGEXP_REPLACE(SUBSTR(GRID48_DR, 1, MNTHS_BTWN - 1),
                   r'\[\*a-zA-Z0-9\]', 'X') || RATE ||  
    SUBSTR(GRID48_DR, LENGTH(GRID48_DR) - MNTHS_BTWN + 1) AS GRID48_NEW
FROM 
    (SELECT  
         REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
         DATE_DIFF(DATE(ARCHIVEDATE), PARSE_DATE('%m%Y', REPORTDATE), MONTH) + 1 AS MNTHS_BTWN
     FROM TRADE)

注：原SQL中SUBSTR(GRID48_DR, 1, LENGTH(GRID48_DR) - (MNTHS_BTWN))逻辑错误，会重复截取开头内容，修正为从倒数第MNTHS_BTWN位开始取剩余部分。

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