如何在BigQuery中基于日期列生成去年日期列以对比GA访问量

错误原因 #

你遇到的DATE_SUB类型不匹配错误，是因为你的date列是字符串格式（如'20230110'），但DATE_SUB函数仅支持DATE/DATETIME/TIMESTAMP类型作为输入参数。必须先将字符串日期转换为DATE类型，才能使用日期计算函数。

完整解决方案 #

以下SQL不仅修正了日期类型问题，还通过关联查询获取到对应日期的去年访问量，最终输出包含「今年日期」「今年访问量」「去年日期」「去年访问量」的结果：

WITH
  date_range AS (
    SELECT
      '20230110' AS start_date,
      '20230126' AS end_date
  ),
  this_year_stats AS (
    SELECT
      date AS current_date,
      SUM(totals.visits) AS current_sessions,
      -- 将字符串日期转DATE后计算去年日期，再转回字符串格式匹配原表
      FORMAT_DATE('%Y%m%d', DATE_SUB(PARSE_DATE('%Y%m%d', date), INTERVAL 364 DAY)) AS last_year_date
    FROM
      `my_table`,
      date_range
    WHERE
      date >= start_date
      AND date <= end_date
    GROUP BY
      date
  ),
  last_year_stats AS (
    SELECT
      date AS last_year_date,
      SUM(totals.visits) AS last_year_sessions
    FROM
      `my_table`,
      date_range
    WHERE
      -- 计算去年对应的统计范围：今年起止日期各减364天
      date >= FORMAT_DATE('%Y%m%d', DATE_SUB(PARSE_DATE('%Y%m%d', start_date), INTERVAL 364 DAY))
      AND date <= FORMAT_DATE('%Y%m%d', DATE_SUB(PARSE_DATE('%Y%m%d', end_date), INTERVAL 364 DAY))
    GROUP BY
      date
  )
SELECT
  t.current_date,
  t.current_sessions,
  t.last_year_date,
  -- 处理去年无数据的情况，默认显示0
  COALESCE(l.last_year_sessions, 0) AS last_year_sessions
FROM
  this_year_stats t
LEFT JOIN
  last_year_stats l
ON
  t.last_year_date = l.last_year_date

关键说明 #

1. 日期类型转换：

   • PARSE_DATE('%Y%m%d', date)：将YYYYMMDD格式的字符串日期转换为BigQuery的DATE类型，满足DATE_SUB的参数要求。
   • FORMAT_DATE('%Y%m%d', ...)：计算完成后将DATE类型转回原字符串格式，方便与原表的date列关联。

2. 关联去年数据：

   • 通过两个WITH子句分别统计今年和去年的访问量，再通过last_year_date字段左连接，确保所有今年的日期都能显示，即使去年对应日期无访问量（用COALESCE填充0）。

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