首页 文章

用group by，2 having和where子句加入4个表

提问于
浏览 次
-1

我有数据库包含4个表：

  • 个用户（id，"name"，姓氏，生日）

  • 友谊（userid1，userid2，"timestamp"）

  • 个帖子（id，userid，"text"，"timestamp"）

  • 喜欢（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行 . 我不知道为什么 . 将感谢可能的帮助 .

postgresql join group-by having having-clause

2 回答

  • 0

    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 for date 做范围时不太好 .

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

    会给你一个月的整月 .

    回复于
  • 1

    试试下面的！使用“count（f.userid1）> 3”的问题在于，如果用户具有例如2个朋友和6个帖子以及3个喜欢他们将获得2 x 6 = 12行，因此12个记录具有非空f.userid1 . 通过计算不同的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);
    回复于

相关问题