猿问
下载APP

来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为

来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复

是我所有表格的结构和查询(请关注最后一个查询,如下所示)。正如你在小提琴中看到的,这是当前的输出:

+---------+-----------+-------+------------+--------------+

| user_id | user_name | score | reputation | top_two_tags |

+---------+-----------+-------+------------+--------------+

| 1       | Jack      | 0     | 18         | css,mysql    |

| 4       | James     | 1     | 5          | html         |

| 2       | Peter     | 0     | 0          | null         |

| 3       | Ali       | 0     | 0          | null         |

+---------+-----------+-------+------------+--------------+

这是正确的,一切都很好。


现在我还有一个名为“category”的存在。每个帖子只能有一个类别。而且我还希望为每个用户获得前两个类别。而这里是我的新的查询。正如您在结果中看到的,发生了一些重复:


+---------+-----------+-------+------------+--------------+------------------------+

| user_id | user_name | score | reputation | top_two_tags |   top_two_categories   |

+---------+-----------+-------+------------+--------------+------------------------+

| 1       | Jack      | 0     | 18         | css,css      | technology,technology  |

| 4       | James     | 1     | 5          | html         | political              |

| 2       | Peter     | 0     | 0          | null         | null                   |

| 3       | Ali       | 0     | 0          | null         | null                   |

+---------+-----------+-------+------------+--------------+------------------------+

看到?css,css,technology, technology。为什么这些是重复的?我只是增加了一个LEFT JOIN对categories,一模一样tags。但它不能按预期工作,甚至也会对标签产生影响。

有谁知道我怎么能做到这一点?

蝴蝶不菲
浏览 61回答 2
2回答

红颜莎娜

您的第二个查询是以下形式:q1 -- PK user_idLEFT JOIN (...     GROUP BY user_id, t.tag) AS q2ON q2.user_id = q1.user_id  LEFT JOIN (...     GROUP BY user_id, c.category) AS q3ON q3.user_id = q1.user_idGROUP BY -- group_concats内部GROUP BY导致(user_id, t.tag)&(user_id, c.category)为键/ UNIQUE。除此之外,我不会解决那些GROUP BY。TL; DR当您加入(q1 JOIN q2)到q3时,它不在其中一个的键/ UNIQUE上,因此对于每个user_id,您为每个可能的标签和类别组合获得一行。因此,最终的GROUP BY输入per(user_id,tag)和per(user_id,category)的重复项,并且每个user_id不恰当地GROUP_CONCATs重复的标签和类别。正确的是(q1 JOIN q2 GROUP BY)JOIN(q1 JOIN q3 GROUP BY),其中所有连接都在公共密钥/ UNIQUE上(user_id),并且没有虚假聚合。虽然有时你可以撤消这种虚假聚合。一个正确的对称INNER JOIN方法:LEFT JOIN q1&q2--1:many - then GROUP BY&GROUP_CONCAT(这是你的第一个查询所做的); 然后分别类似LEFT JOIN q1和q3--1:很多 - 然后GROUP BY&GROUP_CONCAT; 然后INNER JOIN两个结果ON user_id - 1:1。正确的对称标量子查询方法:从q1中选择GROUP_CONCAT作为标量子查询,每个子查询都有一个GROUP BY。正确的累积LEFT JOIN方法:LEFT JOIN q1&q2--1:many - then GROUP BY&GROUP_CONCAT; 然后LEFT JOIN&q3--1:很多 - 然后是GROUP BY&GROUP_CONCAT。像你的第二个查询一样正确的方法:你首先LEFT JOIN q1和q2--1:很多。然后你LEFT JOIN&q3 - 很多:1:很多。它为使用user_id显示的标记和类别的每种可能组合提供一行。然后在GROUP BY之后GROUP_CONCAT - 重复(user_id,tag)对和重复(user_id,category)对。这就是为什么你有重复的列表元素。但是将DISTINCT添加到GROUP_CONCAT会得到正确的结果。(根据wchiquito的评论。)您更喜欢的是通常的工程权衡,根据查询计划和时间,实际数据/使用/统计信息。预期复制量的输入和统计数据,实际查询的时间等。一个问题是多个额外的行:1:多JOIN方法是否抵消了它对GROUP BY的保存。-- cumulative LEFT JOIN approachSELECT    q1.user_id, q1.user_name, q1.score, q1.reputation,     top_two_tags,     substring_index(group_concat(q3.category  ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2) AS categoryFROM     -- your 1st query (less ORDER BY) AS q1     (SELECT         q1.user_id, q1.user_name, q1.score, q1.reputation,          substring_index(group_concat(q2.tag  ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags    FROM         (SELECT              u.id AS user_Id,              u.user_name,             coalesce(sum(r.score), 0) as score,             coalesce(sum(r.reputation), 0) as reputation        FROM              users u            LEFT JOIN reputations r                  ON    r.user_id = u.id                    AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */         GROUP BY              u.id, u.user_name        ) AS q1        LEFT JOIN         (         SELECT             r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation        FROM             reputations r              JOIN post_tag pt ON pt.post_id = r.post_id            JOIN tags t ON t.id = pt.tag_id        WHERE             r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */         GROUP BY             user_id, t.tag        ) AS q2        ON q2.user_id = q1.user_id          GROUP BY             q1.user_id, q1.user_name, q1.score, q1.reputation    ) AS q1    -- finish like your 2nd query     LEFT JOIN     (     SELECT         r.user_id AS user_id, c.category, sum(r.reputation) AS category_reputation    FROM         reputations r          JOIN post_category ct ON ct.post_id = r.post_id        JOIN categories c ON c.id = ct.category_id    WHERE         r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */     GROUP BY         user_id, c.category    ) AS q3    ON q3.user_id = q1.user_id  GROUP BY     q1.user_id, q1.user_name, q1.score, q1.reputationORDER BY     q1.reputation DESC, q1.score DESC ;

慕丝7291255

这里没有一般原则来决定。我说,这是一个权衡,你必须在你的确切情况下(包括重要的是你的期望和优化者)来衡量,就像工程“最好”(一个嵌合体)一样。(不同和累积查询的计划似乎很接近;但数据和统计数据都是玩具。我希望优化器能够实现接近累积或内部连接的子选择查询,因为它们之间有明显的简单转换。我是更加怀疑你的查询中的所有重复,但我想解决连接周围的问题。
打开App,查看更多内容
随时随地看视频慕课网APP
我要回答