mysql - 來自GROUP_BY的兩個LEFT JOIN的GROUP_CONCAT的奇怪重複行為



group-by left-join (1)

Here 是我所有表格的結構和查詢 (請關注 最後一個 查詢,如下所示) 。 正如你在小提琴中看到的,這是 當前的輸出:

+---------+-----------+-------+------------+--------------+
| 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”的存在。 每個帖子只能有一個類別。 而且我還希望為每個用戶獲得前兩個類別。 here 是我的新查詢。 正如您在結果中看到的,發生了一些重複:

+---------+-----------+-------+------------+--------------+------------------------+
| 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,csstechnology, technology 。 為什麼這些是重複的? 我剛剛為 categories 添加了一個 LEFT JOIN ,就像 tags 一樣。 但它不能按預期工作,甚至也會對標籤產生影響。

無論如何,這是 預期的結果:

+---------+-----------+-------+------------+--------------+------------------------+
| user_id | user_name | score | reputation | top_two_tags |        category        |
+---------+-----------+-------+------------+--------------+------------------------+
| 1       | Jack      | 0     | 18         | css,mysql    | technology,social      |
| 4       | James     | 1     | 5          | html         | political              |
| 2       | Peter     | 0     | 0          | null         | null                   |
| 3       | Ali       | 0     | 0          | null         | null                   |
+---------+-----------+-------+------------+--------------+------------------------+

有誰知道我怎麼能做到這一點?

CREATE TABLE users(id integer PRIMARY KEY, user_name varchar(5));
CREATE TABLE tags(id integer NOT NULL PRIMARY KEY, tag varchar(5));
CREATE TABLE reputations(
    id  integer PRIMARY KEY, 
    post_id  integer /* REFERENCES posts(id) */, 
    user_id integer REFERENCES users(id), 
    score integer, 
    reputation integer, 
    date_time integer);
CREATE TABLE post_tag(
    post_id integer /* REFERENCES posts(id) */, 
    tag_id integer REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id));
CREATE TABLE categories(id INTEGER NOT NULL PRIMARY KEY, category varchar(10) NOT NULL);
CREATE TABLE post_category(
    post_id INTEGER NOT NULL /* REFERENCES posts(id) */, 
    category_id INTEGER NOT NULL REFERENCES categories(id),
    PRIMARY KEY(post_id, category_id)) ;

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,
    substring_index(group_concat(q3.category  ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2) AS category
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 
    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.reputation
ORDER BY
    q1.reputation DESC, q1.score DESC ;

您的第二個查詢是以下形式:

q1 -- PK user_id
LEFT JOIN (...
    GROUP BY user_id, t.tag
) AS q2
ON q2.user_id = q1.user_id 
LEFT JOIN (...
    GROUP BY user_id, c.category
) AS q3
ON q3.user_id = q1.user_id
GROUP BY -- group_concats

內部GROUP BYs導致 (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 approach
SELECT
   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 category
FROM
    -- 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.reputation
ORDER BY
    q1.reputation DESC, q1.score DESC ;




group-concat