어제 질문드린 내용인데요..
조언에 힘입어 이렇게까지 작성을 하였습니다.
근데 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를 마지막으로 메일을 주고받은 사람별 마지막 건만 갖고오고싶습니다.
도와주세요 ㅠㅠ
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 ;
조회속도에서 조회는 어떤 조회를 말하는 것인가요?
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 ;
참고하여 수정해보도록 하겠습니다.
한가지 더 질문 드리면 알려주신 쿼리에다 하단의 데이터를 "김태희,조인성,황정민,김수현,이순재" 이런식으로
묶어서 가져오는것도 가능할까요?
단체메일일 경우에는 내가 보냈을때는 수신자를 묶어서 보여주고 싶어서요.. 필요한정보는 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
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 ;
본래 의미상 PK 는 sender, receiver, send_date 의 조합입니다.
인조식별자로 msg_seq 를 만들어 사용하는 거구요.
인조식별자는 마땅히 식별자로 지정할만한 후보키가 없다거나
휴보키가 있긴 한데 너무 많은 항목의 조합이라서 사용이 불편할 때 사용합니다.
인조식별자의 기능은 식별만 하면 됩니다. 의미를 부여할 필요가 없죠.
따라서 인조식별자에 실제 의미있는 데이터의 조합을 사용하기 보다는 단순 순번 주로 사용합니다.
여러 후보키 중 하나를 PK 로 지정하는 것이지.
후보키 두개를 또 다시 조합하여 PK 를 만들 이유가 전혀 없습니다.
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
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
이렇게 같은 그룹으로 여러개 보냈을때 저 붉게 되어있는 걸 묶어서 가져오고 싶은데 ㅠㅠ 제가 한건만 보이게 하고싶은것때문에 저부분도 제일 위에 값만 하나 가져오고있습니다.
방법이없을까요 ㅠ 임의로 아무나 선택해서 보내는것이기 때문에 저 코드를 동일시 하는건 힘들것같고요..
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를 중복제거하여 가져와서 쓰고 싶습니다.
더욱이 인조키를 가지고
MSG_SEQ LIKE '%_' || '1427793428005' || '_%' or MSG_SEQ LIKE '%' || '1427793428005' || '_%'
이런식으로 가공하셨는데 키를 가공하시면 INDEX 활용도 불가할 뿐더러
쿼리를 만들 때도 너무너무너무 불편합니다.
이럴 바에 그냥 sender, receiver, send_date 컬럼을 PK로 만드세요.
같은 의미의 데이터를 중복해서 컬럼으로 두는 것도 공간 낭비죠.
하루에 한번 메시지 보내는 게 아니니까 뭔가 컬럼이 더 추가되어야할 것 같긴하지만..
머릿속이 복잡하니 정리가 부족하네요 죄송합니다ㅠㅠ
기존쿼리로 수신자가 '김태희,조인성,황정민,김수현,이순재' 이렇게 묶여서 잘 나옵니다. 그런데
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
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는 건 별 값을 다 가져오고 싶습니다.
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 ;