将4个表与groupby、2个具有和where子句连接起来

Question

我的数据库由4个表组成：

1. 用户(id，"name"，姓氏，出生日期(
2. 友谊(userid1，userid2，"时间戳"(
3. posts(id，userid，"text"，"timestamp"(
4. 点赞(postid、userid、"timestamp"(

我需要在2018年1月内获得一组拥有3个以上友谊的唯一用户名的结果集，并且他们每个"帖子"的平均点赞数在[10；35(范围内。

我为第一步写了这样的声明：

select  distinct u."name"
from users u
join friendships f on u.id = f.userid1
where f."timestamp" between '2018-01-01'::timestamp and '2018-01-31'::timestamp
group by u.id
having count(f.userid1) > 3;

它运行良好，返回3行。但当我以这种方式添加第二部分时：

select  distinct u."name"
from users u
join friendships f on u.id = f.userid1
join posts p on p.userid = u.id
join likes l on p.id = l.postid
where f."timestamp" between '2018-01-01'::timestamp and '2018-01-31'::timestamp
group by u.id
having count(f.userid1) > 3 
and ((count(l.postid) / count(distinct l.postid)) >= 10 
and (count(l.postid) / count(distinct l.postid)) < 35);

我疯狂地排了94排。我不知道为什么。将感谢可能的帮助。

Answer 1

u.name中不需要distinct，因为聚合会删除重复项。

select
u."name"
from 
users u
inner join friendships f on u.id = f.userid1
inner join posts p on u.id = p.userid
inner join likes l on p.id = l.postid
where 
f."timestamp" >= '2018-01-01'::timestamp 
and f."timestamp" < '2018-02-01'::timestamp
group by 
u."name"
having 
count(distinct f.userid1) > 3 
and ((count(l.postid) / count(distinct l.postid)) >= 10 
and (count(l.postid) / count(distinct l.postid)) < 35);

如评论所述。当你用between代替date进行测距时，这不是一个好主意。

f."timestamp" >= '2018-01-01'::timestamp 
and f."timestamp" < '2018-02-01'::timestamp

会给你一个完整的一月。

Answer 2

尝试以下操作！使用"count(f.userid1(>3"的问题是，如果一个用户有2个朋友、6个帖子和3个赞，他们将得到2 x 6=12行，因此有12条非空f.userid的记录。通过计算不同的f.userid2，你可以计算不同的朋友。用于筛选的其他计数也出现了类似的问题。

select  u."name"
from users u
join friendships f on u.id = f.userid1
join posts p on p.userid = u.id
left join likes l on p.id = l.postid
where f."timestamp" > '2018-01-01'::timestamp and f."timestamp" < '2018-02-01'::timestamp
group by u.id, u."name"
having
--at least three distinct friends
count( distinct f.userid2) > 3 
--distinct likes / distinct posts
--we use l.* to count distinct likes since there's no primary key
and ((count(distinct l.*) / count(distinct p.id)) >= 10 
and ((count(distinct l.*) / count(distinct p.id)) < 35);