You need to enable JavaScript to run this app. SQL如何按周关联两张表的计数结果进行数据对比
优惠活动
大模型
产品
解决方案
定价
更多

SQL如何按周关联两张表的计数结果进行数据对比

解决方法

核心思路是将两段独立的统计逻辑封装为公用表达式(CTE),再按周字段关联即可,同时处理你原有代码中的小问题:

  1. 首先修正第二段查询的语法错误:原有order by count_training_meetings desc中的字段名不存在,需删除或替换为正确字段count1
  2. 用FULL OUTER JOIN关联两个统计结果,保证两边的周数据都不会丢失
  3. 空值处理:对无数据的周的统计值默认补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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.12 15:09:03