BigQuery SUM窗口函数报错求助：参数类型不匹配问题

问题根源 #

你的CTE名称和其中的利润列名称都是profit，在主查询的SUM(profit)中，BigQuery会优先把profit解析为CTE的别名（代表整个行数据，是一个STRUCT类型），而不是你想要的profit列。这就导致SUM函数接收到了STRUCT类型的参数，不符合它支持的数值类型签名，从而触发报错。

另外你窗口函数里的EXTRACT(MONTH FROM date)和ORDER BY ... date中的date是无效列名，CTE里的日期列实际是order_date，这也是后续会出现的问题。

修复方案 #

有两种简单的修复方式：

方式1：修改CTE名称，避免与列名重复

把CTE的名字改成和列名不冲突的标识，比如profit_data：

WITH profit_data AS
(
  SELECT DATE(o.created_at) AS order_date,
         p.category AS product_categories,
         (o.sale_price - i.cost) AS profit
  FROM `bigquery-public-data.thelook_ecommerce.order_items` AS o
  INNER JOIN `bigquery-public-data.thelook_ecommerce.inventory_items` AS i
  ON o.product_id = i.product_id
  INNER JOIN `bigquery-public-data.thelook_ecommerce.products` AS p
  ON o.product_id = p.id
  WHERE DATE(o.created_at) >= "2022-06-01" AND DATE(o.created_at) <= "2022-08-15"
  ORDER BY 2,1
)

SELECT order_date,
       product_categories,
       SUM(profit) OVER(PARTITION BY product_categories, EXTRACT(MONTH FROM order_date) ORDER BY product_categories, order_date) AS current_mtd
FROM profit_data
ORDER BY 2,1

方式2：明确指定列的来源

在SUM中通过CTE别名明确指向利润列，同时修正日期列的错误引用：

WITH profit AS
(
  SELECT DATE(o.created_at) AS order_date,
         p.category AS product_categories,
         (o.sale_price - i.cost) AS profit
  FROM `bigquery-public-data.thelook_ecommerce.order_items` AS o
  INNER JOIN `bigquery-public-data.thelook_ecommerce.inventory_items` AS i
  ON o.product_id = i.product_id
  INNER JOIN `bigquery-public-data.thelook_ecommerce.products` AS p
  ON o.product_id = p.id
  WHERE DATE(o.created_at) >= "2022-06-01" AND DATE(o.created_at) <= "2022-08-15"
  ORDER BY 2,1
)

SELECT order_date,
       product_categories,
       SUM(profit.profit) OVER(PARTITION BY product_categories, EXTRACT(MONTH FROM order_date) ORDER BY product_categories, order_date) AS current_mtd
FROM profit
ORDER BY 2,1

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