BigQuery查询近3个月营收SQL报end of script语法错误求助

问题基本信息 #

• 统计基准日期：2022年4月15日
• 统计需求：查询过去3个月的营收数据，预期返回3个字段：Date（标准日期格式）、Product Categories（产品分类）、Revenue（营收金额）
• 运行报错信息：Syntax error: Expected "(" or "," or keyword SELECT but got end of script at [18:6]

原代码错误点梳理 #

• 核心语法错误：WITH子句定义完revenue2这个CTE后，没有写最终的SELECT查询语句，SQL解析器读到脚本末尾找不到可执行的查询逻辑，直接抛出脚本意外结束的错误，就是本次运行提示的报错。
• 标识符引用错误：BigQuery中带项目ID的公开表名需要用反引号`包裹，原代码用单引号包裹表名，会被解析为字符串而非表名，后续运行也会报错。
• 常量写法错误：where lower(b.status) =complete中的complete是字符串常量，没有加单引号，会被SQL解析为字段名，触发字段不存在的报错。
• 日期逻辑错误：
  • 原代码用format_date('%y,%m', b.created_at)生成的是22,04这类年月拼接的字符串，既不符合要求的标准日期格式，也无法和日期类型的过滤条件做匹配。
  • 日期过滤范围完全不符合需求：需求是查2022年4月15日往前推3个月的数据，原代码写的between 2022-02-01 and 2022-02-15不仅值没加引号、类型不匹配，时间范围也只覆盖了2月半个月，和需求差很远。
• 计算逻辑错误：原代码用sum(b.sale_price)*count(b.order_id)计算销售额属于重复计算，order_items表中每一条记录对应一个订单内的单个商品，直接对sale_price求和就是对应维度的总销售额，乘订单数会让结果成倍放大。
• 输出逻辑错误：原代码用按产品分类排序的窗口函数算累计值，既没有输出需求要求的三个字段，计算结果也不是对应维度的当期营收，不符合需求。

修正后可直接运行的代码 #

WITH revenue_calc AS (
  SELECT
    DATE(b.created_at) AS Date, -- 直接转换为标准日期格式
    a.category AS `Product Categories`,
    SUM(b.sale_price) AS revenue_raw
  FROM `bigquery-public-data.thelook_ecommerce.products` a
  JOIN `bigquery-public-data.thelook_ecommerce.order_items` b
    ON b.product_id = a.id
  WHERE LOWER(b.status) = 'complete' -- 字符串常量加单引号
    -- 过滤基准日往前3个月的数据
    AND DATE(b.created_at) BETWEEN DATE_SUB(DATE('2022-04-15'), INTERVAL 3 MONTH) AND DATE('2022-04-15')
  GROUP BY 1, 2 -- 按日期、产品分类聚合，匹配输出粒度
)
SELECT
  Date,
  `Product Categories`,
  ROUND(revenue_raw, 2) AS Revenue
FROM revenue_calc
ORDER BY Date, `Product Categories`

修正说明 #

• 补全CTE定义后缺失的最终查询语句，解决脚本意外结束的核心语法报错
• 统一修正标识符、常量的引用格式，避免解析错误
• 调整日期生成、过滤逻辑，完全匹配近3个月的时间范围要求，输出标准日期格式
• 修正营收计算逻辑，去掉冗余的乘法计算，保证金额准确
• 移除不需要的冗余字段、不符合需求的累计窗口计算，输出字段和需求完全对齐

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