mysql - 使用 JOIN 时如何优化 COUNT 个子查询

Question

我正在构建一个查询，用于根据所选 Category 生成 Items 的索引页面，通过计算 的数量按相对受欢迎程度排序>Likes 以及该项目在过去 24 小时内被添加到 List 的次数。查询的单个输入是主要类别 ID。

这涉及到总共 4 个表，其中一个是嵌套集合，所以它并不完全是微不足道的。我通常非常擅长编写相当高效的 SQL，但我正在努力使 JOIN 以我想要的方式工作。

类别

由于类别是嵌套的并且项目被分配给单个类别，因此有必要首先选择查询输入中指定类别下的所有类别。

我正在使用 awesome_nested_set gem 来完成这项工作。它添加了 lft 和 rgt 列，可以轻松地从层次结构中进行选择:

SELECT c2.*
FROM categories c1
JOIN categories c2
    ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
WHERE c1.id = [MAIN CATEGORY ID]

项目

然后扩展上面的内容来选择项目就相当简单了:

SELECT i.*
FROM categories c1
JOIN categories c2
    ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
JOIN items i
    ON i.category_id = c2.id
WHERE c1.id = [MAIN CATEGORY ID]

到此为止一切正常，执行速度也很快。最后要做的事情(当然忽略分页)是对它们进行排序。

人气

项目按受欢迎程度排序。计算项目受欢迎程度的方法是:

(number of likes) + (number of times added to list) * 5

例如如果某个项目已被添加到 32 个列表并被点赞 483 次，则流行度指标将为 643。

根据用户查看的是“所有时间最流行”还是“趋势”，我们可能会将这些指标的计算限制在过去一天发生的点赞/列表。

我原以为这会比较琐碎，但事实并非如此。当您将 COUNT 与 JOIN 一起使用时，显然会出现问题，我需要使用 LEFT JOIN 以防该项目有 0 个赞/列表。

目前工作代码如下:

SELECT
    q.*,
    (q.likes + q.lists * 5) AS popularity
FROM
(
    SELECT
        i.*,
        (SELECT COUNT(*) FROM likes l WHERE i.id = l.item_id AND l.created_at > DATE_SUB(NOW(), INTERVAL 1 day)) AS likes,
        (SELECT COUNT(*) FROM list_items li WHERE i.id = li.item_id AND li.created_at > DATE_SUB(NOW(), INTERVAL 1 day)) AS lists
    FROM categories c1
    JOIN categories c2
        ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
    JOIN items i
        ON i.category_id = c2.id
    WHERE c1.id = 37
) q
ORDER BY popularity

但是，这显然是非常可怕的代码。每个项目都需要进行两个子查询，然后需要将整个项目包装起来以进行一些算术运算(尽管我认为这还不错)。

我已经尝试过以下方法，但由于各种原因它们都不起作用:

SELECT
    i.*,
    (SELECT COUNT(*) FROM likes l WHERE i.id = l.item_id AND l.created_at > DATE_SUB(NOW(), INTERVAL 1 day)) AS likes,
    (SELECT COUNT(*) FROM list_items li WHERE i.id = li.item_id AND li.created_at > DATE_SUB(NOW(), INTERVAL 1 day)) AS lists,
    (likes + lists * 5) AS popularity

出于某种原因，您无法对您选择的其他列进行数学计算。

SELECT
    i.*,
    COUNT(l.id) as likes,
    COUNT(li.id) as lists
FROM categories c1
JOIN categories c2
    ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
JOIN items i
    ON i.category_id = c2.id
LEFT JOIN likes l
    ON l.item_id = i.id
LEFT JOIN list_items li
    ON li.item_id = i.id
WHERE c1.id = 37

出于某种原因，您只能获得一个结果。我不明白这是什么原因。

SELECT
    i.*,
    COUNT(l.id) as likes,
    COUNT(li.id) as lists
FROM categories c1
JOIN categories c2
    ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
JOIN items i
    ON i.category_id = c2.id
LEFT JOIN likes l
    ON l.item_id = i.id
LEFT JOIN list_items li
    ON li.item_id = i.id
WHERE c1.id = 37
GROUP BY i.id

添加 GROUP BY 使所有项目返回，但喜欢/列表数量现在完全错误。我认为这是将它们相加之类的。

基本上，我有点卡住了。上面带有子查询的示例确实 有效，但我认为它的工作方式并不理想。我想让它只与 JOIN 一起工作，但我很难理解如何做。

非常感谢任何帮助:)

Answer 1

执行按 item_id 分组的子查询以获取计数，并针对这些子查询进行 LEFT JOIN。

像这样:-

SELECT
    q.*,
    (q.likes + q.lists * 5) AS popularity
FROM
(
    SELECT
        i.*,
        IFNULL(likes_count, 0) AS likes,
        IFNULL(lists_count, 0) AS lists
    FROM categories c1
    JOIN categories c2
        ON c2.lft >= c1.lft AND c2.rgt <= c1.rgt
    JOIN items i
        ON i.category_id = c2.id
    LEFT OUTER JOIN
    (
        SELECT item_id, COUNT(*) AS likes_count FROM likes WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 day) GROUP BY item_id
    ) likes
    ON likes.item_id = i.id
    LEFT OUTER JOIN
    (
        SELECT item_id, COUNT(*) AS lists_count FROM list_items li WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 day) GROUP BY item_id
    ) lists
    ON lists.item_id = i.id
    WHERE c1.id = 37
) q
ORDER BY popularity