重複 - mysql カンマ区切り縦

GROUP_BYの2つのLEFT JOINのGROUP_CONCATからの奇妙な重複動作 (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         |
+---------+-----------+-------+------------+--------------+

それは正しいし、すべて大丈夫です。

「カテゴリ」という名前の存在がもう1つあります。各投稿に含めることができるカテゴリは1つだけです。また、ユーザーごとに上位2つのカテゴリを取得します。そして、これが私の新しいクエリです。結果からわかるように、いくつかの重複が発生しました。

+---------+-----------+-------+------------+--------------+------------------------+
| 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 。なぜこれらが重複しているのですか？ tags ように categories もう1つ LEFT JOIN を追加しました。しかし、期待どおりに機能せず、タグにも影響します。

とにかく、これは 予想される結果です：

+---------+-----------+-------+------------+--------------+------------------------+
| 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 ;

2番目のクエリの形式は次のとおりです。

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 BYは、 (user_id, t.tag) ＆ (user_id, c.category) がキー/ UNIQUEになります。それ以外は、これらのGROUP BYには対処しません。

TL; DR （q1 JOIN q2）をq3に結合すると、それらの1つのキー/ UNIQUEにないため、user_idごとに、タグとカテゴリのあらゆる可能な組み合わせの行を取得します。したがって、最終的なGROUP BY入力は、（user_id、tag）ごとおよび（user_id、category）ごとに重複し、不適切にGROUP_CONCATsはuser_idごとに重複するタグとカテゴリを入力します。正解は（q1 JOIN q2 GROUP BY）JOIN（q1 JOIN q3 GROUP BY）で、すべての結合が共通キー/ UNIQUE (user_id) にあり、偽の集約はありません。時々、そのような偽の集約を取り消すことができます。

正しい対称的な内部結合アプローチ：LEFT JOIN q1＆q2--1：many--then GROUP BY＆GROUP_CONCAT（これが最初のクエリでした）; 次に、同様にLEFT JOIN q1＆q3--1：many--then GROUP BY＆GROUP_CONCAT; 次に、user_idの2つの結果を内部結合します。1：1。

正しい対称スカラーサブクエリアプローチ：GROUP BYを使用したスカラーサブクエリとしてq1からGROUP_CONCATを選択します。

正しい累積LEFT JOINアプローチ：LEFT JOIN q1＆q2--1：many--then GROUP BY＆GROUP_CONCAT; それから左に参加し、q3--1：many--そしてGROUP BY＆GROUP_CONCAT。

2番目のクエリのような正しいアプローチ：最初にLEFT JOIN q1＆q2--1：many。それから左に参加して、3つ目と1つ：多く：1 user_idで表示されるタグとカテゴリのあらゆる可能な組み合わせの行を提供します。 GROUP BYを実行した後、GROUP_CONCATを使用して、重複した（user_id、tag）ペアと重複した（user_id、category）ペアを作成します。そのため、リスト要素が重複しています。ただし、GROUP_CONCATにDISTINCTを追加すると、正しい結果が得られます。（ wchiquito のコメントごと。）

あなたが好むのは、通常のように、実際のデータ/使用量/統計ごとにクエリプランとタイミングによって通知されるエンジニアリングトレードオフです。予想される重複量の入力と統計）、実際のクエリのタイミングなど。1つの問題は、many：1：many 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 ;

重複 - mysql カンマ区切り 縦

GROUP_BYの2つのLEFT JOINのGROUP_CONCATからの奇妙な重複動作 (1)

重複 - mysql カンマ区切り縦