sql - 计算关联数

Question

我想统计每个用户关闭了多少个对话，以及每个用户有多少个消息写的。

一个用户有很多对话

一个对话有很多消息

一个消息可以属于一个用户

这是我得到的查询

select a.id, u.display_name, count(c.id) as closed, count(m.id) as replied
from apps a
left join app_users au on au.app_id = a.id
left join users u on u.id = au.user_id
left join conversations c on c.app_id = a.id and c.closed_by_id = u.id
left join messages m on m.conversation_id = c.id and m.user_id = u.id
group by a.id, u.id
order by closed desc

当我不加入消息而只是计算关闭的对话时，它工作正常。加入消息时，closed 和 replied 列是完全相同的数字(并且两者也不正确)

有什么想法吗？

Answer 1

加入前可以在子查询中进行计数:

select a.id, u.display_name, c.closed, m.replied
from apps a
left join app_users au on au.app_id = a.id
left join users u on u.id = au.user_id
left join lateral (
    select id, count(*) as closed
    from conversations
    where closed_by_id = u.id) c on c.app_id = a.id
left join lateral (
    select count(*) as replied
    from messages
    where user_id = u.id) m on m.conversation_id = c.id
order by c.closed desc;