Q. 다음의 데이터에서 2006년 월별 마지막 날짜의 마지막 순번 데이터를 조회하라.
[데이터]
[예상 결과]
[연산자 IN을 이용하는 방법]
[분석용 함수 ROW_NUMBER()를 이용하는 경우]
[분석용 함수 ROW_NUMBER()를 이용하는 경우2]
; 조회 퀄럼의 개수가 많다면 다음과 같이 사용한다.
[데이터]
WITH T AS
(
SELECT 1 NO, 2006 YYYY, 1 MM, 1 DD, 1 SEQ FROM DUAL
UNION ALL SELECT 2 , 2006 , 1 , 1 , 2 FROM DUAL
UNION ALL SELECT 3 , 2006 , 1 , 2 , 1 FROM DUAL
UNION ALL SELECT 4 , 2006 , 1 , 2 , 2 FROM DUAL
UNION ALL SELECT 5 , 2006 , 1 , 2 , 3 FROM DUAL
UNION ALL SELECT 6 , 2006 , 2 , 1 , 1 FROM DUAL
UNION ALL SELECT 7 , 2006 , 2 , 1 , 2 FROM DUAL
UNION ALL SELECT 8 , 2006 , 2 , 1 , 3 FROM DUAL
UNION ALL SELECT 9 , 2006 , 2 , 2 , 1 FROM DUAL
UNION ALL SELECT 10 , 2006 , 2 , 2 , 2 FROM DUAL
UNION ALL SELECT 11 , 2006 , 3 , 1 , 1 FROM DUAL
UNION ALL SELECT 12 , 2006 , 3 , 2 , 1 FROM DUAL
UNION ALL SELECT 13 , 2006 , 3 , 2 , 2 FROM DUAL
UNION ALL SELECT 14 , 2006 , 3 , 2 , 3 FROM DUAL
UNION ALL SELECT 15 , 2006 , 3 , 3 , 1 FROM DUAL
)
(
SELECT 1 NO, 2006 YYYY, 1 MM, 1 DD, 1 SEQ FROM DUAL
UNION ALL SELECT 2 , 2006 , 1 , 1 , 2 FROM DUAL
UNION ALL SELECT 3 , 2006 , 1 , 2 , 1 FROM DUAL
UNION ALL SELECT 4 , 2006 , 1 , 2 , 2 FROM DUAL
UNION ALL SELECT 5 , 2006 , 1 , 2 , 3 FROM DUAL
UNION ALL SELECT 6 , 2006 , 2 , 1 , 1 FROM DUAL
UNION ALL SELECT 7 , 2006 , 2 , 1 , 2 FROM DUAL
UNION ALL SELECT 8 , 2006 , 2 , 1 , 3 FROM DUAL
UNION ALL SELECT 9 , 2006 , 2 , 2 , 1 FROM DUAL
UNION ALL SELECT 10 , 2006 , 2 , 2 , 2 FROM DUAL
UNION ALL SELECT 11 , 2006 , 3 , 1 , 1 FROM DUAL
UNION ALL SELECT 12 , 2006 , 3 , 2 , 1 FROM DUAL
UNION ALL SELECT 13 , 2006 , 3 , 2 , 2 FROM DUAL
UNION ALL SELECT 14 , 2006 , 3 , 2 , 3 FROM DUAL
UNION ALL SELECT 15 , 2006 , 3 , 3 , 1 FROM DUAL
)
[예상 결과]
NO YYYY MM DD SEQ
---------- ---------- ---------- ---------- ----------
5 2006 1 2 3
10 2006 2 2 2
15 2006 3 3 1
3 rows selected
---------- ---------- ---------- ---------- ----------
5 2006 1 2 3
10 2006 2 2 2
15 2006 3 3 1
3 rows selected
[연산자 IN을 이용하는 방법]
SELECT NO, YYYY, MM, DD, SEQ
FROM T
WHERE YYYY = 2006
AND (MM, DD, SEQ)
IN (SELECT MM, DD, MAX(SEQ)
FROM T
WHERE YYYY = 2006
AND (MM, DD)
IN (SELECT MM, MAX(DD)
FROM T
WHERE YYYY = 2006
GROUP BY MM)
GROUP BY MM, DD)
ORDER BY NO
FROM T
WHERE YYYY = 2006
AND (MM, DD, SEQ)
IN (SELECT MM, DD, MAX(SEQ)
FROM T
WHERE YYYY = 2006
AND (MM, DD)
IN (SELECT MM, MAX(DD)
FROM T
WHERE YYYY = 2006
GROUP BY MM)
GROUP BY MM, DD)
ORDER BY NO
[분석용 함수 ROW_NUMBER()를 이용하는 경우]
SELECT NO, YYYY, MM, DD, SEQ
FROM (SELECT NO, YYYY, MM, DD, SEQ,
ROW_NUMBER() OVER (PARTITION BY YYYY, MM ORDER BY DD DESC, SEQ DESC) RNUM
FROM T)
WHERE RNUM = 1
FROM (SELECT NO, YYYY, MM, DD, SEQ,
ROW_NUMBER() OVER (PARTITION BY YYYY, MM ORDER BY DD DESC, SEQ DESC) RNUM
FROM T)
WHERE RNUM = 1
[분석용 함수 ROW_NUMBER()를 이용하는 경우2]
; 조회 퀄럼의 개수가 많다면 다음과 같이 사용한다.
T2 AS
(
SELECT NO, YYYY, MM, DD, SEQ,
ROW_NUMBER() OVER (PARTITION BY YYYY, MM ORDER BY DD DESC, SEQ DESC) RNUM
FROM T
WHERE YYYY = 2006
)
SELECT T1.NO, T1.YYYY, T1.MM, T1.DD, T1.SEQ
FROM T T1, T2
WHERE T2.RNUM = 1
AND T1.YYYY = 2006
AND T1.MM = T2.MM
AND T1.DD = T2.DD
AND T1.SEQ = T2.SEQ
(
SELECT NO, YYYY, MM, DD, SEQ,
ROW_NUMBER() OVER (PARTITION BY YYYY, MM ORDER BY DD DESC, SEQ DESC) RNUM
FROM T
WHERE YYYY = 2006
)
SELECT T1.NO, T1.YYYY, T1.MM, T1.DD, T1.SEQ
FROM T T1, T2
WHERE T2.RNUM = 1
AND T1.YYYY = 2006
AND T1.MM = T2.MM
AND T1.DD = T2.DD
AND T1.SEQ = T2.SEQ
댓글을 달아 주세요