使用4个表获取数据

Question

我有以下表格：

trendingtopic
  id
  name

trendingtopiccycle
  id
  trendingtopic_id(FK)

tweet
  id 
  text
  tt_cycle_id(FK)
  user_id(FK)

user
  id
  rank

我想知道是否有一种方法可以有效地检索每个热门话题中排名较高的用户的推文。

下面是我当前的查询：

  SELECT tt."name",
  MAX(tu."actual_rank") AS rank,tu."name"
  FROM "trendingtopics_twitteruser" tu
  LEFT JOIN "trendingtopics_tweet" tw ON tu."id" = tw."user_id" 
  LEFT JOIN "trendingtopics_trendingtopiccycle" ttc ON tw."tt_cycle_id" = ttc."id"
  LEFT JOIN "trendingtopics_trendingtopic" tt ON ttc."tt_id" = tt."id" 
  GROUP BY tt."name"

它不工作是因为，根据Postgresql:‘"tu.name“必须出现在GROUP BY子句中或用于聚合函数’。

如果我将tu."name“添加到GROUP BY子句，我最终得到的结果如下所示：

"106 & Park";0;"910Prince"
"106 & Park";0;"ActressAlexiss"
"106 & Park";0;"AmeliaThirlwall"
"106 & Park";0;"_ArielIvy"
"106 & Park";0;"_AyyeVce"
"106 & Park";0;"barcastuff"
"106 & Park";0.42141;"pareexo"
"106 & Park";0.0363;"khleosupporters"
"#15ThingsAboutMyCrush";0;"_ALoyalLady"
"#15ThingsAboutMyCrush";0.22275;"AmberrNikole"
"#15ThingsAboutMyCrush";0;"a_paigeturner"
"#15ThingsAboutMyCrush";0.33942018;"ArleneAndrea_xo"

但我想得到的是：

"106 & Park";0.42141;"pareexo"
"#15ThingsAboutMyCrush";0.33942018;"ArleneAndrea_xo"

Answer 1

实际上，使用子查询中的窗口函数可以很容易地做到这一点：

SELECT t."topic_name", t."rank", t."user_name"
FROM 
    (SELECT tt."name" AS topic_name, tu."actual_rank" AS rank, tu."name" AS user_name,
        row_number() OVER (PARTITION BY tt."name" ORDER BY tu."actual_rank" DESC) user_rank
    FROM "trendingtopics_trendingtopic" tt
    LEFT JOIN "trendingtopics_trendingtopiccycle" ttc ON ttc."tt_id" = tt."id"
    LEFT JOIN "trendingtopics_tweet" tw ON tw."tt_cycle_id" = ttc."id"
    LEFT JOIN "trendingtopics_twitteruser" tu ON tu."id" = tw."user_id") t
WHERE t."user_rank" = 1

此外，我对连接进行了重新排序，以便您从热门主题开始，而不是从twitter用户开始。由于您正在尝试为热门主题获取排名最高的用户，因此(至少对我而言)更有意义的做法是将其作为源表。

有关窗口函数和分区的更多信息，请访问此处：http://www.postgresql.org/docs/8.4/static/tutorial-window.html

Answer 2

最快的解决方案可能是使用DISTINCT ON而不是窗口函数。

SELECT DISTINCT ON (tt."name")
    tt."name", tu."actual_rank" AS rank, tu."name"
  FROM "trendingtopics_twitteruser" tu
  LEFT JOIN "trendingtopics_tweet" tw ON tu."id" = tw."user_id" 
  LEFT JOIN "trendingtopics_trendingtopiccycle" ttc ON tw."tt_cycle_id" = ttc."id"
  LEFT JOIN "trendingtopics_trendingtopic" tt ON ttc."tt_id" = tt."id" 
  ORDER BY tt."name", tu."actual_rank" DESC;

顺便说一句，这是未经测试的，因为您没有提供用于实际创建和加载您所描述的表的语句。如果你确实在问题中提供了这类东西，人们通常会在发帖前测试他们的答案，你就不会有愚蠢的错误需要处理。

Answer 3

SELECT tt."name",
  MAX(tu."actual_rank") AS rank,MAX(tu."name") as name_1
  FROM "trendingtopics_twitteruser" tu
  LEFT JOIN "trendingtopics_tweet" tw ON tu."id" = tw."user_id" 
  LEFT JOIN "trendingtopics_trendingtopiccycle" ttc ON tw."tt_cycle_id" = ttc."id"
  LEFT JOIN "trendingtopics_trendingtopic" tt ON ttc."tt_id" = tt."id" 
  GROUP BY tt."name"