redshift sql中的过滤器

Question

我在prostagesql中有以下查询

SELECT s_campaign_id,  COUNT(b_entry_type_tag)::decimal * 1 AS tags,COUNT(b_entry_type_cnv) AS conversions,
COALESCE ( SUM ( f_revenue ) FILTER(WHERE d_gen_date>='2020-12-01'AND
d_gen_date<='2020-12-01'), 0 ) AS revenue2 FROM tbl_reports WHERE
(d_gen_date>='2021-01-18' AND d_gen_date<='2021-01-24') GROUP BY s_campaign_id

，但在红移，它将不识别过滤器，我得到语法错误这个查询如何与红移工作?

Answer 1

用CASE表达式代替SUM:

SELECT
s_campaign_id,
COUNT(b_entry_type_tag)::decimal * 1 AS tags,
COUNT(b_entry_type_cnv) AS conversions,
SUM(CASE WHEN d_gen_date BETWEEN '2020-12-01' AND '2020-12-01'
THEN f_revenue ELSE 0 END) AS revenue2
FROM tbl_reports
WHERE
d_gen_date BETWEEN '2021-01-18' AND '2021-01-24'
GROUP BY
s_campaign_id;