how
62
2019-06-17 11:24:49
3
217

MYSQL GROUP BY 정렬 관련 질문 드려요.



group by 내부 정렬 관련 질문입니다.

만약 테이블이

id sender name message 로 구성 되어 있을경우

1  1            유저1  메시지1 

2  1            유저1  메시지2 

3  1            유저1  매시지3 


아래와 같은 쿼리를 요청해도 

SELECT A.sender, A.name, A.message, count(*) as count FROM (SELECT sender, name, message FROM msg_data ORDER BY id DESC) as A GROUP BY A.sender ORDER BY A.id DESC;


결과값은 아래와 같이 메시지1이 나오게 됩니다.

sender name message count

1           유저1   메시지1     3


이럴땐 어떻게 쿼리를 날려야 메시지3을 가져올 수 있을까요?


0
0
  • 답변 3

  • 르매
    560
    2019-06-17 11:46:27 작성 2019-06-17 11:56:33 수정됨

    MySQL에서 마음에 들지 않는 부분 중 하나인데요. ANSI SQL 로서 문법 오류인데도 짜깁기 식으로 동작을 시켜준다는 점입니다.

    그리고.. 최종 결과가 어떻게 나오길 바라는 건지 명확하지 않은데요.

    1. 보낸 사람으로 그룹핑한다.
    2. 보낸 메시지의 개수를 카운트한다.
    3. 최근에 보낸 메시지 순서로 보낸 메시지의 내용이 나와야 한다.

    대략 이런 걸 원하시는듯한데.. 3항이 문제네요. 보낸 메시지가 각각의 row에 나와야 하나요? 아니면 컬럼 하나에 메시지 3개의 내용이 합쳐서 나와야 하나요? 아니면 각각의 컬럼에 나와야 하나요?


    - 각 row 옆에 카운트 컬럼을 추가로 붙이기

    SELECT A.sender
        , A.name
        , A.message
        , (SELECT COUNT(*) FROM msg_data WHERE sender = A.sender) AS count 
    FROM msg_data A
    ORDER BY a.id DESC;
    
    -- 또는
    
    SELECT A.sender
        , A.name
        , A.message
        , B.count
    FROM msg_data A
        INNER JOIN (
            SELECT sender, COUNT(*) AS count
            FROM msg_data
            GROUP BY sender
        ) B ON B.sender = A.sender
    ORDER BY A.id DESC;


    - 메시지를 하나의 컬럼에 합치기

    SELECT A.sender
        , A.name
        , GROUP_CONCAT(A.message ORDER BY A.id DESC SEPARATOR '|') AS messages
        , COUNT(*) AS count 
    FROM msg_data A
    GROUP BY A.sender, A.name;


    1
  • how
    62
    2019-06-17 13:07:15

    답변 감사합니다^^

    - 보낸 메시지가 각각의 row에 나와야 하나요?

    > 네 마지막으로 보낸 메시지가 각각의 row에 나오는것을 원합니다.

    여러 sender가 존재한다면 아래와 같은 결과를 원합니다.

    sender_id  name   message                      count

    1               유저1    유저1의 마지막 메시지     3

    2               유저2    유저2의 마지막 메시지     2

    3               유저3    유저3의 마지막 메시지     5

    ...


    답해주신,

    - 각 row 옆에 카운트 컬럼을 추가로 붙이기
    > 답해주신 2개의 쿼리에서 sender_id 별로 그룹을 시키고 싶은데 여기서,, 결과값에 최근(마지막) 메시지를 가져오는걸 실패합니다. 계속 아이디 순으로 정렬되어 가장 오래된 메시지를 가져오네요ㅠ


    0
  • 르매
    560
    2019-06-17 15:02:26 작성 2019-06-17 15:26:24 수정됨

    id 컬럼이 auto_increment 속성이면.. 이 숫자가 높은게 가장 최근 메시지일테니..

    1. sender 로 그룹핑했을 때 max(id) 를 구한다 -> 발신자 별 가장 최근 메시지의 id

    2. JOIN해서 1항의 id 값을 가진 메시지만 가져오면 발신자 별 가장 최근 메시지만 나온다.

    3. 발신자 별 메시지 개수는 스칼라 서브쿼리를 사용한다. (이게 좀 부하가 있을 듯)

    SELECT MD.id
        , MD.sender
        , MD.name
        , MD.message
        , (SELECT COUNT(*) FROM msg_data WHERE sender = MD.sender) AS count 
    FROM msg_data MD
        INNER JOIN (
            SELECT MAX(id) AS id
            FROM msg_data
            GROUP BY sender
        ) S ON S.id = MD.id;


    만약 MySQL 8.x 를 사용다면, 아래처럼 쉽게 풀 수 있어요.

    WITH A AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY sender ORDER BY id DESC) AS seq
        FROM msg_data
    )
    SELECT id, sender, name, message
        , (SELECT COUNT(*) FROM msg_data WHERE sender = A.sender) AS count
    FROM A
    WHERE seq = 1


    p.s

    - sender 컬럼에 인덱스 필수

    - 성능을 고려하면.. 별도의 집계 테이블을 만들어서 발신자 별 최근 메시지의 id와  메시지 개수를 기록하고, SELECT 문에서 JOIN 해서 사용해야 할 듯 하네요. 이걸 실시간으로 매번 그룹핑해서 SELECT 하면 데이터가 많아 졌을 때 바로 악성 쿼리로 전락할 듯...

    1
  • 로그인을 하시면 답변을 등록할 수 있습니다.