时间戳

首页 > 解决方案 > 比较每周加入的两个表的计数 - SQL

sql - 比较每周加入的两个表的计数 - SQL

问题描述

我是 SQL 的新手。我有两张桌子。我想计算第一件事每周发生的次数，第二件事每周发生另一件事的次数，然后比较它们。我已经有了在两个单独的图表中计数的代码，但似乎无法加入它们。

我的第一个计数：

select 
  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 week
order by week asc

我的第二次计数

select 
  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 week
order by count_training_meetings desc

我试图将第一个代码加入到第二个代码中，并在一周内加入，但我似乎无法完成这项工作。

任何想法？

标签： sqljoinperiscope

解决方案

不确定 periscope 是否允许full join，但如果您的第一个数据集（查询）中有几个星期没有出现在第二个数据集（查询）中，反之亦然，您应该使用此运算符来检索所有内容。

coalesce打算获取它识别为非空的第一个值。

在标准sql中，应该是这样的

select
  coalesce(q1.week, q2.week) as week,
  count1,
  count2
from 
  (
    select 
      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 week
  ) q1
  full join
  (
    select 
      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 week
  ) q2
    on q1.week = q2.week

正如我在之前的评论中告诉你的那样，如果你的数据中存在不同年份的几周，那么混合它们可能是错误的，但这只是一个建议

推荐阅读