mysql - Group_BYs के दो LEFT JOINs के 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         |
+---------+-----------+-------+------------+--------------+

यह सही है और सब ठीक है।

अब मेरा एक और अस्तित्व है जिसका नाम "श्रेणी" है। प्रत्येक पोस्ट में केवल एक श्रेणी हो सकती है। और मैं प्रत्येक उपयोगकर्ता के लिए शीर्ष दो श्रेणियां भी प्राप्त करना चाहता हूं। और 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,css , technology, 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

इनर ग्रुप BYs का परिणाम (user_id, t.tag) & (user_id, c.category) कुंजी / UNIQUE होता है। इसके अलावा मैं उन ग्रुप BYs को संबोधित नहीं करूंगा।

TL, DR जब आप q3 में शामिल होते हैं (q1 JOIN q2) तो यह उनमें से किसी एक की कुंजी / UNIQUE पर नहीं है इसलिए प्रत्येक user_id के लिए आपको टैग और श्रेणी के हर संभव संयोजन के लिए एक पंक्ति मिलती है। तो अंतिम GROUP BY इनपुट प्रति (user_id, टैग) और प्रति (user_id, श्रेणी) और अनुचित रूप से GROUP_CONCATs डुप्लिकेट टैग और श्रेणियों प्रति user_id डुप्लिकेट। सही होगा (q1 JOIN q2 GROUP BY) JOIN (q1 JOIN q3 GROUP BY) जिसमें सभी जॉइन कॉमन कुंजी / UNIQUE (user_id) और इसमें कोई विशेष एकत्रीकरण न हो। हालांकि कभी-कभी आप इस तरह के उत्साहपूर्ण एकत्रीकरण को पूर्ववत कर सकते हैं।

एक सममित समरूप ININ JOIN दृष्टिकोण: LEFT JOIN q1 & q2--1: many - उसके बाद GROUP BY & GROUP_CONCAT (जो आपकी पहली क्वेरी है); फिर अलग-अलग समान रूप से LEFT JOIN q1 & q3--1: कई - फिर GROUP BY & GROUP_CONCAT; उसके बाद INNER में दो परिणामों को शामिल करें user_id - 1: 1।

एक सही सममित स्केलर सबक्वेरी अप्रोच: एक समूह BY के साथ प्रत्येक के स्केलर सबक्वेरीज के रूप में q1 से GROUP_CONCATs का चयन करें।

एक सही संचयी बायाँ संयुक्त दृष्टिकोण: बाएँ जोड़ q1 और q2--1: कई - तब समूह BY & GROUP_CONCAT; उसके बाद छोड़ दिया और कि q3--1: कई - तब ग्रुप BY & GROUP_CONCAT।

आपकी दूसरी क्वेरी की तरह एक सही तरीका: आप पहले LEFT JOIN q1 & q2--1: कई। तो फिर तुम छोड़ दो कि और q3 - कई: 1: कई। यह टैग और श्रेणी के हर संभव संयोजन के लिए एक पंक्ति देता है जो user_id के साथ दिखाई देता है। इसके बाद आपके द्वारा ग्रुप बनाकर आप GROUP_CONCAT - ओवर डुप्लिकेट (user_id, टैग) जोड़े और डुप्लिकेट (user_id, श्रेणी) जोड़े पर। यही कारण है कि आपके पास डुप्लिकेट सूची तत्व हैं। लेकिन GROUP_CONCAT में DISTINCT को जोड़ना एक सही परिणाम देता है। (प्रति wchiquito की टिप्पणी के अनुसार।)

जो आप पसंद करते हैं, वह सामान्य रूप से एक इंजीनियरिंग ट्रेडऑफ है जिसे वास्तविक डेटा / उपयोग / आंकड़ों के अनुसार क्वेरी प्लान और टाइमिंग द्वारा सूचित किया जाता है। इनपुट और डुप्लिकेट की अपेक्षित मात्रा के लिए), वास्तविक प्रश्नों का समय आदि। एक मुद्दा यह है कि क्या कई की अतिरिक्त पंक्तियाँ: 1: कई JOIN दृष्टिकोण एक ग्रुप 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