복붙노트

[SQL] 두 LEFT의 GROUP_CONCAT에서 이상한 중복 동작은 GROUP_BYs의 조인

SQL

두 LEFT의 GROUP_CONCAT에서 이상한 중복 동작은 GROUP_BYs의 조인

여기에 내 모든 테이블 '구조와 쿼리 (아래 추가, 마지막 질의에 집중하시기 바랍니다)입니다. 당신이 바이올린에서 볼 때, 여기에 전류 출력입니다 :

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

그것은 정확하고 모두 괜찮아요.

지금은 하나 개 더 존재 "카테고리"라는있다. 각 포스트 캔은 하나의 범주가 있습니다. 그리고 또한 각 사용자에 대해 최고 두 가지 범주를 싶어. 그리고 여기에 나의 새로운 쿼리입니다. 당신이 결과에서 보듯이 일부 중복이 있었 :

+---------+-----------+-------+------------+--------------+------------------------+
| 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, 기술, 기술. 왜이 중복입니까? 난 그냥 하나 더 남아 정확히 태그와 같은 범주에 가입 추가했습니다. 그러나 예상대로 작동하여 하나 태그에 영향을하지 않습니다.

어쨌든,이 예상되는 결과는 다음과 같습니다

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

해결법

  1. ==============================

    1.두 번째 질의는 다음 형식이다 :

    두 번째 질의는 다음 형식이다 :

    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

    그룹 내에서는, 초기치 (USER_ID, t.tag) (USER_ID, c.category) 주도 키 / 순 초래한다. 나는 그 그룹의 총격 사건을 해결하지 않습니다 이외.

    TL; DR에 가입 (Q1, Q2가 가입) Q3으로는 키에 있지 / 그들 중 하나는 각각의 UNIQUE USER_ID 대해 태그 및 카테고리의 모든 가능한 조합에 대한 행을 얻는다. (USER_ID, 태그) 당 (USER_ID, 카테고리) 및 ​​부적절 GROUP_CONCATs 당 입력 중복 BY 최종 GROUP 그래서 USER_ID 당 태그 및 카테고리를 복제. 올바른 것 (Q1이 가입 Q2 GROUP BY)은 모두 공통 키 / UNIQUE (USER_ID)에있는 및 스퓨리어스 집계가없는 조인하는 (분기는 3 분기 GROUP BY 가입) 가입. 가끔하지만 당신은 가짜 집계를 취소 할 수 있습니다.

    원래 올바른 대칭 INNER이 접근 가입 : LEFT는 Q1 및 q2--1 가입 : 많은 - 다음 GROUP BY 및 (첫 번째 쿼리가 한 일이다) GROUP_CONCAT; 다음 별도로 유사 LEFT Q1 및 q3--1 가입 : 많은 - 다음 GROUP BY 및 GROUP_CONCAT; 1 : 1 - 다음 INNER는 USER_ID에있는 두 개의 결과를 가입.

    원래 올바른 대칭 스칼라 하위 쿼리의 접근 방식은 : Q1에서 GROUP_CONCATs을 선택 스칼라 서브 쿼리로 GROUP BY 각.

    원래 올바른 누적 LEFT이 접근 가입 : LEFT는 Q1 및 q2--1 가입 : 많은 - 다음 GROUP BY 및 GROUP_CONCAT; 다음 LEFT 조인 및 q3--1 : 많은 - 다음 GROUP BY 및 GROUP_CONCAT.

    당신의 두번째 쿼리 같은 올바른 접근 방식 : 많은 : 먼저 LEFT 1 분기 및 q2--1 가입하세요. 1 : 많은 많은 - 그런 다음 왼쪽은 & Q3 가입하세요. 그것은 USER_ID 함께 표시하는 태그 및 범주의 모든 가능한 조합에 대한 행을 제공한다. 그런 다음 GROUP BY 당신 GROUP_CONCAT 후 - 이상 중복 (USER_ID, 태그) 쌍은 (USER_ID, 카테고리) 쌍을 중복. 중복 목록 요소를 가질 이유입니다. 그러나 GROUP_CONCAT에 DISTINCT 추가하면 정확한 결과를 제공합니다. (wchiquito의 코멘트 당.)

    당신은 실제 데이터 / 사용 / 통계 당, 쿼리 계획 및 타이밍으로 연락 일반적인 엔지니어링 트레이드 오프 같다 선호한다. 1 : 많은 방법이 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 ;

    2. from https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 3 개 테이블 내부는 조인?  (0) 2020.03.18
[SQL] 빠르고 간단한 쿼리보다보기인가?  (0) 2020.03.18
[SQL] 개별 행을 모든 행을 계산 대신 계산 SQL  (0) 2020.03.18
[SQL] CONCATENATE 행은 T-SQL 값  (0) 2020.03.18
[SQL] HOURS-00904 : 잘못된 식별자  (0) 2020.03.17
,

티스토리툴바