데이터베이스 질문과 답변
LISTAGG 와 KEEP문 조합 그룹별 최근 값 조회 문의 드립니다
1
4
2,689
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를 가져올 수 있는 방법이 있을까요?
도움부탁 드립니다.
by 마농
[2021.08.26 11:14:01]
집계함수를 사용할 곳에 분석함수를 사용했네요. -> 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
;
by 동동동
[2021.08.26 12:19:38]
마농님 답변 감사드립니다..추가 질문 드려도 될까요?
혹시 그룹이 하나가 더 생겼을 경우에는 어떻게 해야 하는지요?
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 |
도움 감사드립니다..
by 마농
[2021.08.26 13:00:33]
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
;
by 동동동
[2021.08.26 16:51:59]
와우.. 마농님 정말 감사드립니다...
정말 공부가 많이 되네요..감사합니다..
) 버튼을 클릭하여 작성 하시면 됩니다.