如何在SQL（Exasol）中基于上月均值与去年同月均值计算打开率、点击率目标并修复1月自连接关联异常

你遇到的核心问题是1月无法匹配去年12月的数据，因为当前的自连接条件只考虑了同一年的上月，没有处理跨年的情况。我们可以通过两种方式修复这个逻辑：

方案1：修改连接条件，覆盖跨年场景 #

直接调整t2（上月数据）的连接条件，增加对1月的特殊处理：当当前月份是1时，匹配去年的12月数据。

修改后的完整查询如下：

select * from (
Select month_col, year_col, t_campaign_cmcategory, t_country, t_brand,
(t2_clicktoopenrate + t3_clicktoopenrate)/2 as target_clicktoopenrate,
(t2_openrate + t3_openrate)/2 as target_openrate
from (
 with CTE as (
 select extract(month from date_col) as month_col,
 extract(year from date_col) as year_col,
 category as t_campaign_cmcategory,
 country as t_country,
 brand as t_brand,
 round(sum(opened)/nullif(sum(delivered),0),3) as OpenRate,
 round(sum(clicked)/nullif(sum(opened),0),3) as ClickToOpenRate
 from public.exasol_last_year_avg
 group by 1, 2, 3, 4, 5
 )
 select t1.month_col, t1.year_col,
 t2.month_col as t2_month_col, t2.year_col as t2_year_col,
 t3.month_col as t3_month_col, t3.year_col as t3_year_col,
 t1.t_campaign_cmcategory, t1.t_country, t1.t_brand,
 t1.OpenRate, t1.ClickToOpenRate,
 t2.OpenRate as t2_OpenRate, t2.ClickToOpenRate as t2_ClickToOpenRate,
 t3.OpenRate as t3_OpenRate, t3.ClickToOpenRate as t3_ClickToOpenRate
 from CTE t1
 -- 修复上月数据的连接条件，处理1月跨年的情况
 left join CTE t2 on 
 (t1.month_col = t2.month_col + 1 and t1.year_col = t2.year_col)
 OR 
 (t1.month_col = 1 and t2.month_col = 12 and t1.year_col = t2.year_col + 1)
 and t1.t_campaign_cmcategory = t2.t_campaign_cmcategory
 and t1.t_country = t2.t_country and t1.t_brand = t2.t_brand
 left join CTE t3 on t1.month_col = t3.month_col and t1.year_col = t3.year_col + 1
 and t1.t_campaign_cmcategory = t3.t_campaign_cmcategory
 and t1.t_country = t3.t_country and t1.t_brand = t3.t_brand
) as target_base
) as final_tbl

关键调整说明： #

原t2的连接条件只适用于非1月的同月跨年，新增的OR分支专门处理：

• 当t1是1月时，匹配t2的12月，且t1的年份比t2大1（即去年的12月）

方案2：使用日期截断函数，更优雅的跨年月匹配 #

这种方法避免硬编码月份判断，通过日期计算直接获取上月的年月，逻辑更简洁且不易出错。

修改后的查询如下：

select * from (
Select 
 extract(month from t1.month_year) as month_col,
 extract(year from t1.month_year) as year_col,
 t1.t_campaign_cmcategory, t1.t_country, t1.t_brand,
(t2_clicktoopenrate + t3_clicktoopenrate)/2 as target_clicktoopenrate,
(t2_openrate + t3_openrate)/2 as target_openrate
from (
 with CTE as (
 -- 直接生成年月的截断日期，方便计算上月
 select date_trunc('month', date_col) as month_year,
 category as t_campaign_cmcategory,
 country as t_country,
 brand as t_brand,
 round(sum(opened)/nullif(sum(delivered),0),3) as OpenRate,
 round(sum(clicked)/nullif(sum(opened),0),3) as ClickToOpenRate
 from public.exasol_last_year_avg
 group by 1, 2, 3, 4
 )
 select 
 t1.month_year,
 t2.month_year as t2_month_year,
 t3.month_year as t3_month_year,
 t1.t_campaign_cmcategory, t1.t_country, t1.t_brand,
 t1.OpenRate, t1.ClickToOpenRate,
 t2.OpenRate as t2_OpenRate, t2.ClickToOpenRate as t2_ClickToOpenRate,
 t3.OpenRate as t3_OpenRate, t3.ClickToOpenRate as t3_ClickToOpenRate
 from CTE t1
 -- 直接匹配上月的年月日期，自动处理跨年
 left join CTE t2 on t1.month_year = t2.month_year + interval '1 month'
 and t1.t_campaign_cmcategory = t2.t_campaign_cmcategory
 and t1.t_country = t2.t_country and t1.t_brand = t2.t_brand
 -- 匹配去年同月的年月日期
 left join CTE t3 on t1.month_year = t3.month_year + interval '1 year'
 and t1.t_campaign_cmcategory = t3.t_campaign_cmcategory
 and t1.t_country = t3.t_country and t1.t_brand = t3.t_brand
) as target_base
) as final_tbl

关键调整说明： #

1. 在CTE中用date_trunc('month', date_col)生成每个记录对应的当月第一天（比如2021-01-01），作为month_year字段。
2. 连接t2时，直接用t1.month_year = t2.month_year + interval '1 month'，这个计算会自动处理跨年：比如2021-01-01的上月就是2020-12-01，完美匹配你的需求。
3. 连接t3（去年同月）时，也可以用t1.month_year = t3.month_year + interval '1 year'，替代原来的月份和年份分开判断的逻辑，更简洁。

两种方案都能解决你1月数据为NULL的问题，方案2更推荐，因为它不需要硬编码月份值，适应性更强，后续维护也更简单。

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