데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 분석함수의 이해
구분1, 구분2, 날짜, 값으로 구성된 테이블이 있습니다.
구분1, 구분2 그룹으로 최종날짜와 금액합계를 구하고,
구분1별 최종날짜와 그에 해당하는 구분2값과 금액합계를 구하시오.
WITH t AS
(
SELECT 'A' gb1, '01' gb2, '20100601' dt, 10 v FROM dual
UNION ALL SELECT 'A', '01', '20100604', 20 FROM dual
UNION ALL SELECT 'A', '02', '20100603', 30 FROM dual
UNION ALL SELECT 'A', '03', '20100602', 40 FROM dual
UNION ALL SELECT 'B', '01', '20100601', 50 FROM dual
UNION ALL SELECT 'B', '02', '20100605', 60 FROM dual
UNION ALL SELECT 'B', '03', '20100603', 70 FROM dual
UNION ALL SELECT 'B', '04', '20100602', 80 FROM dual
)
SELECT * FROM t
;
[원본]
[결과]
GB1
GB2
DT
V
GB1
GB2
DT
V
MAX_DT
MAX_DT_GB2
MAX_DT_V
A
01
20100601
10
A
01
20100604
30
20100604
01
30
A
01
20100604
20
A
02
20100603
30
20100604
01
30
A
02
20100603
30
A
03
20100602
40
20100604
01
30
A
03
20100602
40
B
01
20100601
50
20100605
02
60
B
01
20100601
50
B
02
20100605
60
20100605
02
60
B
02
20100605
60
B
03
20100603
70
20100605
02
60
B
03
20100603
70
B
04
20100602
80
20100605
02
60
B
04
20100602
80
정답은 몇일 뒤 다시 올리겠습니다.
[정답] <=== 트리플클릭
SELECT gb1
, gb2
, MAX(dt) dt
, SUM(v) v
, MAX(MAX(dt)) OVER(PARTITION BY gb1) max_dt
, MAX(gb2) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_gb2
, MAX(SUM(v)) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_v
FROM t
GROUP BY gb1, gb2
ORDER BY gb1, gb2
;
(
SELECT 'A' gb1, '01' gb2, '20100601' dt, 10 v FROM dual
UNION ALL SELECT 'A', '01', '20100604', 20 FROM dual
UNION ALL SELECT 'A', '02', '20100603', 30 FROM dual
UNION ALL SELECT 'A', '03', '20100602', 40 FROM dual
UNION ALL SELECT 'B', '01', '20100601', 50 FROM dual
UNION ALL SELECT 'B', '02', '20100605', 60 FROM dual
UNION ALL SELECT 'B', '03', '20100603', 70 FROM dual
UNION ALL SELECT 'B', '04', '20100602', 80 FROM dual
)
SELECT gb1, gb2, dt, v
, max(dt) over (partition by gb1) max_dt
, substr(max(dt||gb2) over (partition by gb1), 9) max_dt_gb2
, substr(max(dt||to_char(v)) over (partition by gb1), 9) max_dt_v
FROM
(
SELECT gb1, gb2, max(dt) dt, sum(v) v
FROM t
group by gb1, gb2
)
order by gb1, gb2
;
(
SELECT 'A' GB1, '01' GB2, '20100601' DT, 10 V FROM DUAL
UNION ALL SELECT 'A', '01', '20100604', 20 FROM DUAL
UNION ALL SELECT 'A', '02', '20100603', 30 FROM DUAL
UNION ALL SELECT 'A', '03', '20100602', 40 FROM DUAL
UNION ALL SELECT 'B', '01', '20100601', 50 FROM DUAL
UNION ALL SELECT 'B', '02', '20100605', 60 FROM DUAL
UNION ALL SELECT 'B', '03', '20100603', 70 FROM DUAL
UNION ALL SELECT 'B', '04', '20100602', 80 FROM DUAL
)
SELECT GB1
,GB2
,MAX(DT) DT
,SUM(V) V
,MAX(MAX(DT)) OVER(PARTITION BY GB1) AS MAX_DT
,FIRST_VALUE(GB2) OVER(PARTITION BY GB1 ORDER BY MAX(DT) DESC) AS MAX_DT_GB2
,FIRST_VALUE(SUM(V)) OVER(PARTITION BY GB1 ORDER BY MAX(DT) DESC) AS MAX_DT_V
FROM T
GROUP BY GB1,GB2
ORDER BY GB1,GB2
first_value 로도 풀리는군요.
다들 잘 풀어주셨네요.
아...하나 또 배우고 갑니다~~
(
SELECT 'A' gb1, '01' gb2, '20100601' dt, 10 v FROM dual
UNION ALL SELECT 'A', '01', '20100604', 20 FROM dual
UNION ALL SELECT 'A', '02', '20100603', 30 FROM dual
UNION ALL SELECT 'A', '03', '20100602', 40 FROM dual
UNION ALL SELECT 'B', '01', '20100601', 50 FROM dual
UNION ALL SELECT 'B', '02', '20100605', 60 FROM dual
UNION ALL SELECT 'B', '03', '20100603', 70 FROM dual
UNION ALL SELECT 'B', '04', '20100602', 80 FROM dual
)
SELECT
GB1 ,GB2 ,DT
,FIRST_VALUE(DT) OVER(PARTITION BY GB1 ORDER BY ROWNUM) MAX_DT
,FIRST_VALUE(GB2) OVER(PARTITION BY GB1 ORDER BY ROWNUM) MAX_DT_GB2
,FIRST_VALUE(V) OVER(PARTITION BY GB1 ORDER BY ROWNUM) MAX_DT_V
FROM(
SELECT
max(gb1) gb1 ,max(gb2) gb2 ,max(dt) dt ,sum(v) v
from t
group by gb1,gb2
ORDER BY GB1,DT DES
)
ORDER BY GB1, GB2
늘지않는 이 실력 어떻해요... 마농님... ㅜㅜ
눈이 즐거워지네요... ㅎㅎㅎ
사용해서 쿼리 만들었는데... ㅎ 그게 정답으로 나온거네여....
나만.. 몰랐던거구나...
) 버튼을 클릭하여 작성 하시면 됩니다.