BigQuery跨多数据集动态日期查询及UNION ALL优化与日期失效问题求助

为什么你的日期范围没生效？ #

你当前的查询里，WHERE日期过滤只加在了最后一个UNION ALL分支上，前两个数据集的SELECT语句没有任何日期限制，所以这两个数据集会返回所有ga_sessions_20*表的全部数据，直接导致整个查询的动态日期范围失效。

快速修复：给每个UNION分支添加过滤 #

如果暂时不想重构，可以给每个UNION ALL的SELECT都加上日期过滤，确保所有数据集都只拉取指定日期范围的数据：

SELECT Date, LOWER(hits.page.hostname) AS site, IFNULL(COUNT(VisitId),0) AS sessions, IFNULL(SUM(totals.transactions),0) AS orders, 
IFNULL(ROUND(SUM(totals.transactions)/COUNT(VisitId),4),0) AS conv_rate,
CASE 
  WHEN ( channelGrouping LIKE "Organic Search" OR trafficSource.source LIKE "com.google.android.googlequicksearchbox") AND trafficSource.source LIKE "%google%" 
  THEN "Organic Search - Google" 
  ELSE "Other" 
END AS Channel
FROM (
  SELECT * FROM `xxx.43786551.ga_sessions_20*`
  WHERE PARSE_DATE('%Y%m%d',_TABLE_SUFFIX) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  UNION ALL
  SELECT * FROM `xxx.43786097.ga_sessions_20*`
  WHERE PARSE_DATE('%Y%m%d',_TABLE_SUFFIX) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  UNION ALL
  SELECT * FROM `xxx.43786092.ga_sessions_20*`
  WHERE PARSE_DATE('%Y%m%d',_TABLE_SUFFIX) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
CROSS JOIN UNNEST (hits) AS hits
WHERE totals.visits = 1 AND hits.isEntrance IS TRUE
GROUP BY Date, channel, site
ORDER BY sessions DESC

注意：我去掉了GROUP BY里的hits.isEntrance，因为你已经在WHERE里过滤了hits.isEntrance IS TRUE，分组里保留它没有意义，还会导致结果冗余。

更优雅的方案：用数据集通配符避免重复UNION ALL #

既然所有数据集schema一致，我们可以利用BigQuery的数据集通配符和元数据字段_dataset_id来简化查询，让复杂的分类逻辑只写一次：

WITH filtered_ga_data AS (
  SELECT 
    Date,
    VisitId,
    totals,
    channelGrouping,
    trafficSource,
    hits,
    _dataset_id AS source_dataset -- 可选，用来标记数据来自哪个数据集
  FROM `xxx.*.ga_sessions_20*`
  WHERE 
    -- 指定要查询的目标数据集
    _dataset_id IN (
      'xxx.43786551',
      'xxx.43786097',
      'xxx.43786092'
    )
    -- 动态日期范围过滤
    AND PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    AND totals.visits = 1
)
SELECT 
  Date,
  LOWER(hits.page.hostname) AS site,
  IFNULL(COUNT(VisitId), 0) AS sessions,
  IFNULL(SUM(totals.transactions), 0) AS orders,
  IFNULL(ROUND(SUM(totals.transactions)/COUNT(VisitId), 4), 0) AS conv_rate,
  -- 复杂分类逻辑仅定义一次，无需重复
  CASE 
    WHEN (channelGrouping LIKE "Organic Search" OR trafficSource.source LIKE "com.google.android.googlequicksearchbox") AND trafficSource.source LIKE "%google%" 
    THEN "Organic Search - Google" 
    ELSE "Other" 
  END AS Channel
FROM filtered_ga_data
CROSS JOIN UNNEST(hits) AS hits
WHERE hits.isEntrance IS TRUE
GROUP BY Date, Channel, site
ORDER BY sessions DESC

这个方案的优势：

• 无重复代码：复杂的Channel分类逻辑、过滤条件只写一次，后续维护只需修改WITH子句
• 扩展性强：新增数据集时，只需在_dataset_id IN (...)里添加新的数据集ID即可
• 性能更优：BigQuery对通配符查询的优化比多个UNION ALL分支更好

如果你的数据集命名有规律（比如都是以43786开头），还可以进一步简化数据集过滤条件：

WHERE _dataset_id LIKE 'xxx.43786%'

这样新增符合命名规则的数据集时，甚至不需要修改查询语句。

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