Amazon Redshift SQL UNION拼接三类广告支出查询报错问题求助

核心问题点 #

• 语法错误：CAST函数括号不匹配
  你贴的Google Ads、Bing Ads查询部分的SUM聚合转integer的代码多写了一个右括号，错误写法为CAST(SUM(cost)) AS integer)、CAST(SUM(spend)) AS integer)，正确逻辑是把SUM的结果放在CAST的参数里，应该调整为CAST(SUM(cost) AS integer)、CAST(SUM(spend) AS integer)。
• 子查询非法使用ORDER BY
  UNION/UNION ALL拼接的各个独立子查询中，不能单独使用ORDER BY（除非搭配LIMIT限制返回行数），这类排序没有实际意义，还会触发Redshift语法报错。全局排序只需要在整个SQL的最末尾加一次ORDER BY即可。
• 冗余的重复类型转换（可选优化）
  内层facebook子查询已经把ad_customer_group、ad_spend做了varchar(3)、integer的类型转换，外层查询不需要再重复CAST，可直接使用简化代码。

修复后完整代码 #

SELECT 
  inside.date AS "date", 
  CAST('facebook' AS varchar(20)) AS "ad_channel", 
  inside.ad_customer_group AS "ad_customer_group", 
  SUM(inside.ad_spend) AS "ad_spend"
FROM
(
  SELECT 
    CAST(TRUNC(FP.date) AS date) AS "date", 
    CASE WHEN UPPER(SUBSTRING(adset_name,12,3)) = 'RTN' THEN 'RTN' ELSE 'ACQ' END AS "ad_customer_group", 
    CAST(SUM(spend) AS integer) AS "ad_spend"
  FROM public.fact_facebook_paid_placements FP
  GROUP BY CAST(TRUNC(FP.date) AS date), FP.adset_name
) AS "inside"
GROUP BY inside.date, inside.ad_customer_group

UNION ALL

SELECT 
  CAST(TRUNC(timestamp) AS date) AS "date", 
  CAST('google_ads' AS varchar(20)) AS "ad_channel", 
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group", 
  CAST(SUM(cost) AS integer) AS "ad_spend"
FROM public.fact_adwords_ads
GROUP BY CAST(TRUNC(timestamp) AS date)

UNION ALL

SELECT 
  CAST(TRUNC(timestamp) AS date) AS "date", 
  CAST('bing_ads' AS varchar(20)) AS "ad_channel", 
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group", 
  CAST(SUM(spend) AS integer) AS "ad_spend"
FROM public.fact_bing_ad_performance
GROUP BY CAST(TRUNC(timestamp) AS date)

-- 全局排序仅需在末尾声明一次
ORDER BY "date" DESC

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