by 동동동 [SQL Query] Oracle 그룹별최근값 [2021.08.26 10:58:45]
WITH TMP AS ( SELECT '501' CD, '20210203' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '501' CD, '20210202' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '20210101' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '20210501' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '20210502' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '701' CD, '20210701' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '701' CD, '20210201' DY, 'F' AS SEX FROM DUAL ) SELECT CD --, MIN(SEX) KEEP(DENSE_RANK FIRST ORDER BY CD, DY) OVER(PARTITION BY CD) AS FIRST_SEX --, FIRST_VALUE(SEX) OVER(PARTITION BY CD ORDER BY CD, DY) AS FIRST_SEX , LISTAGG(DY ||'^'|| SEX, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY CD ) AS INFO FROM TMP GROUP BY CD ORDER BY CD ;
안녕하세요..다음과 같은 데이터가 있을시
CD | FIRST_SEX | INFO |
501 | F | 20210202^F 20210203^M |
601 | M | 20210101^M 20210501^F 20210502^F |
701 | F | 20210201^F 20210701^M |
이렇게 데이터를 뽑고 싶은데, Group By에 SEX가 걸리네요...
MIN(SEX) KEEP 와 FIRST_VALUE(SEX) 문은 일단 주석처리 했습니다...LASTAGG도 사용 하면서 CD별 처음 발생한 SEX를 가져올 수 있는 방법이 있을까요?
도움부탁 드립니다.
집계함수를 사용할 곳에 분석함수를 사용했네요. -> OVER 를 빼면 됩니다.
listagg 및 keep 의 정렬 기준은 cd 가 아닌 듯 하네요. dy 로 바꿔야죠.
WITH tmp AS ( SELECT '501' cd, '20210203' dy, 'M' sex FROM dual UNION ALL SELECT '501', '20210202', 'F' FROM dual UNION ALL SELECT '601', '20210101', 'M' FROM dual UNION ALL SELECT '601', '20210501', 'F' FROM dual UNION ALL SELECT '601', '20210502', 'F' FROM dual UNION ALL SELECT '701', '20210701', 'M' FROM dual UNION ALL SELECT '701', '20210201', 'F' FROM dual ) SELECT cd , MIN(sex) KEEP(DENSE_RANK FIRST ORDER BY dy) first_sex , LISTAGG(dy ||'^'|| sex, CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY dy) info FROM tmp GROUP BY cd ORDER BY cd ;
마농님 답변 감사드립니다..추가 질문 드려도 될까요?
혹시 그룹이 하나가 더 생겼을 경우에는 어떻게 해야 하는지요?
WITH TMP AS ( SELECT '501' CD, '01' SUB, '20210203' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '501' CD, '02' SUB, '20210202' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '501' CD, '03' SUB, '' DY, '' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '01' SUB, '20210101' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '01' SUB, '20210501' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '601' CD, '02' SUB, '20210802' DY, 'F' AS SEX FROM DUAL UNION ALL SELECT '701' CD, '01' SUB, '20210701' DY, 'M' AS SEX FROM DUAL UNION ALL SELECT '701' CD, '01' SUB, '20210201' DY, 'F' AS SEX FROM DUAL ) SELECT CD , SUB -- , DY -- , SEX -- , MIN(SEX) KEEP(DENSE_RANK FIRST ORDER BY CD, DY) OVER(PARTITION BY CD) AS FIRST_SEX -- , FIRST_VALUE(SEX) OVER(PARTITION BY CD ORDER BY CD, DY) AS FIRST_SEX , LISTAGG(DY ||'^'|| SEX ||'^'||SUB, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY CD ) AS INFO FROM TMP GROUP BY CD, SUB ORDER BY CD, SUB ;
SUB가 추가 되었을 경우 FIRST_SEX는 CD와 DY만 비교 되어야 하고, NULL인 경우에도 표시를 하려고 하는데요..
현재 결과
CD | SUB | FIRST_SEX | INFO |
501 | 01 | M | 20210203^M |
501 | 02 | F | 20210202^F |
501 | 03 | ^ | |
601 | 01 | M | 20210101^M 20210501^F |
601 | 02 | F | 20210802^F |
701 | 01 | F | 20210201^F 20210701^M |
원하는 결과
CD | SUB | FIRST_SEX | INFO |
501 | 01 | F | 20210203^M |
501 | 02 | F | 20210202^F |
501 | 03 | F | ^ |
601 | 01 | M | 20210101^M 20210501^F |
601 | 02 | M | 20210802^F |
701 | 01 | F | 20210201^F 20210701^M |
도움 감사드립니다..
WITH tmp AS ( SELECT '501' cd, '01' sub, '20210203' dy, 'M' sex FROM dual UNION ALL SELECT '501', '02', '20210202', 'F' FROM dual UNION ALL SELECT '501', '03', '' , '' FROM dual UNION ALL SELECT '601', '01', '20210101', 'M' FROM dual UNION ALL SELECT '601', '01', '20210501', 'F' FROM dual UNION ALL SELECT '601', '02', '20210802', 'F' FROM dual UNION ALL SELECT '701', '01', '20210701', 'M' FROM dual UNION ALL SELECT '701', '01', '20210201', 'F' FROM dual ) SELECT cd , sub , FIRST_VALUE(MIN(sex) KEEP(DENSE_RANK FIRST ORDER BY dy)) OVER(PARTITION BY cd ORDER BY MIN(dy)) first_sex , LISTAGG(dy ||'^'|| sex, CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY dy) info FROM tmp GROUP BY cd, sub ORDER BY cd, sub ;
SELECT cd , sub , SUBSTR(MIN(MIN(dy || sex)) OVER(PARTITION BY cd), 9, 1) first_sex , LISTAGG(dy ||'^'|| sex, CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY dy) info FROM tmp GROUP BY cd, sub ORDER BY cd, sub ;