SQL如何按周关联两张表的计数结果进行数据对比
解决方法
核心思路是将两段独立的统计逻辑封装为公用表达式(CTE),再按周字段关联即可,同时处理你原有代码中的小问题:
- 首先修正第二段查询的语法错误:原有
order by count_training_meetings desc中的字段名不存在,需删除或替换为正确字段count1 - 用FULL OUTER JOIN关联两个统计结果,保证两边的周数据都不会丢失
- 空值处理:对无数据的周的统计值默认补0,更便于对比
完整可运行代码
WITH t1_stats AS ( -- Table1统计逻辑 select date_part('year', Table1.activity_date at time zone 'utc' at time zone 'Europe/Paris') as year, date_part('week', Table1.activity_date at time zone 'utc' at time zone 'Europe/Paris') as week, count(distinct Table1.activity_id) as count1 from Table1 left join X on Y1 = Y2 left join W on A1 = A2 and B1 = B2 where activity_dimensions.type in ('Training') and acquisition_opportunity_dimensions.product_family = 'EHR' and activity_dimensions.country = 'fr' and activity_date::date >= date_trunc('[aggregation]', [daterange_start]) and activity_date::date <= [daterange_end] and activity_date::date <= current_date group by year, week ), t2_stats AS ( -- Table2统计逻辑 select date_part('year', Table2.date at time zone 'utc' at time zone 'Europe/Paris') as year, date_part('week', Table2.date at time zone 'utc' at time zone 'Europe/Paris') as week, count(Table2.issue_solved) as count2 from Table2 where date is not null group by year, week ) -- 关联两个统计结果 SELECT COALESCE(t1.year, t2.year) as stat_year, COALESCE(t1.week, t2.week) as stat_week, COALESCE(t1.count1, 0) as table1_weekly_count, COALESCE(t2.count2, 0) as table2_weekly_count FROM t1_stats t1 FULL OUTER JOIN t2_stats t2 ON t1.year = t2.year AND t1.week = t2.week ORDER BY stat_year DESC, stat_week DESC
额外注意事项
- 跨年统计适配:代码中额外增加了年份字段关联,避免不同年份的同一周数被错误合并,如果你只统计单年内的数据可以删除年份相关的逻辑
- 日期过滤对齐:如果需要只对比同一个时间范围内的周数据,可以在最后的查询中加时间过滤条件,或者分别在两个CTE中加上统一的日期范围限制
- 时区一致性:两段统计的时区转换逻辑完全一致,保证周数计算规则统一,不会出现关联时周数对不上的问题
内容的提问来源于stack exchange,提问作者ClaudeLag
相关产品推荐
相关产品推荐

