PostgreSQL查询转BigQuery脚本后返回空结果如何排查解决

问题背景 #

现有存量PostgreSQL查询需要转换为BigQuery脚本，所有数据已存储在BigQuery中，日期字段均为YYYY-MM-DD HH24:MI:SS格式，但表结构定义中为字符串类型。自行改写后查询返回零条结果，怀疑问题出在LEFT JOIN部分。

原PostgreSQL查询语句 #

SELECT to_timestamp(call.date_time, 'YYYY-MM-DD HH24:MI:SS'::text)        AS call_ts,
       call.call_type                                                     AS typez,
       call.jk                                                            AS call_nb_name,
       call.sale_type                                                     AS cost,
       call.partner                                                       AS call_partner,
       call.call_state                                                    AS call_sell_status,
       to_timestamp(click.event_timestamp, 'YYYY-MM-DD HH24:MI:SS'::text) AS click_ts,
       click.campaign,
       click.medium,
       click.source,
       click."OS"                                                         AS device_category,
       click.event_name                                                   AS event_label,
       NULL::text                                                         AS event_category,
       CASE
           WHEN regexp_match(click.region, 'MOSOBL'::text) IS NOT NULL THEN 'MOS'::text
           WHEN regexp_match(click.region, 'LENOBL'::text) IS NOT NULL THEN 'SP'::text
           ELSE NULL::text
           END                                                            AS click_region,
       call.city                                                          AS call_region,
       click."newBuilding_name"                                           AS click_nb_name
FROM "username"."lost_calls" call
         LEFT JOIN "username"."call_track" click
                   ON to_timestamp(call.date_time, 'YYYY-MM-DD HH24:MI:SS'::text) >
                      to_timestamp(click.event_timestamp, 'YYYY-MM-DD HH24:MI:SS'::text) AND
                      to_timestamp(click.event_timestamp, 'YYYY-MM-DD HH24:MI:SS'::text) >
                      (to_timestamp(call.date_time, 'YYYY-MM-DD HH24:MI:SS'::text) - '02:00:00'::interval)
WHERE click.medium !~~ 'test%'::text
  AND call.partner !~~ '%VAS%'::text

改写后存在问题的BigQuery语句 #

SELECT timestamp(call.date_time)                                              AS call_ts,
           call.call_type                                                     AS typez,
           call.jk                                                            AS call_nb_name,
           call.sale_type                                                     AS cost,
           call.partner                                                       AS call_partner,
           call.call_state                                                    AS call_sell_status,
           timestamp(click.event_timestamp)                                   AS click_ts,
           click.campaign,
           click.medium,
           click.source,
           click.OS                                                           AS device_category,
           click.event_name                                                   AS event_label,
           NULL                                                               AS event_category,
           CASE
               WHEN REGEXP_CONTAINS(click.region, 'MOSOBL') IS NOT NULL THEN 'MOS'
               WHEN REGEXP_CONTAINS(click.region, 'LENOBL') IS NOT NULL THEN 'SP'
               ELSE NULL
               END                                                            AS click_region,
           call.city                                                          AS call_region,
           click.newBuilding_name                                             AS click_nb_name
    FROM `myproject.dataset.lost_calls` as call
             LEFT JOIN `myproject.dataset.call_track` as click
                       ON call.date_time > click.event_timestamp
                       AND click.event_timestamp = call.date_time
                       AND TIMESTAMP(click.event_timestamp) > TIMESTAMP_ADD(TIMESTAMP(call.date_time), interval 2 HOUR) 
    WHERE click.medium LIKE 'test%'
        AND call.partner LIKE '%VAS%'

问题点说明 #

• JOIN条件逻辑完全互斥：原逻辑要求点击时间发生在通话前2小时以内，改写后的逻辑同时要求点击时间早于通话时间、点击时间等于通话时间、点击时间晚于通话时间2小时，没有任何数据能同时满足这三个条件，这是返回零结果的核心原因
• WHERE条件逻辑写反：原逻辑要求medium不以test开头、partner不包含VAS，改写后改成了反向过滤条件
• REGEXP_CONTAINS函数返回布尔值，不需要额外判断IS NOT NULL
• 字符串类型的时间字段直接比较存在不可预期问题，需要统一转为TIMESTAMP类型后再做运算

修正后的BigQuery语句 #

SELECT TIMESTAMP(call.date_time)                                              AS call_ts,
       call.call_type                                                         AS typez,
       call.jk                                                                AS call_nb_name,
       call.sale_type                                                         AS cost,
       call.partner                                                           AS call_partner,
       call.call_state                                                        AS call_sell_status,
       TIMESTAMP(click.event_timestamp)                                       AS click_ts,
       click.campaign,
       click.medium,
       click.source,
       click.OS                                                               AS device_category,
       click.event_name                                                       AS event_label,
       CAST(NULL AS STRING)                                                   AS event_category,
       CASE
           WHEN REGEXP_CONTAINS(click.region, 'MOSOBL') THEN 'MOS'
           WHEN REGEXP_CONTAINS(click.region, 'LENOBL') THEN 'SP'
           ELSE NULL
           END                                                                AS click_region,
       call.city                                                              AS call_region,
       click.newBuilding_name                                                 AS click_nb_name
FROM `myproject.dataset.lost_calls` as call
LEFT JOIN `myproject.dataset.call_track` as click
  ON TIMESTAMP(call.date_time) > TIMESTAMP(click.event_timestamp)
  AND TIMESTAMP(click.event_timestamp) > TIMESTAMP_SUB(TIMESTAMP(call.date_time), INTERVAL 2 HOUR)
WHERE click.medium NOT LIKE 'test%'
  AND call.partner NOT LIKE '%VAS%'

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