꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 질문드립니다.

by naverTT [2017.02.02 10:56:51]

문의.PNG (39,616Bytes)

어제 질문드린 내용인데요..

조언에 힘입어 이렇게까지 작성을 하였습니다.

근데 msg_seq가 pk고 숫자로 구성되다보니 max를 가져올때 진정 마지막값이 아닌겁니다ㅠ

msg_Seq 구성을 바꿔야할까요?

  SELECT msg_seq
                   , sender
                   , sender_name
                   , sendeR_dept
                   , receiver
                   , receiver_name
                   , receiver_dept
                   , send_date
                   , confirm_date
                   , CASE WHEN MSG_SEQ LIKE '%_' || '1427793428005' || '_%' THEN 'R'
                                       WHEN MSG_SEQ LIKE '%' || '1427793428005' || '_%' THEN 'S'
                                  END MSG_TYPE
                      FROM MSG_tBL WHERE  (MSG_SEQ LIKE '%_' || '1427793428005' || '_%' or MSG_SEQ LIKE '%' || '1427793428005' || '_%')

이렇게해서 해당 이미지파일처럼 나왔습니다.

그런데 여기서 수정을 좀 해서

SELECT  apt, apt1, apt2
                FROM 
                   (
SELECT DISTINCT DECODE (SENDER,
                        '1427793428005', RECEIVER,
                        SENDER
                       ) apt,
                DECODE (SENDER_NAME,
                        '구마적', RECEIVER_NAME,
                        SENDER_NAME
                       ) apt1,
                DECODE (SENDER_DEPT,
                        '인사팀', RECEIVER_DEPT,
                        SENDER_DEPT
                       ) apt2,
                      MSG_SEQ,send_date
    FROM MSG_TBL WHERE MSG_GRP IS NULL 
           AND (MSG_SEQ LIKE '%_' || '1427793428005' || '_%' or MSG_SEQ LIKE '%' || '1427793428005' || '_%')
              ORDER BY SEND_DATE,SUBSTR(MSG_SEQ,INSTR(MSG_SEQ,'_',1,2)+1)  DESC)
              GROUP BY apt,apt1,apt2

이렇게하려고했으나 msg_type,confirm_date, msg_seq(pk)를 가져와지지 않습니다

사진에 첨부된 내용들을 send_date를 마지막으로 메일을 주고받은 사람별 마지막 건만 갖고오고싶습니다.

도와주세요 ㅠㅠ

by 마농 [2017.02.02 11:34:57]

SELECT *
  FROM (SELECT msg_seq
             , DECODE('1427793428005', sender, receiver     , sender     ) id
             , DECODE('1427793428005', sender, receiver_name, sender_name) name
             , DECODE('1427793428005', sender, receiver_dept, sender_dept) dept
             , send_date
             , confirm_date
             , DECODE('1427793428005', sender, 'S', 'R') msg_type
             , ROW_NUMBER() OVER(
               PARTITION BY DECODE('1427793428005', sender, receiver, sender)
               ORDER BY send_date DESC
               ) rn
          FROM msg_tbl
         WHERE '1427793428005' IN (sender, receiver)
        )
 WHERE rn = 1
;

by 마농 [2017.02.02 13:09:05]

msg_seq 의 구성에 대해서
- msg_seq 가 sender + receiver + send_date 조합이네요.
- 이미 있는 항목으로 조합할 필요가 있을지?
- 아예 msg_seq 를 제거하고 3개 항목으로 PK 를 만들어도 되구요.
- 꼭 msg_seq 가 필요하다면? 그냥 단순 시퀀스를 이용하는게 나을 듯 하네요?

by naverTT [2017.02.02 15:34:40]

데이터가 몇십만건 이렇게 증가할 것 같은데 조회시에 PK로 조회하는데 속도면에서 빠를 것같아서 이렇게 짜봤는데요 ㅠㅠ

by 마농 [2017.02.02 16:10:46]

조회속도에서 조회는 어떤 조회를 말하는 것인가요?
1. PK를 이용한 단건 조회 속도 라면?
   PK 를 어떻게 만들던지 크게 상관 없을 듯 합니다.
   따라서 PK 는 원래의 의미인 3개 항목 복함키로 구성을 하던가?
   3개 항목이 부담스럽다면 인조키로 순번을 사용하는게 단순하고 좋을 듯 합니다.
   굳이 조합해서 만들면 키의 길이가 너무 기네요.
   데이터 저장소만 더 많이 잡아먹게 됩니다.
2. 위의 쿼리. 특정 ID 에 대한 조회속도 라면?
   sender 와 receiver 항목에 각각 인덱스를 만들고
   Union All 로 푸는 것이 좋을 듯 합니다.

SELECT *
  FROM (SELECT msg_seq
             , id
             , name
             , dept
             , send_date
             , confirm_date
             , msg_type
             , ROW_NUMBER() OVER(PARTITION BY id ORDER BY send_date DESC) rn
          FROM (SELECT msg_seq
                     , receiver      id
                     , receiver_name name
                     , receiver_dept dept
                     , send_date
                     , confirm_date
                     , 'S' msg_type
                  FROM msg_tbl
                 WHERE sender = :v_id
                 UNION ALL
                SELECT msg_seq
                     , sender      id
                     , sender_name name
                     , sender_dept dept
                     , send_date
                     , confirm_date
                     , 'R' msg_type
                  FROM msg_tbl
                 WHERE receiver = :v_id
                )
        )
 WHERE rn = 1
;

by naverTT [2017.02.02 16:56:27]

참고하여 수정해보도록 하겠습니다.

한가지 더 질문 드리면 알려주신 쿼리에다 하단의 데이터를 "김태희,조인성,황정민,김수현,이순재" 이런식으로

묶어서 가져오는것도 가능할까요?

단체메일일 경우에는 내가 보냈을때는 수신자를 묶어서 보여주고 싶어서요.. 필요한정보는 sender/receiver, 명단, msg_type 정도입니다.

다만.. 단체메일을 받았을땐 보낸사람 정보만 보고싶어요 ㅠㅠ 테이블 설계부터 잘못됫을까요..

MSG_SEQ MSG_GRP SENDER SENDER_NAME SENDER_DEPT RECEIVER RECEIVER_NAME RECEIVER_DEPT SEND_DATE CONFIRM_DATE MSG_TYPE

1427793428005_1412664833497_20170201151105101   GRP1427793428005_1105101   1427793428005   홍길동   인사팀   1412664833497 김태희 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1412664833460_20170201151105101   GRP1427793428005_1105101   1427793428005   홍길동   인사팀   1412664833460   조인성 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333936_20170201151105101   GRP1427793428005_1105101   1427793428005   홍길동   인사팀   1408317333936 황정민 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333935_20170201151105101   GRP1427793428005_1105101   1427793428005   홍길동   인사팀   1408317333935   김수현 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333933_20170201151105101   GRP1427793428005_1105101   1427793428005   홍길동   인사팀   1408317333933   이순재 인사팀   2017-02-01 오후 3:11:08       S

by 마농 [2017.02.02 17:12:41]

SELECT *
  FROM (SELECT msg_seq
             , id
             , name
             , dept
             , send_date
             , confirm_date
             , msg_type
             , ROW_NUMBER() OVER(PARTITION BY id ORDER BY send_date DESC) rn
          FROM (SELECT LISTAGG(msg_seq      , ',') WITHIN GROUP(ORDER BY receiver) msg_seq
                     , LISTAGG(receiver     , ',') WITHIN GROUP(ORDER BY receiver) id
                     , LISTAGG(receiver_name, ',') WITHIN GROUP(ORDER BY receiver) name
                     , LISTAGG(receiver_dept, ',') WITHIN GROUP(ORDER BY receiver) dept
                     , send_date
                     , MIN(confirm_date) confirm_date
                     , 'S' msg_type
                  FROM msg_tbl
                 WHERE sender = :v_id
                 GROUP BY msg_grp, send_date
                 UNION ALL
                SELECT msg_seq
                     , sender      id
                     , sender_name name
                     , sender_dept dept
                     , send_date
                     , confirm_date
                     , 'R' msg_type
                  FROM msg_tbl
                 WHERE receiver = :v_id
                )
        )
 WHERE rn = 1
;

by naverTT [2017.02.02 17:37:13]

정말감사합니다 ㅠㅠ 그렇다면 pk로 msg_seq, sender, receiver, send_date 다 걸어도 성능면에선 무방할까요?

by 마농 [2017.02.02 17:48:39]

본래 의미상 PK 는 sender, receiver, send_date 의 조합입니다.
인조식별자로 msg_seq 를 만들어 사용하는 거구요.
인조식별자는 마땅히 식별자로 지정할만한 후보키가 없다거나
휴보키가 있긴 한데 너무 많은 항목의 조합이라서 사용이 불편할 때 사용합니다.
인조식별자의 기능은 식별만 하면 됩니다. 의미를 부여할 필요가 없죠.
따라서 인조식별자에 실제 의미있는 데이터의 조합을 사용하기 보다는 단순 순번 주로 사용합니다.
여러 후보키 중 하나를 PK 로 지정하는 것이지.
후보키 두개를 또 다시 조합하여 PK 를 만들 이유가 전혀 없습니다.

by naverTT [2017.02.06 10:54:11]

MSG_SEQ MSG_GRP SENDER SENDER_NAME SENDER_DEPT RECEIVER RECEIVER_NAME RECEIVER_DEPT SEND_DATE CONFIRM_DATE MSG_TYPE

1427793428005_1412664833497_20170201151105101   GRP1427793428005_1202123 1427793428005   홍길동   인사팀   1412664833497 김태희 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1412664833460_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1412664833460   조인성 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333936_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1408317333936 황정민 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333935_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1408317333935   김수현 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333933_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1408317333933   이순재 인사팀   2017-02-01 오후 3:11:08       S

1427793428005_1412664833497_20170201151105101   GRP1427793428005_1251752 1427793428005   홍길동   인사팀   1412664833497 김태희 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1412664833460_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1412664833460   조인성 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333936_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1408317333936 황정민 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333935_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1408317333935   김수현 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333933_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1408317333933   이순재 인사팀   2017-02-01 오후 3:11:08       S

이렇게 같은 그룹으로 여러개 보냈을때 저 붉게 되어있는 걸 묶어서 가져오고 싶은데 ㅠㅠ 제가 한건만 보이게 하고싶은것때문에 저부분도 제일 위에 값만 하나 가져오고있습니다.

방법이없을까요 ㅠ 임의로 아무나 선택해서 보내는것이기 때문에 저 코드를 동일시 하는건 힘들것같고요..

by 마농 [2017.02.06 11:19:28]

음...
동일 질문을 또 하는 이유가???
위 답변에 msg_grp 으로 그룹바이 하는 방법 알려드렸는데요???

by naverTT [2017.02.06 11:25:20]

SELECT MSG_SEQ, IDVAL, NAMEVAL, DEPTVAL, SEND_DATE, CONFIRM_DATE, MSG_TYPE, MSG_GRP
       FROM (SELECT MSG_SEQ
       , ID IDVAL
       , NAME NAMEVAL
       , DEPT DEPTVAL
       , SEND_DATE
       , CONFIRM_DATE
       , MSG_TYPE
       , MSG_GRP
       , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY SEND_DATE DESC) RN
       FROM (SELECT LISTAGG(MSG_SEQ , ',') WITHIN GROUP(ORDER BY RECEIVER) MSG_SEQ
       , LISTAGG(RECEIVER , ',') WITHIN GROUP(ORDER BY RECEIVER) ID
       , LISTAGG(RECEIVER_NAME, ',') WITHIN GROUP(ORDER BY RECEIVER) NAME
       , LISTAGG(RECEIVER_DEPT, ',') WITHIN GROUP(ORDER BY RECEIVER) DEPT
       , SEND_DATE
       , MIN(CONFIRM_DATE) CONFIRM_DATE
       , 'S' MSG_TYPE
       , MSG_GRP
       FROM WO00066.MSG_TBL
       WHERE SENDER = '1427793428005'
       GROUP BY MSG_GRP, SEND_DATE
       UNION ALL
       SELECT MSG_SEQ
       , SENDER ID
       , SENDER_NAME NAME
       , SENDER_DEPT DEPT
       , SEND_DATE
       , CONFIRM_DATE
       , 'R' MSG_TYPE
       , MSG_GRP
       FROM WO00066.MSG_TBL
       WHERE RECEIVER = '1427793428005'
       AND (SENDER != RECEIVER)
       )
       )
       WHERE rn = 1

이렇게 하였더니 수신자 목록은 같지만 MSG_GRP가 다른데 MSG_GRP가 하나만 나옵니다

수신자가 같아도 MSG_GRP가 다르면 MSG_GRP를 중복제거하여 가져와서 쓰고 싶습니다.

by jkson [2017.02.02 18:28:31]

더욱이 인조키를 가지고

MSG_SEQ LIKE '%_' || '1427793428005' || '_%' or MSG_SEQ LIKE '%' || '1427793428005' || '_%'

이런식으로 가공하셨는데 키를 가공하시면 INDEX 활용도 불가할 뿐더러

쿼리를 만들 때도 너무너무너무 불편합니다.

이럴 바에 그냥 sender, receiver, send_date 컬럼을 PK로 만드세요.

같은 의미의 데이터를 중복해서 컬럼으로 두는 것도 공간 낭비죠.

하루에 한번 메시지 보내는 게 아니니까 뭔가 컬럼이 더 추가되어야할 것 같긴하지만..

by 마농 [2017.02.06 11:39:01]

제가 드린 쿼리의 Select List 에는 msg_grp 가 없었죠.
msg_grp 를 추가하셨네요? 이것 떄문에 문제가 되는 건가요?

질문의 내용이 어렵네요?
쿼리를 만들기가 어렵다는게 아니라 한글을 이해하기가 어렵다는 뜻입니다.
원하시는 결과표를 달라고 수차례 말해도 전혀 통하지가 않네요?
중복제거라는 표현이 어떤 형태의 중복제거인지 알수가 없습니다.

원래 질문에 없던걸 추가할 때는 대댓글이 아닌 새로운 글 댓글로 써주세요.
이 글만 읽고 충분이 내 뜻이 전달될지? 고민하신 후에 질문을 올려주세요.

by naverTT [2017.02.06 12:59:42]

머릿속이 복잡하니 정리가 부족하네요 죄송합니다ㅠㅠ

기존쿼리로 수신자가 '김태희,조인성,황정민,김수현,이순재' 이렇게 묶여서 잘 나옵니다. 그런데

MSG_SEQ MSG_GRP SENDER SENDER_NAME SENDER_DEPT RECEIVER RECEIVER_NAME RECEIVER_DEPT SEND_DATE CONFIRM_DATE MSG_TYPE

1427793428005_1412664833497_20170201151105101   GRP1427793428005_1202123 1427793428005   홍길동   인사팀   1412664833497 김태희 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1412664833460_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1412664833460   조인성 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333936_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1408317333936 황정민 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333935_20170201151105101   GRP1427793428005_1202123   1427793428005   홍길동   인사팀   1408317333935   김수현 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333933_20170201151105101   GRP1427793428005_1202123    1427793428005   홍길동   인사팀   1408317333933   이순재 인사팀   2017-02-01 오후 3:11:08       S

1427793428005_1412664833497_20170201151105101   GRP1427793428005_1251752 1427793428005   홍길동   인사팀   1412664833497 김태희 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1412664833460_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1412664833460   조인성 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333936_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1408317333936 황정민 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333935_20170201151105101   GRP1427793428005_1251752   1427793428005   홍길동   인사팀   1408317333935   김수현 인사팀   2017-02-01 오후 3:11:08       S
1427793428005_1408317333933_20170201151105101   GRP1427793428005_1251752    1427793428005   홍길동   인사팀   1408317333933   이순재 인사팀   2017-02-01 오후 3:11:08       S

이렇게 수신자가 '김태희,조인성,황정민,김수현,이순재' 인게 한 건 이상일 때 건 별 MSG_GRP를 묶어서 보고 싶은 것입니다.

수신자가 '김태희,조인성,황정민,김수현,이순재' , 'GRP1427793428005_1105101,GRP1427793428005_1202123,GRP1427793428005_1251752' 이렇게요

rownum 1을 가져오다 보니 'GRP1427793428005_1105101' 이거 하나만 나와서요.

수신자는 수신자별로 묶이되 msg_grp는 건 별 값을 다 가져오고 싶습니다.

by 마농 [2017.02.06 13:33:32]

SELECT *
  FROM (SELECT msg_seq
             , idval
             , nameval
             , deptval
             , send_date
             , confirm_date
             , msg_type
             , LISTAGG(msg_grp, ',') WITHIN GROUP(ORDER BY msg_grp) OVER(PARTITION BY idval) msg_grp
             , ROW_NUMBER() OVER(PARTITION BY idval ORDER BY send_date DESC) rn
          FROM (SELECT LISTAGG(msg_seq      , ',') WITHIN GROUP(ORDER BY receiver) msg_seq
                     , LISTAGG(receiver     , ',') WITHIN GROUP(ORDER BY receiver) idval
                     , LISTAGG(receiver_name, ',') WITHIN GROUP(ORDER BY receiver) nameval
                     , LISTAGG(receiver_dept, ',') WITHIN GROUP(ORDER BY receiver) deptval
                     , send_date
                     , MIN(confirm_date) confirm_date
                     , msg_grp
                  FROM msg_tbl
                 WHERE sender = :v_id
                 GROUP BY msg_grp, send_date
                 UNION ALL
                SELECT msg_seq
                     , sender      idval
                     , sender_name nameval
                     , sender_dept deptval
                     , send_date
                     , confirm_date
                     , 'R' msg_type
                     , msg_grp
                  FROM msg_tbl
                 WHERE receiver = :v_id
                )
        )
 WHERE rn = 1
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변