Postgresql查询 - 根据子查询结果排序

Question

Postgresql查询 - 根据子查询结果排序

9

我有一个查询，想要选择所有喜欢给定艺术家的用户。还有一些其他关于国家等方面的WHERE条件。以下是模式的样子。

          users                     favourite_artists             artists

+----------+------------+    +-----------+------------+    +--------+--------+
|    id    |  country   |    |  user_id  |  artist_id |    |  id    |  name  |
+----------+------------+    +-----------+------------+    +--------+--------+
|     1    |     gb     |    |     1     |      6     |    |   1    |  Muse  |
|     2    |     gb     |    |     1     |      5     |    |   2    |  RATM  |
|     3    |     us     |    |     1     |      3     |    |   3    |  ABBA  | 
|     4    |     us     |    |     2     |      3     |    |   4    |   U2   |
+----------+------------+    +-----------+------------+    +--------+--------+

我想按他们喜欢的艺术家数量对它们进行排序。我还想包括不喜欢任何艺术家但符合 WHERE 条件的用户。预期结果集应如下所示。

+--------+---------------+----------------+
|   id   |    country    |   match_count  |
+--------+---------------+----------------+
|    6   |      gb       |       4        |
|    9   |      gb       |       4        |
|    2   |      gb       |       3        |
|    1   |      gb       |       2        |
|    5   |      gb       |       0        |
|    4   |      gb       |       0        |
+--------+---------------+----------------+

我一直在尝试使用子查询来获取匹配计数并按照该计数排序，但是它的性能相当慢，所以我认为肯定有更好的方法。

   SELECT users.id, users.country
   (SELECT COUNT(*) FROM favourite_artists 
    WHERE user_id = users.id AND artist_id IN (1,3,4,9)) AS match_count        
   FROM "users" 
   WHERE users.country = 'gb'
   ORDER BY match_count DESC;

我正在使用Postgresql 9.0.7，有什么想法吗？

- johnnymire

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bohemian · Accepted Answer

你的查询对于users表中的每一行都会执行一个子查询。这种查询通常被称为“相关子查询”，它们的性能相当糟糕。

相反，你应该使用连接查询：

SELECT users.id, users.country, count(artist_id) as match_count
FROM users
LEFT JOIN favourite_artists ON user_id = users.id AND artist_id IN (1,3,4,9)
WHERE users.country = 'gb'
GROUP BY 1, 2
ORDER BY 3 DESC;

假设您在favourite_artists(user_id)上有一个索引 - 或者更好的是一个多列索引favourite_artists(user_id, artist_id)，那么这个查询将更有效地获取连接行。