複数の「GROUP_BY」を結合するときに「GROUP_CONCAT」の重複を回避するにはどうすればよいですか?

複数の GROUP_CONCAT 結合による GROUP BY の重複結果のアドレス指定

GROUP BY を介して複数の LEFT JOIN 句を結合し、GROUP_CONCAT を使用すると、エントリが重複する可能性があります。これは、各 GROUP BY からの一意のキーがマージされることで発生し、不正確なデータ表現になります。

問題のシナリオ

次のクエリ構造を考えてみましょう:

<code class="language-sql">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
    (...) AS q1
    LEFT JOIN (...) AS q2 ON q2.user_id = q1.user_id 
    LEFT JOIN (...) AS q3 ON q3.user_id = q1.user_id 
GROUP BY
    q1.user_id, q1.user_name, q1.score, q1.reputation</code>

このクエリは、ユーザー、タグ、評判、post_tag、カテゴリ、および post_category のテーブルを想定して、各ユーザーの上位 2 つのタグとカテゴリを取得することを目的としています。 問題は、結合された GROUP BY によって "css,css" や "technology,technology" のような重複が発生する可能性があることです。

効果的な解決策

いくつかの方法でこれらの重複を防ぐことができます:

1. GROUP BY と INNER JOIN を区切ります:

<code class="language-sql">-- Separate grouping and joining approach
SELECT
    q1.user_id, q1.user_name, q1.score, q1.reputation, q1.top_two_tags, q2.category
FROM
    (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
        (...) AS q1
        LEFT JOIN (...) AS q2 ON q2.user_id = q1.user_id 
    GROUP BY
        q1.user_id, q1.user_name, q1.score, q1.reputation
    ) AS q1
    INNER JOIN
    (SELECT
        q1.user_id, 
        SUBSTRING_INDEX(GROUP_CONCAT(q3.category ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2) AS category
    FROM
        (...) AS q1
        LEFT JOIN (...) AS q3 ON q3.user_id = q1.user_id 
    GROUP BY
        q1.user_id
    ) AS q2
    ON q1.user_id = q2.user_id;</code>

このメソッドは、まずタグとカテゴリを別々にグループ化して連結し、次に INNER JOIN の user_id を使用して結果を結合します。 これにより、ユーザーごとに 1 行のみが生成されるようになります。

2. スカラーサブクエリ:

<code class="language-sql">-- Scalar subquery approach
SELECT
    q1.user_id, q1.user_name, q1.score, q1.reputation,
    (SELECT
        SUBSTRING_INDEX(GROUP_CONCAT(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2)
    FROM (...) AS q2
    WHERE q2.user_id = q1.user_id
    ),
    (SELECT
        SUBSTRING_INDEX(GROUP_CONCAT(q3.category ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2)
    FROM (...) AS q3
    WHERE q3.user_id = q1.user_id
    )
FROM
    (...) AS q1;</code>

これは、SELECT リスト内のサブクエリを使用して、各ユーザーの上位のタグとカテゴリを個別に取得し、GROUP BY の曖昧さを回避します。

重要なのは、複数のテーブルで GROUP_CONCAT を使用する場合、単一の GROUP BY 呼び出し内で複数の結合テーブルからのデータを直接連結しないようにすることです。 選択するソリューションは、特定のデータベース構造とパフォーマンスの考慮事項によって異なります。 目標は、正確で重複のない結果を生成することです。

以上が複数の「GROUP_BY」を結合するときに「GROUP_CONCAT」の重複を回避するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。