代码之家 › 专栏 › 技术社区 › Nicolas Blanco

如何根据计算字段连接两个表?

postgresql sql

Nicolas Blanco · 技术社区 · 4 年前

我有两个SQL查询,它们输出相同类型的输出,并且具有相同的分组和顺序:

select date_trunc('month', inserted_at)::date as date, count(id) from payment_logs where payment_logs.event_name = 'subscription_created' group by date order by date desc;

select date_trunc('month', inserted_at)::date as date, count(id) from users group by date order by date desc;

我想根据计算日期字段(即月份)将这两个结果连接起来,并得到一个包含3列的结果:date、count_users和count_payment_logs。

我怎样才能做到这一点?谢谢

1 回复 | 直到 4 年前

SwaD 4 年前

像这样的

select plog.date as odata, usr.cntusr, plog.cntlog 
from (
    select date_trunc('month', inserted_at)::date as date, count(id) cntlog
    from payment_logs 
    where payment_logs.event_name = 'subscription_created' 
    group by date order by date desc
) plog
join (
    select date_trunc('month', inserted_at)::date as date, count(id) cntusr
    from users 
    group by date 
) usr on plog.data = usr.data
order by odata desc

Hambone 4 年前

接受的答案没有错,但我想展示一个替代方案,并添加一些颜色。您也可以使用通用表表达式(CTE)来代替子查询,它提高了可读性,但也具有一些其他功能。以下是使用CTE的示例:

with payments as (
  select
    date_trunc('month', inserted_at)::date as date,
    count(id) as payment_count
  from payment_logs
  where
    event_name = 'subscription_created'
  group by date
),
users as (
  select
    date_trunc('month', inserted_at)::date as date,
    count(id) as user_count
  from users
  group by date
)
select
  p.date, p.payment_count, u.user_count
from
  payments p
  join users u on
    p.date = u.date
order by
  p.date desc

在我看来,抽象更整洁,使代码更易于遵循(从而维护)。

其他注意事项:

这个 order by 是昂贵的,并且您可以在每个子查询/CTE中避免它,因为它无论如何都是在最后完成的。不管怎样,子查询中的那些查询都会被您在主查询中所做的操作所破坏,所以只需完全省略它们。您的结果不会有什么不同,您的查询也会更有效率。

在这个例子中,你可能没有错过任何月份,但有可能。。。特别是如果您将这个概念扩展到将来的查询中。在这种情况下,您可能需要考虑完全的外部联接,而不是内部联接(您有几个月出现在用户中,但可能不在付款中,反之亦然):

select
  coalesce (p.date, u.date) as date,
  p.payment_count, u.user_count
from
  payments p
  full outer join users u on
    p.date = u.date
order by
  1 desc

CTE相对于子查询的另一个好处是可以重用它们。在这个例子中,我想模仿完整的外部联接概念,但有一个额外的转折点——我每月都有来自另一个表的数据,我希望在查询中得到这些数据。CTE让我可以随心所欲地使用CTE进行“支付”和“用户”。在这里,我在all_dates CTE中使用它们,并在主查询中再次使用它们。通过创建“all_dates”,我现在可以使用左联接,避免联接中奇怪的合并(不是错误的,只是丑陋)。

with payments as (
  -- same as above
),
users as (
  -- same as above
),
all_dates as (
  select date from payments  -- referred to payments here
  union
  select date from users
)
select
  a.date, ac.days_in_month, p.payment_count, u.user_count
from
  all_dates a
  join accounting_calendar ac on
    a.date = ac.accounting_month
  left join payments p on -- referred to it here again, same CTE
    a.date = p.date
  left join users u on
    a.date = u.date
order by
  p.date desc

关键是您可以重用CTE。

最后一个优点是,您可以声明CTE物化或非物化(默认)。物化CTE本质上将预处理和存储结果,在某些情况下可能具有更好的性能。另一方面,非物化的on将模拟标准子查询,这很好,因为您可以将where子句条件从查询外部传递到查询内部。