Amazon Redshift SQL - Unions

Question

我在合并这三个花费查询时遇到了问题，尽管尝试了几种不同的修复方法，包括这个版本转换所有变量。

SELECT inside.date AS "date", CAST('facebook' AS varchar(20)) AS "ad_channel", 
CAST(inside.ad_customer_group AS varchar(3)) AS "ad_customer_group", CAST(SUM(inside.ad_spend) AS integer) 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
ORDER BY CAST(TRUNC(FP.date) AS date) DESC
) AS "inside"
GROUP BY inside.date, inside.ad_customer_group
ORDER BY inside.date DESC
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)
ORDER BY CAST(TRUNC(timestamp) AS date) DESC
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 CAST(TRUNC(timestamp) AS date) DESC

Answer 1

这将是一个很大的帮助，如果你张贴一个错误，你得到的查询。但是我可以从你的查询中假设的是，你已经得到了关于GROUP BY语句的错误，因为你只按第一列分组(在其中一个查询中是第二列)，并且没有在任何聚合函数中使用组中的另一个

那么，我猜这个查询会做你想要的

(
SELECT
CAST(TRUNC(FP.date) AS date) AS "date",
CAST(CASE
WHEN UPPER(SUBSTRING(adset_name,12,3)) = 'RTN' THEN 'RTN'
ELSE 'ACQ'
END AS varchar(3)) AS "ad_customer_group",
SUM(spend) AS "ad_spend"
FROM public.fact_facebook_paid_placements FP
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)
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",
SUM(cost) "ad_spend"
FROM public.fact_adwords_ads
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)
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", 
SUM(spend) AS "ad_spend"
FROM public.fact_bing_ad_performance
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)