BigQuery扩大日期范围后contact_support计数未变化问题排查

问题出在子查询的WHERE子句逻辑运算符优先级错误。AND的优先级高于OR，导致你的条件被解析为：

• （日期在指定范围内 并且 event_name是page_view）
• 或者 event_name是contact_support

这意味着所有contact_support事件都会被选中，完全不受日期范围的限制——不管日期是否在你设置的_TABLE_SUFFIX区间内，只要是contact_support事件就会被纳入统计。所以当你扩大日期范围时，page_view的数量会增加（包含了更多日期的page_view），但contact_support的数量始终是所有日期的该事件总数，自然不会变化。

修正后的SQL #

给event_name的判断加上括号，明确逻辑分组：

SELECT
page_location,
COUNT(IF(event_name = "page_view", event_name, NULL)) as page_view,
COUNT(IF(event_name = "contact_support", event_name, NULL)) as contact_support,
FROM (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    event_name,
  FROM
    `blueray-cargo.analytics_111111111111.events_*`
  WHERE 
    _TABLE_SUFFIX BETWEEN "20230411" AND "20230427"
    AND (event_name = "page_view" OR event_name = "contact_support") -- 新增括号明确逻辑
)
WHERE page_location = "https://blueraycargo.id/"
GROUP BY page_location

简化写法（可选） #

用IN运算符替代OR，让逻辑更清晰：

SELECT
page_location,
COUNT(IF(event_name = "page_view", event_name, NULL)) as page_view,
COUNT(IF(event_name = "contact_support", event_name, NULL)) as contact_support,
FROM (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    event_name,
  FROM
    `blueray-cargo.analytics_111111111111.events_*`
  WHERE 
    _TABLE_SUFFIX BETWEEN "20230411" AND "20230427"
    AND event_name IN ("page_view", "contact_support")
)
WHERE page_location = "https://blueraycargo.id/"
GROUP BY page_location

修改后，日期范围会同时作用于两种事件，扩大日期区间时，contact_support的数量会根据新增日期内的事件数正常变化。

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