Redshift查询，用于统计用户观看的票证数量，但用户创建的票证除外

Question

我们的票务系统中有两张桌子。在这个系统中，用户可以创建门票，任何数量的用户都可以观看一张或多张门票。

为此，我们设置了2个表：

1. 存储手表信息-watch_table
2. 存储所有权信息-ownership_table

watch_table
ticket_id | watched_by
=======================
t1        | u1
t2        | u2
t2        | u1

ownership_table
ticket_id | owned_by
=====================
t1        | u1
t2        | u2

我想提取每个用户的手表数量。但是，不包括他们自己门票上的手表。

例如，在上面的例子中，输出应该是：

output:
user_id | count
================
u1      | 1
u2      | 0 

我可以使用子查询来创建一个查询。如下所示：

select watched_by as user_id, count(*) from watch_table wt where
(select count(*) from ownership_table where owned_by = wt.watched_by and ticket_id = wt.ticket_id) = 0
group by watched_by

我的问题是，如何使用联接来实现这一点，以及哪一个联接更具性能？

Answer 1

select w.userid, count(o.ticketid)
from watches w left join owners o
on o.ticketid = w.ticketid and o.userid <> w.userid
group by w.userid

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=19901f70822260cc943e55b42c7fbe

Answer 2

根据您的样本数据，您可能可以使用outer join来完成此操作

select t.owned_by as User_Id, Count(w.ticket_id) as count
from ownership_table t 
left join watch_table w on w.watched_by=t.owned_by and w.ticket_Id != t.ticket_Id
group by t.owned_by

另一种选择可能是相关子查询：

select Owned_By as User_Id, (
select Count(*)
from watch_table w
where w.watched_by=t.Owned_by and w.ticket_Id != t.ticket_Id
)
from ownership_table t